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
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.
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.
Thanks Tommywhosc. That helped!
You can merge more than one file at a time, any reason to not do so?
Hi Reeza,
The BY variable is different for the datasets. So, cannot merge all at once.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.