BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tekish
Quartz | Level 8

Dear experts,

 

I have  data of record with clock in ad clock out. I want to extract the different shifts from the record.

 

here is the sample data

data have;

input username $ local_date $ local_day $ Clock_IN $ Clock_out $ hours $ jobcode $ 49-60;

cards;

 

aaaaa 12/11/17 Mon 08:56 10:31 1:59 Shift Total

aaaaa 12/11/17 Mon 10:31 10:42 0:17 Rest Break

aaaaa 12/11/17 Mon 10:42 12:54 2:2 Shift Total

aaaaa 12/11/17 Mon 12:54 13:26 0:53 Lunch Break

aaaaa 12/11/17 Mon 13:26 16:09 2:71 Shift Total

aaaaa 12/11/17 Mon 16:09 16:17 0:14 Rest Break

aaaaa 12/11/17 Mon 16:17 17:30 1:22 Shift Total

bbbbb 12/11/17 Mon 08:56 10:31 1:59 Shift Total

bbbbb 12/11/17 Mon 10:31 10:42 0:17 Rest Break

bbbbb 12/11/17 Mon 10:42 12:54 2:2 Shift Total

bbbbb 12/11/17 Mon 12:54 13:26 0:53 Lunch Break

bbbbb 12/11/17 Mon 13:26 16:09 2:71 Shift Total

bbbbb 12/11/17 Mon 16:09 16:17 0:14 Rest Break

bbbbb 12/11/17 Mon 16:17 17:30 1:22 Shift Total

run;

 

the expected Report

 

username

local_date

local_day

Clock in

Clock out

Shift Total_1

Rest Break1

Shift Total_2

Lunch

Break

Shift Total_3

Rest Break_2

Shift Total_4

Shift

Total

aaaaa

12/11/17

Mon

 08:56

17:30

1:59

0:17

2:2

0:53

2:71

0:14

1:22

8:56

bbbb

12/11/17

Mon

 09:00

18:00

2:00

0:15

2:00

1:00

3:00

0:15

0:70

9

 

 

How do I create the expected report ?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
data have;
input username $ local_date $ local_day $ Clock_IN : time. Clock_out : time. hours : time. jobcode $20.;
format Clock_IN  Clock_out  hours : time.;
cards;
aaaaa 12/11/17 Mon 08:56 10:31 1:59 Shift Total
aaaaa 12/11/17 Mon 10:31 10:42 0:17 Rest Break
aaaaa 12/11/17 Mon 10:42 12:54 2:2 Shift Total
aaaaa 12/11/17 Mon 12:54 13:26 0:53 Lunch Break
aaaaa 12/11/17 Mon 13:26 16:09 2:71 Shift Total
aaaaa 12/11/17 Mon 16:09 16:17 0:14 Rest Break
aaaaa 12/11/17 Mon 16:17 17:30 1:22 Shift Total
bbbbb 12/11/17 Mon 08:56 10:31 1:59 Shift Total
bbbbb 12/11/17 Mon 10:31 10:42 0:17 Rest Break
bbbbb 12/11/17 Mon 10:42 12:54 2:2 Shift Total
bbbbb 12/11/17 Mon 12:54 13:26 0:53 Lunch Break
bbbbb 12/11/17 Mon 13:26 16:09 2:71 Shift Total
bbbbb 12/11/17 Mon 16:09 16:17 0:14 Rest Break
bbbbb 12/11/17 Mon 16:17 17:30 1:22 Shift Total
;
run;

proc sql;
create table temp as
 select *,min(Clock_IN) as min format=time.,max(Clock_out) as max format=time.
  from have
   group by  username , local_date , local_day
    order by  username , local_date , local_day	, jobcode  ,hours;
quit;
data temp1;
 set temp;
 by username local_date local_day jobcode ;
 if first.jobcode then n=0;
 n+1;
run;
proc transpose data=temp1 out=want ;
by username local_date local_day min max;
id jobcode n;
var hours;
run;

View solution in original post

10 REPLIES 10
HB
Barite | Level 11 HB
Barite | Level 11

I hate that output.

 

It would be much easier to deal with dates and time in datetime format and not character format.  Can be converted.

 

Will there always be a clock in, two breaks, and a lunch for every record, every time?

 

Do you need to deal with sick, vacation, holiday, or other kinds of time?

 

Is this a real world problem or homework?

 

 

 

tekish
Quartz | Level 8

HB,

 

I have the data in the above format and I want to convert it to the expected report.

ballardw
Super User

Please post code examples into a code box opened with the forum {I} menu icon. As posted your data step has the approval status in the job code and approved is blank. The message windows on this forum reformat text and so the fixed columns for jobcode have been shifted.

 

It will likely go better with the times as actual time values instead of character as it appears that you want to calculate intervals to get "shifttotal_1" and "RestBreak1";

 

And if there is some rule for which times would be considered "ShiftTotal_1" or "RestBreak1" that would also help.

tekish
Quartz | Level 8

Ballardw,

 

There is no rule.

HB
Barite | Level 11 HB
Barite | Level 11

Well, okay then. 


We don't know if the records stay the same, we don't know nothing.

 

1.  Convert the local_date + clock and local_date + clock_out to datetimes (my_clock_in and my_clock_out) for each record.

2.  From the data as whole Select records for which job code is shift total

3.  Sort the data by user id and clock_IN.

 

For your report
Clock in appears to be clock_IN of the .first record for a user id

Shift Total_1 appears to be hours for the .first record for a user id

Clock out appears to be the clock_out for the .last record for a user id

Shift Total_4 (assuming you meant 4) appears to be the hours for the .last record

