BI Tools

Tips and tricks for building information maps, OLAP cubes, reports, and dashboards

BI Admin

Learn your way around a SAS BI installation.

Visual Analytics

Learn your way around the SAS Visual Analytics tool

Coding & Data

Extract, transform, and load your data into the SAS BI toolset

Stored Processes

Create and design stored processes like a rock star

Home » BI Tools, Coding & Data, Stored Processes

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

Submitted by on 2013-02-20 – 7:39 AM 12 Comments

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:

stored process ms excel

 

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

 stp_excel_02

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.

 stp_excel_03

This is a SAS macro that tells the Stored Process Server to start streaming the output.

 stp_excel_04

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.

 stp_excel_05

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

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

 stp_excel_07

 

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

stp_excel_08

 

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

stp_excel_09

 

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

stp_excel_10

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

stp_excel_14

 

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.

 
stp_excel_12

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

Learn More about SAS Stored Processes and Prompts

You can learn more tips and tricks for creating, debugging, and using SAS stored processes in the 50 Keys to Learning SAS Stored Processes book. It's a complete guide to SAS stored processes. Check Amazon for best pricing and quick shipping!

Tags: ,

12 Comments »

  • Stig Eide says:

    Amit, I am glad you can get it to work!
    Yes, you can present the user for a static drop down list.

    You need to create a data validation cell, using the list component.
    Then, when you get the prompt, you need to use this cell as the value, and click “Use this Value/reference for future refreshes”, and click “Refresh Automatically when cell value changes”.

  • AMIT says:

    Tricia,

    Thanks that is working perfectly fine and all my peers are surprised. But i want to give the user all option available .i.e. static drop down list. is this possible?

    Thanks;
    Amit

  • Stig Eide says:

    You need the square brackets as well 😉

  • Stig Eide says:

    Amit, if you add &VARIABLE=[“VARIABLE”] to the string, Excel should prompt you for this variable and send it to the stored process server as &VARIABLE=12 (if the user enters 12).
    You need the quotes, even though it is a number.
    Hope this helps!

  • AMIT says:

    Hurray i got it. But i want the user to select from a static list, which is not populating in th excel query.

    Any idea, how to go about it.

  • Tricia says:

    My last comment had the prompt deleted. Yes – just append the prompt to the end of the string as shown in the example above. Also note that note all prompts are fully supported.

  • AMIT says:

    Hi Tricia,

    Now its working, i mean i can see the report but when am including a prompt on top of it, its not asking the user for the input.
    Do i need to add &data or something.

    Plz help!

  • Tricia says:

    Hey Amit:

    Make sure your base link looks like this:
    http://:/SASStoredProcess/do1?_program=%2FShared+Data%2FStored+Processes%2FexcelSTP+TestPrompt Goes Here

    Hope that helps,
    Tricia

  • AMIT says:

    Hi,

    Am trying it out with a prompt in place in the below mention link
    http://:/SASStoredProcess/do1?_program=%2FShared+Data%2FStored+Processes%2FexcelSTP+Test&_action=update%2Cnewwindow&_promptkey=882740257

    but, it not working. is ther any other trement required here?

    Cheers!
    Amit

  • Thanks Stig.

    I’ve used both the guest facility and embedded username and password in the URL in the past. I use /guest? regularly for links to my demo stored process server that I used in my blog:

    http://hcsbi.blogspot.com.

    Have not yet dealt with SSO however. That is usually resolved before I get involved in a project.

  • Stig Eide says:

    Thanks, Don. You are right about the IntrNet comment, I think that was were I started using this method. Then, when the Add-In came, it sort of got superfluous.

    The do1 vs do method is quite useful. Other options are:
    *Creating an STP with _USERNAME_ _PASSWORD_ parameters pre-defined.
    *Setting up anonymous access, so you can use “guest?” instead of “do?” and “do1?”.
    *Setting up web authentication / single signon.

  • Great tip Stig. I had forgotten about the Excel Web Query feature. It was originally documented, I think, by Vince DelGobbo for use with the SAS IntrNet Application Dispatcher back before BI/EBI was an option.

    And so it goes without saying that it will also work with IntrNet (both the Dispatcher and htmSQL).

    I also like the do1 vs do reference. I had never run across it and am going to start making it my standard. One of the other nice things about it is that you can tell it to remember who you are!