DATA Step, Macro, Functions and more

Merge datasets

Reply
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: 10,466

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: 5,071

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

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
PROC Star
Posts: 7,356

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.
PROC Star
Posts: 7,356

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

Re: Merge datasets

right*
PROC Star
Posts: 7,356

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;
PROC Star
Posts: 7,356

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

Ask a Question
Discussion stats
  • 15 replies
  • 152 views
  • 0 likes
  • 4 in conversation