Working with Time Intervals

[primmer300174]

Apologies if this is already been posted, I'm new to this. I'm trying to get 30 min interval data or to the end time for Agent activity for work. the code I have is :-

data test;
set schedule2;
format Mystart MyEnd tod8.;
do Mystart = Start to Stop by 1800;
MyEnd = MyStart + 1800;

output;
end;

run;

This works for the most part, but it's not working if the interval end time is like 16:45:00. This is what I have :-

02-Jun-25	Sick	09:00:00	16:45:00	465	16:00:00	16:30:00
02-Jun-25	Sick	09:00:00	16:45:00	465	16:30:00	17:00:00
02-Jun-25	Meal	16:45:00	17:15:00	30	16:45:00	17:15:00
02-Jun-25	Meal	16:45:00	17:15:00	30	17:15:00	17:45:00

But what I want is :-

02-Jun-25	Sick	09:00:00	16:45:00	465	16:00:00	16:30:00
02-Jun-25	Sick	09:00:00	16:45:00	465	16:30:00	16:45:00
02-Jun-25	Meal	16:45:00	17:15:00	30	16:45:00	17:00:00
02-Jun-25	Meal	16:45:00	17:15:00	30	17:00:00	17:15:00

I know I am missing something but I cannot figure it out. Can someone please assist.

[PaigeMiller]

Please explain further. You say you want 30 minutes intervals, but in the bottom table (which is what you want to get) you have mostly 15 minute intervals.

Also, we would need to see a portion of the starting data, which you have not shown us. (In other words, we need to see a portion of data set SCHEDULE2)

[primmer300174]

The ask of me is to have interval data per agent for productivity. but if the interval ends at 16:45:00, that would be classed as 0.5 for that interval as they are not productive for the whole 30 min interval. Hope this clarifies what I have asked for

[PaigeMiller]

---

@primmer300174 wrote:

The ask of me is to have interval data per agent for productivity. but if the interval ends at 16:45:00, that would be classed as 0.5 for that interval as they are not productive for the whole 30 min interval. Hope this clarifies what I have asked for

---

So, please explain why your desired data is a 30 minute interval for record 1, but 15 minute intervals for records 2 through 4. Also, please SHOW us a portion of the input data in data set SCHEDULE2.

[LinusH]

Apart from @PaigeMillers input, I don't understand why you have a do loop, since you're doing OUTPUT outside of the loop?

[primmer300174]

I'm doing a loop and output so that it gives every iteration for the 30 mins interval I'm requiring

[Kurt_Bremser]

If you want to maximize the end time with stop, do so in a function:
MyEnd = min(MyStart + 1800,stop);

[Tom]

First point is it will be easier for everyone (you included) if you provide the example data as a SAS data step.  And keep it small enough to demonstrate the issue.

From your listing it looks like you started with two observations. But lets use a shorter time range for the first one.

data have;
  input date :date. meal :$10. start :time. stop :time. value;
  format date yymmdd10. start stop tod8.;
cards;
02-Jun-25 Sick 09:00:00 10:45:00 465 
02-Jun-25 Meal 16:45:00 17:15:00 30 
;

Second if you want the last interval to be capped at the STOP time then use the MIN() function.

Third if don't want an extra interval that STARTS at the STOP time then add more logic to prevent that.  You might try subtracting a fraction of a second from the upper bound used in the DO loop.

data want;
  set have ;
  length interval Mystart MyEnd 8;
  do Mystart = Start to Stop-0.01 by '00:30:00't;
    interval=sum(interval,1);
    MyEnd = min(MyStart + '00:30:00't,stop);
    output;
  end;
  format Mystart MyEnd tod8.;
run;

Note: Try to avoid using "magic" numbers in your code.  How is the next programmer that works on this program supposed to know why you used the number 1,800?

[primmer300174]

Firstly, thank you for your advise, noted and will amend current and future codes to prevent the "magic" numbers etc. Makes sense to me but like you said, anyone else looking at it may not understand.

This is almost what I need. for row 5, where it says MyEnd at 17:15:00, think need to be 17:00:00 and then an additional to say MyStart 17:00:00 and MyEnd to say 17:15:00

[Tom]

---

@primmer300174 wrote:

Firstly, thank you for your advise, noted and will amend current and future codes to prevent the "magic" numbers etc. Makes sense to me but like you said, anyone else looking at it may not understand.

This is almost what I need. for row 5, where it says MyEnd at 17:15:00, think need to be 17:00:00 and then an additional to say MyStart 17:00:00 and MyEnd to say 17:15:00

---

As @PaigeMiller said why do you want to switch from 30 minutes intervals to 15 minute intervals on that second subject?  How can the program know to treat that subject differently?

