topic Re: Do loop using a prompt-generated macro variable in SAS Programming

Do loop using a prompt-generated macro variable

AD — Wed, 20 Mar 2013 00:02:51 GMT

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

Re: Do loop using a prompt-generated macro variable

Quentin — Wed, 20 Mar 2013 00:26:41 GMT

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.

Re: Do loop using a prompt-generated macro variable

SteveNZ — Wed, 20 Mar 2013 00:45:10 GMT

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)

Re: Do loop using a prompt-generated macro variable

AD — Wed, 20 Mar 2013 14:18:49 GMT

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

Re: Do loop using a prompt-generated macro variable

Quentin — Wed, 20 Mar 2013 14:35:50 GMT

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-code/

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

--Q.

Re: Do loop using a prompt-generated macro variable

AD — Wed, 20 Mar 2013 14:43:28 GMT

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

Re: Do loop using a prompt-generated macro variable

AD — Wed, 20 Mar 2013 14:49:22 GMT

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

Re: Do loop using a prompt-generated macro variable

jbrisle — Thu, 29 Aug 2013 18:20:35 GMT

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

Re: Do loop using a prompt-generated macro variable

Quentin — Fri, 30 Aug 2013 11:26:05 GMT

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.