topic Merge Files on Two Variables in SAS Programming

Merge Files on Two Variables

BTAinRVA — Tue, 24 Apr 2018 12:33:04 GMT

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

Re: Merge Files on Two Variables

RW9 — Tue, 24 Apr 2018 12:45:56 GMT

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;

Re: Merge Files on Two Variables

FloT — Tue, 24 Apr 2018 13:17:44 GMT

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;

Re: Merge Files on Two Variables

BTAinRVA — Wed, 25 Apr 2018 01:42:11 GMT

RW9,

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

Brian

Re: Merge Files on Two Variables

BTAinRVA — Wed, 25 Apr 2018 01:43:13 GMT

FLoT,

Thanks!

Brian