BookmarkSubscribeRSS Feed
bkoksal
Calcite | Level 5

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
21 REPLIES 21
Haikuo
Onyx | Level 15

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
Calcite | Level 5

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
Calcite | Level 5

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__
Jade | Level 19

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
Onyx | Level 15

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
Opal | Level 21

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__
Jade | Level 19

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

FriedEgg
SAS Employee

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__
Jade | Level 19

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
Opal | Level 21

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
Super User

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
Opal | Level 21

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

Reeza
Super User

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
SAS Employee

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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