Time intervals

[PrudhviB]

Hi i have the following data in SAS EG,

I am trying to get the hour interval for the date-time stamp and also the productive time in that hour. And also any time exceeding that hour i want it to be added to the next hour. any suggestions?

Activity	Start time	End Time	time in hrs	Required Result	total hours between hours HH:MM:SS	Required result
break	Nov 1, 2019 5:18:13 PM	Nov 1, 2019 5:32:03 PM	0.230555556	5h-6h	0:13:50
work	Nov 1, 2019 5:46:45 PM	Nov 1, 2019 5:51:57 PM	0.086666667	5h-6h	0:05:12
work	Nov 1, 2019 6:00:16 PM	Nov 1, 2019 6:02:01 PM	0.029166667	6h-7h	0:01:45
lunch	Nov 1, 2019 7:33:16 PM	Nov 1, 2019 8:00:47 PM	0.458611111	7h-8h	0:27:31	47 seconds added to the next hour
break	Nov 1, 2019 9:46:04 PM	Nov 1, 2019 10:01:37 PM	0.259166667	9h-10h	0:15:33	1 min 37 sec added to the next hour
break	Nov 2, 2019 5:15:26 PM	Nov 2, 2019 5:31:28 PM	0.267222222	5h-6h	0:16:02
Lunch	Nov 2, 2019 7:30:18 PM	Nov 2, 2019 8:01:00 PM	0.511666667	7h-8h	0:30:42	1 min added to the next hour
break	Nov 2, 2019 9:45:13 PM	Nov 2, 2019 10:00:15 PM	0.250555556	9h-10h	0:15:02	15 sec added to the next hour
break	Nov 5, 2019 5:15:06 PM	Nov 5, 2019 5:30:18 PM	0.253333333	5h-6h	0:15:12
lunch	Nov 5, 2019 7:31:05 PM	Nov 5, 2019 8:01:11 PM	0.501666667	7h-8h	0:30:06	1 min 11sec added to the next hour
Break	Nov 5, 2019 9:45:07 PM	Nov 5, 2019 10:02:36 PM	0.291388889	9h-10h	0:17:29	2 min 36sec added to the next hour

[ballardw]

Some details you need to explain:

Is that 5h-6h supposed to be a character or numeric variable?

Why are you ignoring AM/PM values? I would think that 5h is for 5AM not 5PM. You may need to explain exactly how you intend to use the value that you show as 5h-6h. Since the Datetime value SAS uses will be much more amenable to using Hour 17 instead of 5 for PM times how critical is it to have "5h"?

You should also show exactly how you mean that " any time exceeding that hour i want it to be added to the next hour" is to be implemented. Such as showing some start and end data.

Assuming that you actually have SAS datetime values then this is where I would start.

data example;
   starttime ="1Nov2019:19:33:16"dt;
   endtime   ="1Nov2019:20:00:47"dt; 
   starthour = hour(starttime);
   if hour(endtime)> hour(starttime) then carryover = intck('seconds',intnx('hour',starttime,1),endtime);
run;

If you don't have datetime values for starttime and endtime that is the first step.

[PrudhviB]

Hi Ballardw, i used h as example, the hours would be 24 hr format.( for 5 am - 6 am its 5-6 and 5pm -6pm its 17-18).
any time exceeding that hour: may be i can explain with an example.

Example:

start time end time hour interval productive hr
1. 05:00 pm 06:15 pm 17-18 01:00:00
2. 06:15 pm 06:30 pm 18-19 00:30:00
(15+15 min from the previous hour)

If this make sense.

[ballardw]

---

@PrudhviB wrote:
Hi Ballardw, i used h as example, the hours would be 24 hr format.( for 5 am - 6 am its 5-6 and 5pm -6pm its 17-18).
any time exceeding that hour: may be i can explain with an example.

Example:

start time end time hour interval productive hr
1. 05:00 pm 06:15 pm 17-18 01:00:00
2. 06:15 pm 06:30 pm 18-19 00:30:00
(15+15 min from the previous hour)

If this make sense.

---

Please show with actual data the values that get changed with your intervals. As in actual input data and actual output data examples.

And why do you think that you need that hyphenated value? How does the hyphen add value? The only way to have a hyphen as part of the value is to have a character variable. Which will not sort correctly for most uses and if any calculations are contemplated the first thing you will need to do is create a numeric value.

You can create a custom format to show a desired value with the hyphen. You will need to decide what you want a time value that crosses midnight to look like.

[PrudhviB]

@ballardw may be i am not being clear what i want. have attached the data below let me know if this work 

Input data:

this is employee logging into a in house system for adherence. 

some time the activity can be 2 hours long.

