finding the number of business days

[bkoksal]

Dear SAS Users,

Let's assume that the date below consists of business days (for SP500 index downloaded from a finance website for example). We don't have observations for weekends and official holidays in this dataset.

How can I calculate the number of business days for the preceeding year for each date?

For example, if current date is 5-Jan-2011 than I need to count back in this data set and find the number of business days between 5-Jan-2010 and 5-Jan-2011. If 5-Jan-2010 is a Saturday, for example, then the counting should be between  7-Jan-2010 and 5-Jan-2011. Thanks in advance.

date

4-Jan-2010

5-Jan-2010

6-Jan-2010

7-Jan-2010

8-Jan-2010

11-Jan-2010

12-Jan-2010

13-Jan-2010

14-Jan-2010

15-Jan-2010

…

3-Jan-2011

4-Jan-2011

5-Jan-2011

6-Jan-2011

7-Jan-2011

10-Jan-2011

11-Jan-2011

12-Jan-2011

13-Jan-2011

14-Jan-2011

17-Jan-2011

[Haikuo]

Try the following code, it uses the combination of intck() and intnx(), plus 'weekday' as interval unit:

data have;

infile cards;

input date anydtdte21.;

format date date9.;

cards;

4-Jan-2010

5-Jan-2010

6-Jan-2010

7-Jan-2010

8-Jan-2010

11-Jan-2010

12-Jan-2010

13-Jan-2010

14-Jan-2010

15-Jan-2010

3-Jan-2011

4-Jan-2011

5-Jan-2011

6-Jan-2011

7-Jan-2011

10-Jan-2011

11-Jan-2011

12-Jan-2011

13-Jan-2011

14-Jan-2011

17-Jan-2011

;

data want;

set have;

weekdays=intck('weekday', date,intnx('year',date,-1,'sameday'));

run;

proc print;run;

Regards,

Haikuo

[bkoksal]

Hi Haikuo,

Thanks for your reply.

Consider the attached dates file. When I run the statement you suggested, I get numbers like 261. But for example if you look at 2-jan-1989, the number of business days between 4-jan-1988 and 2-jan-1989 is 253.

Maybe my description of the problem in my original post was not clear, but what I need to find is the number of business days between exactly one year before today and today.  (for each date of course).

bülent

[bkoksal]

I would like to make one point clear though. In my dates file, there are some official holidays. Therefore rather than using the weekday argument in SAS, I need to look back and count the number of days until one year ago.

[data_null__]

You need to create an Interval dataset to define a special interval to use in INTCK.

I forget the details exactly but you can find it in the documentation with examples in SAS/ETS UG.

INTERVALDS= System Option

[Haikuo]

Yes, data _null_ has pointed to the right dircection. You need manually acount for the holidays, and set up a dataset for these purpose. 'weekday' can only take care of Mon-Fri, regardless holidays.

Regards,

Haikuo

[art297]

I also agree with data_null and am currently investigating the various potential uses of the intervalds option as part of a paper for SGF that FriedEgg, KSharp and I will be presenting at this year's SGF.

However, the real reason I'm replying to your post is that, while hardly mentioned in the documentation, one of the variables you can establish in an interval data set is "cycle".  And, once you create such datasets and invoke the intervalds option specifying those datasets, all of the interveral functions appear to be useable.

In short, I think that either the "cycle" or "season" variables could be used as "weekday" substitutes.

[data_null__]

art297 wrote:
I also agree with data_null and am currently investigating the various potential uses of the intervalds option as part of a paper for SGF that FriedEgg, KSharp and I will be presenting at this year's SGF.

Here's and idea for your paper.  In a clinical trial subjects can go on and off Investigation Product(IP) the total time on IP is IPRISK, the off time could be thought of as holidays.  AE's (there may be thousands) can occur any time during the trial.  It is desired to find IPRISK at AE onset call that AERISK.  My thought was to create an interval data set to describe the time off IP for each subject.  SUBJID becomming the special INTERVAL name. A special interval for every subject.  Then it would be easy to calculate AERISK using

INTCK(SUBJID,TRTSTDT,AESTDT);

I'm sure the developers of the interval data sets and related functions must have had this example in mind.

[FriedEgg]

there is one place where I would see this causeing trouble.  The intervalds option has a maximum length of, I beleive, 250 characters.  This would make defining a custom interval file for each subject very unlikely unless you would process them in very small chunks and keep reassigning the option in a loop.

[data_null__]

I see I think.  Each custom interval is define as a separate INTERVALDS so I would need one data set for each subject.  I was thinking I could use one INTERVALDS to define all the custom intervals. 

I wonder if there is a way to trick it.

[art297]

There is an example in the documentation: http://support.sas.com/documentation/cdl/en/etsug/63348/HTML/default/viewer.htm#etsug_intervals_sect...

The example, specifically, is:

options intervalds=(BankingDays=BankDayDS);

data BankDayDS(keep=BEGIN);

   start = '15DEC1998'D;

   stop  = '15JAN2002'D;

   nwkdays = INTCK('WEEKDAY',start,stop);

   do i = 0 to nwkdays;

      BEGIN = INTNX('WEEKDAY',start,i);

      year = YEAR(BEGIN);

      if BEGIN ne HOLIDAY("NEWYEAR",year) and

         BEGIN ne HOLIDAY("MLK",year) and

         BEGIN ne HOLIDAY("USPRESIDENTS",year) and

         BEGIN ne HOLIDAY("MEMORIAL",year) and

         BEGIN ne HOLIDAY("USINDEPENDENCE",year) and

         BEGIN ne HOLIDAY("LABOR",year) and

         BEGIN ne HOLIDAY("COLUMBUS",year) and

         BEGIN ne HOLIDAY("VETERANS",year) and

         BEGIN ne HOLIDAY("THANKSGIVING",year) and

         BEGIN ne HOLIDAY("CHRISTMAS",year) then

      output;

   end;

   format BEGIN DATE.;

run;

data CountDays;

   start = '01JAN1999'D;

   stop  = '31DEC2001'D;

   ActualDays = INTCK('DAYS',start,stop);

   Weekdays   = INTCK('WEEKDAYS',start,stop);

   BankDays   = INTCK('BankingDays',start,stop);

   format start stop DATE.;

run;

[Reeza]

I don't know if the holiday function alone will work, because of the day in lieu. Ie Jan 2 is actually the day the stock market was closed this year because Jan 1 was on Sunday.

You can program around those things, but still needs to be considered.

[art297]

Agreed, but is that a problem with the method or with the dates that have been assigned to the holiday function?

[Reeza]

A bit of both?

The Holiday function is somewhat ineffective because you have to list all the holidays anyways, what I guess I'd want is a function such as isholiday that takes a date and returns if it's a holiday or day in lieu. The rules for day in lieu are pretty standard for banks so that could be a good starting point.

And the method uses the holiday function.

You might be able to work around that in the solution, but if you have to hardcode things might as well hardcode in the dates IMO.

[FriedEgg]

This post on sascommunity.org will give you a lot of the necessary components to use the intervalds option along with a generated list of holiday's (thanks to the holiday function) in order to build a set of business days which can be used along with intnx function to iterate through operational business days.

http://www.sascommunity.org/wiki/Generating_Holiday_Lists

The part of the paper which I contributed that Art spoke of in his post is an expansion on this routine that includes Jewish and Islamic holidays based off of there respective luni/solar calendars, and a expansion of the holiday function.