BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Quartz | Level 8



I am trying to write a Generic query to exeute a SAS code using Excel, we dont have SAS Addins.

I have written a Sample stp but trying to figure out to run this using MS-Excel.


Please help me on this.





Accepted Solutions
Quartz | Level 8
Thank you for the solution, this seems to be interesting and I hope this works for me.


View solution in original post

Super User

To run a SAS stored process from Excel, you either need the AddIn for MS Office, or you have to do some low-level IOM programming yourself.

Seeing that the AddIn needs no additional license, I see no reason not to use it.


If your IT people don't want to install the AddIn on clients, you can make the STP stream-output a table to the web and open the STP URL from Excel.

Rhodochrosite | Level 12

I think the techniques discussed on page 4 of this (very old) paper still largely apply:


Techniques for SAS® Enabling Microsoft® Office in a Cross-Platform Environment


The SAS Logon Manager presents a challenge when running a stored process because you must authenticate prior to code execution.  You can get around that issue if you enable guest/anonymous access:


Usage Note 43149: Configuring anonymous access for the SAS® Stored Process Web Application


Then use "guest" instead of "do":


http://your-server:7980/SASStoredProcess/guest?_program=/Products/SAS Intelligence Platform/Samples/Sample: Hello World


Verify that the URL works when specified in the address field of a Web browser.


Then you can specify the URL in the Excel Open dialog box technique.


To use the Excel Web Query technique, click Data in the Excel ribbon, and then click From Web in the Get External Data group.


Click Options in the New Web Query dialog, select Full HTML formatting, and then click OK.


Excel Web Query Options


Specify the URL in the Address field and then click Go.  The results of the stored process are displayed in the lower pane, and you can then select all of the output or output tables of interest.


Excel New Web Query Dialog


When using the Hello World sample, click the arrow to select the entire table, and then click Import.  Select either Existing worksheet or New worksheet and then specify the cell location for the upper, left corner of the output (XML table in existing worksheet is selected by default).


Excel Import Data Options


You can save the query when you're satisfied with the results.  Right-click in the cell specified in the Import Data dialog box above, and then select Edit Query from the pop-up menu.  Click the Save Query button to the left of the Options button (see earlier New Web Query dialog screen shot) and then specify a name for the IQY file, noting the location of the file.  You can distribute this file to your end-users and they can install it on their machines. 


To execute the IQY file from within Excel, click Data in the Excel ribbon, and then click Existing Connections in the Get External Data group.


You can also edit the IQY file to add parameters and prompts, for example:




http://your-server:7980/SASStoredProcess/guest?_program=/Products/SAS Intelligence Platform/Samples/Sample: Hello World&_debug=["_debug", "Specify a Debug value:"]


A quick search of the Web resulted in a number of hits for using VBA to open a URL from within Excel.


All of these techniques are a little clunky, and the SAS Add-in for Microsoft Office provides a much more integrated approach.  It's available as part of SAS Office Analytics (in addition to SAS Business Intelligence Server and SAS Enterprise Business Intelligence Server):


Excel Web Query References:


Get external data from a Web page



Create a Web query parameter query



Vince DelGobbo


Quartz | Level 8
Thank you for the solution, this seems to be interesting and I hope this works for me.



Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.


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
  • 3 replies
  • 3 in conversation