Loop across several datasets

[AlexAlex]

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 have fifty datasets, named as data11, data12, … data60. All begin with the same letters (data) and end up with consecutive numbers starting from 11.

I need to make the following steps in the loop: 

• To open each of them consecutively; to keep just two variables (variable names are ID and VAR*, where VAR* all end with the same numbers as in the respective dataset names: VAR11, VAR12…VAR60); to create one additional variable being named as d11, d12…d60 (in line with the numbers in respective dataset names) and to make it being equal 1.
• Then I need to merge them all to a dataset named data0 by the variable ID.

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.

[Reeza]

Post sample data in and sample expected output.

[AlexAlex]

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?

[Reeza · Accepted solution]

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;

[AlexAlex]

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

[esita]

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?

[Reeza]

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;

[esita]

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

[Reeza]

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