Loop through code changing table name each time

Accepted Solution Solved
Reply
Contributor
Posts: 46
Accepted Solution

Loop through code changing table name each time

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!


Accepted Solutions
Solution
‎06-04-2018 01:54 PM
Respected Advisor
Posts: 2,989

Re: Loop through code changing table name each time

[ Edited ]
Posted in reply to sas-inquirer

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


All Replies
PROC Star
Posts: 1,772

Re: Loop through code changing table name each time

Posted in reply to sas-inquirer

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;

Contributor
Posts: 46

Re: Loop through code changing table name each time

Posted in reply to novinosrin
My bad. I typo'd in my question, but the actual code is %to. Thanks for your suggestion!
PROC Star
Posts: 1,772

Re: Loop through code changing table name each time

Posted in reply to sas-inquirer

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

Solution
‎06-04-2018 01:54 PM
Respected Advisor
Posts: 2,989

Re: Loop through code changing table name each time

[ Edited ]
Posted in reply to sas-inquirer

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
Contributor
Posts: 46

Re: Loop through code changing table name each time

Posted in reply to PaigeMiller
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.
Super User
Posts: 23,683

Re: Loop through code changing table name each time

Posted in reply to sas-inquirer
Contributor
Posts: 46

Re: Loop through code changing table name each time

Thanks for the link Reeza. This helps me understand a bit more of what I'm running. I appreciate the link.
Super User
Posts: 13,508

Re: Loop through code changing table name each time

Posted in reply to sas-inquirer

@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.

Contributor
Posts: 46

Re: Loop through code changing table name each time

Posted in reply to sas-inquirer
It’s a business register library. No connection. Not sure what it stands for though.
☑ This topic is solved.

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

Discussion stats
  • 9 replies
  • 162 views
  • 1 like
  • 5 in conversation