topic Re: Flag consecutive and non consecutive dates in New SAS User

Flag consecutive and non consecutive dates

NewUsrStat — Sat, 05 Nov 2022 16:45:35 GMT

Guys,
I need to do the following but I don't know how.
I need to distinguish the first case from the second:

case1:

ID Start End

0001 - 18DEC2019
0001 19DEC2019 -

case2:

ID Start End

0001 - 18DEC2019
0001 11JAN2020 -

Meaning, is there a way to flag if the end is continuous with the start from the case in which there is a gap from the previous end and the next start? Continuity in terms of years is sufficient.

Thank you in advance

Re: Flag consecutive and non consecutive dates

mkeintz — Sat, 05 Nov 2022 16:51:39 GMT

If the data are sorted by ID/start, then:

data want (drop=next_:);
  set have (keep=id);
  by id;
  merge have
        have (firstobs=2 keep=start rename=(start=next_start);

  if first.id=1 or start-1 ^=lag(end) then backward_consecutive='N';
  else backwards_consecutive='Y';

  if last.id=1 or end+1 ^= next_start then forward_consecutive='N';
  else forward_consecutive='Y';
run;

This uses a self-merge with offset approach (i.e. the firstobs=2 in one of the MERGE argument. It also uses SET plus BY statements to generate the first.id and last.id dummies.

Re: Flag consecutive and non consecutive dates

NewUsrStat — Sat, 05 Nov 2022 18:03:42 GMT

Thank you very much! It works perfectly!