Need help with coding for flag

[Merdock]

Hello,

I am hoping someone can help me with a coding issue. I have the dataset below ("have") and I want to obtain the dataset "want".

Basically, what I want is to flag the records where the "reason" for the previous record is "dose escalation" but the dose amount is actually lower than the previous record, or if the "reason"="dose reduction" but the dose amount is higher than previously, or if the "reason"="stopped for innefficacy-will start different med" but the participant is still on the same med.

data long;
input patid med$ startdate:mmddyy. stopdate:mmddyy. dose reason$;
format startdate mmddyy10. stopdate mmddyy10.;
datalines;
1     X     9/28/2018   11/6/2019   5     1
1     X     11/7/2019   1/18/2020   10    .
2     Y     8/15/2015   9/30/2016   20    2
2     Y     10/10/2016  3/20/2020   15    3
2     X     04/30/2020  11/5/2020   6     .
3     Y     4/15/2018   12/23/2018  19    2
3     Y     12/24/2018  3/5/2019    25    3
3     Y     3/6/2019    12/5/2019   13    .
4     X     12/1/2019   12/20/2019  12    3
4     Y     12/25/2019  2/14/2020   4     1
4     Y     2/18/2020   10/25/2020  2     .
;
run;
proc print data=long; run;

proc format;
      value $reason '1'='dose escalation'
                          '2'='dose reduction'
                          '3'='stopped for innefficacy-will start different med';
run;

data have;
      set long;
format reason $reason.;
run;
proc print data=have; run;

data want;
input patid med$ startdate:mmddyy. stopdate:mmddyy. dose reason$ flag;
format startdate mmddyy10. stopdate mmddyy10.;
format reason $reason.;
datalines;
1     X     9/28/2018   11/6/2019   5     1 0
1     X     11/7/2019   1/18/2020   10    . 0
2     Y     8/15/2015   9/30/2016   20    2 0
2     Y     10/10/2016  3/20/2020   15    3 0
2     X     04/30/2020  11/5/2020   6     . 0
3     Y     4/15/2018   12/23/2018  19    2 0
3     Y     12/24/2018  3/5/2019    25    3 1
3     Y     3/6/2019    12/5/2019   13    . 1
4     X     12/1/2019   12/20/2019  12    3 0
4     Y     12/25/2019  2/14/2020   4     1 0
4     Y     2/18/2020   10/25/2020  2     . 1
;
run;
proc print data=want; run;

For example, we see that for patid#3 there was supposed to be a dose reduction for the record with dose starting on 12/24/2018-03/05/2019, yet the dose=25 which is higher than the previous record dose=19. Additionally, the next record starting with the 03/06/2019-12/05/2019 was supposed to have switched to a different medication, yet the med is still Y.

I tried the following (just for the escalation/reduction checks, without the stopp/switch) and other variations on this but can't get it to do what I want:

data Checks;
  set have;
  
  /* Initialize previous dose value for the first record within each patient */
  by patid;
  if first.patid then do;
    prev_dose = .;
    escalation_check = 'No';
    reduction_check = 'No';
  end;

  /* Check if reason="dose escalation" but the dose for the next record is lower */
  if reason = '1' and not last.patid then do;
    set have(firstobs=2 keep=dose rename=(dose=next_dose reason=next_reason)) point=_n_;
    if patid = _iorc_ and next_reason = '1' and dose > next_dose then escalation_check = 'Yes';
  end;

  /* Check if reason="dose reduction" but the dose for the next record is higher */
  if reason = '2' and not last.patid then do;
    set have (firstobs=2 keep=dose rename=(dose=next_dose reason=next_reason)) point=_n_;
    if patid = _iorc_ and next_reason = '2' and dose < next_dose then reduction_check = 'Yes';
  end;
  drop prev_dose next_dose next_reason;
run;
proc print data=Checks; run;

Hope this makes sense, thanks in advance for any help.

[Tom]

Is the reason on the NEW record (so that you need to check the previous value) or on the OLD record (so that you need to check to next value)?

Remembering things is much easier

set have;
by patid med startdate notsorted;
lag_dose= lag(dose);
lag_med = lag(med);
if first.patid then lag_med = ' ';
if first.med then lag_dose=.;

than predicting the future.

set have;
by patid med startdate notsorted;
set have(firstobs=2 keep=med dose rename=(med=lead_med dose=lead_dose))
    have(obs=1 drop=_all_)
;
if last.med then lead_dose=.;
if last.patid then lead_med=' ';

[Merdock]

@Tom, let's say dose=19 and the reason="dose escalation" but the next record has dose=17, then I want this record with dose=17 flagged as the dose is lower than 19 but the reason for the previous record said that the dose should have been escalated for the next record.

[Tom]

You should be able to figure out the logic once you have the data you need.

data want;
  set have;
  by patid med notsorted;
  first_med=first.med;
  last_med=last.med;
  lag_dose = lag(dose);
  set have(firstobs=2 keep=dose rename=(dose=lead_dose)) have(obs=1 drop=_all_);
  if first.med then lag_dose=.;
  if last.med then lead_dose=.;
run;

Here is what those variables will look like for your example data.

[Quentin]

As Tom wrote, the look-back is straight forward, using the LAG function.

I would treat this like an assertion.  You look back to value of REASON on the prior record.  And use that value to assert an expectation you have about the data on the current record.  If that expectation is false, set a flag, e.g.:

data want ;
  set long ;
  lagpatid=lag(patid) ;
  lagmed=lag(med) ;
  lagdose=lag(dose) ;
  lagreason=lag(reason) ;
  
  flag=0 ;
  if lagreason='1' then do ;
    *Expect this to be same patid, same med, increased dose ;
    if NOT (patid=lagpatid and med=lagmed and dose>lagdose>0) then flag=1 ;
  end ;
  else if lagreason='2' then do ;
    *Expect this to be same patid, same med, decreased dose ;
    if NOT (patid=lagpatid and med=lagmed and 0<dose<lagdose) then flag=1 ;
  end ;
  else if lagreason='3' then do ;
    *Expect this to be same patid, different med ;
    if NOT (patid=lagpatid and med ne lagmed) then flag=1 ;
  end ;

  * drop lag:  ;
run ;

[mkeintz]

You can use the DIF function to determine whether there has been an increase or decrease in dose, along with LAG of reason:

data want;
  set have;
  by patid med notsorted;
  flag=0;
  if lag(reason)='3' and first.med=0 then flag=1;
  if lag(reason)='1' and first.med=0 and dif(dose)<0 then flag=1;
  if lag(reason)='2' and first.med=0 and dif(dose)>0 then flag=1;
run;

Note you can't do ELSE IF's here, because the LAG (and related DIF) functions must be able to always update their underlying queues even when the corresponding IF condition is not met.

Editted note: you could consolidate the IF tests as below, but I posted the above for a bit more clarity:

  if first.med=0 and 
     (
       (lag(reason)='3') or
       (lag(reason)='1' and dif(dose)<0) or
       (lag(reason)='2' and dif(dose)>0)
     )
    then flag=1;