Using Multiple Selection Prompts in SAS Stored Process Code

7

It is definitely easier to force single selections for prompts used in SAS Stored Processes, however it isn't very usable when the majority of users need to select multiple values. For example, let us say we create a prompt for region (called 'region_prompt') and then use that in the query of sashelp.shoes.

Forcing only 1 selection to write to the macro &region;_prompt the resulting SQL query would look like:

     proc sql; create table test as select * from sashelp.shoes where shoes.region="&region_prompt";   
     quit;

Pretty simple really.

Now if we allow users to select 1 or more values for region, SAS creates a macro with the same name but adding the term _Count. Such as &region_prompt_count, this represents the amount of selections the user chose. Therefore the SQL query needs to take all of these selections into account. ALSO - if only 1 selection is chosen, there is no &region_prompt1 - so you must account for that as well.
Here is an example:

     proc sql; create table test as select * from sashelp.shoes
             where shoes.region in (
             %if &REGION_PROMPT_COUNT = 1 %then 
                            "&Region_Prompt";
             %else %do i=1 %to &REGION_PROMPT_COUNT;
                         "&&Region_Prompt&i"
             %end; 
              ); 
    quit; 

Reference: Overview of Input Parameters http://support.sas.com/documentation/cdl/en/stpug/61271/HTML/default/a003152642.htm

Share

About Author

Angela Hall

Senior Technical Architect

Angela offers tips on using the SAS Business Intelligence solutions. She manages a team of SAS Fraud Framework implementers within the SAS Solutions On-Demand organization. Angela also has co-written two books, 'Building BI using SAS, Content Development Examples' & 'The 50 Keys to Learning SAS Stored Processes'.

Related Posts

7 Comments

  1. for all of those that have SAS Enterprise Guide: there is a macro available, called _eg_WhereParamThis macro will deal with the various types of prompts that can be created in EG, and of course it is also great for use in a Stored Process. to get to the source of the macro simply export all code of a process flow where a prompt is used for instnace in a query

  2. Thank you so much for the reply and explanation:)Recently I also have an issue using multiple selection in Stored Process, but the variable appear after group by and column define in the PROC REPORT.as,******************Proc sql;.......group by ***,&variable;(maybe &variable1;,&variable2..;);quit;Proc Report ...........column *** &variable; ..........;define ......;define &variable; ........;........*******************If here need multiple selection, very difficult for me to code, so I just use IF statement with all case of number of variables, and duplicate the proc sql and proc report. I know there should be a better way same as your example, but i could not solve the problem of ',' and ';' among them. Could u help me think about it? Thanks a lot.

  3. The "," isn't required in a where clause. In SAS 9.1 and 9.2, you can run both of the following successfully:select * from sashelp.shoes where region in ("Africa" "Canada");select * from sashelp.shoes where stores in (2 5);

Leave A Reply

Back to Top