I am trying to do a macro loop for merging datasets with non-sequential names. For example: cust_012011, cust_022011, cust042011.
Unsure as to how I can get the macro loop to work. I have the following at the moment but this stops when there is a no dataset for a particular month:
%macro combine;
%local tempTable;
%do year = &startyr. %to &endyr.;
%do month = 1 %to 12;
%let tempTable = cust_data_&year%sysfunc(putn(&month, z2.));
%if %sysfunc(exist(&temptable)) %then %do;
Data want_temp_&month.;
set &tempTable;
keep cust_no data1;
rename data1= data1&month.;
run;
%end;
%end;
%end;
data dataset;
set
%do i = 1 %to 12;
want_temp_&i.
%end;
;
by cust_no;
run;
%mend ombine;
%combine;
The best way to do this would be to build a list in a macro variable based on only datasets that exist using a DICTIONARY table:
proc sql noprint;
select memname
into :data_list separated by ' '
from dictionary.members
where libname = "WORK" and memtype = "DATA"
and substr(memname, 1, 4) = 'CUST'
;
quit;
%put &data_list;
Are you positive you need a macro here?
It looks like an attempt at a proc transpose in a really long way.
Does this get you part of the way there?
data _test;
set cust_data_: indsname=source;;
monthV = source;
run;
If you have any control over your naming convention, SAS works with prefixes so changing the names to be cust_data_YEAR_MONTH would make this a lot more efficient.
@eemrun wrote:
I am trying to do a macro loop for merging datasets with non-sequential names. For example: cust_012011, cust_022011, cust042011.
Unsure as to how I can get the macro loop to work. I have the following at the moment but this stops when there is a no dataset for a particular month:
%macro combine; %local tempTable; %do year = &startyr. %to &endyr.; %do month = 1 %to 12; %let tempTable = cust_data_&year%sysfunc(putn(&month, z2.)); %if %sysfunc(exist(&temptable)) %then %do; Data want_temp_&month.; set &tempTable; keep cust_no data1; rename data1= data1&month.; run; %end; %end; %end; data dataset; set %do i = 1 %to 12; want_temp_&i. %end; ; by cust_no; run; %mend ombine; %combine;
As per this documentation, it seems that it is not possible in SAS to 'Loop through a nonsequential list of values with a macro DO loop'
Thank you.
- Dr. Abhijeet Safai
@DrAbhijeetSafai wrote:
As per this documentation, it seems that it is not possible in SAS to 'Loop through a nonsequential list of values with a macro DO loop'
Thank you.
- Dr. Abhijeet Safai
Not sure why you're answering on a question from 2018.
If you read the note you cite you will also see that it proposes alternative syntax to achieve logically the same - a loop using a distinct list of values.
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.