Do loop using a prompt-generated macro variable

Accepted Solution Solved
Reply
Contributor AD
Contributor
Posts: 31
Accepted Solution

Do loop using a prompt-generated macro variable

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


Accepted Solutions
Solution
‎03-19-2013 08:45 PM
Frequent Contributor
Posts: 87

Re: Do loop using a prompt-generated macro variable

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


All Replies
PROC Star
Posts: 1,322

Re: Do loop using a prompt-generated macro variable

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.

Solution
‎03-19-2013 08:45 PM
Frequent Contributor
Posts: 87

Re: Do loop using a prompt-generated macro variable

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)

Contributor AD
Contributor
Posts: 31

Re: Do loop using a prompt-generated macro variable

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

PROC Star
Posts: 1,322

Re: Do loop using a prompt-generated macro variable

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.

Contributor AD
Contributor
Posts: 31

Re: Do loop using a prompt-generated macro variable

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!!

Contributor AD
Contributor
Posts: 31

Re: Do loop using a prompt-generated macro variable


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

New Contributor
Posts: 4

Re: Do loop using a prompt-generated macro variable

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

PROC Star
Posts: 1,322

Re: Do loop using a prompt-generated macro variable

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.

🔒 This topic is solved and locked.

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

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