Writing a Stored Process

Using Input Parameters

Most stored processes require information from the client to perform their intended function. This information can be in the form of presentation options for a report, selection criteria for data to be analyzed, names of data tables to be used or created, or an unlimited number of other possibilities. Input parameters are the most common way to deliver information from a client to a stored process.

Input parameters are defined as name/value pairs. They appear in a stored process program as global macro variables. For example, if you have a stored process that analyzes monthly sales data, you might accept MONTH and YEAR as input parameters. The stored process program might be similar to the following code:

 *ProcessBody;
 %stpbegin;

 title "Product Sales for &MONTH, &YEAR";
 proc print data=sales;
 where Month eq "&MONTH" and Year eq &YEAR;
 var productid product sales salesgoal;
 run;

 %stpend;

Because input parameters are simply macro variables, they can be accessed through normal macro substitution syntax (&param-name ) or through any other SAS functions that access macro variables (SYMGET , SYMGETC , or SYMGETN ). Parameters follow the same rules as SAS macro variables. Names must start with an alphabetic character or underscore and can contain only alphanumeric characters or underscores. The name can be no more than 32 characters long and is not case sensitive. Values can contain any character except a null character and can be up to 65,534 characters in length on the stored process server. Values are limited to approximately 5950 bytes in length and cannot contain nonprintable characters (including line feeds or carriage returns) on the workspace server.

Each stored process client interface provides one or more methods to set input parameters. The Stored Process Service API provides a direct programming interface to set name/value pairs. The SAS Stored Process Web Application allows name/value pairs to be specified directly on a URL or indirectly through posting HTML form data. The SAS Add-In for Microsoft Office provides a property sheet interface to specify parameters.

There are many reserved parameters that are created by the server or the stored process client interface. For a list of these variables, see Using Reserved Macro Variables.

Standard Header for Parameters

Parameters are not initialized in the same way for the stored process server and the workspace server. The stored process server sets parameter values before the stored process begins to execute. This means the first line of code in the stored process can access any input parameter macro variable. The workspace server does not set input parameters into macro variables until it reaches a *ProcessBody; comment line in the stored process:

 *ProcessBody;

A stored process that does not contain this line never receives input parameters when executed on a workspace server. Also, without this comment, the stored process is not able to use reserved macro variables, such as _METAUSER.

It is recommended that you begin all stored processes (regardless of the server types) with %GLOBAL declarations for all of your input parameters followed by the *ProcessBody; comment:

 /* *****************************************************
 * Standard header comment documenting your
 * stored process and input parameters.
 * ************************************************** */
 %global parmone parmtwo parmthree;
 %global parmfour;
 *ProcessBody;

 ... remainder of the stored process ...

The %GLOBAL declarations create an empty macro variable for each possible input parameter and enable you to reference the macro variable in the stored process even if it was not set by the stored process client. If you do not declare input parameters in a %GLOBAL statement, then any references to an unset input parameter will result in WARNING messages in the SAS log.

Defining Input Parameters

Most stored process client interfaces allow a client to pass any input parameter. Input parameters are defined in SAS Management Console as prompts. Several types of macro variables are generated from prompts, depending on the type of prompt and what other information is included in the prompt definition. There is no requirement to define parameters before executing the stored process, but there are many advantages to describing parameters in stored process metadata. Here are some of the advantages:

Parameter definitions can specify labels and descriptive text. This information can be used by client interfaces to present a more attractive and informative user interface. Other presentation options include grouping parameters.
Default values can be specified. The default value is used if the parameter value is not specified by the client.
Default values can be flagged as read-only to allow a fixed parameter value to always be passed in a stored process. This can be useful when using an existing program that accepts many input parameters. You can register a new, simpler stored process that has some fixed value parameters and fewer client-specified parameters. You can also register multiple stored processes for a single program. Each stored process definition can pass in unique fixed parameter values to the executing program to force a particular operation or otherwise affect the execution of the stored process.
Parameters can be flagged as required. A stored process will not run unless the client specifies values for these parameters.
Parameters can be limited to a specific type such as text or date. Defining a parameter type causes certain client user interfaces (such as SAS Add-In for Microsoft Office) to present more appropriate input controls. All interfaces will reject stored process requests with input parameters that do not match the specified type.
Parameter values can be limited by specifying enumerated lists or ranges of valid values for a parameter.
Dates and times, as well as date ranges and time ranges, can be specified as relative values.
Input parameters can be shared between stored processes. Other applications or software features that support prompts can also take advantage of these prompts.
Input parameters can be populated dynamically from a data source.
Dependencies can be specified between input parameters.
Selection groups can be used.

Parameter metadata for a stored process can be added or modified using SAS Management Console. To define an input parameter for a stored process, click New Prompt in the New Stored Process Wizard or on the Parameters tab in the Stored Process Properties dialog box. For an example of how to add an input parameter to a stored process definition, see Adding a Parameter to the Stored Process Definition.

For information about using prompt features, see Using Prompts. For more information about how to specify values for prompt, and macro variables that are generated by prompts, see Formatting Prompt Values and Generating Macro Variables from Prompts. For more information about prompt types and defining prompts, see the product Help.

