topic Re: How to transpose column in SAS Programming

How to transpose column

tekish — Tue, 19 Dec 2017 13:44:39 GMT

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

How do I create the expected report ?

Re: How to transpose column

HB — Mon, 18 Dec 2017 18:36:37 GMT

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?

Re: How to transpose column

ballardw — Mon, 18 Dec 2017 18:43:18 GMT

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.

Re: How to transpose column

tekish — Mon, 18 Dec 2017 18:52:59 GMT

Ballardw,

There is no rule.

Re: How to transpose column

tekish — Mon, 18 Dec 2017 18:54:46 GMT

HB,

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

Re: How to transpose column

HB — Mon, 18 Dec 2017 19:52:45 GMT

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

Re: How to transpose column

andreas_lds — Tue, 19 Dec 2017 13:00:55 GMT

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?

Re: How to transpose column

tekish — Tue, 19 Dec 2017 13:47:40 GMT

andreas_lds,

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

Re: How to transpose column

Ksharp — Tue, 19 Dec 2017 14:54:58 GMT

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;

Re: How to transpose column

art297 — Tue, 19 Dec 2017 15:38:03 GMT

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

Re: How to transpose column

ballardw — Tue, 19 Dec 2017 16:59:42 GMT

@tekish wrote:

andreas_lds,

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?