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?
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.
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.
I compared it to the example in the documentation.
The only difference I could find was END so I dropped it and that worked.
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.
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!
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.
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.
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...
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.
@ChrisHemedinger Feel free to change the correct answer to Tammy's, I don't really need any more points from correct answers 🙂
@TammyJackson: I agree and thanks for looking into it! @snoopy369: Joe, very much appreciated!
Art, CEO, AnalystFinder.com
We will add this information to the next release, 9.4M5 of the SAS 9.4 Functions and CALL Routines: Reference document.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.