topic Re: finding the number of business days in SAS Programming

finding the number of business days

bkoksal — Mon, 06 Feb 2012 09:37:45 GMT

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

finding the number of business days

Haikuo — Mon, 06 Feb 2012 12:48:46 GMT

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

Re: finding the number of business days

bkoksal — Mon, 06 Feb 2012 13:24:37 GMT

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

Re: finding the number of business days

bkoksal — Mon, 06 Feb 2012 13:27:32 GMT

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.

Re: finding the number of business days

data_null__ — Mon, 06 Feb 2012 13:36:36 GMT

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

Re: finding the number of business days

Haikuo — Mon, 06 Feb 2012 13:47:41 GMT

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

Re: finding the number of business days

art297 — Mon, 06 Feb 2012 14:17:32 GMT

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.

Re: finding the number of business days

data_null__ — Mon, 06 Feb 2012 15:08:48 GMT

art297 wrote:

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.

finding the number of business days

FriedEgg — Mon, 06 Feb 2012 16:25:32 GMT

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.

finding the number of business days

FriedEgg — Mon, 06 Feb 2012 16:42:25 GMT

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.

finding the number of business days

Astounding — Mon, 06 Feb 2012 17:03:45 GMT

Another approach is possible, which is to create your own format using your own "business day" scale. Assuming you have a list of holidays, you would need to create the equivalent of:

value busdt today='1' tomorrow='2' nextday='3' etc.

You have already made one key decision, which is if a date falls on a holiday or weekend, it should map to the same outcome as the next (rather than the previous) business day. In that case, the programming will be easier if you work backwards. Start with the final day of the time period you want to measure, equivalent to:

value busdt finalday = '9999' prior day = '9998' etc.

The details of such a program are complex but relatively short.

The advantage of such a format as that you can save it permanently and use it (relatively) easily:

interval = input( put(ending_day, busdt.), 4. ) - input( put(starting_day , busdt.), 4.);

Using cntlin= data sets, it would be equally plausible to set up a separate format for each subject in a study where the "holidays" vary by subject. The cntlin= data set can generate a separate, permanent format for each subject, using values for FMTNAME like pid001_, pid002_, etc. Using these formats would be a little trickier. You would have to switch from PUT to PUTN, to allow the name of the format to be data-driven based on the patient ID.

Good luck.

finding the number of business days

art297 — Mon, 06 Feb 2012 18:50:47 GMT

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

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;

finding the number of business days

data_null__ — Mon, 06 Feb 2012 19:28:02 GMT

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.

Re: finding the number of business days

Reeza — Mon, 06 Feb 2012 22:00:20 GMT

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.

finding the number of business days

Reeza — Mon, 06 Feb 2012 22:11:02 GMT

Since your dataset doesn't have the holidays or weekends you could consider creating a list of distinct dates from that list and a second list of all dates. This will allow you to identify dates that are holidays/weekends and create a lookup table that has all the dates and count of the business days before.

I've done this before when constructing dimensions for BI implementations, though I usually hardcode the holiday dates b/c I'm in Canada and the holidays are different and let SAS takes care of weekends.

The lookup table comes in handy for other things as well, ie identifying/grouping quarter, fiscal years, calendar months, etc...

finding the number of business days

art297 — Mon, 06 Feb 2012 23:22:31 GMT

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

finding the number of business days

Reeza — Tue, 07 Feb 2012 00:04:12 GMT

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.

finding the number of business days

bkoksal — Tue, 07 Feb 2012 06:41:30 GMT

All this discussion seems tpo me that it makes the problem more difficult than it is.

First of all, for example, in islamic calendar dates of some religious holidays are according to the lunar calendar and they change every day. So it seems not managable to hardcode the dates.

I imagine that the easiest solution is this. Just download dates for an index from the stock exchange of the country in question. For US, it is SP500, for example. Since the stock exchange is closed for the holidays, there will not be dates for those days. Then use this "dates" dataset to calculate the business days between any two dates.

This approach makes the need to have a list of the holidays redundant.

finding the number of business days

bkoksal — Tue, 07 Feb 2012 06:42:48 GMT

I meant the dates for holidays in islamic calendar changes every year.

finding the number of business days

art297 — Tue, 07 Feb 2012 13:25:26 GMT

Then I'll leave it for the coauthor of our upcomming SGF paper to respond, as he is the one who wrote the code for building an Islamic calendar interval dataset. I don't have any problem with posting it here in advance of SGF, as that way we can get some good tests of the code but, given it is his code, I'll leave the decision up to him.