cumul duration time by range time

[Nasser_DRMCP]

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)

thanks in advance

kind regards

Nass

[PaigeMiller · Accepted solution]

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;