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!
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.
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;
Ok good. Please post your log to the community for anybody to examine further. Thank you
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.
Maybe this example is helpful?
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.