DATA Step, Macro, Functions and more

How to transpose column

Accepted Solution Solved
Reply
Contributor
Posts: 70
Accepted Solution

How to transpose column

[ Edited ]

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 ?

 


Accepted Solutions
Solution
‎12-21-2017 01:13 PM
Super User
Posts: 10,691

Re: How to transpose column

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


All Replies
Super Contributor
Super Contributor
Posts: 266

Re: How to transpose column

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?

 

 

 

Contributor
Posts: 70

Re: How to transpose column

[ Edited ]

HB,

 

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

Super User
Posts: 13,338

Re: How to transpose column

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.

Contributor
Posts: 70

Re: How to transpose column

Ballardw,

 

There is no rule.

Super Contributor
Super Contributor
Posts: 266

Re: How to transpose column

[ Edited ]

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

Valued Guide
Posts: 521

Re: How to transpose column

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?
Contributor
Posts: 70

Re: How to transpose column

Posted in reply to andreas_lds

,

 

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

Super User
Posts: 13,338

Re: How to transpose column


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?

 

Solution
‎12-21-2017 01:13 PM
Super User
Posts: 10,691

Re: How to transpose column

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;
PROC Star
Posts: 8,149

Re: How to transpose column

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 260 views
  • 1 like
  • 6 in conversation