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
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
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
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.
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.
Hi,
I was wondering about the term
single quote " prevent dereferencing"..............
did u mean single quote "prevent referencing"?????
Thanks
Try the experiment.
%let myvar=Macro Variable value;
%put '&myvar';
%put "&myvar";
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.