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 ?
Thanks
Sarunas
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.
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
http://www2.sas.com/proceedings/sugi27/p174-27.pdf
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://server:port/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
Vince DelGobbo
SAS R&D
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.