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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.