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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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