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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.