I don't understand all the details of the calculations you want, but hopefully this is close enough, and sufficiently simple for you to alter if needed. (note: The first data step just recalculates the intervals and overlaps you already have, so just look at WANT2).
data HAVE;
input ID START_TIME time. STOP_TIME : time. ;
format START_TIME time. STOP_TIME : time. ;
cards;
1 8:23 8:47
1 8:10 9:30
1 9:25 9:40
2 9:12 10:20
2 10:02 10:14
2 10:08 10:23
run;
data WANT1;
merge HAVE
HAVE(firstobs=2
rename=(ID=IDx START_TIME=START_TIMEx STOP_TIME=STOP_TIMEx))
end=LASTOBS ;
array INTERVAL[0:23] INTERVAL0-INTERVAL23;
array OVERLAP [0:23] OVERLAP0-OVERLAP23;
do I= 0 to 23;
T1 = (I)*3600; %* start of hour ;
T2 = (I+1)*3600; %* end of hour;
INTERVAL[I]= ( START_TIME <= T2 & T1 <= STOP_TIME )
* ( min(STOP_TIME,T2) - max(START_TIME,T1) ) / 60;
if ID = IDx then
OVERLAP[I]= ( START_TIME <= T2 & T1 <= STOP_TIME )
* ( START_TIMEx <= T2 & T1 <= STOP_TIMEx )
* ( min(T2, STOP_TIME, STOP_TIMEx) - max(T1,START_TIME,START_TIMEx) ) / 60;
end;
run;
data WANT2;
merge WANT1
WANT1(firstobs=2
keep =ID INTERVAL:
rename=(ID=IDx
%macro loop;%local i;%do i=0% to 23; INTERVAL&i=INTERVALx&i%end;%mend;%loop
))
end=LASTOBS ;
array INTERVAL[ 0:23] INTERVAL0 - INTERVAL23;
array INTERVALx[0:23] INTERVALx0- INTERVALx23;
array OVERLAP [0:23] OVERLAP0 - OVERLAP23;
array OVERPRV [0:23] OVERPRV0 - OVERPRV23;
do I= 0 to 23;
if ID = IDx then
OVERPRV[I]= INTERVAL[I]+INTERVALx[I]-OVERLAP[I];
end;
run;
proc print data=WANT2 noobs;
var ID START_TIME STOP_TIME INTERVAL8 OVERLAP8 OVERPRV8
INTERVAL9 OVERLAP9 OVERPRV9
INTERVAL10 OVERLAP10 OVERPRV10
INTERVAL11 OVERLAP11 OVERPRV11;
run;
ID
START_TIME
STOP_TIME
INTERVAL8
OVERLAP8
OVERPRV8
INTERVAL9
OVERLAP9
OVERPRV9
INTERVAL10
OVERLAP10
OVERPRV10
INTERVAL11
OVERLAP11
OVERPRV11
1
8:23:00
8:47:00
24
24
50
0
0
30
0
0
0
0
0
0
1
8:10:00
9:30:00
50
0
50
30
5
40
0
0
0
0
0
0
1
9:25:00
9:40:00
0
15
0
0
2
9:12:00
10:20:00
0
0
0
48
0
48
20
12
20
0
0
0
2
10:02:00
10:14:00
0
0
0
0
0
0
12
6
21
0
0
0
2
10:08:00
10:23:00
0
0
15
0
... View more