Using Excel as the Input Parameter to a Stored Process

Stored Processes are one of the most useful tools that can be found within the SAS Business Intelligence platform. They can be used through standard SAS client applications such as Enterprise Guide, the SAS Web Applications or in Excel through the Microsoft Office Add-in. One of their most useful features is the ability to specify input parameters.

SAS have introduced streams as a new method of passing values into input parameters . Streams are used when data is too large to be manually entered as parameters. This allows the user to select a range of cells from within Excel to be the input parameters for a Stored Process at run time.

The following steps provide the details on how to achieve this.

First the following code needs to be used in the stored process definition in order to read the data stream:

/*Read Excel values as input parameters*/
libname instr xml;

data work.input_from_excel;
  set instr.&_webin_sasname;
run;

/*Simply print its contents*/
proc print;
run;

Where XML is the engine used to access the data and the reserved macro variable &_webin_sasname is the name of the data in the range.

Within the stored processes definition, the Data Source (input streams to a stored process) area needs to be accessed to define the data stream.

• Form of Data needs to be XML based;
• Expected content type will automatically default to text/xml;
• Fileref is set to InStr (to match the code specified before);
• Allow rewinding stream is selected to allow reading from excel;
• Label is used to enter a descriptive message to present the user.

Once the Stored Process is defined and saved, it can be accessed through Microsoft Excel.

Enter the data range (including column names) and data will be read into the stored process. This example simply outputs it into a new worksheet, but once the data is read in, any form of data set manipulation is available.