DATA Step, Macro, Functions and more

Why isn't intervalds working with intck?

Accepted Solution Solved
Reply
PROC Star
Posts: 7,492
Accepted Solution

Why isn't intervalds working with intck?

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?

 


Accepted Solutions
Solution
‎05-09-2017 11:25 PM
Super User
Posts: 19,873

Re: Why isn't intervalds working with intck?

[ Edited ]
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.

View solution in original post


All Replies
Solution
‎05-09-2017 11:25 PM
Super User
Posts: 19,873

Re: Why isn't intervalds working with intck?

[ Edited ]
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.

PROC Star
Posts: 7,492

Re: Why isn't intervalds working with intck?

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

 

Art, CEO, AnalystFinder.com

 

Super User
Posts: 19,873

Re: Why isn't intervalds working with intck?

I compared it to the example in the documentation. 

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

SAS Employee
Posts: 10

Re: Why isn't intervalds working with intck?

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

 

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.

Super Contributor
Posts: 253

Re: Why isn't intervalds working with intck?

Posted in reply to TammyJackson

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!

SAS Employee
Posts: 10

Re: Why isn't intervalds working with intck?

Posted in reply to snoopy369

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#p0syn64amro...

 

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. Smiley Happy
 
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.
Super Contributor
Posts: 253

Re: Why isn't intervalds working with intck?

Posted in reply to TammyJackson
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) Smiley Happy 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. -##
SAS Employee
Posts: 10

Re: Why isn't intervalds working with intck?

Posted in reply to TammyJackson

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.

Super Contributor
Posts: 253

Re: Why isn't intervalds working with intck?

[ Edited ]
Posted in reply to TammyJackson

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...

Community Manager
Posts: 2,956

Re: Why isn't intervalds working with intck?

Posted in reply to snoopy369

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.

Super User
Posts: 19,873

Re: Why isn't intervalds working with intck?

Posted in reply to ChrisHemedinger

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

PROC Star
Posts: 7,492

Re: Why isn't intervalds working with intck?

Posted in reply to TammyJackson

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

 

Art, CEO, AnalystFinder.com

 

SAS Employee
Posts: 5

Re: Why isn't intervalds working with intck?

Posted in reply to snoopy369

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

Community Manager
Posts: 2,956

Re: Why isn't intervalds working with intck?

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 16 replies
  • 436 views
  • 25 likes
  • 6 in conversation