BookmarkSubscribeRSS Feed
Kavishah
Calcite | Level 5

how do we merge 3 datasets in sas by an observation which is found in exactly 2 of the 3 data sets

15 REPLIES 15
ballardw
Super User

Please expand on what you have and what you need. There are many ways of combining data sets and if there are any rules you need to provide them. Example data sets to represent EACH of your 3 sets and the desired result. The data sets should be in the form of data step code with enough variables and records to demonstrate the cases you expect to encounter in the full problem.

 

Kavishah
Calcite | Level 5
I have 3 datasets with different variables. I have to merge those 3 datasets with a common variable. And the observations for this variable should be present in exactly 2 datasets out of the 3. Does this help to provide a solution?
Astounding
PROC Star

It doesn't really help.  You haven't given examples of what the data sets would look like.

 

Here is a guess that assumes your data sets are already sorted:

 

data want;

merge d1 (in=in1) d2 (in=in2) d3 (in=in3);

by common_variable;

if in1 + in2 + in3 = 2;

run;

Kavishah
Calcite | Level 5
data ds1 ;
input PTID MRN $9. ;
datalines ;
1 514779-MT
2 M200-8X
3 999-M
4 79750
;
data ds2 ;
input PTID WBC ;
datalines ;
4 450
3 340
2 .
5 870
6 .
;
data ds3 ;
input PTID WEIGHT ;
datalines ;
2 157
1 163
4 .
6 130
;
Kavishah
Calcite | Level 5
this is the data I have
Kavishah
Calcite | Level 5
Common variable is PTID
art297
Opal | Level 21

Given those examples, what do you want the resulting file to look like?

 

Art, CEO, AnalystFinder.com

 

Kavishah
Calcite | Level 5
A table which has PTID's which are common to exactly 2 datasets out of the three.
art297
Opal | Level 21

Then just sort your 3 files and use @Astounding's suggested code

 

Art, CEO, AnalystFinder.com

Kavishah
Calcite | Level 5
That code does not give me the write output.
Kavishah
Calcite | Level 5
right*
art297
Opal | Level 21

That's why I asked you what you want the resulting file to look like. Rather than simply repeating your description, show the desired resulting file in the form of a datastep.

 

Art, CEO, AnalystFinder.com

 

Kavishah
Calcite | Level 5
proc sort data=ds1;
proc sort data=ds2;
proc sort data=ds3;
by PTID;
run;
data ds4;
merge d1 (in=inds1) d2 (in=inds2) d3 (in=inds3);
by PTID;
if inds1 + inds2 + inds3 = 2;
MRNnew=compress(MRN,'7 9 'M'');
run;
title "Merged dataset";
proc print data=ds4;
run;
art297
Opal | Level 21

I have NO idea what you are trying to explain with your incorrect use of the compress function, however, you use proc sorts with no by variable and try to merge 3 files that are different than those which you said you have. So, other than the new variable you are trying to create, does the following do the merge you wanted?:

 

proc sort data=ds1;
  by ptid;
run;

proc sort data=ds2;
  by ptid;
run;

proc sort data=ds3;
  by ptid;
run;

data want;
  merge ds1a (in=in1) ds2a (in=in2) ds3a (in=in3);
  by ptid;
  if in1 + in2 + in3 = 2;
run;

Art, CEO, AnalystFinder.com

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
  • 15 replies
  • 1266 views
  • 0 likes
  • 4 in conversation