Hi,
Can some one help me with the issue below? Please see my steps below.
thanks
/* --- Start of code for "Program". --- */
proc print data = sashelp.class ;
where (sex eq "&MULTISELECT_GENDER") ;
run ;
/* --- End of code for "Program". --- */
Steps taken in SAS EG 7.1 to create a prompt:
a) Create a prompt called "MULTISELECT_GENDER" from the General tab, then move on to the Prompt Type and Values tab.
b) Select text for prompt type and user selects from a static list and the multiple values option.
c) Load the sas data from the "get values" button
d) get the values via the "get values" button which will load "F" and "M"
e) then click OK and run the SAS stored processes.
When the user gets the prompt, i select both of them (F and M). But since i selected "F" first, my report is generated with Female only and vice versa. I also tried my code with "in" (where (sex in ("&MULTISELECT_GENDER")) operator for the where clause, but still doesnt work.
Can someone point out whats going on here? Am trying to do a test run here.
***SAS LOG***;
33 proc print data = sashelp.class ;
34 where (sex eq "&MULTISELECT_GENDER") ;
35 run ;
NOTE: There were 9 observations read from the data set SASHELP.CLASS.
WHERE sex='F';
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
If you just want to leave the rest of your program the same here is way to convert the mulitple values into a single delimited list.
First le'ts make your example macro variables so we can test.
%let MULTISELECT_GENDER=M;
%let MULTISELECT_GENDER0=2;
%let MULTISELECT_GENDER1=M;
%let MULTISELECT_GENDER2=F;
%let MULTISELECT_GENDER_COUNT=2;
The first thing we need to do is add code to make sure it works when count=1 by making sure that mvar1 is created.
%let multiselect_gender1=&multiselect_gender;
Now let's just use a data step to quote and concatenate the individual values. Remember to make the variable long enough for the maxium set of values. If that is more than 32K characters (including quotes and spaces) then you will need to use a different method.
data _null_;
length str $200 ;
do i=1 to &multiselect_gender_count;
str=catx(' ',str,quote(symget(cats('multiselect_gender',i)),"'"));
end;
call symputx('multiselect_gender',str);
run;
Did you have code that worked for your cases while testing without the prompt?
The IN would require a comparison such as:
Where sex in ("F" "M");
If you prompt has a single value of "F M" then using that "value" looks to SAS like
Where sex in ("F M");
So without changing your prompt you may want to investigate one of the character search functions such as Index or Find
where index("&MULTISELECT_GENDER",sex)>0;
>Thanks for your response.
>Here is the code(log) that i had tested(working) without the prompt:
%let MULTISELECT_GENDER = %str('M','F') ;
proc print data = sashelp.class ;
where sex in (&MULTISELECT_GENDER) ;
run ;
>Then i tried your code as well and it works without the prompt
%let MULTISELECT_GENDER = %str('M','F') ;
proc print data = sashelp.class ;
where index("&MULTISELECT_GENDER",sex) > 0 ;
run ;
>However, when i try to run the code with the multiple values prompt (user selects from a static list) "MULTISELECT_GENDER"; User selects male "M" first and then female"F" next; but the result only picks the first entered value. Please see the code and log below.
Something is going on with how the prompts are functioning here. Can someone point to some PDF papers that discuss creating a prompt with user selecting multiple TEXT values. So far i was able to find only the single value prompts from SUGI papers.
Please advise - thanks in advance.
proc print data = sashelp.class ;
where index("&MULTISELECT_GENDER",sex) > 0 ;
run ;
24 proc print data = sashelp.class ;
25 where index("&MULTISELECT_GENDER",sex) > 0 ;
26 run ;
NOTE: There were 10 observations read from the data set SASHELP.CLASS.
WHERE INDEX('M', sex)>0;
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
I believe that it generates MULTIPLE macro variables when you tell it to allow multiple selections.
There have been other questions about this recently.
If you just want to leave the rest of your program the same here is way to convert the mulitple values into a single delimited list.
First le'ts make your example macro variables so we can test.
%let MULTISELECT_GENDER=M;
%let MULTISELECT_GENDER0=2;
%let MULTISELECT_GENDER1=M;
%let MULTISELECT_GENDER2=F;
%let MULTISELECT_GENDER_COUNT=2;
The first thing we need to do is add code to make sure it works when count=1 by making sure that mvar1 is created.
%let multiselect_gender1=&multiselect_gender;
Now let's just use a data step to quote and concatenate the individual values. Remember to make the variable long enough for the maxium set of values. If that is more than 32K characters (including quotes and spaces) then you will need to use a different method.
data _null_;
length str $200 ;
do i=1 to &multiselect_gender_count;
str=catx(' ',str,quote(symget(cats('multiselect_gender',i)),"'"));
end;
call symputx('multiselect_gender',str);
run;
Thanks Tom - your code works great both in EG as well as in the stored process. Am missing one basic question here; Please see the tested code below in EG; the Catx is putting a space between the gender valuess, however, when it gets resolved in the "in" operator in where clause, it resolves as 'F' , 'M'. Is the "in" operator in SAS forces the commas to appear in the "where" clause in the print procedure?
Please clarify.
34 %let MULTISELECT_GENDER1=M;
35 %let MULTISELECT_GENDER2=F;
36 %let MULTISELECT_GENDER_COUNT=2;
37
38 data _null_;
39 length str $200 ;
40 do i=1 to &multiselect_gender_count;
41 str=catx(' ',str,quote(symget(cats('multiselect_gender',i)),"'"));
42 end;
43 call symputx('multiselect_gender',str);
44 run;
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
45 %put multiselect_gender = &multiselect_gender. ;
multiselect_gender = 'M' 'F'
46
47 proc print data = sashelp.class ;
48 where sex in (&multiselect_gender.) ;
49 run ;
2 The SAS System 12:34 Friday, May 12, 2017
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
WHERE sex in ('F', 'M');
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
Try some of these.
where age between 10 and 12;
where age in (12 11 10);
where 1=0;
where 1=1;
The commas don't optimize the WHERE clause. They probably include them to make the where clause more compatible with external databases just in case the where clause ends up being pushed down into the external database.
Or it just could be that the SAS programmer that wrote the code that generates the WHERE clause that is printed didn't know that SAS supports either spaces or commas as the delimiters in the list of values for the IN operator.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.