topic Re: Why isn't intervalds working with intck? in SAS Programming

Why isn't intervalds working with intck?

art297 — Wed, 10 May 2017 03:01:22 GMT

I know I'm probably overlooking something, but I figured that the following should show me the number or working days between two dates. However, it simply produces an error:

options intervalds=(wdays=wrkdays);
data wrkdays (keep=begin end);
  format begin end date9.;
  array holidays(6);
  do date = '01jan1971'd to '31dec2070'd ;
    if date eq intnx('year',date,0,'b') then do;
      call missing(of holidays(*));
      i=0;
    end;
    if date eq holiday('NEWYEAR', year(date)) or
       date eq holiday('USINDEPENDENCE',year(date)) or
       date eq holiday('THANKSGIVING', year(date)) or
       date eq holiday('CHRISTMAS', year(date)) or
       date eq holiday('MEMORIAL', year(date)) or
       date eq holiday('LABOR', year(date)) then do;
      i+1;
      holidays(i)=date;
    end;
    if date not in holidays and weekday(date) not in (1,7) then do;
      begin=date;
      end=date;
      output;
    end;
  end;
run;
data test;
  days=intck('wdays','1jan2017'd,'31jan2017'd)+1;
run;

Anyone have a clue as to what I did wrong?

Re: Why isn't intervalds working with intck?

Reeza — Wed, 10 May 2017 17:32:52 GMT

data wrkdays (keep=begin end);

Remove the END variable. For some reason it only wants the BEGIN variable. Works then, without error on SAS UE.

Editor's note: further in-depth explanation from INTCK developer @TammyJackson:

I'm the developer for custom intervals and INTCK and I can explain this to you.

Use this code to examine the beginning and ending of 2017:

data beg2017;
     set wrkdays(where=(begin>'25DEC2016'D and begin<'07JAN2017'D));
run;
proc print;run;
data end2017;
     set wrkdays(where=(begin>'25DEC2017'D and begin<'07JAN2018'D));
run;
proc print;run;

Here are the results:

Obs        begin          end
                             
  1    26DEC2016    26DEC2016
  2    27DEC2016    27DEC2016
  3    28DEC2016    28DEC2016
  4    29DEC2016    29DEC2016
  5    30DEC2016    30DEC2016
  6    02JAN2017    02JAN2017
  7    03JAN2017    03JAN2017
  8    04JAN2017    04JAN2017
  9    05JAN2017    05JAN2017
 10    06JAN2017    06JAN2017
 
Obs        begin          end
                             
 1     26DEC2017    26DEC2017
 2     27DEC2017    27DEC2017
 3     28DEC2017    28DEC2017
 4     29DEC2017    29DEC2017
 5     02JAN2018    02JAN2018
 6     03JAN2018    03JAN2018
 7     04JAN2018    04JAN2018
 8     05JAN2018    05JAN2018

Notice that January 1, 2017 is in a hole between the 30DEC2016 and 02JAN2017. January 1, 2017 is not included in any defined interval listed in the data set. That is, the date 01JAN2017 is not defined for the custom interval since the code identified it as a holiday. The same occurs for January 1, 2018. When the END variable is not present, it is impossible to have holes because END(i) is inferred as BEGIN(i+1)-1. Holes could be useful if observations absolutely should not occur during a time period. For instance, if the cash register gets opened while a store is closed, then that might be a sign of theft. However, if non-work days are considered to be an extension of the previous or next business days (as in ATM transactions), then you want to simply make your custom interval to include the off periods.

If you wanted to include the non-work days in the next business day, here is how I would modify your code:


options intervalds=(wdaysB=wrkdaysB);
data wrkdaysB (keep=begin end);
  format begin end date9.;
  array holidays(6);
  /* Last weekday preceding 01JAN1971 */
  end = INTNX('weekday','31DEC1970'D,0);
  do date = '01jan1971'd to '31dec2070'd ;
    if date eq intnx('year',date,0,'b') then do;
      call missing(of holidays(*));
      i=0;
    end;
    if date eq holiday('NEWYEAR', year(date)) or
       date eq holiday('USINDEPENDENCE',year(date)) or
       date eq holiday('THANKSGIVING', year(date)) or
       date eq holiday('CHRISTMAS', year(date)) or
       date eq holiday('MEMORIAL', year(date)) or
       date eq holiday('LABOR', year(date)) then do;
      i+1;
      holidays(i)=date;
    end;
    if date not in holidays and weekday(date) not in (1,7) then do;
      begin=end+1;
      end=date;
      output;
    end;
  end;
