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 ?
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;
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?
HB,
I have the data in the above format and I want to convert it to the expected report.
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.
Ballardw,
There is no rule.
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
Some things to think about:
The real data is similar to the have data, hours is the total worked hours by the employee(clock out -clock in).
@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?
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.