BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AD
Calcite | Level 5 AD
Calcite | Level 5

I've got some code that runs in Enterprise Guide after the user has "prompted" for a few input parameters.  One of those prompts (FID) asks the user to identify one or more FormID values.  If the user enters just one value (e.g., 1), then the following macro variables are created, among others:

       %LET FID_count = 1;

       %LET FID1 = 1;

   

If the user enters just more than one value (e.g., suppose the user enters 1, 5 and 9), then the following macro variables are created, among others:

       %LET FID_count = 3;

       %LET FID1 = 1;

       %LET FID2 = 5;

       %LET FID3 = 9;

I need help with the WHERE statement in the code below. 

%macro temp;

PROC SQL;

   SELECT t1.CANDIDATE_ID,

          t1.FORM_ID,

          t1.TOTAL_SCORE,
        FROM ORA_SAS.CANDIDATE_EXAMS t1,
        WHERE t1.FORM_ID IN (?????????)

        ORDER BY  t1.CANDIDATE_ID;
QUIT;

%mend;

%temp;

Specifically, if the user enters only 1 value (e.g. 1), then the WHERE statement should resolve to:

        WHERE t1.FORM_ID IN (1)

If the user enters multiple values (e.g. 1, 5, and 9), then the WHERE statement should resolve to:

        WHERE t1.FORM_ID IN (1,5,9)

Thanks!!  Please let me know if something isn't clear.  As a reminder, I'm using the prompting interface inside of Enterprise Guide and will eventually be building some Stored Processes with this code. -- Andy

1 ACCEPTED SOLUTION

Accepted Solutions
SteveNZ
Obsidian | Level 7

Different option:

%macro macfix ;
%do i=1 %to &FID_count ;
   &&FID&i
%if &i ne &FID_count %then %do ;
,
%end ;
%end ;
%mend macfix ;

Then:

WHERE t1.FORM_ID IN (%macfix)

View solution in original post

8 REPLIES 8
Quentin
Super User

Hi Andy,

Your message is clear.  This is one of the few cases where I feel the word "Hate" is appropriate with regard to a SAS feature.  This method SAS uses for storing "check all that apply" prompt values is just horrible.  I think a list of macro vars (&prompt1-&promptN) would have been fine, or even a single macro var &prompt holding a delimited list of the values.  But such is life.  At least there is a logic to what SAS is doing, which makes it possible to write a macro to transform the prompt values for you.  I've been using below, which I based on a macro I saw in a paper, or a book, or somewhere....

%macro ExpandParm(parm);

/*==============================================================================
Macro:         ExpandParm

Abstract:      Macro function that returns space delim list of items chosen in
               a check all that apply stored process prompt.


Description:   Stored processes pass CHECK All That Apply parameters in an odd fashion
                 PROMPT_Count always exists, holds number of options selected (0-n);
                 PROMPT1 - PROMPTN are created if count>=2, if count IN (1,0), they are not created;
                 PROMPT is always created ;

Usage:         %let sitelist=%expandparm(site)

================================================================================*/

%local i return;

%if &&&parm._Count ge 2 %then %do i = 1 %to &&&parm._Count;
  %let return=&return &&&parm&i;
%end;
%else %if &&&parm._Count = 0 or &&&parm._Count = 1 %then %do;
  %let return=&&&parm;
%end;
%else %put ER%str()ROR: USER;

&return

%mend;

HTH,

--Q.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
SteveNZ
Obsidian | Level 7

Different option:

%macro macfix ;
%do i=1 %to &FID_count ;
   &&FID&i
%if &i ne &FID_count %then %do ;
,
%end ;
%end ;
%mend macfix ;

Then:

WHERE t1.FORM_ID IN (%macfix)

AD
Calcite | Level 5 AD
Calcite | Level 5

Thanks to you both!! I found Steve's answer to be slightly simpler and more in line with what I was looking for.

Quentin
Super User

I would just double check that %macfix() handles the situation when user only selects one value.

In your question, you wrote that if a user selected only one value, SAS would set &FID_count=1 and &FID1=<users value>.

But my memory is if a user selects only one value, SAS sets &FID_count=1 and &FID=<users value>, but &FID1 will not exist.

See e.g.:

http://support.sas.com/documentation/cdl/en/stpug/61271/HTML/default/viewer.htm#a003292503.htm

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

Unless you are handling this already, you might want to extend %macfix() to handle this case when user selects onlye one value.

--Q.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
AD
Calcite | Level 5 AD
Calcite | Level 5

Ahhhh, that is true and a good point. I didn't realize, but the &FID1 macro variable is not created when the user only specifies 1 value.  I actually created my own solution based on the feedback from both of you which is...

WHERE  t1.FORM_ID IN (

             %do i=1 %to &FID_count ;

                 &&FID&i

             %end;

               )

This is simpler, and it works for all situations.  I was confused earlier thinking that the list of values (if more than one value is selected) needed to be separated by commas in the IN statement.

Thanks!!

AD
Calcite | Level 5 AD
Calcite | Level 5


Actually no, haha, that still doesn't work for only 1 value, but I know what to do to fix.  Thanks!!

jbrisle
Calcite | Level 5

Can you share what you did to fix it? I'm running into the same problem!

Quentin
Super User

I don't know what solution Andy ended up using, but I think the macro I posted about it works. 

I blogged about this approach here:

http://www.bi-notes.com/2013/08/sas-stored-process-taming-selection-list-prompts/

And see the comments where Don Henderson was kind enough to describe some of the background on how this parameterization was chosen, and provided links to additional solutions.

--Q.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 3088 views
  • 6 likes
  • 4 in conversation