Calculate BMI Dataset

[serena13lee]

I have a dataset that looks like the following for many patients

     Patient      VisitDate     Value     Unit     Type
      A            Jan12019        1           m       Height
      A            Jan12019       50         kg       Weight
      A            Jan52019        2           m       Height
      A            Jan52019       55         kg       Weight

I am trying to add BMI to get the following dataset for those patients:

    Patient     VisitDate      Value     Unit      Type
     A            Jan12019         1          m       Height
     A           Jan12019         50         kg      Weight
     A            Jan52019         2          m       Height
     A           Jan52019         55         kg      Weight
     A           Jan12019     50/1^2    kg/m2     BMI
     A           Jan52019     55/2^2    kg/m2     BMI

I am trying to understand the logic beyond programming this in SAS. Below is what I have so far in psuedo code:

Create BMI data set. For each patient on each visit date, value = weight/height^2. Type = BMI. Unit = kg/m2. Keep Patient, VisitDate info.

[ballardw]

Some questions you should answer before going any further:

Do you have any units other than kg and M for weight and height? If so you will need to get everything into the same units.

Next is you need to get the height and weight measure on the same record to have both variables available to do the calculation.

That is often a transpose something similar to:

proc sort data=have;
   by patient visitdate;
run;

proc transpose data=have out=need;
by patient visitdate;
id type unit;
var value;
run;

You would use the NEED data set just built in another data step to do the actual bmi calculation. If you have more than one set of measurement units for height and weight though you will want to get Height in m and weigth in KG from them.

Then

bmi = weightkg / heightm**2;

the **2 is one way to do exponentiation.

I don't know if your date values got scrambled on pasting here but that particular format is going to be a pain to deal with.

You will usually want to get your dates into a SAS date value  so you can use the functions and formats to manipulate the dates but the values you show are going to be a bit obnoxious to read into SAS dates.

[serena13lee]

Hi and thanks for your response. To answer your concerns, I do not have any other units other than kg and m. I am using dummy dates since I am more concerned about logic. I can change that to a SAS date.

[Jagadishkatam · Accepted solution]

data have;
input Patient $     VisitDate $   Value     Unit  $   Type $;
cards;
A Jan12019 1 m Height
A Jan12019 50 kg Weight
A Jan52019 2 m Height
A Jan52019 55 kg Weight
;

proc sort data=have;
by patient VisitDate;
run;

data want;
set have;
by patient VisitDate;
retain height_;
if first.VisitDate then height_=.;
if type='Height' then height_=Value;
if last.VisitDate then bmi=value/height_**2;
output;
if bmi ne . then do;
type='BMI';
value=bmi;
unit='kg/m2';
output;
end;
drop height_ bmi;
run;