Using Multiple Selection Prompts (Migration issues of SAS 9.1 to 9.2)

Web Suggestion: (From Angela Hall)

For example in the old box (i.e., SAS 9.1), let us say we create a prompt for region (called 'region_prompt') and then use that in the query of sashelp.shoes. The GUI Prompt created only one macro variable called region_prompt which contained all user selection:

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

But now in the new box (i.e., SAS 9.2), if we allow users to select 1 or more values for region, SAS creates n number of macro variables with the same name but adding _Count to it. 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;

SAS Tech Support Suggestion:

Since the user is writing their own code that uses the prompts, Enterprise Guide does not automatically change over the macro variable code (like it would if they were using the prompt in a Query). But user can go through and update their code where they had the WHERE clause so that it uses the new %_eg_WhereParam macro variable. This is what is now used to account for parameter lists. This is how it would look in the code. The first parameter is the dataset.variable the user is querying, the second is the macro variable, the third is the operator, and the fourth is S or N for string or numeric type.
where %_eg_WhereParam(a.unq_catg_desc, unq_catg_desc, IN, TYPE=S)

Both these suggestion will work for a single code but user cannot make changes to each and every code during Migration and hence can make use of the below autocall macro:

%macro param_macro(var= /*Required Macro variable Name*/
,FMT_Char=/*Y/N Required to present it with quote or without*/);
/*
Name : param_macro.sas
Purpose : %param_macro(), converts an array of values entered in an
UI prompt to 1 macro variable

Call the macro by passing the macro variable name assigned
in the parameter manager.

Usage : OPTIONS SASAUTOS=('Path' '!SASROOT/sasautos');
%param_macro(var=)
*/

options mlogic mprint symbolgen;

%global &&var.;

Data _null_;
length x $20000.;
%if &&&var._COUNT GT 0 %then %do;
%if %upcase(&FMT_CHAR)=Y %then %do;
x = '"'"&&&var.1"'"';
%end;
%else %do;
x="&&&var.1";
%end;
%end;
%if &&&var._COUNT = 1 %then %do;
%if %upcase(&FMT_CHAR)=Y %then %do;
x = '"'"&&&var."'"';
%end;
%else %do;
x = "&&&var.";
%end;
%end;
%else %do i=2 %to &&&var._COUNT;
%if %upcase(&FMT_CHAR)=Y %then %do;
x = strip(x)',"'"&&&&&var.&i"'"';
%end;
%else %do;
x = strip(x)",""&&&&&var.&i";
%end;
%end;
call symput("&var.",x);
run;

%put NOTE: Number of selections made in the UI : &&&var._COUNT;
%put NOTE: Macro Variable &&var. resolves to: &&&var.;
%mend param_macro;

More: http://blogs.sas.com/content/bi/2009/11/10/using-multiple-selection-prompts-in-sas-stored-process-code/