Calculating time off in proc sql

[Malathi13]

Hi,

I'm trying to calculate time off for a person for the first quarter. I have three excel files one is the time off, number of working days in a quarter and the regular file where it has all working days.

I want to calculate the actual days a person has worked after subtracting his timeoff and holidays in a quarter.Any help on this is much appreciated. I want to calcualte it in proc sql.

Here are my two excel sample files. The third file is number of days he worked which I'm not providing.

[collinelliot]

I'm not brave enough to download your Excel files. Without seeing the data, I think you can probably do what you want by making calculations based on summary values of the variables in question, but if you can show a sample of your data in datalines I'm sure you'll get some good answers.

[Reeza]

I think there's two options here:

1. Create a custom calendar interval and use that to calculate the number of working days between dates, by using INTNX. This is probably easier to maintain and user later on. 

2. Create a custom calendar dataset (sounds like you already have that), use SQL to sum the number of working days between the two dates and then subtract the number of days off.

How did you build your calendar dataset?

Here's an example of how I strutuctred mine and generated my data set. There's a flag for working day, that's a 1 or 0, so you can select all records between your dates and sum the working day variable.

https://gist.github.com/statgeek/9606118

[Malathi13]

Hi,

Here is my two datasets:

data new;

infile datalines;

input Emp_ID  Date Timeoff workingdays;

cards;

111   2/1/17    8    4

112  2/17/17  24   2

113  2/20/17  40   0

114  2/24/17  32   1

115  1/23/17  16   3

116  2/24/17   0    5

116  1/20/17   4    4.5

;

run;

So this the employee who took off on these dates for those hours (timeoff) and when timeoff is subtracted from the working days then workingdays is the actual days worked. Usually the number of hours worked is 8 hours in a day so when a person took 8 hours off that means that person worked for only 4 days in that week.

Now my second dataset has just employee ID and dates.

e.g.

data new;

infile datalines;

input emply_ID dates  productsold;

cards;

111  2/1/17         10

111  2/13/17       11

111 2/21/17         12

112  2/19/17        10

115  2/21/17         13

118  3/1/17           10

119  2/15/17         12

;

run;

Some of the employees have not taken any timeoff in that week or quarter but others did. I want to calculate timeoff for the employees who took time off and the products sold by other employees after subtracting the timeoff for each employee. So I want to calculate the average products sold in a quarter by each employee in the working days.

can any one help me in this process. I just created a dummy datasets, they might be having any formatting issues but the concept I guess should be the same as described.

Thank you

M

[Reeza]

What have you tried, what are you having problems with? 

The number of hours off per employee is known. 

The number of sales per quarter is known. 

The number of hours available per quarter is fixed and known? 

Consolidate to a quarterly file for each dataset and merge by quarter. 

How do you define a quarter? Because it can cross weeks. 

What about people taking less than a whole day off, is 2 hrs less significant or different than 6 hours? 

[Malathi13]

Hi Reeza,

If a employee is taking time off for four hours or less we ignore them (doesn't  include in  calculation) and also if an employee adds weekends or holidays also we don't include them.

I sorted the datasets by emp_id and merged both the datasets by emply_id. But employees who didn't take timeoff are excluded when I merged them or for some reason the employees who didn't take time off are all coming as 40 hours time off and zero working days.

proc sort data= new; by emp_id; run;

proc sort data=new1; by emp_id; run;

data final;

merge new (in=in1)  new1 (in-in2)

by empl_id;

if in1;

run;

What I'm geting is

empl_id       timeoff    workeddays   productssold

111          8                   4                      40

112          16                3                       30

111          8              4                       40

113          32               1                     10

119       40                 0                    0

120        40                 0                      0 

121        40               0                       0

I don't remember exactly the emply_id from my previous datasets as I don't see them. But the emp_ids which are not in dataset new and are in new1 (e.g. 120 121 122 123 etc. )are coming as if they also took 40 hours off and have sold zero products whcih is not the case. The emp_id 120 121 and so on didn't take any timeoff in that quarter and sold 50 products in a week.

I want to get the results something like this

emp_id   timeoff    week#   daysworked      productssold

111            8                1            4                       40

112            16                1          3                       30

111            16               2           3                       30

113            32              2           1                        10

114             40             1           0                        0

115            24              1           2                       20

115            40             2            0                       0

115            16             6            3                       30

116             24             7          2                       20

118            8                9           4                     40

and so on.

Thank you

M