DATA Step, Macro, Functions and more

finding the number of business days

Reply
Occasional Contributor
Posts: 11

finding the number of business days

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
Respected Advisor
Posts: 3,156

finding the number of business days

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

Occasional Contributor
Posts: 11

Re: finding the number of business days

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

Attachment
Occasional Contributor
Posts: 11

Re: finding the number of business days

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.

Respected Advisor
Posts: 3,799

Re: finding the number of business days

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

Respected Advisor
Posts: 3,156

Re: finding the number of business days

Posted in reply to data_null__

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

PROC Star
Posts: 7,469

Re: finding the number of business days

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.

Respected Advisor
Posts: 3,799

Re: finding the number of business days

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

Trusted Advisor
Posts: 1,301

finding the number of business days

Posted in reply to data_null__

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.

Respected Advisor
Posts: 3,799

finding the number of business days

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.

PROC Star
Posts: 7,469

finding the number of business days

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;

Super User
Posts: 19,780

Re: finding the number of business days

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.

PROC Star
Posts: 7,469

finding the number of business days

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

Super User
Posts: 19,780

finding the number of business days

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.

Trusted Advisor
Posts: 1,301

finding the number of business days

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.

Ask a Question
Discussion stats
  • 21 replies
  • 2602 views
  • 0 likes
  • 7 in conversation