SAS Stored Processes: Querying a Stored Process from Excel without the Add-In

This post was kindly contributed by Business Intelligence Notes for SAS® BI Users - go there to comment and to read the full post.

What do I have in common with a guy in Norway? Well, if you look at my last name Aanderud – you might think he is a family member. Turns out Stig Eide and I both use SAS Stored Processes to solve issues! Recently this SAS platform administrator asked if he could demonstrate a trick for using a Excel to query data in a stored process without using the Add-In. Of course I had to see this trick and share it with you! Take it away Stig.

Using a Stored Process as a Web Query

If you would like to query a SAS table from within Excel, without requiring the users to install the Add-In for Microsoft Office; this post describes an alternative method. Excel has a nice feature where you are allowed to query a web page for data. I will describe how you can set up the query against a stored process, and how to pass parameters.

First, you need to create a stored process that can return a HTML table. I do not want to describe the process of registering a stored process here, but it can be done in several methods [See Angela’s example in this You Tube video: Create a Stored Process in One Little Minute.]

This is the stored process program that I created. It’s just a simple query of the Class dataset from the SAS HELP sample data:

There are some elements in the code that might need explanation:

	CHTML means compact HTML, which ensures no extra formatting is sent over from the stored process to Excel. You can use HTML in combination with _ODSSTYLE if you want the table to be formatted in Excel.
	This is a SAS macro that tells the Stored Process Server to start streaming the output.
	The code that produces the result. Notice that I want to set age dynamically by using a macro variable. I will explain later how to set the value of this in Excel.
	This is a SAS macro that tells the Stored Process Server to stop streaming the output.

Locate an Alternate URL for the Stored Process

Now that the stored process is created, we need to find the URL that we need to use in Excel. Open up your Stored Process Web Application, by using the following URL:

http://<servername>:<port>/SASStoredProcess/do1?

Notice that I am using “do1” instead of the usual “do“. This is a trick to get the Web application to prompt for username and password the “old” way, instead of using the Logon application:

From the SAS Stored Process Web Application window, click the List of Available Stored Processes and Reports link and browse to the stored process you created. Do not worry that it fails. This is because the AGE variable is not set yet. Right-click in the frame that displays the error and select Properties to display the address of the report. Remove the _action parameter from the URL, and you should have something like this:

http://<servername>:<port>/SASStoredProcess/do1?_program=%2FDVH_Skade%2F8_BI%2FReportStudio%2FShared%2FReports%2FDVHSkade%2FSalg%2FSTP%2FExcel+Get+Data

Set Up the Web Query in Excel

Open Excel and click the Data ribbon, then select Get External Data > From Web:

Paste the URL of the stored process into the Address field, but add the missing parameter to the end, with the brackets replacing the value as shown on the picture. You might get prompted again for username and password. Do not worry; it will not be a part of the query.

You will again get an error, because the parameter is not set yet. Click the small yellow arrow at the top of the window (the arrow turns into a green check mark once selected), ignore the error for now and click Import. (The language is Norwegian, by the way.)

Select whether you want the result to be displayed in a new worksheet or inside an existing worksheet:

This time, Excel prompts you for the value of the Age variable. Enter a valid value and click OK:

Excel executes the web query and returns the results. Now you have a dynamic Excel query!

Running the Stored Process as a Dynamic Query

You might want the query to execute every time the Excel workbook opens. In that case, go to Data ribbon and select Properties. The External Data Range Properties window appears. In the Refresh Control area, select Refresh data when opening the file check box. Click Ok and save the spreadsheet.

What about Security?

If you send this spreadsheet file to the users, they will get prompted for the dynamic variable first, then they are prompted for their username and password. Security is thus taken care of; the user will not be able to execute the stored process unless authorized in the metadata.

Any Other Tips?

You might have issues with dates and numbers not having the format you would like. Or worse, that Excel interprets them as different values. If that is the case, you need to make sure that the formats that are used in the stored process are recognized by Excel. For example, the default decimal separator in SAS is a dot “.”. In some countries, it is a comma “,”. If that is the case, Excel will not understand that “21.2″ is a number, and you need to use a proper format in SAS (numx in this example).
When using a character value that contains a space in the prompt, ensure the user enters an underscore for the space. For example, if the stored process prompts for a country name, then the user would type United_States instead of United States in the field.
This method is more successful with simple prompts. The SAS Prompt Framework is more advanced than the Excel web query prompts. Some of the advanced methods (range prompts, error checking) may not work as excepted using this method.

Author Information

Stig Eide works for KLP insurance as a platform administrator. He has many years of experience with SAS EBI and DI from an administrators perspective. He holds the highest possible “Certified Platform Administrator for SAS 9″, Level 4. In addition, he considers himself an expert in BASE coding