patient height weight visit_date BMI
1001 5 130 2012-01-05 130(5*5)
1001 145 2012-06-01 145(5*5)
1001 5.2 148 2013-01-02 148(5.2*5.2)
1002 5.1 130 2012-01-05 130(5.1*5.1)
1002 145 2012-06-01 145(5.1*5.1)
1002 148 2013-01-02 148(5.1*5.1)
1003 130 2012-01-05
1003 5.3 2012-06-01
1003 148 2013-01-02 148(5.3*5.3)
1004 130 2012-01-05
1004 2012-06-01
1004 148 2013-01-02
1004 5.5 2013-01-02
If weight is null no BMI
If height is null, take height from earliest record
How to get BMI's for patients the following way?
For patient 1001 the BMI in second record is calculated by taking height from previous visit.
For 1002 the BMi is recorded from height in first record
For 1003 height is from second record.
For 1004 NO BMI
data testdata;
input patient $1-4 height $16-20 weight $34-37 date $43-53 BMI $58-70;
cards;
1001 5 130 2012-01-05 130(5*5)
1001 145 2012-06-01 145(5*5)
1001 5.2 148 2013-01-02 148(5.2*5.2)
1002 5.1 130 2012-01-05 130(5.1*5.1)
1002 145 2012-06-01 145(5.1*5.1)
1002 148 2013-01-02 148(5.1*5.1)
1003 130 2012-01-05
1003 5.3 2012-06-01
1003 148 2013-01-02 148(5.3*5.3)
1004 130 2012-01-05
1004 2012-06-01
1004 148 2013-01-02
1004 5.5 2013-01-02
;
run;
data t2;
set testdata;
by patient date;
retain new_ht;
if first.patient and height ne '' then new_ht=height;
run;
BMI usually uses weight in kg/(height in m squared)
I doubt if your 5 for height is in meters and the weights look more likely to be pounds as well.
Try this...
Build a lookup table where height is not missing with the earliest date. The data step merge will populate height (only when missing).
proc sql;
create table hgt as
select patient, height, visit_date from HAVE
where height is not missing
group by patient
having min(visit_dt)=visit_dt;
quit;
proc sort data=have;
by id;
proc sort data=hgt;
by id;
run;
data want;
merge have(in=a) hgt(in=b rename=(height=heightb));
by id;
if a;
if height=. then height=heightb;
drop heightb;
run;
Hope this helps.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.