Merging the data sets using macro code

[YoYo]

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!

[LinusH]

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

[YoYo]

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

[Astounding]

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.

[YoYo]

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!

[Astounding · Accepted solution]

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.

[YoYo]

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

[JohnW_]

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!

[YoYo]

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!

[YoYo]

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

[JohnW_]

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!

[YoYo]

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.