Lapis Lazuli | Level 10

cumul duration time by range time

```data have;
input employee \$ tsp_start tsp_end datetime18. status \$ ;
informat tsp_start tsp_end datetime18.;
format tsp_start tsp_end datetime18. ;
format employee status \$4. ;
datalines ;
AAAA 10SEP2121:08:00:00 10SEP2121:08:04:00 BUSY
AAAA 10SEP2121:08:05:30 10SEP2121:08:12:30 CALL
AAAA 10SEP2121:08:14:20 10SEP2121:08:15:20 BUSY
AAAA 10SEP2121:08:28:20 10SEP2121:08:34:20 CALL
AAAA 10SEP2121:08:35:00 10SEP2121:08:40:10 BUSY
;
run;

data want;
input employee \$ range_below range_top datetime18. status \$ duration time. ;
informat range_below range_top datetime18.;
format range_below range_top datetime18. ;
format employee status \$4. ;
format duration time. ;
datalines ;
AAAA 10SEP2121:08:00:00 10SEP2121:08:30:00 BUSY 00:05:00
AAAA 10SEP2121:08:00:00 10SEP2121:08:30:00 CALL 00:08:40
AAAA 10SEP2121:08:30:00 10SEP2121:09:00:00 BUSY 00:05:10
AAAA 10SEP2121:08:30:00 10SEP2121:09:09:00 CALL 00:04:20
;
run;
```

Hello

some emlpoyees make calls. calls have begin time and end time so duration.

there are "call" phases and "busy" phases.

I would like to calculate duraiton time of "call" phase and "busy" phase but for every range time. a range is 30min duration. so

range1 is from 08:00 to 08:30

range2 is from 08:30 to 09:00

range3 is from 09:00 to 09:30

...

range 24 from 19:30 to 20:00

for example, in the "want" table, row 2 the duration is 08:40 (concerning range 08:00 to 08:30) because we cumulate 7 min (08:05:30 to 08:12:30) and 1:30 (coming from row 5, 08:28:20 to 08:30:00)

kind regards

Nass

1 ACCEPTED SOLUTION

Accepted Solutions
Diamond | Level 26

Re: cumul duration time by range time

``````data modify;
set have;
/* Determine if start to end time crosses 30 minute boundary */
start_period=round(tsp_start-900,1800);
end_period=round(tsp_end-900,1800);
/* If start_period < end_period then we need to split the row into two or more rows */
if start_period < end_period then do;
delta=floor(end_period-start_period)/1800;
do i=0 to delta;
duration=(min(end_period,tsp_end)-tsp_start);
output;
start_period=start_period+1800;
tsp_start=end_period;
end_period=end_period+1800;
end;
end;
else do;
duration=tsp_end-tsp_start;
output;
end;
format start_period end_period datetime18. duration time.;
drop i;
run;
proc summary data=modify nway;
class start_period status;
var duration;
output out=want sum=;
run;``````
--
Paige Miller
Diamond | Level 26

Re: cumul duration time by range time

``````data modify;
set have;
/* Determine if start to end time crosses 30 minute boundary */
start_period=round(tsp_start-900,1800);
end_period=round(tsp_end-900,1800);
/* If start_period < end_period then we need to split the row into two or more rows */
if start_period < end_period then do;
delta=floor(end_period-start_period)/1800;
do i=0 to delta;
duration=(min(end_period,tsp_end)-tsp_start);
output;
start_period=start_period+1800;
tsp_start=end_period;
end_period=end_period+1800;
end;
end;
else do;
duration=tsp_end-tsp_start;
output;
end;
format start_period end_period datetime18. duration time.;
drop i;
run;
proc summary data=modify nway;
class start_period status;
var duration;
output out=want sum=;
run;``````
--
Paige Miller
Discussion stats