SAS EG 7.1 Text Prompt not working for multiple values

[srihari1]

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

[ballardw]

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;

[srihari1]

>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

[Tom]

I believe that it generates MULTIPLE macro variables when you tell it to allow multiple selections.

There have been other questions about this recently.

[srihari1]

Yes, I was looking at the log and it had macro variables as shown below. Am just trying to figure out how to make it work with in the prompt. Any references for similar problem questions would be very helpful.
Thanks.
>>> SAS Macro Variables:
MULTISELECT_GENDER=M
MULTISELECT_GENDER0=2
MULTISELECT_GENDER1=M
MULTISELECT_GENDER2=F
MULTISELECT_GENDER_COUNT=2

[Tom · Accepted solution]

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;

[srihari1]

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

[Tom]

1. The IN operator does NOT require commas as delimiters.
2. SAS will optimize the WHERE statement for you.
3. When it reformats the optimized WHERE statement it uses the commas.

Try some of these.

where age between 10 and 12;
where age in (12 11 10);
where 1=0;
where 1=1;

[srihari1]

I remember the 1 and 2 but didn't know that WHERE statement optimizes with commas.
Thank you.

[Tom]

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.