topic Re: Computing days between Dates with customized holidays and work days in SAS Programming

Computing days between Dates with customized holidays and work days

RandyStan — Sat, 18 Aug 2018 18:54:50 GMT

Dear All

I want to compute days between dates, taking account of holidays.

The holidays (non working days) and working days that fall on a Saturday are customized.

1. 05JAN2018 (FRIDAY) and 08JAN2018 (MONDAY) were holidays.

2. 17FEB2018 (SATURDAY) and 19FEB2018 (MONDAY) were working days.

The last day of each ID_ for each ID should be -50000

ID ID_A Date

1 A 02JAN2018

1 A 03JAN2018

1 A 04JAN2018

1 A 09JAN2018

1 A 12JAN2018

1 B 06FEB2018

1 B 08FEB2018

1 B 12FEB2018

2 A 16FEB2018

2 A 17FEB2018

2 A 20FEB2018

2 B 02FEB2018

2 B 05FEB2018

I want to construct a variable WORKDAYSBETWEENDATES

ID ID_A Date WORKDAYSBETWEENDATES

1 A 02JAN2018 1

1 A 03JAN2018 1

1 A 04JAN2018 1

1 A 09JAN2018 1

1 A 12JAN2018 -50000

1 B 06FEB2018 2

1 B 08FEB2018 1

1 B 12FEB2018 4

2 A 16FEB2018 1

2 A 17FEB2018 2

2 A 20FEB2018 -50000

2 B 02FEB2018 1

2 B 05FEB2018 -50000

Thank you in Advance

Re: Computing days between Dates with customized holidays and work days

Reeza — Sat, 18 Aug 2018 19:16:57 GMT

Why the -5000?

Here's a detailed write up on creating custom calendars, though if you have your own already that step is easier.

If you go the Usage Examples section you'll see how to add this to your intervals and then you can use INTNX with your custom calendar.

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

If you're having issues, post back with more details and your code.

Re: Computing days between Dates with customized holidays and work days

RandyStan — Sat, 18 Aug 2018 19:40:56 GMT

The -50000 is because that is the last line for variables ID and ID_A

These are not holidays that occur on fixed days of the year. They are random. For example in one year 08FEB2011 is a holiday; in another year the same holiday may fall on, for example, 12FEB2013.

Re: Computing days between Dates with customized holidays and work days

Reeza — Sat, 18 Aug 2018 20:51:01 GMT

A lot of holidays are like that, August long weekend and family day for example.

I don’t see how that affects the suggested solution. Which specific sections isn’t working as expected? Is it giving you incorrect results?

@RandyStan wrote:

The -50000 is because that is the last line for variables ID and ID_A

These are not holidays that occur on fixed days of the year. They are random. For example in one year 08FEB2011 is a holiday; in another year the same holiday may fall on, for example, 12FEB2013.

Re: Computing days between Dates with customized holidays and work days

RandyStan — Sun, 19 Aug 2018 04:12:59 GMT

Using the readings suggested by you I constructed the working days file.

Now I am having an issue with constructing the variable WORKDAYSBETWEENDATES

Do I specify, in the command line,

intervalds (have=workingdays)

and then use the INTCK function to find the work days between dates?

Thanks so much

Randy

Re: Computing days between Dates with customized holidays and work days

Reeza — Sun, 19 Aug 2018 22:20:59 GMT

@RandyStan wrote:

Do I specify, in the command line,

intervalds (have=workingdays)

and then use the INTCK function to find the work days between dates?

Thanks so much

Randy

Yes, but it's option intervalds etc....

For the INTCK, it looks like you're using the date ahead for the calculation.

Here's a little trick to get those dates in the same line:

data stocks;
merge sashelp.stocks 
    sashelp.stocks (firstobs=2 /*starts at obs=2 to move up one observation*/
                    keep=stock date /*keep only variables of interest*/                  
                    rename=date=next_date /*rename variables that are the same name in both datasets*/
                    );
by stock;

if last.stock then next_open = .;

days_between = intck('workingdays', date, next_date);

run;