how to calculate by using value from another observation

[HitmonTran]

Hello,

I have 9 records that contain missing SI values (in 10^9/L - LBSTRESU) for paramcd= 'EOS' test while raw values (in % unit- LBORRES) are provided,  I would need to add the following derivation to  convert values to the SI units in my analysis dataset (variable AVAL ) to handle the missing values while raw values (in % unit) are provided:

EOS (SI unit, 10^9/L, Column AVAL) = WBC (10^9/L, Column AVAL from WBC test at the same time/visit - AVISIT) x EOS in percentage % (Column LBORRES from EOS test at the same time/visit - AVISIT) , where WBC is the lab result from the same patient at the same time/visit (i.e., both WBC and EOS are collected at the same time - AVISIT).

current data (green are missing - need calculated values):

want (calculated values are color-coded. Previously green colored - eg 4.5 * .04 = 0.18)

attached is the data in excel format (sheet 3)

Thank you very much!

[mkeintz]

In the absence of sample data in the form of a working DATA step, here is untested code.

This code assumes every EOS record is preceded by a matching WBC record:

data want;
  set have;
  array var     aval    anrlo     anrhi;
  array pct     lborres lbornrlo  lborrnrhi;

  do over var;
    var=ifn(param='EOS',sum(0,lag(var))*pct/100,var);
  end;
run;

The reason for the "sum(0,lag(var))" expression is to avoid an error message with the first observation, for which lag(var) is missing, and therefore would cause a missing value result when multiplied by "pct/100").

You could test for matching WBC record with something like:

data want;
  set have;
  array var     aval    anrlo     anrhi;
  array pct     lborres lbornrlo  lborrnrhi;

  do over var;
    var=ifn(param='EOS' and lag(param)='WBC' 
              and subjid=lag(subjid) and avisit=lag(avisit)
           ,sum(0,lag(var))*pct/100
           ,var);
  end;
run;

Again, untested.

[Ksharp · Accepted solution]

That would be very easy as long as your data structure looks like what you showed. (a.ka. two obs for each subject and each visit ,first is k/ul ,second is %)

data have;
input subject param $ visit aval lb;
cards;
1 WBC 1 4.5 4.5
1 EOD 1 .   4
1 WBC 2 8.1 8.1
1 EOD 2 .   0.2
2 WBC 1 4.5 4.5
2 EOD 1 .   2
2 WBC 2 8.1 8.1
2 EOD 2 .   0.2
;
data want;
 set have;
 lag_aval=lag(aval);
 if missing(aval) then aval=lag_aval*lb*0.01;
 drop lag_aval;
run;