Desktop productivity for business analysts and programmers

Count minutes between three different time spaces

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Count minutes between three different time spaces

Hello,

 

Example of data:

 

service      time 

Ryk            14:01:00

Ryk            14:02:00

Ryk            14:03:00

Ryk            15:01:00

Ryk            15:02:00

Ryk            15:03:00

Ryk            15:04:00

Ryk            17:38:00

Ryk            17:39:00

Ryk            17:40:00

 

How could I calculate  the time spent on  the service "ryk" which is 10 minutes. I have tried several function, but no luck.  


Accepted Solutions
Solution
‎11-09-2017 03:48 AM
Super User
Posts: 13,942

Re: Count minutes between three different time spaces

This accumulates seconds for service=Ryk. With the previous response about the first three records = 2 minutes, that would be 60 seconds. The second total is carried across all records.

Note that if you have times that cross midnight this will not work. If so you should use datetime values instead of times.

If you need this total for other services this approach could get very cumbersome as you would need a different accumulator variable for each series and assign the total as needed (that is not the hard part) and would require knowing how many services and what each one is before hand.

 

 

data have;
   informat service  $5.    time time.;
   input service time;
   format time time.;
datalines;
Ryk          14:01:00
Ryk          14:02:00
Ryk          14:03:00
HHH          14:04:00
HHH          14:05:00
HHH          14:06:00
Ryk          15:01:00
Ryk          15:02:00
Ryk          15:03:00
Ryk          15:04:00
HHH          17:24:00
Ryk          17:38:00
Ryk          17:39:00
Ryk          17:40:00
;
run;

data want;
   set have;
   by notsorted service ;
   retain svcsum .;
   timedif= dif(time);
   if first.service then ;
   else if service='Ryk' then svcsum=sum(svcsum,timedif); 
   drop timedif;
run;

View solution in original post


All Replies
Super User
Posts: 10,592

Re: Count minutes between three different time spaces

From where do you get the ten minutes? I see more than three hours. Or do you just count the rows?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 12

Re: Count minutes between three different time spaces

Posted in reply to KurtBremser
Count minutes between three different time spacesthis a better example of how data looks like   
 

service      time 

Ryk            14:01:00

Ryk            14:02:00

Ryk            14:03:00

HHH          14:04:00

HHH          14:05:00

HHH          14:06:00

Ryk            15:01:00

Ryk            15:02:00

Ryk            15:03:00

Ryk            15:04:00

HHH           ........

Ryk            17:38:00

Ryk            17:39:00

Ryk            17:40:00

 

I want only the time spent on the service "Ryk", so for example the time between 15:04:00 and 17:38:00 is spent on another service.

 

 

Super User
Posts: 13,942

Re: Count minutes between three different time spaces

So does this interval:

service      time 

Ryk            14:01:00

Ryk            14:02:00

Ryk            14:03:00

 

represent 2 or 3 minutes?

Occasional Contributor
Posts: 12

Re: Count minutes between three different time spaces

sorry to minutes of course! Smiley Happy 

Super User
Posts: 13,942

Re: Count minutes between three different time spaces


raafat wrote:

sorry to minutes of course! Smiley Happy 


Nothing to be sorry about but intervals usually have a start and end. We don't have any information whether that time value represents either. So there may have been an implied duration of one minute associated with each value, or 10 seconds, or something else.

Super User
Posts: 10,592

Re: Count minutes between three different time spaces

So you have a strict sequence of times, and the service changes. Everytime the service changes, a new block starts, and the time shall be counted at the next service change. Since every row represents one minute, the first block would be three minutes, although the span between first and last time is only two minutes.

 

Is that a correct summary?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎11-09-2017 03:48 AM
Super User
Posts: 13,942

Re: Count minutes between three different time spaces

This accumulates seconds for service=Ryk. With the previous response about the first three records = 2 minutes, that would be 60 seconds. The second total is carried across all records.

Note that if you have times that cross midnight this will not work. If so you should use datetime values instead of times.

If you need this total for other services this approach could get very cumbersome as you would need a different accumulator variable for each series and assign the total as needed (that is not the hard part) and would require knowing how many services and what each one is before hand.

 

 

data have;
   informat service  $5.    time time.;
   input service time;
   format time time.;
datalines;
Ryk          14:01:00
Ryk          14:02:00
Ryk          14:03:00
HHH          14:04:00
HHH          14:05:00
HHH          14:06:00
Ryk          15:01:00
Ryk          15:02:00
Ryk          15:03:00
Ryk          15:04:00
HHH          17:24:00
Ryk          17:38:00
Ryk          17:39:00
Ryk          17:40:00
;
run;

data want;
   set have;
   by notsorted service ;
   retain svcsum .;
   timedif= dif(time);
   if first.service then ;
   else if service='Ryk' then svcsum=sum(svcsum,timedif); 
   drop timedif;
run;
Occasional Contributor
Posts: 12

Re: Count minutes between three different time spaces

Thank you very much. Exactly what i was looking for Smiley Happy

☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 311 views
  • 0 likes
  • 3 in conversation