## Merge datasets

Occasional Contributor
Posts: 9

# Merge datasets

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

Super User
Posts: 13,941

## Re: Merge datasets

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.

Occasional Contributor
Posts: 9

## Re: Merge datasets

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?
Super User
Posts: 6,934

## Re: Merge datasets

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;

Occasional Contributor
Posts: 9

## Re: Merge datasets

Posted in reply to Astounding
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
;
Occasional Contributor
Posts: 9

## Re: Merge datasets

this is the data I have
Occasional Contributor
Posts: 9

## Re: Merge datasets

Common variable is PTID
Super User
Posts: 8,216

## Re: Merge datasets

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

Art, CEO, AnalystFinder.com

Occasional Contributor
Posts: 9

## Re: Merge datasets

A table which has PTID's which are common to exactly 2 datasets out of the three.
Super User
Posts: 8,216

## Re: Merge datasets

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

Art, CEO, AnalystFinder.com

Occasional Contributor
Posts: 9

## Re: Merge datasets

That code does not give me the write output.
Occasional Contributor
Posts: 9

right*
Super User
Posts: 8,216

## Re: Merge datasets

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

Occasional Contributor
Posts: 9

## Re: Merge datasets

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;
Super User
Posts: 8,216

## Re: Merge datasets

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

Discussion stats
• 15 replies
• 178 views
• 0 likes
• 4 in conversation