When a dynamic prompt is defined for use in a SAS^® Stored Process, the Sort order field for the prompt contains the following choices:

• Use default sort order
• Ascending by formatted value
• Descending by formatted value
• Ascending by unformatted value
• Descending by unformatted value

Some prompts might ignore the Use default sort order option and present the prompt values in ascending sorted order, instead of retaining the order of the variable values in the data source.

Consider the following example, which illustrates the problem:

A data source contains two variables, RESPONSE and DESCRIPTION

response    description

   NA       Not Applicable
   1        Strongly Agree
   2        Agree
   3        Neither Agree Nor Disagree
   4        Disagree
   5        Strongly Disagree
   ?        Did not respond

A dynamic prompt is created using the variable RESPONSE for the Unformatted Values column, and using variable DESCRIPTION for the Formatted (Displayed) Values column. Use default sort order is selected for the prompt to retain the order of the values in the data source.

The prompt ignores the Use default sort order option and presents the DESCRIPTION values in ascending sorted order, rather than in the order the values appear in the data source:

    Agree
    Did Not Respond
    Disagree
    Neither Agree Nor Disagree
    Not Applicable
    Strongly Agree
    Strongly Disagree

To circumvent this problem, perform the following steps:

1. Create a new variable in the data source to establish the desired order for the values of the prompt variable. In the above example, numeric values 1-7 or character values A-G could be used:

           if response="NA" then sort="A"; 
           else if response="1" then sort="B";
           

...and so on.

2. Designate the new variable (SORT) for the Unformatted Values column of the dynamic prompt, and leave the variable (DESCRIPTION) as the Formatted (Displayed) Values column of the dynamic prompt. Then, select the Ascending by unformatted value option for the Sort order.
3. Modify the underlying SAS Stored Process code to reference the newly created variable values for input parameter processing:

           if &prompt="B" then do;

   instead of...

           if &prompt="1" then do;
           

...and so on.

Operating System and Release Information