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.
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;
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;
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.
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)
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;
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.