Special Character Quoting

Input parameter values are specified by the stored process client at run time. The author of a stored process has little control over the values a client can specify. Setting the values directly into SAS macro variables enables clients to insert executable macro code into a stored process and can lead to unexpected behavior or unacceptable security risks. For example, if an input parameter named COMP was set to Jones&Comp. and passed directly into the macro variable, then any references to &COMP in the stored process program would lead to an invalid recursive macro reference. To avoid this problem, stored process parameters are masked with SAS macro quoting functions before being set into macro variables. In the Jones&Comp example, the COMP parameter has the following setting:

 %let COMP=%nrstr(Jones&Comp.);

The stored process can then freely use &COMP without special handling for unusual input values. Special characters that are masked for input parameters are the ampersand (&), apostrophe ('), percent sign (%), quotation marks ("), and semicolon (;).

There might be special cases where you want to unmask some or all of the special characters in an input parameter. The STPSRV_UNQUOTE2 function unmasks only matched apostrophe (') or quotation mark (") characters. For more information, see STPSRV_UNQUOTE2 Function. This can be useful for passing in parameters that are used as SAS options. The %UNQUOTE macro function unquotes all characters in an input parameter, but you should use this function only in very limited circumstances. You should carefully analyze the potential risk from unexpected client behavior before unquoting input parameters. Remember that stored processes can be executed from multiple clients. Some client interfaces perform little or no checking of input parameter values before they are passed in to the stored process.

Note: An input parameter to a stored process that is executing on a workspace server cannot contain both apostrophe (') and quotation mark (") characters. Attempting to set such an input parameter will result in an error. [cautionend]

Input Parameters with Multiple Values

Parameters with multiple values (or alternatively, multiple input parameters with the same name) can be useful in some stored processes. For example, an HTML input form that is used to drive a stored process might contain a group of four check boxes, each named CBOX. The value associated with each box is optOne, optTwo, optThree, and optFour. The HTML for these check boxes might be

<input type="CHECKBOX" name="CBOX" value="optOne">
<input type="CHECKBOX" name="CBOX" value="optTwo">
<input type="CHECKBOX" name="CBOX" value="optThree">
<input type="CHECKBOX" name="CBOX" value="optFour">

If you select all four boxes and submit the form to the SAS Stored Process Web Application, then the query string looks like this:

&CBOX=optOne&CBOX=optTwo&CBOX=optThree&CBOX=optFour

Macro variables cannot hold more than one value. The two types of servers that execute stored processes handle this problem in different ways.

The stored process server uses a macro variable naming convention to pass multiple values to the stored process. A numeric suffix is added to the parameter name to distinguish between values. The number of values is set in <param-name> 0, the first value is set in <param-name>1, and so on. In the previous example, the following macro variables are set as shown in the following table:

Automatically Generated Variables
Name/Value Pair	Description
CBOX = optOne	Specifies the first value.
CBOX0 = 4	Specifies the number of values.
CBOX1 = optOne	Specifies the first value.
CBOX2 = optTwo	Specifies the second value.
CBOX3 = optThree	Specifies the third value.
CBOX4 = optFour	Specifies the fourth value.

Note that the original parameter macro variable (CBOX) is always set to the first parameter value.

Any client application can generate multiple value parameters. The typical uses for multiple values are check box groups in HTML input forms and selection lists that allow multiple selection.

If the parameter name is the same as one of the generated variables, then the following error is returned:

Multiple definitions of a prompt name are not allowed.  Certain prompt types 
expand to multiple prompt names.

Hiding Passwords and Other Sensitive Data

If you are creating a prompt for a password and want the text to be masked as the user is typing, use a text type prompt, and then select Masked single line (for password entry) as the text type. For more information, see the prompt help in SAS Management Console.

Even if you decide not to use a masked prompt, the SAS log exposes programs and input parameters, which could pose a security issue. There are some actions that you can take to hide passwords and other sensitive data from the SAS log. Password values are hidden from the SAS log for any input parameters with the _PASSWORD suffix anywhere in the parameter name (for example, ABC_PASSWORD, _PASSWORDABC). You can disable the SAS log with the DebugMask Web application initialization parameter. For more information, see Debugging in the SAS Stored Process Web Application. You can also use the prefix _NOLOG_ with macro variables to hide request variable values.

The _NOLOG_ prefix enables you to create special macro variables that can be sent to the stored process server without publishing the macro variable values in the SAS log. The special macro variables must start with the prefix _NOLOG_. The prefix is not case sensitive. Here is an example of an input parameter with the _NOLOG_ prefix:

http://yourserver/SASStoredProcess/do?
_program=/WebApps/Sales/Employee+Salary&_nolog_salary=secretpw

If _NOLOG_SALARY is displayed in the SAS logs, the log shows the following:

_NOLOG_SALARY=XXXXXXXX;

Note: The _NOLOG_ prefix and the _PASSWORD suffix are effective only if your stored process is running on a stored process server. [cautionend]

Top of Page