topic Negative values when using lag function to calculate the area under curve in SAS Programming

Negative values when using lag function to calculate the area under curve

jomag — Mon, 18 Dec 2017 18:23:21 GMT

Please see below for the code I used to calculate AUC. The code works fine and the AUC calculation is accurate. The issue is however, I was expecting a "." for the first observation of each ID. However, from the second ID, the first observation of each ID returns a negative value instead of missing ("."). Does anyone know how to fix this issue without me having to give this statement 'if auc<0 then auc=.' ? Thank you so much.

SAS Code I used:

data newdata.trial1ab;
set check4;
retain StudyID_Old;
if (_N_ = 1) then StudyID_Old = study_ID;
if (study_ID ne StudyID_Old) then
do;
x_1=.;
y_1 = .;
StudyID_Old = study_ID;
end;
x = dep_new_days;
y = Mean_Hf;
xx = x;
lny = log(y);
x_1 = lag(x);
y_1 = lag(y);
x_xx = x - x_1;
cc = ( y + y_1 ) / 2;
auc = cc * x_xx;
run;

Re: Negative values when using lag function to calculate the area under curve

mkeintz — Mon, 18 Dec 2017 18:48:31 GMT

Because the lag function is a queue update function, not a "lookback", you need to update the queue with every observations, but use a missing value when at the beginning of a by group. But your code only updates the queue when not at the start of a new id.

You need something like:

data want;

set have;

by studyid;

x1=ifn(first.studyid,.,lag(x);

y1=ifn(first.studyid,.,lag(y);

The ifn function will always update the lag queue, but will return a dot when at the start of an id.

You should be able to take it from there.

Re: Negative values when using lag function to calculate the area under curve

jomag — Mon, 18 Dec 2017 18:59:15 GMT

Thank you!

Re: Negative values when using lag function to calculate the area under curve

Tom — Mon, 18 Dec 2017 19:00:41 GMT

You are very close, but pay attention to the order of your statements in the data step.

You need to calculate the lagged values, then make the decision of whether they are valid or not because of by group transition.

data newdata.trial1ab;
  set check4;
  retain StudyID_Old;
  if (_N_ = 1) then StudyID_Old = study_ID;
  x = dep_new_days;
  y = Mean_Hf;
  xx = x;
  lny = log(y);
  x_1 = lag(x);
  y_1 = lag(y);
  if (study_ID ne StudyID_Old) then do;
    x_1=.;
    y_1 = .;
    StudyID_Old = study_ID;
  end;
  else do;
    x_xx = x - x_1;
    cc = ( y + y_1 ) / 2;
    auc = cc * x_xx;
  end;
run;