BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sharath_rk
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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.

View solution in original post

25 REPLIES 25
RichardinOz
Quartz | Level 8

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
Quartz | Level 8

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
Calcite | Level 5

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
PROC Star

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
Calcite | Level 5

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
Calcite | Level 5

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

Astounding
PROC Star

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
Calcite | Level 5

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
PROC Star

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
Obsidian | Level 7

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
Calcite | Level 5

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
Obsidian | Level 7

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
Calcite | Level 5


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
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 25 replies
  • 6108 views
  • 8 likes
  • 5 in conversation