BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
art297
Opal | Level 21

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
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

16 REPLIES 16
Reeza
Super User
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.

art297
Opal | Level 21

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

 

Art, CEO, AnalystFinder.com

 

Reeza
Super User

I compared it to the example in the documentation. 

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

TammyJackson
SAS Employee

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.

snoopy369
Barite | Level 11

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!

TammyJackson
SAS Employee

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. 🙂
 
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.
snoopy369
Barite | Level 11
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. -##
TammyJackson
SAS Employee

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.

snoopy369
Barite | Level 11

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

ChrisHemedinger
Community Manager

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.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Reeza
Super User

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

art297
Opal | Level 21

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

 

Art, CEO, AnalystFinder.com

 

daharr
SAS Employee

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

ChrisHemedinger
Community Manager

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.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

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
  • 16 replies
  • 3473 views
  • 25 likes
  • 6 in conversation