BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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

1 ACCEPTED SOLUTION

Accepted Solutions
Vince28_Statcan
Quartz | Level 8

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

6 REPLIES 6
Vince28_Statcan
Quartz | Level 8

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

Reeza
Super User

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

80sMetalForever
Calcite | Level 5

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.

robertrao
Quartz | Level 8

Hi,

I was wondering about the term

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

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

Thanks

Tom
Super User Tom
Super User

Try the experiment.

%let myvar=Macro Variable value;

%put '&myvar';

%put "&myvar";

robertrao
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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