topic Re: summary by date in SAS Programming

summary by date

Marcusliat — Wed, 04 Feb 2015 19:47:33 GMT

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;

Re: summary by date

ballardw — Wed, 04 Feb 2015 19:59:32 GMT

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?

Re: summary by date

Marcusliat — Wed, 04 Feb 2015 20:25:55 GMT

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

Re: summary by date

Reeza — Wed, 04 Feb 2015 20:33:33 GMT

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.

Re: summary by date

ballardw — Wed, 04 Feb 2015 20:38:56 GMT

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).

Re: summary by date

Marcusliat — Thu, 05 Feb 2015 15:17:12 GMT

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

name	date	duration	count
Beth	19-Apr-12	2:15	17
Beth	20-Apr-12	7:16	19
Alex	20-Apr-12	11:31	22

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.

Re: summary by date

Marcusliat — Thu, 05 Feb 2015 15:17:50 GMT

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

name	date	duration	count
Beth	19-Apr-12	2:15	17
Beth	20-Apr-12	7:16	19
Alex	20-Apr-12	11:31	22

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