topic Re: merge data with unequal observations in SAS Procedures

merge data with unequal observations

deleted_user — Sat, 20 Jun 2009 00:52:06 GMT

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 .
.
.
.
.
.
.
..
.

Re: merge data with unequal observations

sbb — Sat, 20 Jun 2009 01:25:12 GMT

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.

Re: merge data with unequal observations

deleted_user — Sat, 20 Jun 2009 01:46:23 GMT

> 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

Re: merge data with unequal observations

sbb — Sat, 20 Jun 2009 13:07:11 GMT

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.

Re: merge data with unequal observations

Peter_C — Sat, 20 Jun 2009 16:58:48 GMT

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 ;