DATA Step, Macro, Functions and more

Merging the data sets using macro code

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Merging the data sets using macro code

Hey,

I am new to macro code. Here is my question: have data1- data60 and I want to merge each one of them with dataB based on a common variable "ID". How can  I do that? Thank you!


Accepted Solutions
Solution
‎01-30-2014 03:56 PM
Super User
Posts: 5,513

Re: Merging the data sets using macro code

In that case, JohnW's macro should suit you pretty well.  I would add just one statement:

%macro MergeData (n=);

   proc sort data=dataB; by ID; run;

   %local i;

   %do i=1 %to &n;

      proc sort data=data&i; by ID; run;

      data merged&i; merge data&i dataB; by ID; run;

   %end;

%mend MergeData;

To actually use the macro, you would add later in the same program:

%MergeData (n=60)

Just so you can better see what the result is, also add this statement before using the macro:

options MPRINT;

That should get you started on the right path.  One way to learn about macros would be to take the working macro and figure out how it works.  Good luck.

View solution in original post


All Replies
Super User
Posts: 5,432

Re: Merging the data sets using macro code

%DO

If you don't what do with it, I suggest that you take some kind of class, or at least try to go trough the documentation, which has some samples as well.

Data never sleeps
Occasional Contributor
Posts: 7

Re: Merging the data sets using macro code

Suregoing trough the documentation with samples is something I want to try first. Do you have any suggestions about books or other learning materials?

Super User
Posts: 5,513

Re: Merging the data sets using macro code

If you want a detailed answer, you will have to supply a little more information.  Are you original data sets already sorted by ID?  What names would you like to use for the final data sets, after merging?  The programming will be relatively easy once you have supplied the information.

Good luck.

Occasional Contributor
Posts: 7

Re: Merging the data sets using macro code

Posted in reply to Astounding

Hi Astounding,

Sorry about not providing enough info. The original data sets are not sorted yet. And for final results, they can be any name. Let's say merged1-merged60...Thank you!

Solution
‎01-30-2014 03:56 PM
Super User
Posts: 5,513

Re: Merging the data sets using macro code

In that case, JohnW's macro should suit you pretty well.  I would add just one statement:

%macro MergeData (n=);

   proc sort data=dataB; by ID; run;

   %local i;

   %do i=1 %to &n;

      proc sort data=data&i; by ID; run;

      data merged&i; merge data&i dataB; by ID; run;

   %end;

%mend MergeData;

To actually use the macro, you would add later in the same program:

%MergeData (n=60)

Just so you can better see what the result is, also add this statement before using the macro:

options MPRINT;

That should get you started on the right path.  One way to learn about macros would be to take the working macro and figure out how it works.  Good luck.

Occasional Contributor
Posts: 7

Re: Merging the data sets using macro code

Posted in reply to Astounding

your code works so great! Thank you for your suggestion and surely I'll learn by playing with Macro! :smileycool:

Occasional Contributor
Posts: 18

Re: Merging the data sets using macro code

I find when writing a macro that it helps to do what I want normally first, then "macronize" it.  For example here to merge the first table your code is

PROC SORT DATA=dataB;
    BY ID;
RUN;
PROC SORT DATA=data1;
    BY ID;
RUN;
DATA NewData1;
    MERGE Data1 dataB;
    BY ID;
RUN;


Now all you need to do is loop through the last two steps and change the number each time using %DO as suggested above (all within %MACRO and %MEND tags).  So it would look something like this:

%MACRO MergeData(n=60);
    PROC SORT DATA=dataB; BY ID; RUN;
   
%DO i = 1 %TO &n;
        PROC SORT DATA=data&i; BY ID; RUN;
        DATA NewData&i; MERGE Data&i dataB; BY ID; RUN;
   
%END;
%MEND;

You could improve on the above by allowing a prefix entry for the datasets and/or an input for the table to be merged (here, dataB).  If you had only two datasets, you could call the previous macro like this:



%
MergeData(n=2);

Have fun learning macros, they can greatly increase your efficiency!
Occasional Contributor
Posts: 7

Re: Merging the data sets using macro code

Hi John,

The codes look so good. I will try to see how it works. Thank you so much! Copy&paste always works for me when I only have less than 10 data sets... when it comes to 60... Oh, my gosh, I need macros!!!! I will try my best to learn it. Thank you!

Occasional Contributor
Posts: 7

Re: Merging the data sets using macro code

I like the way how you "macronize" it. It is fun when you get things done in a more efficient way, thank youSmiley Happy

Occasional Contributor
Posts: 18

Re: Merging the data sets using macro code

I guess alternatively you could combine all of the DATAi sets into one (especially if they have similar columns), keep track of which is which with the IN= data set option, merge, then split them back apart.  Pick your poison!

Occasional Contributor
Posts: 7

Re: Merging the data sets using macro code

that's a good suggestion. But there are duplicated ID in data1-data60 with different values of other variables, so I guess combining data1-data60 and then merging with dataB might be problematic.  This reminds me that my dataB actually have duplicated IDs but my data1-data60 only have unique ID in each one of them. What I want is do a one-to-many match for each datai (i=1 to 60) and dataB merge.

🔒 This topic is solved and locked.

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

Discussion stats
  • 11 replies
  • 3362 views
  • 5 likes
  • 4 in conversation