topic Re: Previous or Current recorded height in SAS Programming

Previous or Current recorded height

SASPhile — Thu, 05 Oct 2017 19:39:45 GMT

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

Re: Previous or Current recorded height

error_prone — Thu, 05 Oct 2017 20:09:30 GMT

Please post the data as data step and show the code you have already.

Idea: use a hash object.

Re: Previous or Current recorded height

SASPhile — Thu, 05 Oct 2017 20:36:52 GMT

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;

Re: Previous or Current recorded height

ballardw — Thu, 05 Oct 2017 21:59:20 GMT

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.

Re: Previous or Current recorded height

ShiroAmada — Fri, 06 Oct 2017 01:38:01 GMT

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.