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.
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.
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.
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.
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).
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
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.