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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.