topic Re: Merge datasets in SAS Programming

Merge datasets

Kavishah — Fri, 24 Mar 2017 15:41:57 GMT

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

Re: Merge datasets

ballardw — Fri, 24 Mar 2017 16:38:14 GMT

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.

Re: Merge datasets

Kavishah — Fri, 24 Mar 2017 18:06:45 GMT

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?

Re: Merge datasets

Astounding — Fri, 24 Mar 2017 18:28:29 GMT

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;

Re: Merge datasets

Kavishah — Fri, 24 Mar 2017 18:43:38 GMT

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
;

Re: Merge datasets

Kavishah — Fri, 24 Mar 2017 18:43:59 GMT

this is the data I have

Re: Merge datasets

Kavishah — Fri, 24 Mar 2017 18:44:32 GMT

Common variable is PTID

Re: Merge datasets

art297 — Fri, 24 Mar 2017 18:52:58 GMT

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

Art, CEO, AnalystFinder.com

Re: Merge datasets

Kavishah — Fri, 24 Mar 2017 18:55:32 GMT

A table which has PTID's which are common to exactly 2 datasets out of the three.

Re: Merge datasets

art297 — Fri, 24 Mar 2017 18:58:10 GMT

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

Art, CEO, AnalystFinder.com

Re: Merge datasets

Kavishah — Fri, 24 Mar 2017 19:09:35 GMT

That code does not give me the write output.

Re: Merge datasets

Kavishah — Fri, 24 Mar 2017 19:12:28 GMT

right*

Re: Merge datasets

art297 — Fri, 24 Mar 2017 19:12:43 GMT

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

Re: Merge datasets

Kavishah — Fri, 24 Mar 2017 19:26:59 GMT

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;

Re: Merge datasets

art297 — Fri, 24 Mar 2017 19:36:20 GMT

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

Re: Merge datasets

Astounding — Fri, 24 Mar 2017 20:08:11 GMT

Each PROC SORT requires its own BY statement.