topic Re: macro loop for non-sequential numbers in SAS Enterprise Guide

macro loop for non-sequential numbers

eemrun — Wed, 19 Sep 2018 03:17:15 GMT

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;

Re: macro loop for non-sequential numbers

SASKiwi — Wed, 19 Sep 2018 03:55:13 GMT

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;

Re: macro loop for non-sequential numbers

eemrun — Wed, 19 Sep 2018 03:58:59 GMT

thanks for this! But still unclear as to how to implement it.

Re: macro loop for non-sequential numbers

Reeza — Wed, 19 Sep 2018 04:13:32 GMT

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;

Re: macro loop for non-sequential numbers

DrAbhijeetSafai — Tue, 26 Dec 2023 07:35:08 GMT

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

Re: macro loop for non-sequential numbers

Patrick — Tue, 26 Dec 2023 09:08:22 GMT

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