Pass SAS macro variables between another program (VB or Java)

2
votes

I have a series of SAS programs that take user options as Macro variables and perform some analysis which is exported to Excel. I am building more user-friendly interfaces for these in Excel VBA and Java (for 2 separate software packages, with the Java package also using Java to do some different things).

I have found a variety of ways to run SAS through VB or Java, but I can't find a way to get SAS to then return feedback to the calling programs. Specifically, I want to do something like:

-A calling program runs SAS

-SAS does some calculations and returns some Macro variables as strings (or it could be a single string of concatenated macro variables)

-The calling program reads this string from SAS

-The calling program displays some results based on it and does some other stuff

I've looked into running SAS with Shell commands, using OLE automation, using the SAS IOM bridge with Java, and using DDE (although this doesn't work AFAIK because SAS runs as a batch job when called from another program), and have not found anything yet.

I'm open to any and all methods for doing this that don't involve creating some other file for the calling program to then read. I want to keep all the interaction between SAS and the calling program only.

For reference, I am using Base SAS 9.2 or 9.3 (have both), MS Office 2007 Excel VBA, and JRE 1.7.

Thanks,

BP

UPDATE:

For the Excel program, I went with using the clipboard and it seems to be working well. I used these sources:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a002571877.htm

http://excelhelphq.com/how-to-read-or-access-the-clipboard-with-excel-vba/

For the Java program, I am going to learn more about using sockets. Thanks again!

1
javavbasas
Can you use the clipboard perhaps? SAS is capable of writing directly to the clipboard as if it were a file. – Joe
@Joe Good thought but I'd say it's pretty risky... – Robert Penridge
Yeah, it's not ideal... but you can write the process ID as part of it, to make it relatively safe. – Joe
Also - @OP - what about using JavaObj (ie, running a java instance inside SAS)? SAS definitely will work with that neatly; not sure it would communicate with the calling Java instance, but you could possibly run all of your post-SAS processes there. – Joe
That is also something that I have looked at Joe, and I couldn't quite get it to work but it is definitely something to consider. The clipboard idea could also work for what I am doing in Excel, as its pretty basic stuff: we have a set form template that needs to be filled out in Excel, and I have SAS go and extract all the fields from some underlying SQL tables and give them back to populate the form. Could you point me to somewhere to learn more about it? – BPRogramming

1 Answers

2
votes

There are two things I can think of depending on your infrastructure, unfortunately neither of them are straightforward.

  • Setup your calling program to listen on a socket and have SAS write the results directly back to that socket.
  • Create a RESTful interface API, and have SAS call back to it once processing is complete.

There's plenty of whitepapers around that discuss doing the first. The implementation of it would probably be too long for a SO answer so I'm going to just link some whitepapers that should point you in the right direction. Here's a few examples to get you started:

http://analytics.ncsu.edu/sesug/2000/p-1003.pdf

http://www2.sas.com/proceedings/sugi24/Coders/p083-24.pdf

http://www2.sas.com/proceedings/sugi22/INTERNET/PAPER184.PDF

The second approach is a lot more work but probably a better solution overall. It may be overkill for your needs though.

EDIT : If you happened to be running from UNIX (which you aren't unfortunately) you may have been able to use the STDIO option.

I reread your question and you did mention that it's to eventually end up in excel. I just wanted to also mention that SAS can export data to excel in many different ways, some of which are direct (such as DDE).