Actual Start	Actual End
Nov 1, 2019 5:02:49 PM	Nov 1, 2019 5:02:55 PM
Nov 1, 2019 5:41:18 PM	Nov 1, 2019 5:46:45 PM
Nov 1, 2019 5:46:45 PM	Nov 1, 2019 5:51:57 PM
Nov 1, 2019 5:51:57 PM	Nov 1, 2019 6:00:16 PM
Nov 1, 2019 6:00:16 PM	Nov 1, 2019 6:02:01 PM
Nov 1, 2019 6:02:01 PM	Nov 1, 2019 6:06:44 PM
Nov 1, 2019 6:31:08 PM	Nov 1, 2019 6:32:24 PM
Nov 1, 2019 6:35:57 PM	Nov 1, 2019 6:37:30 PM
Nov 1, 2019 6:41:48 PM	Nov 1, 2019 6:41:56 PM
Nov 1, 2019 6:42:42 PM	Nov 1, 2019 6:43:10 PM
Nov 1, 2019 6:44:01 PM	Nov 1, 2019 6:44:21 PM
Nov 1, 2019 6:47:52 PM	Nov 1, 2019 6:48:00 PM
Nov 1, 2019 8:26:36 PM	Nov 1, 2019 8:26:45 PM
Nov 1, 2019 8:37:05 PM	Nov 1, 2019 8:39:12 PM
Nov 1, 2019 8:43:21 PM	Nov 1, 2019 8:44:15 PM
Nov 1, 2019 9:35:37 PM	Nov 1, 2019 9:35:51 PM

required output : 

Shift time is employee's shift and i want this to be a divided into hour groups starting from 00 - 24 (which will include business hours for the company)

Productive time: this is the time difference or the actual time the employee is logged into the system from the input data.

Date	Shift-time (4pm-12am)	Productive Time
1-Nov-19	16:00-17:00	0 min
1-Nov-19	17:00-18:00	7 min
1-Nov-19	18:00-19:00	10 min
1-Nov-19	19:00-20:00	0 min
1-Nov-19	20:00-21:00	3 min
1-Nov-19	21:00-22:00	1 min
1-Nov-19	22:00-23:00	0 min
1-Nov-19	23:00-00:00	0 min

the reason why i want the hour interval to be 4-5 format is, i have another data set(files worked in each hour) that i will be joining to this which has hour interval as 4-5.

Hope this clarifies the doubt. please let me know if you have any questions. 

[novinosrin]

HI @PrudhviB  Welcome to SAS commnunities. In your table(dataset), do you have all variables in place other than Required Result 1 and Required Result2?

[PrudhviB]

Thank you and yes, except the last three columns i have all other.

[novinosrin]

Hi @PrudhviB  Can we make this a little bit more interactive to make it interesting than me having to work like a typist plz?

 I have done the most part. Just run the below and see what you have. The last part is very simple math. Can you attempt? can you try?

data have;
infile cards  truncover;
input Activity $ @9 Start_time anydtdtm22. @33	End_Time anydtdtm22.  timeinhrs  ;
format  Start_time	End_Time datetime20.;
cards;
break   11/1/2019  5:18:13 PM	11/1/2019  5:32:03 PM	0.230555556
work	11/1/2019  5:46:45 PM	11/1/2019  5:51:57 PM	0.086666667
work	11/1/2019  6:00:16 PM	11/1/2019  6:02:01 PM	0.029166667
lunch	11/1/2019  7:33:16 PM	11/1/2019  8:00:47 PM	0.458611111
break	11/1/2019  9:46:04 PM	11/1/2019  10:01:37 PM	0.259166667
break	11/2/2019  5:15:26 PM	11/2/2019  5:31:28 PM	0.267222222
Lunch	11/2/2019  7:30:18 PM	11/2/2019  8:01:00 PM	0.511666667
break	11/2/2019  9:45:13 PM	11/2/2019  10:00:15 PM	0.250555556
break	11/5/2019  5:15:06 PM	11/5/2019  5:30:18 PM	0.253333333
lunch	11/5/2019  7:31:05 PM	11/5/2019  8:01:11 PM	0.501666667
Break	11/5/2019  9:45:07 PM	11/5/2019  10:02:36 PM	0.291388889
;


data want;
set have;
total_hours_between_hours=End_Time-Start_time;
hr=intck('hour',Start_time,End_Time);
if hr>0 then do;
/*additions*/
hr_addition=ifn(hr-1>0,hr-1,.);
min_addition=ifn(minute(End_Time)>0,minute(End_Time),.);
sec_addition=ifn(second(End_Time)>0,second(End_Time),.);
end;
drop hr;
format total_hours_between_hours time10.;
run;

If you understand the simple logic, it's easy to figure, nonetheless come back to us. Have a good one!