topic Re: Loop through code changing table name each time in SAS Programming

Loop through code changing table name each time

sas-inquirer — Mon, 04 Jun 2018 17:26:38 GMT

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!

Re: Loop through code changing table name each time

novinosrin — Mon, 04 Jun 2018 17:32:14 GMT

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;

Re: Loop through code changing table name each time

sas-inquirer — Mon, 04 Jun 2018 17:33:58 GMT

My bad. I typo'd in my question, but the actual code is %to. Thanks for your suggestion!

Re: Loop through code changing table name each time

novinosrin — Mon, 04 Jun 2018 17:35:25 GMT

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

Re: Loop through code changing table name each time

PaigeMiller — Mon, 04 Jun 2018 17:45:14 GMT

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.

Re: Loop through code changing table name each time

Reeza — Mon, 04 Jun 2018 17:43:51 GMT

Maybe this example is helpful?

http://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n1qvxz5u3uru7yn1nk7q64ohvwak.htm&docsetVersion=9.4&locale=en

Re: Loop through code changing table name each time

sas-inquirer — Mon, 04 Jun 2018 17:54:25 GMT

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.

Re: Loop through code changing table name each time

sas-inquirer — Mon, 04 Jun 2018 17:55:42 GMT

Thanks for the link Reeza. This helps me understand a bit more of what I'm running. I appreciate the link.

Re: Loop through code changing table name each time

ballardw — Mon, 04 Jun 2018 21:04:15 GMT

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

Re: Loop through code changing table name each time

sas-inquirer — Mon, 04 Jun 2018 23:16:44 GMT

It’s a business register library. No connection. Not sure what it stands for though.