topic Re: How To Count the number of weekends between two date columns in SAS Programming

How To Count the number of weekends between two date columns

sharath_rk — Sat, 29 Mar 2014 02:38:11 GMT

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

Re: How To Count the number of weekends between two date columns

RichardinOz — Sat, 29 Mar 2014 03:07:25 GMT

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

Re: How To Count the number of weekends between two date columns

RichardinOz — Sat, 29 Mar 2014 03:11:08 GMT

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

Re: How To Count the number of weekends between two date columns

Astounding — Sun, 30 Mar 2014 02:22:49 GMT

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?

Re: How To Count the number of weekends between two date columns

sharath_rk — Sun, 30 Mar 2014 03:05:15 GMT

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.

Re: How To Count the number of weekends between two date columns

sharath_rk — Sun, 30 Mar 2014 03:12:41 GMT

Richard,

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

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

Thanks.

Re: How To Count the number of weekends between two date columns

sharath_rk — Sun, 30 Mar 2014 04:10:51 GMT

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

Re: How To Count the number of weekends between two date columns

pradeepalankar — Sun, 30 Mar 2014 08:51:16 GMT

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

Re: How To Count the number of weekends between two date columns

Astounding — Sun, 30 Mar 2014 15:02:44 GMT

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.

Re: How To Count the number of weekends between two date columns

sharath_rk — Mon, 31 Mar 2014 04:31:52 GMT

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;

Re: How To Count the number of weekends between two date columns

sharath_rk — Mon, 31 Mar 2014 04:38:40 GMT

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 )

Re: How To Count the number of weekends between two date columns

pradeepalankar — Mon, 31 Mar 2014 09:51:21 GMT

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?

Re: How To Count the number of weekends between two date columns

Astounding — Mon, 31 Mar 2014 13:08:55 GMT

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;

Re: How To Count the number of weekends between two date columns

Trancho — Tue, 01 Apr 2014 08:30:22 GMT

Good day sharath_rk,
Use the following code to get the number of weekends btween two dates (assuming weekend days are Sunday and Saturday) :

data result (drop=i day_diff);

set have;

day_diff = deliverydate - orderdate + 1;

weekends = 0;

do i = 1 to day_diff;

if weekday(intnx('day',orderdate,i-1)) in (1,7) then weekends = weekends + 1;

end;

run;

Re: How To Count the number of weekends between two date columns

sharath_rk — Tue, 01 Apr 2014 09:57:48 GMT

Good Day,

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

File WORK.HAVE.DATA does not exist.

Re: How To Count the number of weekends between two date columns

sharath_rk — Tue, 01 Apr 2014 11:00:35 GMT

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

Re: How To Count the number of weekends between two date columns

pradeepalankar — Tue, 01 Apr 2014 11:31:17 GMT

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

Re: How To Count the number of weekends between two date columns

pradeepalankar — Tue, 01 Apr 2014 11:36:21 GMT

this link also serve the same purpose

http://blogs.sas.com/content/sasdummy/2011/05/09/calculating-the-number-of-working-days-between-two-dates/

just few modification were needed to get your desired output.

Re: How To Count the number of weekends between two date columns

sharath_rk — Tue, 01 Apr 2014 11:37:53 GMT

Thank you Pradeep. I need the count of weekends for another calculation and in your code provided above is there a way to not enter datalines as i have 300,000 rows with diff dates between two columns and the third column should just give the total business days. Instead of datalines can i reference the data set.

Re: How To Count the number of weekends between two date columns

pradeepalankar — Tue, 01 Apr 2014 12:12:51 GMT

there are actually two data steps used in the code, in second datastep you can set your dataset, ignore the previous data set with datalines.