topic Re: Date difference in days excluding holidays and sundays.. in SAS Programming

Date difference in days excluding holidays and sundays..

syam_india_kochi — Sun, 26 Jun 2011 18:28:30 GMT

Hi All,

I have a data set as shown below. (Actual data set contains more records)

city sol_id date1 date2

Kochi 0010 12mar2011 30may2011

Kochi 0011 07Apr2011 10jun2011

Delhi 0567 23feb2011 20jun2011

Mumbai 0710 17may2011 18may2011

Cherai 0110 12jan2011 26may2011

Cherai 0510 03jun2011 16jun2011

And i have the list of holidays peratining to each city in another data set like this.

city holiday

Kochi 16mar2011

Kochi 02may2011

kochi 03may2011

Delhi 01jan2011

Delhi 28feb2011

Delhi 16jun2011

Mumbai 01jan2011

Mumbai 09jan2011

Cherai 12jan2011

Cherai 13feb2011

Cherai 06jun2011

Cherai 11jun2011

I wanna find out the the number of working days between date1 and date2 from the first data set excluding all sundays as well as all holidays falling in between from the second data set..

I would prefer to have a macro as my both tables contains more number of records(cities).

Thanks in advance..

Date difference in days excluding holidays and sundays..

DF — Sun, 26 Jun 2011 23:21:46 GMT

This article talks about using proc FCMP to do this, using a dataset of holidays. I guess it could be adapted to also allow you to specfiy a city/location as part of the calculation. You'd need to be running 9.2 as well of course! (If not the logic can likely be replicated in a data step, and/or via a macro).

http://sas-x.com/2011/05/calculating-the-number-of-working-days-between-two-dates/

HTH.

Re: Date difference in days excluding holidays and sundays..

Ksharp — Mon, 27 Jun 2011 05:25:32 GMT

There are no need to use macro for your simple situation, I think .

data date;
input city   $  sol_id   $  date1  : date9.   date2 : date9.;
format date1 date2 date9.;
datalines;
Kochi     0010     12mar2011     30may2011
Kochi     0011     07Apr2011     10jun2011
Delhi      0567     23feb2011      20jun2011
Mumbai  0710     17may2011    18may2011
Cherai    0110      12jan2011      26may2011
Cherai    0510      03jun2011      16jun2011
;
run;
data holiday;
input city   $  holiday : date9.;
format holiday date9.;
datalines;
Kochi     16mar2011
Kochi     02may2011
Kochi     03may2011
Delhi     01jan2011
Delhi     28feb2011
Delhi     16jun2011
Mumbai     01jan2011
Mumbai     09jan2011
Cherai     12jan2011
Cherai     13feb2011
Cherai     06jun2011
Cherai     11jun2011
;
run;

data temp;
set date;
num_holiday=0;
do i=1 to _nobs;
  set holiday(rename=(city=_city))  nobs=_nobs point=i;
  if city eq _city and holiday ge date1 and holiday le date2 then num_holiday+1;
  if i=_nobs then output;
end;
drop i _city holiday;
run;

data want;
set temp;
num_workday=date2-date1-num_holiday+1;
run;

Ksharp

Re: Date difference in days excluding holidays and sundays..

Patrick — Mon, 27 Jun 2011 09:56:04 GMT

Hi Ksharp

The OT asked for the number of working days for a 6 days week only (excluding Sunday).

Assuming that working days are from Monday to Friday and that the holiday data set has no days on a Sun wouldn't the calculation in your code be:

num_workday=intck('weeday1w',date1,date2)-num_holiday;

Cheers

Patrick

Re: Date difference in days excluding holidays and sundays..

Peter_C — Mon, 27 Jun 2011 13:20:22 GMT

why is no one suggesting INTERVALDS option where you can specify a data set that defines your custom interval.?

Re: Date difference in days excluding holidays and sundays..

Ksharp — Tue, 28 Jun 2011 03:58:55 GMT

Hi Patrick.

Yes. I did not consider this situation, thanks for your reminder.

But if OP want ,he or she also can add Sunday , Saturday into the second dataset ,you see?

Cheers!

Ksharp

Re: Date difference in days excluding holidays and sundays..

Ksharp — Tue, 28 Jun 2011 04:02:41 GMT

Hi Peter

Can you give us an example to display how to use the second dataset as a inter-range dataset to achieve it.

It is very interesting .I think.

Cheers

Ksharp

Re: Date difference in days excluding holidays and sundays..

data_null__ — Tue, 28 Jun 2011 20:30:21 GMT

