Your SAS programs, embedded in web apps and elsewhere

Is it possible to access SSP from Ms Excel using VBA through SAS Stored Process Web App

Reply
Contributor
Posts: 33

Is it possible to access SSP from Ms Excel using VBA through SAS Stored Process Web App

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

Bpa
N/A
Posts: 1

Re: Is it possible to access SSP from Ms Excel using VBA through SAS Stored Process Web App

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

SAS Employee
Posts: 284

Re: Is it possible to access SSP from Ms Excel using VBA through SAS Stored Process Web App

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://serverSmiley Tongueort/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

Ask a Question
Discussion stats
  • 2 replies
  • 673 views
  • 0 likes
  • 3 in conversation