Create flag for next record based on condition

[Abishekaa]

This is my sample dataset:

PtID visit Flag

001   1      0

001   2       .

001   3       .

002   1      1

002   2       . 

For each PtID, if the first.Flag = 0, I want to Flag the next visit. This is the dataset I try to create:

PtID visit Flag

001   1      0

001   2      1

001   3       .

002   1      1

002   2       . 

[Tom]

Are the visits always so nicely numbered (1,2,3,....)?

data want;
  set have ;
  if visit=2 and lag(flag)=0 then flag=1;
run;

Or not?

data want;
  set have ;
  by ptid ;
  if first.ptid then lag_flag=flag;
  else do;
    if lag_flag=0 then flag=1;
    lag_flag=.;
  end;
  retain lag_flag ;
  drop lag_flag;
run;

[mkeintz · Accepted solution]

---

@Abishekaa wrote:

This is my sample dataset:

PtID visit Flag

001   1      0

001   2       .

001   3       .

002   1      1

002   2       . 

For each PtID, if the first.Flag = 0, I want to Flag the next visit. This is the dataset I try to create:

PtID visit Flag

001   1      0

001   2      1

001   3       .

002   1      1

002   2       . 

---

Do you really mean "first.flag=0", or (as I suspect) you mean the first obs for a given ID has flag=0.   If it's the latter, then:

data want;
  set have;
  by ptid;
  if first.ptid=0 and lag(first.ptid)=1 and lag(flag)=0 then flag=0;
run;

The if statement tests for

1. The current obs is the second for a given ID -- first.ptid=0 and lag(first.ptid)=1.  (or you could replace those 2 conditions with    dif(first.ptid)=-1  (dif(x) is defined as x-lag(x).
2. The immediately preceding flag is zero  --  lag(flag)=0

[Abishekaa]

This works perfectly! Thank you so much.  I didn't realize the lag function could be used to fully solve this problem 🙂