Internal Matching - More than one day data

Reply
Occasional Contributor
Posts: 18

Internal Matching - More than one day data

Hi

I have an employee working time details. It has last 3 days of data.

The fields are  emp_name, date, status.

The status field says about the employee working hours (paid hours), split up of status like meeting, leave, break and non-working hours (sum of all the split up)

All I need to extract the non-working time for each employee for each day.

If employee does not have any leave or meeting or break on the particular day, then his/her non-working time is zero.

Challenge I am facing is not all employee have 3 days of data. If there is an update in the back log then only it reflects in this file.

My approach

  • - Sort the data based on name, date.
  • - Split the datasets into three where status in (working time, non-working time).
  • - Process each file by validating the status and then concatenate into one.

I believe the above approach will work, but felt is not an ideal one, can we do it without the splitting the file or in alternative way. 

Please advise.

Sample data below.

NAME

DATE

STATUS

TIME

Sam

4/11/2013

Working time

480

Vicky

4/11/2013

Working time

480

Jay

3/11/2013

Working time

390

Jay

4/11/2013

Working time

330

Francisco

2/11/2013

Working time

480

Francisco

4/11/2013

Working time

480

Peter

4/11/2013

Working time

480

Peter

4/11/2013

IN MEETING

390

Peter

4/11/2013

NON-Working time

390

Eamonn

4/11/2013

Working time

480

Gregory

4/11/2013

Working time

480

Cruz

4/11/2013

Working time

480

Cruz

4/11/2013

SICK LEAVE

480

Cruz

4/11/2013

NON-Working time

480

Andy

2/11/2013

Working time

480

Andy

3/11/2013

Working time

480

Andy

4/11/2013

Working time

240

Andy

2/11/2013

SICK LEAVE

105

Andy

3/11/2013

MEETING

30

Andy

2/11/2013

NON-Working time

105

Andy

3/11/2013

NON-Working time

30

Tony

4/11/2013

Working time

480

James

4/11/2013

Working time

480

Michelle

4/11/2013

Working time

480

Ben

3/11/2013

Working time

480

Ben

4/11/2013

Working time

480

Steve

2/11/2013

Working time

480

Stev

4/11/2013

Working time

480

Wendy

2/11/2013

Working time

480

Wendy

3/11/2013

Working time

480

Wendy

4/11/2013

Working time

360

Wendy

3/11/2013

SICK LEAVE

480

Wendy

3/11/2013

NON-Working time

480

Trusted Advisor
Posts: 1,137

Re: Internal Matching - More than one day data

Please try the below code and let me know if this meets your expectation

proc import datafile="E:\source\book1.xls"

out=have

dbms=excel

replace;

getnames=yes;

run;

proc sort data=have ;

    by name date;

run;

data have_;

    set have;

    output;

    by      name date;

    if last.date and status eq 'Working time' then do;

    status='NON-Working time';

    time=0;

    output;

    end;

run;

Thanks,

Jagadish

Thanks,
Jag
Occasional Contributor
Posts: 18

Re: Internal Matching - More than one day data

Posted in reply to Jagadishkatam

Thanks Jagadhish, its working correctly.

But, I forget to mention about the percentage calculation. I.E., Non-working hrs/working hrs for each employee for each day to write into an new column.

I add the below steps after your code to calculate the PCT as below.

Please advise if it can be achievable in the same step.

PROC SORT data=hav_ (where=(code in (working hrs', 'non-working hrs')))

out=hav2;

by id date;

RUN;

DATA hav3;

    set hav2;

    by id  date;

  retain var1 var2;

  if first.date and code='working hrs' then var1=time;

  if last.date and code='non-working hrs' then var2=time;

    output;

RUN;

DATA hav4;

    set hav3;

  if code='working hrs' then delete;

  if code='non-working hrs' then ;

  PCT=var2/var1;

RUN;

Trusted Advisor
Posts: 1,137

Re: Internal Matching - More than one day data

Please try

proc import datafile="M:\samples.xls"

out=have

dbms=excel

replace;

getnames=yes;

run;

proc sort data=have ;

    by name date;

run;

data have_;

    set have;

    output;

    by name date;

    retain working;

    if first.date and status eq 'Working time' then

    working=time;

    if last.date and status eq 'Working time' then do;

    status='NON-Working time';

    time=0;

    output;

    end;

run;

data want;

    set have_;

    by name date;

    if last.date and status='NON-Working time' then perct=time/working*100;

    drop working;

run;

Thanks,

Jagadish

Thanks,
Jag
Super Contributor
Posts: 644

Re: Internal Matching - More than one day data

You could sort the data, omitting records like "SICK LEAVE", then transpose:

Proc sort

          data = have (where = (status IN ("Working time", "NON-Working time")))

          out = sorted

          ;

     By Name date descending Status ;

run ;

Proc Transpose

          data = sorted

          out = want

          ;

     By name date ;

     Id Status ;

run;

At this point you can if required use a datastep to subtract Non_working_time from working_time,

and if required  replace null values of Non_working_time with zero

    Non_working_time = sum(Non_working_time, 0) ;

If you need your data to be sequential you can transpose it back again.

Richard

Ask a Question
Discussion stats
  • 4 replies
  • 224 views
  • 0 likes
  • 3 in conversation