Finding a Total of Non-overlapping/Non-stacked Time Scheduled

[jpagitt]

Hello,

I am trying to find a way to produce a total of non-overlapping (or non-stacked as synonymous terminology for this purpose) time scheduled in minutes grouped at a room_nbr and day level. Here is some simplified example course schedule data of courses who are taught within the same room on the same day, but vary in the time that they are taught (i.e. courses that are partially combined for group-work or certain lecture topics):

Room_Nbr	Day	Class	Start_Time	End_Time	Minutes	Overlap	Non_overlap
1	Monday	101	8:00	9:00	60	0	60
1	Monday	102	8:00	10:00	120	60	60
1	Monday	103	8:00	11:00	180	120	60
2	Tuesday	201	11:00	13:00	120	0	120
2	Tuesday	202	12:00	14:00	120	60	60
3	Wednesday	301	8:00	12:00	240	0	240
3	Wednesday	302	9:00	10:00	60	60	0
4	Thursday	401	8:00	9:00	60	0	60
4	Thursday	402	11:00	12:00	60	0	60

In the example data there are 4 different rooms scheduled for 4 different days (the days themselves could be the same day since the desired result is grouped at the room_nbr level first). I have the data for room_nbr through minutes and I want to be able to produce the overlap and non_overlap fields. Looking at the example data I'd like to be able to produce a running check against previous classes for the same room_nbr and day - i.e. class 101 as the earliest course at the room_nbr and day grouping does not consider another course for determining overlapping and non-overlapping time; class 102 checks against class 101 to find time overlapped, but does not consider class 103; class 103 checks against any previous classes with the same room_nbr and day value (101 and 102). If there are 2+ courses scheduled for the same minute(s) in the same room on the same day, then I want to only count that overlapped time once in non_overlap preferably for the earliest course at the room_nbr and day level although I am open to any suggestions from you all.

Please let me know if you have any questions. I appreciate any guidance you can give me.

SAS 9.4

[ballardw]

What do you expect as output?

My initial approach to something like this might be graph where the horizontal axis is day of week, vertical axis it time, and use a high-low graph to connect start-end times, group by variable that combines class and room number.

[mkeintz · Accepted solution]

This is a good problem to utilize an array, indexed in minute-of-the-day, that progressively tracks room usage for each of those minutes.  Check each minute of the current class against the cumulative array and add 1 minute to overlap if the cumulative array indicates prior usage for that minute:

data want (drop=_:);
  set have ;
  by room_nbr day;

  array prior_minutes{1440} _temporary_;   /*1440 minutes in a day */
  if first.day then call missing(of prior_minutes{*});

  overlap=0;
  _beg_min= 60*hour(start_time)+ minute(start_time);
  _end_min= 60*hour(end_time) + minute(end_time);
  do _min=_beg_min+1 to _end_min;
    if prior_minutes{_min}>0 then overlap=sum(overlap,1);
    prior_minutes{_min}+1;
  end;

  non_overlap=minutes-overlap;
run; 

The loop starts at beg_min+1 to avoid overcounting an interval (i.e. 8:00-9:00 should be 60 iterations, not 61).

[jpagitt]

Exactly what I needed. Thank you! I hadn't really found a reason to work with arrays before, but seeing a cool application of them now makes me think I could probably work them in elsewhere. Thanks again.