How to calculate events on diffetent rows in particular condition

[Trus]

Hi all,

I have a table that show a Code, a Start_Date and an End_Date.

code	START_DATE	END_DATE
A	09Jan2018 11:30:00	09Jan2018 15:40:00
A	09Jan2018 11:30:00	09Jan2018 15:40:00
B	10Jan2018 9:00:00	10Jan2018 9:33:44
B	10Jan2018 9:40:43	10Jan2018 10:30:00
B	10Jan2018 11:00:00	10Jan2018 14:20:00
A	10Jan2018 14:30:00	10Jan2018 14:30:00
B	18Jan2018 9:00:00	18Jan2018 15:20:00
B	18Jan2018 8:00:00	18Jan2018 15:20:00

I have to calculate the number of the Events (for a same Code) and the Sum of each duration.

Moreover in case of multiple event with same Code and Start_date less than 1 hour, the Duration will be considered as Min Start_Date and the Max End_date and will be counted just one event.

Below the desired output:

code	START_DATE	END_DATE	DURATION	Event
A	09Jan2018 11:30:00	09Jan2018 15:40:00	4h 10m	1
A	09Jan2018 11:30:00	09Jan2018 15:40:00
B	10Jan2018 9:00:00	10Jan2018 9:33:44	1h 30m	1
B	10Jan2018 9:40:43	10Jan2018 10:30:00
B	10Jan2018 11:00:00	10Jan2018 14:20:00	3h 20 m	1
B	18Jan2018 9:00:00	18Jan2018 15:20:00
B	18Jan2018 8:00:00	18Jan2018 15:20:00	7h 20m	1

Could anyone help me?

Thanks for your assistance.

[Astounding · Accepted solution]

This will get you most of the way there:

proc sort data=have;

by code start_date end_date;

run;

data events;

set have;

by code;

seconds = dif(start_date);

if first.code or seconds > 3600 then event_block + 1;

run;

proc summary data=events;

   var start_date end_date;

   by code event_block;

   output out=want min(start_date) = start_date max(end_date) = end_date;

run;

This gives you one observation per event, with the minimum start date and the maximum end date for that event.  There is obviously more processing to do to get this into final form.  For example, you could compute DURATION in seconds by taking end_date minus start_date.  And the number of events is the number of observations.  But at least you have all the pieces needed.

[Trus]

Thanks for your reply, it was very helpful for me.