Desktop productivity for business analysts and programmers

SAS EG 7.1 Text Prompt not working for multiple values

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

SAS EG 7.1 Text Prompt not working for multiple values

 

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

 


Accepted Solutions
Solution
‎05-12-2017 05:56 PM
Super User
Super User
Posts: 6,842

Re: SAS EG 7.1 Text Prompt not working for multiple values

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;

View solution in original post


All Replies
Super User
Posts: 11,101

Re: SAS EG 7.1 Text Prompt not working for multiple values

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;

Occasional Contributor
Posts: 10

Re: SAS EG 7.1 Text Prompt not working for multiple values

>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

 

Super User
Super User
Posts: 6,842

Re: SAS EG 7.1 Text Prompt not working for multiple values

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

There have been other questions about this recently.

 

Occasional Contributor
Posts: 10

Re: SAS EG 7.1 Text Prompt not working for multiple values

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
Solution
‎05-12-2017 05:56 PM
Super User
Super User
Posts: 6,842

Re: SAS EG 7.1 Text Prompt not working for multiple values

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;
Occasional Contributor
Posts: 10

Re: SAS EG 7.1 Text Prompt not working for multiple values

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

 

Super User
Super User
Posts: 6,842

Re: SAS EG 7.1 Text Prompt not working for multiple values

[ Edited ]
  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;
Occasional Contributor
Posts: 10

Re: SAS EG 7.1 Text Prompt not working for multiple values

I remember the 1 and 2 but didn't know that WHERE statement optimizes with commas.
Thank you.
Super User
Super User
Posts: 6,842

Re: SAS EG 7.1 Text Prompt not working for multiple values

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 247 views
  • 1 like
  • 3 in conversation