Hi SAS Users,
Could you help me figure out how to calculate duration of event dates and number of events based on a moving criteria? I would like to assign an event number based on any new event that occurs 30 days after the last grouping. Below is my sample dataset, initial code and the wanted dataset. You can see from the wanted dataset, that once the duration exceeds 30 days, then a new event number is created and the count to 30 days starts over (e.g. subject=A1 date_event=03/19/2017). I'm struggling with how to create the new events.
TIA,
Sophie
data have;
input SubjectID $3 date_event;
informat date_event mmddyy10.;
format date_event mmddyy10.;
datalines;
A1 10/12/2016
A1 11/05/2016
A1 11/06/2016
A1 03/19/2017
A1 04/01/2017
A1 08/08/2018
A1 09/01/2017
B2 12/10/2018
B2 12/14/2018
B2 01/03/2019
B2 06/20/2019
B2 07/02/2019
;;
run;
proc sql;
create table days30 as
select distinct a.SubjectID, a.date_event as date1, b.date_event as date2
from have as a
left join
have as b
on a.SubjectID=b.SubjectID
order by a.SubjectID, a.date_event
;
quit;
data days30;
set days30;
duration_event=intck('day',date1,date2);
run;
data want;
input SubjectID $ date_event duration_event no_event;
informat date_event mmddyy10.;
format date_event mmddyy10.;
datalines;
A1 10/12/2016 0 1
A1 11/05/2016 24 1
A1 11/06/2016 25 1
A1 03/19/2017 0 2
A1 04/01/2017 13 2
A1 08/08/2018 0 3
A1 09/01/2018 24 3
B2 12/10/2018 0 1
B2 12/14/2018 4 1
B2 01/03/2019 24 1
B2 06/20/2019 0 2
B2 07/02/2019 12 12
;;
run;