02-23-2017 01:56 PM - edited 02-23-2017 01:57 PM
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.
02-23-2017 02:56 PM
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.
02-23-2017 03:19 PM
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.
03-02-2017 09:44 PM
Here is my two datasets:
input Emp_ID Date Timeoff workingdays;
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
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.
input emply_ID dates productsold;
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
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.
03-02-2017 11:35 PM
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?
03-03-2017 07:08 AM
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;
merge new (in=in1) new1 (in-in2)
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.