LAG function help

[Patrick]

Your definition of the logic required still leaves room for interpretation. 

Please provide a data "have" table created via fully tested SAS code posted here that covers all the different cases. Then show us the desired result.

If you can't spend the time to provide such representative sample data via a fully working and tested SAS data step here then I can't spend more time trying to provide a solution for you.

[Emma2021]

HAVE DATA:
data have;
input id date date9. flag;
format date date9.;
cards;
1 1jul2021 .
1 1jul2021 .
1 6jul2021 1
1 10jul2021 4
1 16jul2021 2
1 18jul2021 1
2 1jul2021 .
2 6jul2021 .
;

WANTED DATA:
1 1jul2021 .
1 6jul2021 1
1 16jul2021 2
2 1jul2021 .
2 6jul2021 .


RULES:
1. Keep first data if lag within 5 days and all missing flag (when multiple more than than 5 days) 1 1jul2021 .
1 1jul2021 ., then keep first row. Note: your code was working for example, see 2 1jul2021 .
2 6jul2021 .

Rule (2) and (3) -your code is working for those rules.
Thank you so much!

[Emma2021]

Could you update your code so that it would work for below data:
Thank you so much!

HAVE DATA:
data have;
input id date date9. flag;
format date date9.;
cards;
1 1jul2021 .
1 1jul2021 .
1 6jul2021 1
1 10jul2021 4
1 16jul2021 2
1 18jul2021 1
2 1jul2021 .
2 6jul2021 .
;

WANTED DATA:
1 1jul2021 .
1 6jul2021 1
1 16jul2021 2
2 1jul2021 .
2 6jul2021 .

[Tom · Accepted solution]

It sounds like what you want is the first observation per group where a new group starts when:

• A new ID starts
• A transition of the FLAG state
• A gap of more than 5 days

It would be easier if you had already assigned the grouping variable, but we can just convert your current FLAG variable into one that only has two possible values and then use that.

data have;
  input id date :date. flag;
  format date date9.;
cards;
1 1jul2021 .
1 1jul2021 .
1 6jul2021 1
1 10jul2021 4
1 16jul2021 2
1 18jul2021 1
2 1jul2021 .
2 6jul2021 .
;

data want;
   set have ;
   by id ;
   any_flag=not missing(flag);
   if first.id or any_flag ne lag(any_flag) or dif(date)=>5 ;
run;

Obs    id         date    flag    any_flag

 1      1    01JUL2021      .         0
 2      1    06JUL2021      1         1
 3      1    16JUL2021      2         1
 4      2    01JUL2021      .         0
 5      2    06JUL2021      .         0