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:

 %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. For stored processes that are compatible with 9.2 and that run on the workspace server, values are limited to approximately 5950 bytes in length and cannot contain nonprintable characters (including line feeds or carriage returns).

Each stored process client interface provides one or more methods to set input parameters. The Stored Process Java 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.

For stored processes that are compatible with 9.2, 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.

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

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(JonesComp.);

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.

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:

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.

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;