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

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

1 ACCEPTED SOLUTION

Accepted Solutions
santosh_pat69
Quartz | Level 8
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

3 REPLIES 3
Kurt_Bremser
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.

Vince_SAS
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

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

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

Thanks
Santtosh



sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 1654 views
  • 2 likes
  • 3 in conversation