10-20-2011 03:02 AM
Dear SAS Experts,
I have a client's need to access a SAS Stored Process from Ms Excel (in this case a front-end application) through SAS Stored Process WEB Application (SSPWA). I know it is possible to do the same using IOM, but this violates client's security policy - all applications must use only HTTP standard ports to access services.
What I need is to access SSP through the URL using VBA in the Excel. The SSP should return back an XML formatted data (I know how to do this from AJAX to SSP). Then VBA parses the XML data and presents it.
Is it possible at all ?
05-08-2013 11:37 AM
You could try using the SAS BI Web Services REST API to get Xml Responses when executing SAS stored processes. You can retrieve the results of the package output by a stored process, I believe. The web services are not as robust as IOM, though. I couldn't figure out a good way to access the SAS log, for instance, when an error such as a locked dataset prevented the stored process from succeeding. I was only able to see that the process failed. SOAP is an option as well, but overcomplicated in my opinion.
05-20-2013 08:15 AM
The same general technique that you use for AJAX should work for VBA. Here's a link to an old paper that discusses calling the SAS/IntrNet Application Dispatcher as a service:
Techniques for SAS® Enabling Microsoft® Office in a Cross-Platform Environment
Sample VBA code to call the service is in the Appendix. Change the URL to call the SAS Stored Process Web Application as a service (ignore line wrapping):
Workbooks.Open("http://serverort/SASStoredProcess/do?_program=/Products/SAS Intelligence Platform/Samples/Sample: Hello World&_username=uname&_password=pw")
You can hardcode the username/password, prompt for them in VBA, use Basic Authentication, or enable Anonymous Access:
Authentication in the Stored Process Web Application