DATA Step, Macro, Functions and more

Utilize macro variable within WHERE clause

Accepted Solution Solved
Reply
Contributor
Posts: 39
Accepted Solution

Utilize macro variable within WHERE clause

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 = 8) ;

  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


Accepted Solutions
Solution
‎08-30-2013 03:01 PM
Super Contributor
Posts: 339

Re: Utilize macro variable within WHERE clause

Posted in reply to 80sMetalForever

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;

to

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

View solution in original post


All Replies
Solution
‎08-30-2013 03:01 PM
Super Contributor
Posts: 339

Re: Utilize macro variable within WHERE clause

Posted in reply to 80sMetalForever

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;

to

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

Super User
Posts: 19,860

Re: Utilize macro variable within WHERE clause

Posted in reply to Vince28_Statcan

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

Contributor
Posts: 39

Re: Utilize macro variable within WHERE clause

Posted in reply to Vince28_Statcan

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.

Super Contributor
Posts: 1,041

Re: Utilize macro variable within WHERE clause

Posted in reply to 80sMetalForever

Hi,

I was wondering about the term

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

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

Thanks

Super User
Super User
Posts: 7,076

Re: Utilize macro variable within WHERE clause

Posted in reply to robertrao

Try the experiment.

%let myvar=Macro Variable value;

%put '&myvar';

%put "&myvar";

Super Contributor
Posts: 1,041

Re: Utilize macro variable within WHERE clause

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 5155 views
  • 1 like
  • 5 in conversation