[primmer300174]

I have to convert each duration into 30 minute intervals for comparing against the scheduled activity which is in 30 minute intervals. 

This is what the Resource planning area have given me and will not change this. I am trying to work out how many agents I would have (whether fully or partly) in that 30 minute interval. I have examples where a break has been place at 10:55:00 - 11:05:00 which would mean I need to have the MyStart as 10:55:00 - MyEnd 11:05:00 and then an additional row that would say MyStart as 11:05:00 and MyEnd as 11:30:00.

Hope this make sense and clarifies the confusion.

[Quentin]

This is still confusing, as you've introduced a new concept of a 'break'.  Please provide an example of the sample data you HAVE (schedule2), and the output you WANT from that sample data.   As a DATA step with CARDS statement, as Tom showed, and Paige asked for at the beginning of this thread.

[Tom]

So you want to merge a series of 30 minutes intervals with your existing time period.

You could do something like this to calculate the intervals for each subject separately.

data want;
  retain window '00:30:00't;
  set have ;
  length interval Mystart MyEnd 8;
  base_time = window*int(start/window);
  do interval=1 by 1 until(MyEnd >= Stop);
    myend = base_time + interval*window;
    mystart = myend-window;
    output;
  end;
  format Mystart MyEnd tod8.;
  drop window base_time;
run;

Or you could generate all of the intervals in a 24 hour day and join the two datasets using PROC SQL.

data intervals;
  do mystart=0 to '24:00:00't by '00:30:00't ;
    myend=mystart + '00:30:00't;
    output;
  end;
  format mystart myend tod8.;
run;

proc sql;
create table want as 
select a.*,b.*
from have a
  left join intervals b
  on a.start < b.myend
  and a.stop > b.mystart
order by a.date,b.mystart,b.myend
;
quit;

You might want to add more logic if you want the MYstart and MYend times not extend beyond the start and stop times.

proc sql;
create table want as 
select a.*
     , max(a.start,b.mystart) as MyStart format=tod8.
     , min(a.stop,b.myend) as MyEnd format=tod8.
from have a
  left join intervals b
  on a.start < b.myend
  and a.stop > b.mystart
order by a.date,b.mystart,b.myend
;
quit;

[ballardw]

---

@primmer300174 wrote:

I have to convert each duration into 30 minute intervals for comparing against the scheduled activity which is in 30 minute intervals. 

This is what the Resource planning area have given me and will not change this. I am trying to work out how many agents I would have (whether fully or partly) in that 30 minute interval. I have examples where a break has been place at 10:55:00 - 11:05:00 which would mean I need to have the MyStart as 10:55:00 - MyEnd 11:05:00 and then an additional row that would say MyStart as 11:05:00 and MyEnd as 11:30:00.

Hope this make sense and clarifies the confusion.

---

As an aside, you keep saying "30 minute interval" and then show something like "MyStart as 10:55:00 - MyEnd 11:05:00 and then an additional row that would say MyStart as 11:05:00 and MyEnd as 11:30:00." Neither of these start/end pairs have a 30 minute interval. 

This sounds more like you have to add an observation to the data set when an existing interval that does not end in either 00 or 30 minutes such that the additional observation starts at the end of the existing observation ends at such a multiple. Is this a clearer understanding of what your requirement is? If so, is there ever a requirement to add more than one observation and what in the data tells you that a particular observation should have such multiple extensions.

If the objective is to count intervals then I think that you don't need to add anything to the data. The INTCK function allows for custom intervals involving multiples of an existing unit.

Consider this program which has a given start period and then increments and end period value starting one minute later and running for a bit.

data example;
   starttime = '01:23:00't;
   do endtime   = '01:24:00't to '02:12:00't by 60;
      intervals = intck('minute30',starttime,endtime);
      output;
   end;
   format starttime endtime time.;
run;

If you examine the output you will see that the interval count returned is 0 until the endtime value reaches 1:30:00 and then increments, then again at 2:00:00.

So depending on your need you may either use this interval directly or add 1 to get a 3 for the number of 30 minute periods involved.

BTW, computers are stupid and "30 minute interval" with no other information could start at 5 and go to 35 minutes or any other start/end. You need to explicitly state the periods need to end at minute 00 and 30.

[Tom · Accepted solution]

Using the MINUTE30 interval should work.

data want;
  set have;
  do interval=0 to intck('minute30',start,stop);
    MyStart=max(start,intnx('minute30',start,interval,'b'));
    MyEnd=min(stop,intnx('minute30',start,interval+1,'b'));
    output;
  end;
  format mystart myend tod8.;
run;

Result