Repetitive Macro Question

[sasmaverick]

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

[Tommywhosc · Accepted solution]

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]

Thanks Tommywhosc. That helped!

[Reeza]

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

[sasmaverick]

Hi Reeza,

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