finding the number of business days

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
Posts: 3,167

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,

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

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.

Posts: 3,852

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

Posts: 3,167

Re: finding the number of business days

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: 8,164

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.

Posts: 3,852

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.

Posts: 1,318

finding the number of business days

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.

Posts: 3,852

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: 8,164

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: 23,711

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: 8,164

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: 23,711

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.

Posts: 1,318

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.

Discussion stats
• 21 replies
• 3181 views
• 0 likes
• 7 in conversation