Internal Matching - More than one day data

[angorwat]

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

[Jagadishkatam]

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

[angorwat]

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;

[Jagadishkatam]

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

[RichardinOz]

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