Help using Base SAS procedures

merge data with unequal observations

Reply
N/A
Posts: 0

merge data with unequal observations

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 .
.
.
.
.
.
.
..
.
Super Contributor
Super Contributor
Posts: 3,174

Re: merge data with unequal observations

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.
N/A
Posts: 0

Re: merge data with unequal observations

> 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
Super Contributor
Super Contributor
Posts: 3,174

Re: merge data with unequal observations

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.
Valued Guide
Posts: 2,174

Re: merge data with unequal observations

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 ;
Ask a Question
Discussion stats
  • 4 replies
  • 108 views
  • 0 likes
  • 3 in conversation