BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Trus
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

2 REPLIES 2
Astounding
PROC Star

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
Calcite | Level 5
Thanks for your reply, it was very helpful for me.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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