DATA Step, Macro, Functions and more

Merge Files on Two Variables

Accepted Solution Solved
Reply
Regular Contributor
Posts: 151
Accepted Solution

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;

 

View solution in original post


All Replies
Super User
Super User
Posts: 9,866

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;
Regular Contributor
Posts: 151

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;

 

Regular Contributor
Posts: 151

Re: Merge Files on Two Variables

FLoT,

 

Thanks!

 

Brian

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 119 views
  • 0 likes
  • 3 in conversation