run;
data test;
  days=intck('wdaysB','1jan2017'd,'31jan2017'd)+1;
run;
data beg2017;
     set wrkdaysB(where=(begin>'25DEC2016'D and begin<'07JAN2017'D));
run;
proc print;run;
data end2017;
     set wrkdaysB(where=(begin>'25DEC2017'D and begin<'07JAN2018'D));
run;
proc print;run;

Here are the results - no holes:

Obs        begin          end
                             
 1     27DEC2016    27DEC2016
 2     28DEC2016    28DEC2016
 3     29DEC2016    29DEC2016
 4     30DEC2016    30DEC2016
 5     31DEC2016    02JAN2017
 6     03JAN2017    03JAN2017
 7     04JAN2017    04JAN2017
 8     05JAN2017    05JAN2017
 9     06JAN2017    06JAN2017
                             
Obs        begin          end
                             
 1     27DEC2017    27DEC2017
 2     28DEC2017    28DEC2017
 3     29DEC2017    29DEC2017
 4     30DEC2017    02JAN2018
 5     03JAN2018    03JAN2018
 6     04JAN2018    04JAN2018
 7     05JAN2018    05JAN2018
 8     06JAN2018    08JAN2018

Notice in this case, that a transaction that occurs on January 1, 2017 is considered to be the same business day as January 2, 2017.

Re: Why isn't intervalds working with intck?

art297 — Wed, 10 May 2017 03:26:57 GMT

@Reeza: Much appreciated! How did you deduce that was the problem?

Art, CEO, AnalystFinder.com

Re: Why isn't intervalds working with intck?

Reeza — Wed, 10 May 2017 03:34:54 GMT

I compared it to the example in the documentation.

The only difference I could find was END so I dropped it and that worked.

Re: Why isn't intervalds working with intck?

ChrisHemedinger — Wed, 10 May 2017 14:32:12 GMT

A while ago I implemented a version of NETWORKDAYS, a function cribbed from Microsoft Excel. I wrote it mainly as an example of FCMP, but folks have been using it as a utility.

@art297, We're chasing down the "END" variable nuance you discovered. @Rick_SAS found that the name ("end") seems to be a trigger. Dropping or renaming it solves the problem. If it's a bug, we'll dedicate the fix to you and give you naming rights.

Re: Why isn't intervalds working with intck?

art297 — Wed, 10 May 2017 14:46:54 GMT

@ChrisHemedinger: It's definitely an inconsistency. Paragraph 2 of the page http://support.sas.com/documentation/cdl/en/etsug/63939/HTML/default/viewer.htm#etsug_intervals_sect008.htm

states:

Create a data set that describes the custom interval. The data set must contain a BEGIN variable. It can also contain an END and a SEASON variable. It should contain a FORMAT statement for the BEGIN variable that specifies a SAS date, SAS datetime, or numeric format that matches the BEGIN variable data. If the END variable is present, it should also be included in the FORMAT statement. A numeric format that is not a SAS date or SAS datetime format indicates that the values are observation numbers. If the END variable is not present, then the implied value of END at each observation is one less than the value of BEGIN at the next observation.

However, I'll trade Naming rights for a free year's SAS license 🙂

Art, CEO, AnalystFinder.com

Re: Why isn't intervalds working with intck?

snoopy369 — Wed, 10 May 2017 14:51:39 GMT

