Date difference in days excluding holidays and sundays..

[syam_india_kochi]

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

[DF]

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.

[Ksharp]

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

[Patrick]

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

[Peter_C]

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

[Ksharp]

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

[data_null__]

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_sect...

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;

[Ksharp]

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

Ksharp

[hellind]

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.

[data_null__]

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'

[Ksharp]

I prefer to Hash Table.

[Ksharp]

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

[bollurajkumar]

Hi

I want exclude holidays from a holidays list(dataset)

if it is possible Kindly explain