Blogs

Blogs

Using Multiple Selection Prompts in SAS Stored Process Code

7
By Topics | Advanced Analytics Data Visualization

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

Tags business intelligence Stored Processes
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. MD4 on

    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

  3. Angela Hall on

    My favorite mechanism to deal with scenarios such as these is to use the data _null_; call execute statements.There are some really great examples. Just use the custom search engine I created to search on "call execute".Here is a direct link:http://www.google.com/cse?cx=partner-pub-0369052478647500:413bdc-x2c9&ie;=ISO-8859-1&q;=call+execute&sa;=Search&siteurl;=ig.gmodules.com/gadgets/ifr%3Fview%3Dhome%26url%3Dhttp://www.google.com/coop/api/partner-pub-0369052478647500/cse/413bdc-x2c9/gadget%26nocache%3D0%26lang%3Den%26country%3Dus%26.lang%3Den%26.country%3Dus%26synd%3Dig%26mid%3D92%26ifpctok%3D-3104201402973716933%26exp_split_js%3D1%26exp_track_js%3D1%26exp_new_js_flags%3D1%26exp_ids%3D17259,300070%26parent%3Dhttp://www.google.com%26refresh%3D3600%26libs%3Dcore:core.io:core.iglegacy:auth-refresh%26is_signedin%3D1

  4. lovemango on

    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.

  5. Angela Hall on

    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);

  6. lovemango on

    Hi,in your sample code, where( var1,var2,var3), where is the (,)comma between each two variables,?

  7. DonH on

    It also creates a variable called Region_Prompt0 if there was more than one value. This is the same technique as used in the IntrNet App Dispatcher.See http://support.sas.com/kb/26/159.html for a description of a macro "multipleNames" that you can use to handle such scenarios. There is also a link to download the code

Back to Top