DATA Step, Macro, Functions and more

Loop across several datasets

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Loop across several datasets

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, VAR12VAR60); to create one additional variable being named as d11, d12d60 (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.


Accepted Solutions
Solution
‎10-06-2014 04:47 PM
Super User
Posts: 17,824

Re: Loop across several datasets

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;

View solution in original post


All Replies
Super User
Posts: 17,824

Re: Loop across several datasets

Post sample data in and sample expected output.

New Contributor
Posts: 3

Re: Loop across several datasets

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?

Solution
‎10-06-2014 04:47 PM
Super User
Posts: 17,824

Re: Loop across several datasets

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;

New Contributor
Posts: 3

Re: Loop across several datasets

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

Contributor
Posts: 25

Re: Loop across several datasets

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?

Super User
Posts: 17,824

Re: Loop across several datasets

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 Smiley Happy

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;

Contributor
Posts: 25

Re: Loop across several datasets

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

Super User
Posts: 17,824

Re: Loop across several datasets

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 380 views
  • 0 likes
  • 3 in conversation