How To Count the number of weekends between two date columns

[sharath_rk]

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.

[pradeepalankar]

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.

[Trancho]

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;

[sharath_rk]

Good Day,

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

File WORK.HAVE.DATA does not exist.

[Trancho]

Mr. sharath_rk,

Replace the "have" data set (which is a dummy name) by the data set you are working on to get the required results.

[sharath_rk]

Thank you all. The issue has been resolved using below.

data want;

set have;

weekend_days=0;

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

do _n_=OE_DATE to DLVR_STRT_DATE;

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

end;

run;

[sharath_rk]

Hi,

in the below code is there a way to include the hour/24 to get the difference in hours between OE_DATE to DLVR_STRT_DATE. For example : OE_DATE = 22MAR2014:00:03:29.000000 & DLVR_STRT_DATE = 25MAR2014:13:29:27.000000 means order placed at 12:03 and delivered at 13:29 I need the calc to exclude weekends & reduce the difference of hours in the weekend.

data want;

set have;

weekend_days=0;

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

do _n_=OE_DATE to DLVR_STRT_DATE;

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

end;

run;

[sharath_rk]

Team,

In the below data i need the desired output in hours per below where 29th & 30th are weekends in which only weekend hours are to be excluded. In the first row the calculation should consider starting 31st mar 12:00AM till 31st mar 15:13:46.000000 which would be 15 hours. In the second row the calculation should start from 31st mar 12:00AM till 01APR2014:08:37:39 which would be 24 + 8 = 32 hours. Please advise how do i exclude the weekend hours to get the business hours between

ORD_ENTRY_DATE & DLVR_STRT_DTS.

ORD_ENTRY_DATE	DLVR_STRT_DTS	Desired Output In Hours
29MAR2014:00:06:17.000000	31MAR2014:15:13:46.000000	15
30MAR2014:00:32:39.000000	01APR2014:08:37:39.000000	32

[RichardinOz]

Try this

Data want ;

  Set have ;

  If Missing (ORD_ENTRY_DATE)

  Or Missing (DLVR_STRT_DTS)

  Or DLVR_STRT_DTS < ORD_ENTRY_DATE

       Then

            Do ;

                 Output ; /* Missing value for Hours */

                 Return ;

            End ;

  Time = 0 ;

  Date = ORD_ENTRY_DATE ;

  Do until (Date >= DLVR_STRT_DTS) ;

       End_Date = MIN(DLVR_STRT_DTS, INTNX ('DTDAY', Date, 1)) ;

       If Weekday (Datepart (Date)) in (1, 7) ;  /* Weekend */

       Else

            Time + (End_Date - Date) ; /* Seconds */

       Date = End_Date ;

  End ;

  Hours = INT (Time / 3600) ;

  Output ;

  Drop Date Time End_Date ;

Run ;

Alert - untested code.

The first part of the code tests for invalid values and returns a missing value .

The next part tests whether the date is a weekend day, and if not calculates the time in seconds from the start to the end (either midnight, or the given order and delivery timestamps).

Richard

[sharath_rk]

Richard,

The code worked Thank you very much.

Regards

Sharath

[pradeepalankar]

this link also serve the same purpose

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

just few modification were needed to get your desired output.