BookmarkSubscribeRSS Feed
Marcusliat
Calcite | Level 5

Hi,

I have two grouping variables: date and name. There is one day of data for each staff for illustration. I would like to calculate the number of worked hours per day per name. Beth works 9:30 pm to 7:30 am. Note that date were arranged in increasing order so it started at 00:24:01 up to 07:16:17, then 21:44:47 to 23:45:30.  Alex works 10 am to 10 pm. I would like to calculate the total count per day per name. For each date I would like also to create a corresponding datetime: top of the hour if minute of date is less than 30, half past the hour if minute of date is greater than 30.

I appreciate any help.

DATA staff;

   LENGTH status $ 6;

   INPUT @1 date datetime16. @18 name$ @23 count

CARDS;

19APR12:00:24:01 Beth 4

19APR12:00:43:00 Beth 3

19APR12:01:13:42 Beth 0

19APR12:02:42:52 Beth 5

19APR12:04:24:04 Beth 5

19APR12:05:20:44 Beth 2

19APR12:06:07:51 Beth 0

19APR12:07:16:17 Beth 0

19APR12:21:44:47 Beth 6

19APR12:23:06:25 Beth 5

19APR12:23:45:30 Beth 2

20APR12:10:14:00 Alex 1              

20APR12:11:07:19 Alex 3              

20APR12:12:00:40 Alex 1              

20APR12:12:38:06 Alex 4              

20APR12:13:04:36 Alex 0              

20APR12:14:01:03 Alex 0              

20APR12:14:23:18 Alex 1              

20APR12:15:20:22 Alex 6              

20APR12:16:46:00 Alex 1              

20APR12:18:09:02 Alex 1              

20APR12:19:04:47 Alex 2              

20APR12:20:57:22 Alex 1              

20APR12:21:45:00 Alex 1              

;

run;

6 REPLIES 6
ballardw
Super User

What would you do if someone works 10PM to 6AM? Which "day" should the total count for, each, begin or end of the period?

Do you want a dataset result or a report table?

What should the output look like?

You say that you want to calculate "worked hours per day". Does the first date/time listed represent actual start of work? Does the last represent end?

Marcusliat
Calcite | Level 5

If someone works for 10 PM to 6AM then that would be a different day:10PM to 12 midnight one day then 00 AM to 6AM the next day. In the example I used Alex schedule is always 9:30 pm to 7:30 am the next day. The first datetime does not necessarily represent start of work.

I would like

1.a data output for worked hours per day per staff based on the example data AND

2. the same example data table with additional column representing corresponding date as described earlier.

I hope it is clear.

Thanks for replying

Reeza
Super User

It's helpful if you link back to the original question.

I think you want some sort of variable to represent a SHIFT worked, where a worker can have multiple shifts per day, not a DATE.

You haven't specified how you would like to differentiate a SHIFT. I suggested a 5 hour delay, but what makes sense based on your business. Is a 2 hour time difference a 'break' or the end of SHIFT? 

So for Alex example above, then he works 2 SHIFTS if it crosses a date boundaries or is it one SHIFT?  That doesn't quite make sense I would think compared to someone who worked from Midnight to 7AM.  If only date matters, then the summary initially proposed would work.

 In the example I used Alex schedule is always 9:30 pm to 7:30 am the next day.
Marcusliat
Calcite | Level 5

Here is the data having start time for Beth (shift from 9:30 pm and end at 7:30 pm the next day) and Alex (shift from 10 am and end at 10 pm the same day).

19APR12:21:44:47 Beth 6

19APR12:23:06:25 Beth 5

19APR12:23:45:30 Beth 2

20APR12:00:24:01 Beth 4

20APR12:00:43:00 Beth 3

20APR12:01:13:42 Beth 0

20APR12:02:42:52 Beth 5

20APR12:04:24:04 Beth 5

20APR12:05:20:44 Beth 2

20APR12:06:07:51 Beth 0

20APR12:07:16:17 Beth 0

20APR12:10:14:00 Alex 1

20APR12:11:07:19 Alex 3

20APR12:12:00:40 Alex 1

20APR12:12:38:06 Alex 4

20APR12:13:04:36 Alex 0

20APR12:14:01:03 Alex 0

20APR12:14:23:18 Alex 1

20APR12:15:20:22 Alex 6

20APR12:16:46:00 Alex 1

20APR12:18:09:02 Alex 1

20APR12:19:04:47 Alex 2

20APR12:20:57:22 Alex 1

20APR12:21:45:00 Alex 1

;

run;

The output for summary would look like

namedatedurationcount
Beth19-Apr-122:1517
Beth20-Apr-127:1619
Alex20-Apr-1211:3122

where duration is in hours and minutes.

The output for a new table would look like

Date                          NewDate             Name Count

19APR12:21:44:47 19APR12:21:30:00 Beth 6

19APR12:23:06:25 19APR12:23:00:00 Beth 5

19APR12:23:45:30 19APR12:23:30:00 Beth 2

19APR12:00:24:01 19APR12:00:00:00 Beth 4

