Compare current value to previous value

[Lav001]

Hi All,

Could you let me know how to achieve for below logic:-

I need to flag the below records from table 1 in such a way that, I need to get the second table Flag column populated.

Data is sorted by column 1,2.

       Scenario : should be flagged as 1 for first visit

                         and for remaining visits we need to --> compare the current visit with the previous visit based on the Reason column. If records have same                                                                                             reason, then compare to prior visit Reason (Until 3 times, only if Reason value is same).

                         Please see the logic column in the Desired outcome table.

Patient_ID	Visit	Reason	Flag
500	1	Heart
500	2	Kidney
500	3	Lungs
500	4	Lungs
500	5	Lungs

Desired Outcome:

Patient_ID	Visit	Reason	Flag	Logic
500	1	Heart	1	since first visit we will flag as 1
500	2	Kidney	1	comparing current visit (Kidney) with previous (Heart), flagging as 1 when different value
500	3	Lungs	1	comparing current visit (Lungs) with previous (Kidney), flagging as 1 when different value
500	4	Lungs	0	comparing current visit (Lungs) with previous (Lungs), when same value, compare with prior value  which is (Kidney)(2nd visit), since it is different we will assign 0
500	5	Lungs	1	comparing current visit (Lungs) with previous (Lungs), when same value, compare with prior value  which is (Lungs)(3rd visit), since it is same we will assign 1

 Note:- we need to compare only until last 3 visits based on Reason column.

Thanks

Lavanya

[ChrisNZ]

Something like this?

FLAG = ifn(PATIENT_ID ne lag(PATIENT_ID)                         ,  1
     , ifn(PATIENT_ID = lag1(PATIENT_ID) & REASON ne lag1(REASON),  1
     , ifn(PATIENT_ID = lag2(PATIENT_ID) & REASON ne lag2(REASON),  0
     , ifn(PATIENT_ID = lag3(PATIENT_ID) & REASON ne lag3(REASON),  1
     ,                                                              0 ))));

[mkeintz]

What is your rule for visit 2, if both visit 1 and visit 2 are HEART?  This is a case where a given visit is the same as the prior visit, but there in no earlier visit to use as the criterion.

[mkeintz]

Putting aside my question about what to do if visit1=visit2, your logic can be restated as. 

   The flag always=1 EXCEPT when the current reason is the same as both prior reasons, in which case it is zero.

So just defaulting to 1 and looking for the special condition for zero is tempting:

data want;
  set have;

  flag=1;
  if (reason=lag(reason)=lag2(reason)) and (patient_id=lag2(patient_id)) then flag=0;
run;

or, using syntax that might be slightly more general than  "reason=lag(reason)=lag2(reason)", you could used:

  if (reason=lag(reason)) and (reason=lag2(reason)) and (patient_id=lag2(patient_id)) then flag=0;

[andreas_lds]

I would not use lag at all, but retain the last two reasons.

data want;
   set have;
   by Patient_ID;
   
   length Flag 8 _r1 _r2 $ 8; /* Change the length of _r1 and _r2 to match with the length of "reason" */
   retain _r1 _r2;
   drop _r1 _r2;
   
   if first.Patient_ID then do;
      Flag = 1;
      call missing(_r1, _r2);
   end;
   else do;
      Flag = (Reason ^= _r1 or Reason = _r1 and Reason = _r2);
   end;
   
   _r2 = _r1;
   _r1 = Reason;
run;

[ChrisNZ]

> I would not use lag at all, but retain the last two reasons.
Yep, two possible methods.
I find mine more legible.