Calculating a duration between dates and phasing over months

[Go210]

Hi Base SAS community,

I have tried looking for something similar to this issue but have not succeeded.

I am trying to calculate business working hours (excluding weekends, hopefully holidays too eventually with a list) between two dates in hours and then phase these hours over the months in between these two dates by a task duration.

I have been using a function in Excel [NETWORKDAYS.INTL] that can exclude weekends and also holidays if you provide a list. [There might be a SAS equivalent]

Hopefully the below will help you understand my issue. (Assume the end date includes a full working day & 8 working hours a day)

Many thanks for your help in advanced.

Data:
Task	Start Date	End Date	Task Hours
A	2/08/2018	11/10/2018	200
B	6/02/2019	19/02/2019	30
C	1/11/2018	5/02/2019	100

Calculations that need to happen:

Calculations:	Task	Start Date	End Date	Number of Available Days (exclude Weekends)	Available Hours (8hrs / Day)	Monthly Task Hours
	A	2/08/2018	31/08/2018	22	176	86.3	*Calc (176 / Total Hours (176,160,72=408)*200(Task Hours)
	A	1/09/2018	30/09/2018	20	160	78.4	*Calc (160 / Total Hours (176,160,72=408)*200(Task Hours)
	A	1/10/2018	11/10/2018	9	72	35.3	*Calc (72 / Total Hours (176,160,72=408)*200(Task Hours)
	B	6/02/2019	19/02/2019	10	80	30	*Calc (80/80)*30
	C	1/11/2018	30/11/2018	22	176	31.9	*Calc (176 / 552 *100(Task Hours)
	C	1/12/2018	31/12/2018	21	168	30.4	*Calc (168 / 552 *100(Task Hours)
	C	1/01/2019	31/01/2019	23	184	33.3	*Calc (184 / 552 *100(Task Hours)
	C	1/02/2019	5/02/2019	3	24	4.3	*Calc (24/ 552 *100(Task Hours)

Expected SAS Output:

Output:
Task
	Start Date	End Date	Task Hours	Month-Year	Monthly Task Hours
A	2/08/2018	11/10/2018	200	Aug-18	86.3
A	2/08/2018	11/10/2018	200	Sep-18	78.4
A	2/08/2018	11/10/2018	200	Oct-18	35.3
B	6/02/2019	19/02/2019	30	Feb-18	30.0
C	1/11/2018	5/02/2019	100	Nov-18	31.9
C	1/11/2018	5/02/2019	100	Dec-18	30.4
C	1/11/2018	5/02/2019	100	Jan-19	33.3
C	1/11/2018	5/02/2019	100	Feb-19	4.3

[RW9]

Post test data in the form of a datastep, not here to type it in.  As such this is just an overview:
1) Expand the dates with a do loop so you have one record per date.  

2) Merge onto this dataset your list of holiday dates.

3) With this expanded dataset, have a flag which is set for either weekday(date) in (1,7) - i.e. weekends, or if date=merged date then flag as well.

4) Then summarise the dataset ignoring the records you flagged and multiply by number of working hours per day.

[Go210]

Thanks for your reply

Here is the dataset have for testing. My dates are SAS dates.

data have;
infile datalines dlm=',';
input Task $ Start_Date: DDMMYY10. End_Date: DDMMYY10. Task_Hours;
datalines;
A,02/08/2018,11/10/2018,200
B,06/02/2019,19/02/2019,30
C,01/11/2018,05/02/2019,100
;

I was thinking of needing to write a function to encapsulate the the holidays dates along with the intck(Weekday,start,end) function. Something which mimics the excel function NETWORKDAYS. Your suggestion of just creating a dataset of holidays would work too.

[RW9]

So here is an example (its a bit verbose and not optimised - you could for instance look at proc expand to get larger dataset), but should be close to what you want:

data have;
  infile datalines dlm=',';
  input Task $ Start_Date: DDMMYY10. End_Date: DDMMYY10. Task_Hours;
datalines;
A,02/08/2018,11/10/2018,200
B,06/02/2019,19/02/2019,30
C,01/11/2018,05/02/2019,100
;
run;

data hols;
  date='05aug2018'd; output;
  date='08aug2018'd; output;
run;

/* Expand */
data inter;
  set have;
  do date=start_date to end_date;
    output;
  end;
run;

/* Add flag */
proc sql;
  create table inter2 as
  select a.*,
         case when b.date ne . or weekday(a.date) in (1,7) then 1
              else 0 end as flag
  from   inter a
  left join hols b
  on     a.date=b.date;
quit;

/* Summarise based on 8 hours per day where flag ne 1 */
proc sql;
  create table want as
  select task,
         start_date,
         end_date,
         count(*) as days,
         calculated days * 8 as hours
  from   inter2
  group by task,start_date,end_date;
quit;

[Go210]

Thanks RW9.

I can come configure this a bit further to assist in the output.

[ballardw]

---

@Go210 wrote:

Thanks for your reply

Here is the dataset have for testing. My dates are SAS dates.

data have;
infile datalines dlm=',';
input Task $ Start_Date: DDMMYY10. End_Date: DDMMYY10. Task_Hours;
datalines;
A,02/08/2018,11/10/2018,200
B,06/02/2019,19/02/2019,30
C,01/11/2018,05/02/2019,100
;

I was thinking of needing to write a function to encapsulate the the holidays dates along with the intck(Weekday,start,end) function. Something which mimics the excel function NETWORKDAYS. Your suggestion of just creating a dataset of holidays would work too.

---

Holidays are location dependent. SAS does provide HOLIDAYNAME and HOLIDAYTEST functions that will report if the date is a holiday or specific holiday (many organizations work on some holidays such as Valentines Day, Columbus Day or Veterans day) in the US or Canada. If you need a different list of holidays you can create a data set with the holiday information using the DATEKEYS procedure with locale information so you could specify the locale to test.

[Astounding]

A couple of issues to consider first ...

Are you counting 8 hours per day or 7.5?  The total hours in your example aren't multiples of 8.

Do you count both the starting date and the ending date in the computations? 

Here's a solution that counts 8 hours per weekday (ignoring holidays) and includes both the start and end day:

data want;

set have;

total = 0;

do d = start_date to end_date;

   if (1 < weekday(d) < 7) then total + 8;

end;

drop d;

run;

Holidays is a more complex issue.  When pressed to do this, I actually turned the list of holidays into a format to remove some of the complexity from the DATA step:

proc format;

value holiday date1='Holiday'

date2='Holiday'

date3='Holiday'

other='Workday';

run;

Use unformatted dates (or date literals).  Then the DATA step could use:

 if (1 < weekday(d) < 7) and put(d, holiday.) = 'Workday' then total + 8;

[Go210]

It's 8 hours a day. The Task duration is an estimate of hours which is spread on the available monthly working hours between the start and end dates of the task.

It won't be an exact figure because the available work hours is not always equal to the task duration. 

Thanks for the proc format idea on the holidays issue. I didn't even consider that.

[Go210]

If my start date or end date is missing.... what error control do I have in the Do loop? is it continue?

data inter;
  set have;
  do date=start_date to end_date;
    output;
  end;
run;

Edit: I will just put an if statement before the loop. 🙂