20APR12:00:43:00 20APR12:00:30:00 Beth 3

20APR12:01:13:42 20APR12:01:00:00 Beth 0

20APR12:02:42:52 20APR12:02:30:00 Beth 5

20APR12:04:24:04 20APR12:04:00:00 Beth 5

20APR12:05:20:44 20APR12:05:00:00 Beth 2

20APR12:06:07:51 20APR12:06:00:00 Beth 0

20APR12:07:16:17 20APR12:07:00:00 Beth 0

20APR12:10:14:00 20APR12:10:00:00 Alex 1

20APR12:11:07:19 20APR12:11:00:00 Alex 3

20APR12:12:00:40 20APR12:12:00:00 Alex 1

20APR12:12:38:06 20APR12:12:30:00 Alex 4

20APR12:13:04:36 20APR12:13:00:00 Alex 0

20APR12:14:01:03 20APR12:14:00:00 Alex 0

20APR12:14:23:18 20APR12:14:00:00 Alex 1

20APR12:15:20:22 20APR12:15:00:00 Alex 6

20APR12:16:46:00 20APR12:16:30:00 Alex 1

20APR12:18:09:02 20APR12:18:00:00 Alex 1

20APR12:19:04:47 20APR12:19:00:00 Alex 2

20APR12:20:57:22 20APR12:20:30:00 Alex 1

20APR12:21:45:00 20APR12:21:30:00 Alex 1

Thanks.

ballardw
Super User

Without a start time and end time then requirement 1 can not be met with the data in the data set as currently proposed.

Any approach would be based on "guessing" as start time and we wouldn't have a clue regarding the end time.

We can calculate intervals between the first and last, and insert a break for midnight.

Please post what you expect the output to look like given your example data (which does not cross midnight, so you may want to provide some example that does).

Marcusliat
Calcite | Level 5

Here is the data having start time for Beth (9:30 pm and end at 7:30 pm the next day) and Alex 10 am and end at 10 pm the same day)

19APR12:21:44:47 Beth 6

19APR12:23:06:25 Beth 5

19APR12:23:45:30 Beth 2

20APR12:00:24:01 Beth 4

20APR12:00:43:00 Beth 3

20APR12:01:13:42 Beth 0

20APR12:02:42:52 Beth 5

20APR12:04:24:04 Beth 5

20APR12:05:20:44 Beth 2

20APR12:06:07:51 Beth 0

20APR12:07:16:17 Beth 0

20APR12:10:14:00 Alex 1

20APR12:11:07:19 Alex 3

20APR12:12:00:40 Alex 1

20APR12:12:38:06 Alex 4

20APR12:13:04:36 Alex 0

20APR12:14:01:03 Alex 0

20APR12:14:23:18 Alex 1

20APR12:15:20:22 Alex 6

20APR12:16:46:00 Alex 1

20APR12:18:09:02 Alex 1

20APR12:19:04:47 Alex 2

20APR12:20:57:22 Alex 1

20APR12:21:45:00 Alex 1

;

run;

The output for summary would look like

namedatedurationcount
Beth19-Apr-122:1517
Beth20-Apr-127:1619
Alex20-Apr-1211:3122

where duration is in hours and minutes.

The output for a new table would look like

Date                          NewDate             Name Count

19APR12:21:44:47 19APR12:21:30:00 Beth 6

19APR12:23:06:25 19APR12:23:00:00 Beth 5

19APR12:23:45:30 19APR12:23:30:00 Beth 2

19APR12:00:24:01 19APR12:00:00:00 Beth 4

20APR12:00:43:00 20APR12:00:30:00 Beth 3

20APR12:01:13:42 20APR12:01:00:00 Beth 0

20APR12:02:42:52 20APR12:02:30:00 Beth 5

20APR12:04:24:04 20APR12:04:00:00 Beth 5

20APR12:05:20:44 20APR12:05:00:00 Beth 2

20APR12:06:07:51 20APR12:06:00:00 Beth 0

20APR12:07:16:17 20APR12:07:00:00 Beth 0

20APR12:10:14:00 20APR12:10:00:00 Alex 1

20APR12:11:07:19 20APR12:11:00:00 Alex 3

20APR12:12:00:40 20APR12:12:00:00 Alex 1

20APR12:12:38:06 20APR12:12:30:00 Alex 4

20APR12:13:04:36 20APR12:13:00:00 Alex 0

20APR12:14:01:03 20APR12:14:00:00 Alex 0

20APR12:14:23:18 20APR12:14:00:00 Alex 1

20APR12:15:20:22 20APR12:15:00:00 Alex 6

20APR12:16:46:00 20APR12:16:30:00 Alex 1

20APR12:18:09:02 20APR12:18:00:00 Alex 1

20APR12:19:04:47 20APR12:19:00:00 Alex 2

20APR12:20:57:22 20APR12:20:30:00 Alex 1

20APR12:21:45:00 20APR12:21:30:00 Alex 1

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 6 replies
  • 1097 views
  • 3 likes
  • 3 in conversation