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

I have the following Macro:

/* Sort by Match Var 4 */

%macro sort(file);

proc sort data=&FILE nodupkey;

by Match_Var_4;

run;

%mend sort;


%sort(FILE1_2);

%sort(FILE2_2);

%sort(FILE3_2);

%sort(FILE4_2);

%sort(FILE5_2);

/*Merge by Match Var 4*/

%macro merge(fnum1,fnum2,file1,file2);

data Name_State_&FNUM1._&FNUM2.;

merge &FILE1.(in=ina) &FILE2.(in=inb);

by Match_Var_4;

if ina and inb then Name_State_Flag_&FNUM1._&FNUM2.=1;

else Name_State_Flag_&FNUM1._&FNUM2.=0;

if Name_State_Flag_&FNUM1._&FNUM2.=1;

run;

%mend merge;

%merge(1,2,FILE1_2,FILE2_2);

%merge(2,3,FILE2_2,FILE3_2);

%merge(3,4,FILE3_2,FILE4_2);

%merge(4,5,FILE4_2,FILE5_2);

My issue with the above is that even though I have used macro, I still need to repeat the above steps lot of times. Eg. In the first step, I need to sort by Match_Var_1 and create the dataset Name_City_&FNUM1._&FNUM2. (second step). Then sort by Match_Var_2 and create the dataset Name_Address_&FNUM1._&FNUM2 and so on.


What approach can I use? Thanks for the help

1 ACCEPTED SOLUTION

Accepted Solutions
Tommywhosc
Obsidian | Level 7

One approach.. wrap all the coding in one macro, using %DO loops to handle the sorting, merging:

%macro doit(DSN,MV);
%do I = 1 %to 5;
PROC SORT DATA=FILE&I._2 nodupkey; by MATCH_VAR_&MV; run;
%end;

%do J = 1 %to 4;
%let K = %eval(&j + 1);
DATA &DSN&j._&k ;
MERGE  FILE&J._2(in=a)
   FILE&K._2(in=b);
   by MATCH_VAR_&MV;
IF a & b;

&DSN&j._&k = 1;
run;
%end;

%mend DOIT;
%DOIT(NAME_CITY_,1)
%DOIT(NAME_ADDRESS_,2)  etc...

This (untested) code should get you started.

A concern: Are you sure you want to NODUPKEY on those data sets, 4 times? That is, might you lose some obs on dup'd MATCH_VAR_1 that you'll want when you sort, merge on MATCH_VAR_2?

Hope this helps.

View solution in original post

4 REPLIES 4
Tommywhosc
Obsidian | Level 7

One approach.. wrap all the coding in one macro, using %DO loops to handle the sorting, merging:

%macro doit(DSN,MV);
%do I = 1 %to 5;
PROC SORT DATA=FILE&I._2 nodupkey; by MATCH_VAR_&MV; run;
%end;

%do J = 1 %to 4;
%let K = %eval(&j + 1);
DATA &DSN&j._&k ;
MERGE  FILE&J._2(in=a)
   FILE&K._2(in=b);
   by MATCH_VAR_&MV;
IF a & b;

&DSN&j._&k = 1;
run;
%end;

%mend DOIT;
%DOIT(NAME_CITY_,1)
%DOIT(NAME_ADDRESS_,2)  etc...

This (untested) code should get you started.

A concern: Are you sure you want to NODUPKEY on those data sets, 4 times? That is, might you lose some obs on dup'd MATCH_VAR_1 that you'll want when you sort, merge on MATCH_VAR_2?

Hope this helps.

sasmaverick
Obsidian | Level 7

Thanks Tommywhosc. That helped!

Reeza
Super User

You can merge more than one file at a time, any reason to not do so?

sasmaverick
Obsidian | Level 7

Hi Reeza,

The BY variable is different for the datasets. So, cannot merge all at once.

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
  • 4 replies
  • 901 views
  • 0 likes
  • 3 in conversation