Your SAS programs, embedded in web apps and elsewhere

Stored Process for MS Excel

Accepted Solution Solved
Reply
Contributor
Posts: 66
Accepted Solution

Stored Process for MS Excel

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


Accepted Solutions
Solution
‎10-20-2016 10:17 AM
Contributor
Posts: 66

Re: Stored Process for MS Excel

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

Thanks
Santtosh



View solution in original post


All Replies
Super User
Posts: 6,936

Re: Stored Process for MS Excel

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
SAS Employee
Posts: 285

Re: Stored Process for MS 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

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.

 

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:

 

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

Solution
‎10-20-2016 10:17 AM
Contributor
Posts: 66

Re: Stored Process for MS Excel

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

Thanks
Santtosh



☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 391 views
  • 2 likes
  • 3 in conversation