merge data with unequal observations

[deleted_user]

Hi,

I am trying to add AGE and BMI data in data1 to the data2 based on #ID. Since the two data sets have different No. of observations, I have trouble to add them. Could some one help me with this?

Thanks

DATA1
#ID AGE BMI
5001 41 22.7
5001 41 22.7
5001 41 22.7
5003 50 24.5
5003 50 24.5
5003 50 24.5
5003 50 24.5
.
.
.
.
.
.


DATA2
#ID TIME AMT
5001 0 0
50010 0
5001 0 0
5001 0 0
5001 0 0
5001 0.01 6800
5001 0.01 6800
5001 0.01 6800
5001 0.083 0
5003 0 .
5003 0.01 8900
5003 0.083 .
5003 0.167 .
5003 0.25 .
5003 0.333 .
5003 0.5 .
5003 0.75 .
.
.
.
.
.
.
..
.

[sbb]

You should explain what the OUTPUT side (expected) is to appear. The MERGE process can use a BY statement, listing the merge variable values.

Scott Barry
SBBWorks, Inc.

[deleted_user]

> You should explain what the OUTPUT side (expected) is
> to appear. The MERGE process can use a BY statement,
> listing the merge variable values.
>
> Scott Barry
> SBBWorks, Inc.

sorry about that.

The output should be like following:
#ID TIME AMT AGE BMI
5001 0 0 41 22.7
5001 0 0 41 22.7
5001 0 0 41 22.7
5001 0 0 41 22.7
5001 0 0 41 22.7
5001 0.01 6800 41 22.7
5001 0.01 6800 41 22.7
5001 0.01 6800 41 22.7
5001 0.083 0 41 22.7
5003 0 . 50 24.5
5003 0.01 8900 50 24.5
5003 0.083 . 50 24.5
5003 0.167 . 50 24.5
5003 0.25 . 50 24.5
5003 0.333 . 50 24.5
5003 0.5 . 50 24.5
5003 0.75 . 50 24.5

[sbb]

As was suggested, with your two files loaded as SAS datasets, sort and use a DATA step MERGE with a BY statement.

Scott Barry
SBBWorks, Inc.

[Peter_C]

sounds like : take one case for each ID of AGE and BMI (hopefully there would only be one unique pair of values for one ID ) apply in 1:n merge with data2 by ID
proc sql _method ;
create table joined as
select a.*, b.age, b.bmi
from data2 as a
join (select distinct id, age, bmi from data1 ) as b
on a.id=b.id
;
quit ;
proc print ;
run ;