Help using Base SAS procedures

How to calculate events on diffetent rows in particular condition

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

How to calculate events on diffetent rows in particular condition

Hi all,

 

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

 

codeSTART_DATEEND_DATE
A09Jan2018 11:30:0009Jan2018 15:40:00
A09Jan2018 11:30:0009Jan2018 15:40:00
B10Jan2018 9:00:0010Jan2018 9:33:44
B10Jan2018 9:40:4310Jan2018 10:30:00
B10Jan2018 11:00:0010Jan2018 14:20:00
A10Jan2018 14:30:0010Jan2018 14:30:00
B18Jan2018 9:00:0018Jan2018 15:20:00
B18Jan2018 8:00:0018Jan2018 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:

 

codeSTART_DATEEND_DATEDURATIONEvent
A09Jan2018 11:30:0009Jan2018 15:40:004h 10m1
A09Jan2018 11:30:0009Jan2018 15:40:00  
B10Jan2018 9:00:0010Jan2018 9:33:441h 30m1
B10Jan2018 9:40:4310Jan2018 10:30:00  
B10Jan2018 11:00:0010Jan2018 14:20:003h 20 m1
B18Jan2018 9:00:0018Jan2018 15:20:00  
B18Jan2018 8:00:0018Jan2018 15:20:007h 20m1

 

Could anyone help me?

Thanks for your assistance.


Accepted Solutions
Solution
‎06-04-2018 08:58 AM
Super User
Posts: 6,785

Re: How to calculate events on diffetent rows in particular condition

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.

View solution in original post


All Replies
Solution
‎06-04-2018 08:58 AM
Super User
Posts: 6,785

Re: How to calculate events on diffetent rows in particular condition

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.

Occasional Contributor
Posts: 7

Re: How to calculate events on diffetent rows in particular condition

Posted in reply to Astounding
Thanks for your reply, it was very helpful for me.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 136 views
  • 0 likes
  • 2 in conversation