rolling calculations - duration of admission

[sophia_SAS]

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;

[novinosrin · Accepted solution]

Hi @sophia_SAS 

 

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/2018
B2 12/10/2018
B2 12/14/2018
B2 01/03/2019
B2 06/20/2019
B2 07/02/2019
;;

run;

 
data want;
set have;
by subjectid;
retain t;
if first.subjectid then do; t=date_event; duration=0;no_event=1;end;
else do;
duration= ifn(intck('days',t,date_event)>30,0,intck('days',t,date_event));
if duration=0 then do;	no_event+1;	t= date_event;end;
end;
drop t;
run;

[sophia_SAS]

Thank you! This is perfect! Definitely much better than any of my approaches.