topic Re: Flagging events occurring outside a 14 day window in SAS Programming

Flagging events occurring outside a 14 day window

srbeer — Tue, 25 Oct 2022 16:52:56 GMT

Hello!

I have a dataset, with an ID and an event date.

data have;
input ID event_date : DATE9. ;
format event_date DATE9.;
cards;
18901 01JUL2022
18901 14JUL2022
18901 15JUL2022
18901 30JUL2022
18901 31JUL2022
18901 15AUG2022
;

Once the first event is encountered, I want to flag that row first. Then flag any row that occurs outside a 14 day time window. So, skip row 2 since it's within 14 days and flag row 3. Once Row 3 is flagged, the 14 day counter starts again and row 4 gets flagged. Row 5 is skipped now with the new 14 day counter in effect and then flag row 6 that occurs outside the 14 day counter and so on for any number of iterations.

Tried several ways, but I'm mainly struggling with resetting the 14 day counter once a flag's activated. Any help's much appreciated.

want:

ID	Event_Date	Flag
18901	01JUL2022	1
18901	14JUL2022	0
18901	15JUL2022	1
18901	30JUL2022	1
18901	31JUL2022	0
18901	15AUG2022	1

Re: Flagging events occurring outside a 14 day window

PeterClemmensen — Tue, 25 Oct 2022 17:16:03 GMT

data have;
input ID event_date : DATE9. ;
format event_date DATE9.;
cards;
18901 01JUL2022
18901 14JUL2022
18901 15JUL2022
18901 30JUL2022
18901 31JUL2022
18901 15AUG2022
18902 01JUL2022
18902 14JUL2022
18902 15JUL2022
18902 30JUL2022
18902 31JUL2022
18902 15AUG2022
;

data want(drop = c);
   set have;
   by ID;

   c + dif(event_date);

   if first.ID | c ge 14 then do;
      flag = 1;
      c = 0;
   end;
   
run;

Re: Flagging events occurring outside a 14 day window

srbeer — Tue, 25 Oct 2022 17:29:16 GMT

thanks for the awesome solution Peter! I had like 100 lines of code typed in with transposes and unsuccessful lag functions

Re: Flagging events occurring outside a 14 day window

PeterClemmensen — Tue, 25 Oct 2022 17:31:44 GMT

Anytime 🙂 We all write ourselves into dead ends now and then..