This program is copied from the example of counting days omitting holidays found here http://support.sas.com/documentation/cdl/en/etsug/63348/HTML/default/viewer.htm#etsug_intervals_sect008.htm

The only difference is this program creates 4 custom intervals instead of one. I use a data step and hash to create all four custom intervals in one step.

dm 'clear log; clear output';

proc datasets kill;

run;

data range;

input (city sol_id)($) (date1 date2)(:date.);

format date: weekdate.;

cards;

Kochi 0010 12mar2011 30may2011

Kochi 0011 07Apr2011 10jun2011

Kochi 0011 16mar2011 17mar2011

Kochi 0011 16mar2011 16mar2011

Kochi 0011 17mar2011 18mar2011

Kochi 0011 17mar2011 17mar2011

Delhi 0567 23feb2011 20jun2011

Mumbai 0710 17may2011 18may2011

Cherai 0110 12jan2011 26may2011

Cherai 0510 03jun2011 16jun2011

;;;;

run;

data holiday;

input city $ holiday :date.;

format holiday date9.;

cards;

Kochi 16mar2011

Kochi 02may2011

Kochi 03may2011

Delhi 01jan2011

Delhi 28feb2011

Delhi 16jun2011

Mumbai 01jan2011

Mumbai 09jan2011

Cherai 12jan2011

Cherai 13feb2011

Cherai 06jun2011

Cherai 11jun2011

;;;;

run;

*** Create custom interval data sets;

data _null_;

if 0 then set holiday;

** Lookup table for holidays by city;

declare hash holidayH(dataset:'Holiday',ordered:'Y');

holidayH.definekey('city','holiday');

holidayH.definedata('city','holiday');

holidayH.definedone();

holidayH.output(dataset:'holicity');

** List of CITYs, really just a fancy array;

declare hash cityH(dataset:'Holiday',ordered:'Y');

cityH.definekey('city');

cityH.definedata('city');

cityH.definedone();

cityH.output(dataset:'city');

declare hiter cityITER('cityH');

** Output: interval datasets one for each CITY/ custom interval;

attrib begin length=8 format=weekdate.;

declare hash intervalds(ordered:'Y');

intervalds.definekey('begin');

intervalds.definedata('begin');

intervalds.definedone();

attrib intervaldsOPTION length=$256;

do rc=cityITER.next() by 0 while(rc eq 0);

start = '01jan2011'd;

end = '30jun2011'd;

do begin = start to end;

isHoliday = not holidayH.check(key:city,key:begin);

isWeekend = weekday(begin) in(1,7);

if not(isHoliday or isWeekend) then rc=intervalds.add();

end;

intervaldsOPTION = catx(' ',intervaldsOPTION,catx('=',city,city));

intervalds.output(dataset:city);

intervalds.clear();

rc=cityITER.next();

end;

put intervaldsOPTION=;

call execute(cats('options intervalds=(',intervaldsOPTION,');'));

stop;

run;

proc options option=intervalds;

run;

proc report data=kochi panels=3 nowd;

define begin / display;

run;

data range2;

set range;

daysSpecial = intck(city,date1,date2);

days = intck('DAY',date1,date2);

WeekDays = intck('WEEKDAY',date1,date2);

run;

proc print;

run;

Re: Date difference in days excluding holidays and sundays..

Ksharp — Wed, 29 Jun 2011 02:55:47 GMT

I am also not familiar with this method.I need some more time to digest. Thanks your code.

Ksharp

Re: Date difference in days excluding holidays and sundays..

hellind — Wed, 10 Dec 2014 14:36:07 GMT

I have to share my script written for SAS 9.2 with a colleague who is running SAS 9.1.3.

And therefore I need an equivalent of INTCK("weekday", start_date, end_date);

The above code looks complicated to me. How would I modify the above just to incorporate counting the number of days between two dates excluding weekends?

Thanks.

Re: Date difference in days excluding holidays and sundays..

data_null__ — Wed, 10 Dec 2014 14:47:38 GMT

Are you saying that WEEKDAY is not a valid interval in SAS 9.1.3.

The example program I wrote for this thread uses a custom interval which I don't think applies to your question regarding INTCK('WEEKDAY'

Re: Date difference in days excluding holidays and sundays..

Ksharp — Sun, 14 Dec 2014 03:59:53 GMT

I prefer to Hash Table.

Re: Date difference in days excluding holidays and sundays..

bollurajkumar — Fri, 21 Apr 2017 04:24:54 GMT

I want exclude holidays from a holidays list(dataset)

if it is possible Kindly explain