Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- Re: How To Count the number of weekends between two date columns

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 03-28-2014 10:38 PM
(7426 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

25 REPLIES 25

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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**

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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 )

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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=**22**MAR2014 deliverydate=**24**MAR2014 CurrentTotalDays=**3** DesiredOutputTotalDays=**1**

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

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

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

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

**SAS Innovate 2025** is scheduled for May 6-9 in Orlando, FL. Sign up to be **first to learn** about the agenda and registration!

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.

Ready to level-up your skills? Choose your own adventure.