BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sas-inquirer
Quartz | Level 8

Good afternoon experts,

    I have very limited experience with loops and am attempting to adapt a working code but am not having any luck. I have a list of table names in a macro (&table_names). I want to loop through a piece of code for each table listed in the macro. The table names don't always exist (they differ because tables are based on reference periods and some times a table is not created for each reference period). 

 

There will never be more than 12 table names (corresponding to months in a year).

 

The code itself is gathering data from the SIR library for each table in the &table_names macro (if the table exists) based on ID's in a previously generated table and naming the table incrementally (&index) as it creates each new table (current_table_1, current_table_2 etc.).

 

%macro run_macro;
%do index = 1 % to 12;

proc sql;
create table current_table_&index as
select *
from sir.&&table_names&index a where exists
(select * 
from work.previous_table b
where ID = ID);
quit;

%end;
%mend;
%run_macro;

I'm not getting anything when I run the code. I'm not sure where I'm going wrong. 

 

If you have any suggestions, I would be so grateful for your help! I would be happy to add additional information. Please let me know.

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

I have a list of table names in a macro (&table_names)

 

Actually, this is a macro variable, not a macro.

 

Since it is a list of names, you cannot use a construct like 

&&table_names&index

because when &index=1, this resolves to &table_names1, and that doesn't exist, you only have &table_names. 

 

So you might want to try replacing the line in your code with &&table_names&index with this line:

 

from sir.%scan(&table_names,&index,%str( )) a where exists

and this should work because it is finding the words in &table_names.

 

I'm not getting anything when I run the code. I'm not sure where I'm going wrong. 

 

From now on, when you have an error, we need to see the SASLOG as well as the SAS code. If it involves a macro, you need to turn on OPTIONS MPRINT; before you run the macro.

--
Paige Miller

View solution in original post

9 REPLIES 9
novinosrin
Tourmaline | Level 20

a quick look tells me this is some typo here before even looking further

%do index = 1 % to 12;

 

should be 

 

%do index = 1 %to 12;

sas-inquirer
Quartz | Level 8
My bad. I typo'd in my question, but the actual code is %to. Thanks for your suggestion!
novinosrin
Tourmaline | Level 20

Ok good. Please post your log to the community for anybody to examine further. Thank you

PaigeMiller
Diamond | Level 26

I have a list of table names in a macro (&table_names)

 

Actually, this is a macro variable, not a macro.

 

Since it is a list of names, you cannot use a construct like 

&&table_names&index

because when &index=1, this resolves to &table_names1, and that doesn't exist, you only have &table_names. 

 

So you might want to try replacing the line in your code with &&table_names&index with this line:

 

from sir.%scan(&table_names,&index,%str( )) a where exists

and this should work because it is finding the words in &table_names.

 

I'm not getting anything when I run the code. I'm not sure where I'm going wrong. 

 

From now on, when you have an error, we need to see the SASLOG as well as the SAS code. If it involves a macro, you need to turn on OPTIONS MPRINT; before you run the macro.

--
Paige Miller
sas-inquirer
Quartz | Level 8
That did it! Thanks so much Paige! You totally saved me. Thank you also for your explanations. I learned something new today thanks to you.
sas-inquirer
Quartz | Level 8
Thanks for the link Reeza. This helps me understand a bit more of what I'm running. I appreciate the link.
ballardw
Super User

@sas-inquirer 

Just for giggles could you mention what your SIR library is used for? I have an annual project where I use SIR for a School Immunization Report and just curious about if the usage is at all similar.

sas-inquirer
Quartz | Level 8
It’s a business register library. No connection. Not sure what it stands for though.

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
  • 9 replies
  • 1764 views
  • 1 like
  • 5 in conversation