BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AlexAlex
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

8 REPLIES 8
Reeza
Super User

Post sample data in and sample expected output.

AlexAlex
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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
Calcite | Level 5

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
Super User

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;

esita
Calcite | Level 5

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
Super User

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

SAS Innovate 2025: Call for Content

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!

Submit your idea!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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