topic Re: Utilize macro variable within WHERE clause in SAS Programming

Utilize macro variable within WHERE clause

80sMetalForever — Fri, 30 Aug 2013 18:47:08 GMT

Howdy, new to SAS (using a couple weeks) and am currently trying to create 1 state table per state and have the state abbreviation be used in the WHERE clause to only insert records for that state. I have tried MANY incarnations of this code so far and believe I'm close. Seems I need to quote my variable and like I said I've tried doing that many ways. Currently receiving the error on all iterations, with the abbreviation changing:

where TABLE1.STATE_AB = DE;

ERROR: The following columns were not found in the contributing tables: DE.

%LET stateab= DE PA MI MN MA WI NY;

%createstatetables

%MACRO createstatetables;

%LET i =1 ;

%DO %UNTIL(&i = 😎 ;

proc sql feedback;

%LET st = %SCAN(&stateab,&i);

create table &st as

select *

from testdata.table1 where state_ab = &st;

RUN;

%LET i=%EVAL(&i+1);

%END;

%MEND createstatetables;

thank you in advance for any insight

Re: Utilize macro variable within WHERE clause

Vince28_Statcan — Fri, 30 Aug 2013 19:01:45 GMT

It's because your values are recognized as code (and thus expects a name variable) instead of as the string their string representation. Macro variables make no distinction of variable types, it's all (or at least mostly) just text parsing.

You simply need to change

where state_ab = &st;

where state_ab = "&st";

Make sure you use double quotes as sigle quotes prevent dereferencing macro variables (treat the ampersand as text and not macro code components)

Also, not that it is a necessary fix but you could've used

%do i=1 %to 7;

block

%end;

to avoid having to handle the increment yourself

Vincent

Re: Utilize macro variable within WHERE clause

Reeza — Fri, 30 Aug 2013 20:37:17 GMT

You'd still need to know the number of values in the array.

Preferably something along the lines of:

%do %while (%SCAN(&stateab,&i) ne %str());

%end;

Probably a bit over your head, but generally this isn't recommended. SAS has BY group processing that is super effective and usually negates the need to separate by variables.

Here's a good but technical read on the different methods and at the bottom is a link to a generic sql solution.

http://www.sascommunity.org/wiki/Split_Data_into_Subsets

Re: Utilize macro variable within WHERE clause

80sMetalForever — Tue, 03 Sep 2013 13:01:20 GMT

Thank you, I didn't try that as I was certain it wouldn't work LOL. It worked correctly and I've updated the loop to your more 'elegant' version.

Thanks for the link Reeza, I will check it out.

Re: Utilize macro variable within WHERE clause

robertrao — Tue, 03 Sep 2013 15:06:33 GMT

Hi,

I was wondering about the term

single quote " prevent dereferencing"..............

did u mean single quote "prevent referencing"?????

Thanks

Re: Utilize macro variable within WHERE clause

Tom — Tue, 03 Sep 2013 15:28:34 GMT

Try the experiment.

%let myvar=Macro Variable value;

%put '&myvar';

%put "&myvar";

Re: Utilize macro variable within WHERE clause

robertrao — Tue, 03 Sep 2013 17:19:08 GMT

Hi Tom,

Thanks so much...

Importantly, a couple of days ago i read your response to one of the questions on macro on how to use braces....

I could not find it now .could you help me locate the question and the responses???/

It uses the QUOTE function!!!!

Thanks