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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

11 REPLIES 11
LinusH
Tourmaline | Level 20

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

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

Astounding
PROC Star

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

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
PROC Star

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

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

JohnW_
Calcite | Level 5

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

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

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

JohnW_
Calcite | Level 5

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

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 11 replies
  • 8947 views
  • 5 likes
  • 4 in conversation