I don't think it's END specifically (though I would obviously defer to Chris and Rick and company since they've got the inside scoop).

What I think is happening is that with END specified the way Art did - there are days that are not in _any_ interval. While I would implement this differently (to tolerate that), obviously it wasn't.

Reeza's suggestion of dropping END must mean that SAS assumes the END is the _next_ begin; so that works. But you could also make this work directly. Here's the simpler (weekday only, ignoring holiday) version, though Art's could be nearly identically modified.


data work.an_interval;
  format begin end date9.; 
  do begin = '01JAN2010'd to '01JAN2018'd;
    end=begin;
    do while (weekday(end) in (1,7));
      end=end+1;
    end;
    output;
    begin=end;
  end;
run;

options intervalds=(testint=work.an_interval);

data test;
  days=intck('testint','1jan2017'd,'31jan2017'd)+1;
run;

That works fine (while the BEGIN/END always equal solution doesn't, since 1/1/17 was a weekend).

Of course, I'm not sure what the value of END is then, since you need to have complete coverage; perhaps it lets you build intervals that intentionally exclude dates that shouldn't be considered valid inputs to the program?

Re: Why isn't intervalds working with intck?

TammyJackson — Wed, 10 May 2017 15:18:50 GMT

Hi,

I'm the developer for custom intervals and INTCK and I can explain this to you.

Use this code to examine the beginning and ending of 2017:

data beg2017;
set wrkdays(where=(begin>'25DEC2016'D and begin<'07JAN2017'D));
run;
proc print;run;

data end2017;
set wrkdays(where=(begin>'25DEC2017'D and begin<'07JAN2018'D));
run;
proc print;run;

Here are the results:

Obs        begin          end

1    26DEC2016    26DEC2016
2    27DEC2016    27DEC2016
3    28DEC2016    28DEC2016
4    29DEC2016    29DEC2016
5    30DEC2016    30DEC2016
6    02JAN2017    02JAN2017
7    03JAN2017    03JAN2017
8    04JAN2017    04JAN2017
9    05JAN2017    05JAN2017
10    06JAN2017    06JAN2017

Obs        begin          end

1     26DEC2017    26DEC2017
2     27DEC2017    27DEC2017
3     28DEC2017    28DEC2017
4     29DEC2017    29DEC2017
5     02JAN2018    02JAN2018
6     03JAN2018    03JAN2018
7     04JAN2018    04JAN2018
8     05JAN2018    05JAN2018

If you wanted to include the non-work days in the next business day, here is how I would modify your code:

options intervalds=(wdaysB=wrkdaysB);
data wrkdaysB (keep=begin end);
format begin end date9.;
array holidays(6);
/* Last weekday preceding 01JAN1971 */
end = INTNX('weekday','31DEC1970'D,0);
do date = '01jan1971'd to '31dec2070'd ;
    if date eq intnx('year',date,0,'b') then do;
      call missing(of holidays(*));
      i=0;
    end;
    if date eq holiday('NEWYEAR', year(date)) or
       date eq holiday('USINDEPENDENCE',year(date)) or
       date eq holiday('THANKSGIVING', year(date)) or
       date eq holiday('CHRISTMAS', year(date)) or
       date eq holiday('MEMORIAL', year(date)) or
       date eq holiday('LABOR', year(date)) then do;
      i+1;
      holidays(i)=date;
    end;
    if date not in holidays and weekday(date) not in (1,7) then do;
      begin=end+1;
      end=date;
      output;
    end;
end;
run;
data test;
days=intck('wdaysB','1jan2017'd,'31jan2017'd)+1;
run;

data beg2017;
set wrkdaysB(where=(begin>'25DEC2016'D and begin<'07JAN2017'D));
run;
proc print;run;

data end2017;
set wrkdaysB(where=(begin>'25DEC2017'D and begin<'07JAN2018'D));
run;
proc print;run;

Here are the results - no holes:

Obs        begin          end

1     27DEC2016    27DEC2016
2     28DEC2016    28DEC2016
3     29DEC2016    29DEC2016
4     30DEC2016    30DEC2016
5     31DEC2016    02JAN2017
6     03JAN2017    03JAN2017
7     04JAN2017    04JAN2017
8     05JAN2017    05JAN2017
9     06JAN2017    06JAN2017

Obs        begin          end

1     27DEC2017    27DEC2017
2     28DEC2017    28DEC2017
3     29DEC2017    29DEC2017
4     30DEC2017    02JAN2018
5     03JAN2018    03JAN2018
6     04JAN2018    04JAN2018
7     05JAN2018    05JAN2018
8     06JAN2018    08JAN2018

Notice in this case, that a transaction that occurs on January 1, 2017 is considered to be the same business day as January 2, 2017.

Re: Why isn't intervalds working with intck?

snoopy369 — Wed, 10 May 2017 15:22:33 GMT

Thanks for the detailed explanation, Tammy. Any chance the documentation could have a little extra bit added to it to make this more clear? I figured it out basically by guessing, but at least the way I read the documentation it's not explicit that holes in the begin/end rows will be treated this way.

Thanks!

Re: Why isn't intervalds working with intck?

TammyJackson — Wed, 10 May 2017 15:34:55 GMT

Quite often users just omit the END variable. It is extra programming, and if omitted, END[i] = BEGIN[i+1]-1, which gives the usually desired result.

http://support.sas.com/documentation/cdl/en/lefunctionsref/69762/HTML/default/viewer.htm#p0syn64amroombn14vrdzksh459w.htm#n1n5ezpid8tfkhn1nzmb3foy5opw

Reasons for Using Custom Time Intervals

Standard time intervals (for example, QTR, MONTH, WEEK, and so on) do not always fit the data. Also, some time series are measured at standard intervals where there are gaps in the data. For example, you might want to use fiscal months that begin on the 10th day of each month. In this case, using the MONTH interval is not appropriate because the MONTH interval begins on the 1st day of each month. You can use a custom interval to model data at a frequency that is familiar to the business and to eliminate gaps in the data by compressing the data. The intervals must be listed in ascending order. There cannot be gaps between intervals, and intervals cannot overlap.

OK, so there can be gaps between intervals - at your own risk. 🙂

Create a data set that describes the custom interval.

The data set must contain the begin variable; it can also contain end and season variables. In your SAS program, include a FORMAT statement that is associated with the begin variable that specifies a SAS date, datetime, or numeric format that matches the begin variable data. If an end variable is present, include it in the FORMAT statement. A numeric format that is not a SAS date or SAS datetime format indicates that the values are observation numbers. If the end variable is not present, the implied value of end at each observation is one less than the value of begin at the next observation.

Re: Why isn't intervalds working with intck?

snoopy369 — Wed, 10 May 2017 15:38:28 GMT

I think if that last sentence of the first paragraph were in the main
interval page it would be perfect (with the added detail you imply in the
editorial sentence of course a bonus) 🙂 Unfortunately that particular
page isn't one that came up in my first glance at the topic, though perhaps
it would have eventually...

Thanks!

##- Please type your reply above this line. Simple formatting, no
attachments. -##

Re: Why isn't intervalds working with intck?

TammyJackson — Wed, 10 May 2017 15:39:30 GMT

I can contact the documentation person and ask them to add a statement like this:

Omitting the END variable ensures that no gaps exist in the data definition. Since gaps in the data can cause errors in processing, if the END variable is included, extra care should be taken to properly define the END variable to avoid gaps.

Re: Why isn't intervalds working with intck?

snoopy369 — Wed, 10 May 2017 15:42:21 GMT

Perfect, thank you!

Edit: @ChrisD Email replies seem a bit buggy... both of them double-posted and include the ##- Please Type Your Reply Above ... text...

Re: Why isn't intervalds working with intck?

art297 — Wed, 10 May 2017 15:46:24 GMT

@TammyJackson: I agree and thanks for looking into it! @snoopy369: Joe, very much appreciated!

Art, CEO, AnalystFinder.com

Re: Why isn't intervalds working with intck?

ChrisHemedinger — Wed, 10 May 2017 17:24:11 GMT

Thanks @snoopy369 - I think you meant to mention me, not poor ChrisD. And all: I'm going to modify the accepted solution on this to include the excellent explanation from @TammyJackson, as I think that will serve future readers quite well. Don't worry: @Reeza still gets the credit.

Re: Why isn't intervalds working with intck?

Reeza — Wed, 10 May 2017 17:29:24 GMT

@ChrisHemedinger Feel free to change the correct answer to Tammy's, I don't really need any more points from correct answers 🙂

Re: Why isn't intervalds working with intck?

daharr — Wed, 10 May 2017 18:45:23 GMT

We will add this information to the next release, 9.4M5 of the SAS 9.4 Functions and CALL Routines: Reference document.