topic Re: Create flag for next record based on condition in SAS Programming

Create flag for next record based on condition

Abishekaa — Thu, 22 Apr 2021 19:01:27 GMT

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 .

Re: Create flag for next record based on condition

Tom — Thu, 22 Apr 2021 19:14:19 GMT

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;

Re: Create flag for next record based on condition

mkeintz — Fri, 23 Apr 2021 00:33:37 GMT

@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

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).
The immediately preceding flag is zero -- lag(flag)=0

Re: Create flag for next record based on condition

Abishekaa — Thu, 29 Apr 2021 01:05:05 GMT

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