How To Count the number of weekends between two date columns

[sharath_rk]

How To Count the number of weekends between two date columns. Is there a weekend function just like weekday function. Please advise.

[RichardinOz]

Here is a method of counting the number of weekdays between 2 dates:

Calculating the number of working days between two dates - The SAS Dummy

Use this method to find the number of weekend days by subtraction from the total number of days (end - start +1), divide by 2, and round up (use the ceil function)

Richard

[RichardinOz]

You will have to add one to the answer if weekday(start) = 1 (Sunday) and weekday(end) = 7 (Saturday), assuming end dates are counted if they are weekend days.

Richard

[sharath_rk]

Richard,

could you please be specific as to how can i get the count per below.

i need the count of weekends between order date column & delivery date column. irrespective of the start & end days i just need the count. For example the date range between order date column & delivery date column is from 15th march till 23 march the result should be 4. 15/16 & 22/23 are weekends. Please advise.

The function has to check the entire order date column & delivery date columns to give the count of weekends.

Thanks.

[Astounding]

There is no function to calculate the number of weekends.

You have to make four decisions first.  What if the start date is on a Saturday or Sunday?  What if the end date is on a Saturday or Sunday?  Does the weekend that includes the start date or end date get counted?

[sharath_rk]

i need the count of weekends between order date column & delivery date column. irrespective of the start & end days i just need the count. For example the date range between order date column & delivery date column is from 15th march till 23 march the result should be 4. 15/16 & 22/23 are weekends. Please advise.

[sharath_rk]

The start of week will be Saturday & end will be Friday. Please advise.

[Astounding · Accepted solution]

OK, that clarifies it.  Any weekend day should be counted.  Here's an easy way, not necessarily the fastest way if you have long time periods:

data want;

set have;

weekend_days=0;

do _n_=order_date to delivery_date;

  if weekday(_n_) in (1, 7) then weekend_days + 1;

end;

run;

I'm assuming here that if the order date is Sunday and the delivery date is the following Thursday then you want a count of 1.

[sharath_rk]

Thank you. I tried the code and got the below error. Please advise.

ERROR: Invalid DO loop control information, either the INITIAL or TO expression is missing or the BY expression is missing, zero,

or invalid.

data want;

set have;

weekend_days=0;

do _n_=ORD_ENTRY_DATE to DLVR_STRT_DATE;

  if weekday(_n_) in (1, 7) then weekend_days + 1;

end;

OE_TO_SHIP_BIZ_DAYS = OE_TO_SHIP_DAYS - weekend_days;

run;

[Astounding]

That message means that the program is correct but some of the data is bad.  More specifically, some values for either ORD_ENTRY_DATE or DLVR_STRT_DATE are missing.  In that case, the number of weekend days can't really be calculated.  So this fix will bypass the missing values, although it won't fix the data:

if (ORD_ENTRY_DATE > .) and (DLVR_STRT_DATE > .) then

do _n_ = ORD_ENTRY_DATE to DLVR_STRT_DATE;

[pradeepalankar]

hi check this, is this what you want:

data have;

input dateid orderdate :mmddyy. deliverydate :mmddyy.;

format orderdate deliverydate yymmdd.;

datalines;

1 1/5/2014 2/10/2014

2 1/7/2014 3/8/2014

3 2/3/2014 4/4/2014

4 2/10/2014 2/15/2014

5 2/10/2014 3/10/2014

6 3/15/2014 3/23/2014

;

data want(drop=i);

set have;

CountOfWeekend=0;

do i=orderdate to deliverydate;

if weekday(i) in (1) then CountOfWeekend+2;

end;

by dateid;

if last.dateid then output;

put (orderdate deliverydate CountOfWeekend)(=);

run;

output:

-------------

orderdate=14-01-05 deliverydate=14-02-10 CountOfWeekend=12

orderdate=14-01-07 deliverydate=14-03-08 CountOfWeekend=16

orderdate=14-02-03 deliverydate=14-04-04 CountOfWeekend=16

orderdate=14-02-10 deliverydate=14-02-15 CountOfWeekend=0

orderdate=14-02-10 deliverydate=14-03-10 CountOfWeekend=8

orderdate=14-03-15 deliverydate=14-03-23 CountOfWeekend=4

[sharath_rk]

Thank you Pradeep. It gives the count of weekends however i have two date columns and in the third column for each row i need the count of weekends to be calculated.I need the weekend day reduced in the third column when compared with order date & delivery date. i am not sure whether adding datalines would help as i need the calc to happen as per the dates in the order date & delivery date columns. Please advise. if i can get the count of weekend for each row between two date columns i will be good to go.

for example : row 1 --- order date --- 15th mar -- delivery date 17 th mar --- output would be 1 ( 15th, 16th & 17th would be 3 - 15th/16th are weekends & hence 1 )

                    row 2 --- order date ---- 16th mar-- delivery date 18th march --- output would be 2 ( 16th, 17th & 18th would be 3 and 16th is a weekend )
                   

[pradeepalankar]

hi,

In above comment you said "Thank you Pradeep. It gives the count of weekends" and then again " if i can get the count of weekend for each row between two date columns i will be good to go."

so isn't it what you are getting from code??

i am confused here .... can you draw eaxmple input and output data sets with columns you need?

[sharath_rk]

Pradeep,

Please see sample data below. i need the current total days column to give me the total business days between order dare & delivery date as per desired output. Please advise.

order date         delivery date       current total days                    desired output ( total days )

22MAR2014      24MAR2014            3                                        1 ( exclude 22 & 23 which are weekends )

23MAR2014      25MAR2014            3                                        2 exclude 23 weekend

21MAR2014      24MAR2014             4                                        2 exclude 22/23 weekend

20MAR2014      25MAR2014             6                                        4 exclude 22/23 weekend

23MAR2014      26AR2014                4                                        3exclude 23 weekend

[pradeepalankar]

Hi It would have been lot more simpler if you had provided this in begining, while you created the post, we were counting no of weekend days and your requirement if no of days without weekends:

you might have done this with little changes to the code provided earlier:

data have;

input dateid orderdate date9. deliverydate :date9.;

format orderdate deliverydate date9.;

datalines;

1 22MAR2014      24MAR2014

2 23MAR2014      25MAR2014

3 21MAR2014      24MAR2014

4 20MAR2014      25MAR2014

5 23MAR2014      26MAR2014

;

data want(drop=i);

set have;

DesiredOutputTotalDays=0;

CurrentTotalDays=0;

do i=orderdate to deliverydate;

if weekday(i) not in (1,7) then DesiredOutputTotalDays+1;

CurrentTotalDays+1;

end;

by dateid;

if last.dateid then output;

put (orderdate deliverydate CurrentTotalDays DesiredOutputTotalDays)(=);

run;

orderdate=22MAR2014 deliverydate=24MAR2014 CurrentTotalDays=3 DesiredOutputTotalDays=1

orderdate=23MAR2014 deliverydate=25MAR2014 CurrentTotalDays=3 DesiredOutputTotalDays=2

orderdate=21MAR2014 deliverydate=24MAR2014 CurrentTotalDays=4 DesiredOutputTotalDays=2

orderdate=20MAR2014 deliverydate=25MAR2014 CurrentTotalDays=6 DesiredOutputTotalDays=4

orderdate=23MAR2014 deliverydate=26MAR2014 CurrentTotalDays=4 DesiredOutputTotalDays=3