Shift_total appears to be the difference between my_clock_in on the .first record and my_clock_out on the .last record for a user.  This is where the datetime format is useful

 

4.  From this data subset, select records which are not .first or .last

Shift Total_2 appears to be hours for the .first record
Shift Total_3 (assuming you meant 3) appears to be hours for the .last record

 

5.  From the data as a whole, select records for which job code is rest break

6.  Sort by userid and clock_in

 

Rest Break1 appears to be hours for .first record for a user

Rest Break_2 appears to be hours for the .last record for a user

 

7.  Select records for which job code is lunch

Lunch Break appears to be hours

 

8.  Put it all together

andreas_lds
Jade | Level 19

Some things to think about:

  • Two columns in the report are named "Shift Total_2", a mistake?
  • The data in have does not match the data in the expected output.
  • "hours" is not the difference between "clock_in" and "clock_out".
  • "approved_status" is unused, why keeping it in "want"?
  • How should the report look like if someone skipped a break?
tekish
Quartz | Level 8

,

 

The real data is similar to the have data, hours is the total worked hours by the employee(clock out -clock in).

ballardw
Super User

@tekish wrote:

,

 

The real data is similar to the have data, hours is the total worked hours by the employee(clock out -clock in).


Is there anything entered for what an employee is out such as a medical appointment? What do you want in the output if only one shift value is present and no breaks, which could happen when out for medical appointment or similar?

 

Ksharp
Super User
data have;
input username $ local_date $ local_day $ Clock_IN : time. Clock_out : time. hours : time. jobcode $20.;
format Clock_IN  Clock_out  hours : time.;
cards;
aaaaa 12/11/17 Mon 08:56 10:31 1:59 Shift Total
aaaaa 12/11/17 Mon 10:31 10:42 0:17 Rest Break
aaaaa 12/11/17 Mon 10:42 12:54 2:2 Shift Total
aaaaa 12/11/17 Mon 12:54 13:26 0:53 Lunch Break
aaaaa 12/11/17 Mon 13:26 16:09 2:71 Shift Total
aaaaa 12/11/17 Mon 16:09 16:17 0:14 Rest Break
aaaaa 12/11/17 Mon 16:17 17:30 1:22 Shift Total
bbbbb 12/11/17 Mon 08:56 10:31 1:59 Shift Total
bbbbb 12/11/17 Mon 10:31 10:42 0:17 Rest Break
bbbbb 12/11/17 Mon 10:42 12:54 2:2 Shift Total
bbbbb 12/11/17 Mon 12:54 13:26 0:53 Lunch Break
bbbbb 12/11/17 Mon 13:26 16:09 2:71 Shift Total
bbbbb 12/11/17 Mon 16:09 16:17 0:14 Rest Break
bbbbb 12/11/17 Mon 16:17 17:30 1:22 Shift Total
;
run;

proc sql;
create table temp as
 select *,min(Clock_IN) as min format=time.,max(Clock_out) as max format=time.
  from have
   group by  username , local_date , local_day
    order by  username , local_date , local_day	, jobcode  ,hours;
quit;
data temp1;
 set temp;
 by username local_date local_day jobcode ;
 if first.jobcode then n=0;
 n+1;
run;
proc transpose data=temp1 out=want ;
by username local_date local_day min max;
id jobcode n;
var hours;
run;
art297
Opal | Level 21

I think that the following comes close to what you want:

data have;
  informat jobcode $11.;
  input username $ local_date $ local_day $ Clock_IN $ Clock_out $ hours $ jobcode &;
  cards;
aaaaa 12/11/17 Mon 08:56 10:31 1:59 Shift Total
aaaaa 12/11/17 Mon 10:31 10:42 0:17 Rest Break
aaaaa 12/11/17 Mon 10:42 12:54 2:2 Shift Total
aaaaa 12/11/17 Mon 12:54 13:26 0:53 Lunch Break
aaaaa 12/11/17 Mon 13:26 16:09 2:71 Shift Total
aaaaa 12/11/17 Mon 16:09 16:17 0:14 Rest Break
aaaaa 12/11/17 Mon 16:17 17:30 1:22 Shift Total
bbbbb 12/11/17 Mon 08:56 10:31 1:59 Shift Total
bbbbb 12/11/17 Mon 10:31 10:42 0:17 Rest Break
bbbbb 12/11/17 Mon 10:42 12:54 2:2 Shift Total
bbbbb 12/11/17 Mon 12:54 13:26 0:53 Lunch Break
bbbbb 12/11/17 Mon 13:26 16:09 2:71 Shift Total
bbbbb 12/11/17 Mon 16:09 16:17 0:14 Rest Break
bbbbb 12/11/17 Mon 16:17 17:30 1:22 Shift Total
;
run;

data need;
  set have;
  by username local_date;
  format _Shift_Total time5.;
  retain _Shift_Total;
  if first.local_date then do;
    i=1;
    _Shift_Total=input(Clock_IN,time5.);
  end;
  else if jobcode eq 'Shift Total' then i+1;
  if last.username then _Shift_Total=input(Clock_out,time5.)-_Shift_Total;
run;

data need;
  format Shift_Total time5.;
  do until (last.local_date);
    set need;
    by username local_date;
    if last.local_date then Shift_Total=_Shift_Total;
  end;
  do until (last.local_date);
    set need;
    by username local_date;
    output;
  end;
run;

proc transpose data=need out=want (where=(not missing(_NAME_)));
  by username local_date;
  copy Shift_Total;
  var hours;
  id jobcode i;
run;

Art, CEO, AnalystFinder.com

 

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
  • 10 replies
  • 1449 views
  • 1 like
  • 6 in conversation