Stored Process for MS Excel

[santosh_pat69]

Hi,

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.

Regards,

S....

[Kurt_Bremser]

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.

[Vince_SAS]

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

http://www2.sas.com/proceedings/sugi27/p174-27.pdf

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

http://support.sas.com/kb/43/149.html

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:

WEB

1

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):

http://www.sas.com/en_us/software/business-intelligence/office-analytics.html

Excel Web Query References:

Get external data from a Web page

https://support.office.com/en-us/article/Get-external-data-from-a-Web-page-708f2249-9569-4ff9-a8a4-7...

or https://goo.gl/QIkSma

Create a Web query parameter query

https://support.office.com/en-us/article/Create-a-Web-query-parameter-query-0af114fe-d9a2-4f05-b92c-...

or https://goo.gl/Ealcpb

Vince DelGobbo

SAS R&D

[santosh_pat69 · Accepted solution]

Hi,
Thank you for the solution, this seems to be interesting and I hope this works for me.

Thanks
Santtosh