BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BTAinRVA
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
FloT
Fluorite | Level 6

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

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
BTAinRVA
Quartz | Level 8

RW9,

 

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

 

Brian

FloT
Fluorite | Level 6

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;

 

BTAinRVA
Quartz | Level 8

FLoT,

 

Thanks!

 

Brian

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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