I am a beginner with SAS, and will greatly appreciate your help with creating a loop across SAS datasets (I found in the book how to make a loop within one dataset, but no information on how to loop across multiple datasets).
Here is my problem:
I need to make the following steps in the loop:
I have to do all that without changing any of the initial datasets, but by creating new datasets.
Could you please kindly advise me how can I do that? Thank you very much in advance.
untested:
%macro merge();
%do i=11 %to %60;
data xxx.datanew_&i;
set xxx.data&i;
keep ID VAR_&i;
d_&i=1;
run;
proc sort data=xxx.datanew_&i; by ID; run;
proc sort data=xxx.data0; by ID; run;
data xxx.data0_merged;
merge xxx.data0 xxx.datanew_&i; by ID; run;
%end;
%mend;
%merge;
Post sample data in and sample expected output.
I am not sure how can I make sample data out of my datasets... I just have several datasets, each of them correspond to different days. Each of them contain ID (unique identifier) and many other variables. All the variables in these datasets contain the "day" number in their names (as I mentioned in the example above, in the dataset data12, all the variable would have names VAR_12, etc.).
I want to do the following procedure for each of my dataset:
****
data xxx.datanew_11;
set xxx.data11;
keep ID VAR_11;
d_11=1;
run;
proc sort data=xxx.datanew_11; by ID; run;
proc sort data=xxx.data0; by ID; run;
data xxx.data0_merged;
merge xxx.data0 xxx.datanew_11; by ID; run;
****
I can just copy-paste this code and manually change the numbers in the names of files and variables (from 11 to 12, 13, ...60). However, I would need to repeat this code 60 times. Is there any other way to do it more efficiently?
untested:
%macro merge();
%do i=11 %to %60;
data xxx.datanew_&i;
set xxx.data&i;
keep ID VAR_&i;
d_&i=1;
run;
proc sort data=xxx.datanew_&i; by ID; run;
proc sort data=xxx.data0; by ID; run;
data xxx.data0_merged;
merge xxx.data0 xxx.datanew_&i; by ID; run;
%end;
%mend;
%merge;
I tested it, and it works! Thank you so much, Reeza! I did not know the "macro" function, started looking into it and it seems like a very straightforward solution.
I only changed three things (just for records):
1) change the order of "keep..." and "d_..." (it was my mistake of quick typing; keep should go after the variables' formation);
2) put sorting of data0 outside the macro (so that it is not repeatedly sorted => should save some time, I guess);
3) put merge command outside the macro as "merge data0 data11 data12...", so that it merges all together and not one by one (not sure, but maybe it saves time).
Thanks, Reeza! I'm very new to macro so I'm taking baby steps in learning. I got it and this is what I did to solve it.
%macro combine();
%do i=1 %to 30;
proc sort data=a.cell_&i; by cell_id; run;
proc sort data=a.well; by cell_id; run;
data a.well_cell;
merge a.well a.cell_1- a.cell_30; by cell_id; run;
%end;
%mend;
%merge;
So, if I have 50 datasets with different names do I have to specify all the names in the merge command?
Technically no, but Alex question is different than yours. It's useful to either start a new question or branch of an old question when it's similar but not quite the same
I also don't think your code is quite correct:
The following is along the lines of what you should have:
proc sort data=a.well; by cell_id; run;
%macro combine();
%do i=1 %to 30;
proc sort data=a.cell_&i; by cell_id; run;
%end;
%mend;
%combine;
data a.well_cell;
merge a.well a.cell_1- a.cell_30;
by cell_id;
run;
Hi Reeza,
I had similar issue as Alex and I used macro to merge 30 sas datasets. I used similar code but that would output only the last dataset instead of merging all datasets together. This is the code I used
%macro combine();
%do i=1 %to 30;
proc sort data=a.cell_&i; by cell_id; run;
proc sort data=a.well; by cell_id; run;
data a.well_cell;
merge a.well a.cell_&i; cell_id; run;
%end;
%mend;
%merge;
So, a.well_cell would contain only cell_30 data. And if I do i= 1 %to 29 it would output cell_29 data. Some how I'm not able to merge all datasets.
Thanks,
Esita
Note Alex's comment in his last post.
In this step you are only merging the original one each time to the last one, rather than the last MERGED data set.
proc sort data=a.well; by cell_id; run;
data a.well_cell;
merge a.well a.cell_&i; cell_id; run;
If you can't figure out how to fix it beyond this let me know and I'll post a more detailed solution,.
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.