Good Morning!
I want to merge 2 files (say a and b) (based on 2 variables) and create the
following data sets:
1 data set with observations that merged completely (2 variables matched)
1 data set with the observations that did not merge from a
1 data set that did not merge from b
Thanks,
Brian
Hello,
You can try this code:
/* test data */
data a;
infile datalines delimiter="|";
length var1 $10 var2 $10 var3 $10;
input var1 $ var2 $ var3 $;
datalines;
ab1|text1|text2
a2|text3|text4
ab3|text5|text6
ab4|text5|text8
;
run;
data b;
infile datalines delimiter="|";
length var1 $10 var2 $10 var4 $10;
input var1 $ var2 $ var4 $;
datalines;
ab1|text1|text11
b2|text9|text10
ab3|text5|text12
ab4|text5|text13
;
run;
/* we sort datasets */
proc sort data=a;
by var1 var2;
run;
proc sort data=b;
by var1 var2;
run;
/* then we merge them */
data aorb;
merge a b;
by var1 var2;
run;
data aandb;
merge a (in=a) b (in=b);
by var1 var2;
if a and b;
run;
data aonly;
merge a (in=a) b (in=b);
by var1 var2;
if a and not b;
run;
data bonly;
merge a (in=a) b (in=b);
by var1 var2;
if b and not a;
run;
You have written the code already. Something like:
data both nota notb; merge file1 file2; by some_variables; if a and b then output both; else if b then output nota; else if a then output notb; run;
RW9,
Thanks for the reply! I was thinking it was something like that.
Brian
Hello,
You can try this code:
/* test data */
data a;
infile datalines delimiter="|";
length var1 $10 var2 $10 var3 $10;
input var1 $ var2 $ var3 $;
datalines;
ab1|text1|text2
a2|text3|text4
ab3|text5|text6
ab4|text5|text8
;
run;
data b;
infile datalines delimiter="|";
length var1 $10 var2 $10 var4 $10;
input var1 $ var2 $ var4 $;
datalines;
ab1|text1|text11
b2|text9|text10
ab3|text5|text12
ab4|text5|text13
;
run;
/* we sort datasets */
proc sort data=a;
by var1 var2;
run;
proc sort data=b;
by var1 var2;
run;
/* then we merge them */
data aorb;
merge a b;
by var1 var2;
run;
data aandb;
merge a (in=a) b (in=b);
by var1 var2;
if a and b;
run;
data aonly;
merge a (in=a) b (in=b);
by var1 var2;
if a and not b;
run;
data bonly;
merge a (in=a) b (in=b);
by var1 var2;
if b and not a;
run;
FLoT,
Thanks!
Brian
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.