BookmarkSubscribeRSS Feed
sarunas
Calcite | Level 5

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

2 REPLIES 2
Bpa
Calcite | Level 5 Bpa
Calcite | Level 5

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.

SAS(R) 9.3 Integration Technologies: Overview

SAS(R) 9.3 BI Web Services: Developer's Guide

Vince_SAS
Rhodochrosite | Level 12

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

http://tinyurl.com/mxvafwz

Vince DelGobbo

SAS R&D

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1606 views
  • 0 likes
  • 3 in conversation