## Merge Files on Two Variables

Solved
Frequent Contributor
Posts: 140

# Merge Files on Two Variables

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

Accepted Solutions
Solution
‎04-24-2018 09:42 PM
Occasional Contributor
Posts: 8

## Re: Merge Files on Two Variables

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;`

All Replies
Super User
Posts: 9,227

## Re: Merge Files on Two Variables

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;```
Frequent Contributor
Posts: 140

## Re: Merge Files on Two Variables

RW9,

Thanks for the reply! I was thinking it was something like that.

Brian

Solution
‎04-24-2018 09:42 PM
Occasional Contributor
Posts: 8

## Re: Merge Files on Two Variables

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;`

Frequent Contributor
Posts: 140

## Re: Merge Files on Two Variables

FLoT,

Thanks!

Brian

☑ This topic is solved.