topic Re: Macros to loop and append multiple files with year-month name in SAS Programming

Macros to loop and append multiple files with year-month name

kevsma — Wed, 30 Nov 2022 19:48:22 GMT

Hello, I have been trying to accomplish this task for a while but without luck. I want to grab multiple files and append them together, and add a new variable to the final dataset indicating the source of each data. Below is my code:

%macro loop(start,end);
	%let start=%sysfunc(inputn(&start,yymmn6.));
	%let end=%sysfunc(inputn(&end,yymmn6.));
	%let year=&start;
	%let month=&start;
	%let incr=0;
	%do %while(&year<=&end);
		%let incr=%eval(&incr+1);
		%put &=incr;
		%let year=%sysfunc(intnx(year,&start,&incr,b));
		%let filename=cm%sysfunc(putn(&year,year4))%sysfunc(month(&start),z2);
		data current; set lcmf.&filename.(keep=uci status); 
			source="&filename."; 
		output;
	%end;
	run;
%mend;

%loop(201202,201402)

However, the final dataset only contains the last dataset in the loop although the log shows SAS is grabbing each file in the loop, but the loop replaces the previous dataset with the latter one, which is not something I want. The new variable "source" is also incorrect. Basically I want a stacked dataset with datasets: cm201202, cm201302, cm201402 combined with a new variable "source" indicating the year-month information.

Please help!!!! Thanks.

Re: Macros to loop and append multiple files with year-month name

Kurt_Bremser — Wed, 30 Nov 2022 19:59:06 GMT

You only need the loop for the dataset names in the SET statement.

%macro loop(start,end);
%let start=%sysfunc(inputn(&start,yymmn6.));
%let end=%sysfunc(inputn(&end,yymmn6.));
%let year=&start;
%let month=&start;
%let incr=0;
data current;
length dname $41;
set
%do %while(&year<=&end);
  %let incr=%eval(&incr+1);
  %put &=incr;
  %let year=%sysfunc(intnx(year,&start,&incr,b));
  %let filename=cm%sysfunc(putn(&year,year4))%sysfunc(month(&start),z2);
  lcmf.&filename. (keep=uci status)
%end;
  indsname=dname
;
source = dname;
run;
%mend;

Re: Macros to loop and append multiple files with year-month name

kevsma — Wed, 30 Nov 2022 20:15:15 GMT

Thanks Kurt!! Your revision works perfectly except for the new "source" variable. Somehow it shifted by 1 value: currently it shows "cm201302", "cm201402" and "cm201502" instead of "cm201202", "cm201302" and "cm201402", which are the correct values I expect. I know this has something to do with the filename loop set up, do you know any quick fix of this? I actually only want the year and month info, so values like 201202, 201302, 201402 would work as well.

Re: Macros to loop and append multiple files with year-month name

Kurt_Bremser — Wed, 30 Nov 2022 20:29:08 GMT

Please post the code you ran.

Re: Macros to loop and append multiple files with year-month name

kevsma — Wed, 30 Nov 2022 20:44:40 GMT

exactly the one you replied to above, I also noticed the data files SAS was grabbing are wrong, instead of grabbing cm201202, cm201302, cm201402, it's grabbing cm201302, cm201402, and cm201502. Is there something that needs to be revised to the line "%let incr=%eval(&incr+1);"? Welcome your thoughts.

%macro loop(start,end);
%let start=%sysfunc(inputn(&start,yymmn6.));
%let end=%sysfunc(inputn(&end,yymmn6.));
%let year=&start;
%let month=&start;
%let incr=0;
data current;
length dname $41;
set
%do %while(&year<=&end);
  %let incr=%eval(&incr+1);
  %put &=incr;
  %let year=%sysfunc(intnx(year,&start,&incr,b));
  %let filename=cm%sysfunc(putn(&year,year4))%sysfunc(month(&start),z2);
  lcmf.&filename. (keep=uci status)
%end;
  indsname=dname;
	source = dname;
run;
%mend;
%loop(201202,201402)

Re: Macros to loop and append multiple files with year-month name

kevsma — Wed, 30 Nov 2022 20:47:20 GMT

I just thought of a quick fix, which is to shift the start and end date backward by 1, so that SAS is grabbing the correct years I need. No need to tweak the code itself.

Re: Macros to loop and append multiple files with year-month name

Kurt_Bremser — Wed, 30 Nov 2022 20:52:30 GMT

It's a logical mistake I copied from your code. The incrementation statement needs to be moved:

%do %while(&year<=&end);
  %put &=incr;
  %let year=%sysfunc(intnx(year,&start,&incr,b));
  %let filename=cm%sysfunc(putn(&year,year4))%sysfunc(month(&start),z2);
  lcmf.&filename. (keep=uci status)
  %let incr=%eval(&incr+1);
%end;