CODEWITHSUNDEEP
sas
Sandra Guerrero
Sandra Guerrero

Reputation: 323

Merge more than two datasets don't work properly sas

I have been merge more than two datasets in sas, but the output isn't I expected. The original dataset is:

data test;
input id days value date:date9.;
format date date9.;
datalines;
128330 150 3903053 01jul2016
;
run;

The bases to merge:

data base1;
input id days1 value1 date1:date9.;
format date1 date9.;
datalines;
128330 0 3849050 01jun2015
128330 0 3827305 01jul2015
128330 0 3822779 01aug2015
128330 30 3771383 01feb2015
128330 0 3756117 01jan2015
128330 0 . 01nov2015
128330 0 3818253 01sep2015
128330 0 . 01oct2015
128330 90 3668595 01may2015
128330 60 3705683 01apr2015
128330 30 3690417 01mar2015
128330 0 3802639 01dec2015
;
run;

data base2;
input id days1 value1 date1:ddmmyy10.;
format date1 date9.;
datalines;
128330 0 3805129 1/01/2016
128330 0 3887603 1/02/2016
128330 30 3890093 1/03/2016
128330 60 3892583 1/04/2016
128330 90 3896073 1/05/2016
128330 120 3899563 1/06/2016
128330 150 3903053 1/07/2016
128330 180 3906543 1/08/2016
128330 210 3906543 1/09/2016
128330 240 3906543 1/10/2016
128330 270 3906543 1/11/2016
128330 300 3906543 1/12/2016
;
run;

data base3;
input id days1 value1 date1:ddmmyy10.;
format date1 date9.;
datalines;
128330 330 3906543 1/01/2017
128330 360 3906543 1/02/2017
128330 390 3906543 1/03/2017
128330 420 3906543 1/04/2017
128330 450 3906543 1/05/2017
128330 480 3906543 1/06/2017
128330 510 3906543 1/07/2017
128330 540 3906543 1/08/2017
128330 570 3906543 1/09/2017
128330 600 3906543 1/10/2017
128330 630 3906543 1/11/2017
;
run;

Merge

data merge1;
merge test(in=info) base1 base2 base3;
by id;
if info;
run;

The output just twelve rows, that ones belong in base3: enter image description here

I expected:

enter image description here

I need a merge, since I work bases with thousands records, and I need merge datasets by id. Thanks for help me.

Upvotes: 0

Views: 182

Answers (1)

Tom
Tom

Reputation: 51566

You cannot MERGE those datasets since you have more than one dataset with multiple observations per ID. In that case the MERGE will match the observations in order and values will get overwritten. Note that if your BASEx datasets each had distinct sets of ID values such that you never had observations from one BASEx dataset merging with observations from a different BASEx then it could actually work.

So you have three datasets in one format that each could have multiple observations per id that you want to first concatenate and then merge with the other dataset that has only one observation per id.

data base ;
  set base1-base3 ;
  by id;
run;

data want ;
  merge test base ;
  by id;
run;

You could make the first data step generate a view instead of a dataset if you are worried about disk space.

data base / view=base ;
  set base1-base3 ;
  by id;
run;

Upvotes: 1

Related Questions