topic Juletip #12 - How to count days across bank holidays using custom intervals in SAS Community Nordic

Juletip #12 - How to count days across bank holidays using custom intervals

GertNissen — Thu, 12 Dec 2019 08:03:41 GMT

A #SASJuletip advent calendar about a calendar 😉

You might already be using the INTCK functions to calculate various distances between different standard dates, but there are some limitation as it does not handle national bank holidays or perhaps even special company holidays.

As always there are many ways to accomplice this, but this #SASjuletip will introduce an old and perhaps a little know/used feature.

Imagine these dates.

How many days are between the dates marked with yellow?

Properly different answers depending on your country.

And it can be calculated using SAS in many different ways, depending on what you want and your preferred method. Below is one way, using a simple example of introducing Custom Date Intervals used together with INTCK().

* Table with public bank holidays we will not count a workdays *;
* http://www.nationalbanken.dk/da/Kontakt/aabningstider/Sider/Default.aspx *;
data bankdays;
  attrib begin format=date. ;
  infile datalines dlm=':';
  input begin :anydtdte18. beskrivelser $30.;
datalines;
1. januar 2019 : Nytårsdag
18. april 2019 : Skærtorsdag
19. april 2019 : Langfredag
22. april 2019 : 2. påskedag
17. may 2019 : Store bededag
30. may 2019 : Kristi himmelfartsdag
31. may 2019 : Banklukkedag
5. june 2019 : Banklukkedag
10. june 2019 : 2. pinsedag
24. december 2019 : Banklukkedag
25. december 2019 : Juledag
26. december 2019 : 2. juledag
31. december 2019 : Banklukkedag
1. januar 2020 : Nytårsdag
;
run;

proc sort data=bankdays; 
  by begin;
run;

* All dates, but not saturday/sundays *;
data xdanish(keep=begin);
  format begin date9.;
  do begin = '20dec2019'd to '05jan2020'd;
    if weekday(begin) not in (1,7) then output; * not sat/sun *;
  end;
run;

proc sort data=xdanish; 
  by begin;
run;

* remove bank holidays from date tabel *;
data xdanish;
 merge xdanish(keep=begin)
       bankdays(keep=begin in=remove);
  by begin;
  if remove then delete;
run;

options intervalds=(xDanish=work.xdanish);

* TEST count days *;
data test;
  format start end date.;
  start = '20dec2019'd;
  end = '22dec2019'd;
  put (start end) (=);
  day =intck('day',     start, end); put day=;                            
  wday=intck('weekday', start, end); put wday=;                        
  xday=intck('xDanish', start, end); put xday= /;   
  output;

  start = '23dec2019'd;
  end = '27dec2019'd;
  put (start end) (=);
  day =intck('day',     start, end); put day=;                             
  wday=intck('weekday', start, end); put wday=;                             
  xday=intck('xDanish', start, end); put xday= /;
  output;

  start = '20dec2019'd;
  end = '2jan2020'd;       
  put (start end) (=);
  day =intck('day',     start, end);  put day=;                                 
  wday=intck('weekday', start, end);  put wday=;                             
  xday=intck('xDanish', start, end);  put xday=;
  output; 
run;

Do you agree with the counts? Or did your expect another result? How would you solve this?

Re: Juletip #12 - How to count days across bank holidays using custom intervals

MichelleHomes — Thu, 12 Dec 2019 08:42:17 GMT

Excellent tip @GertNissen! I had known about custom intervals but hadn’t used/seen it being used, so thank you! Very useful for date calculations.

Re: Juletip #12 - How to count days across bank holidays using custom intervals

PeterClemmensen — Thu, 12 Dec 2019 09:39:34 GMT

@GertNissen very nice juletip.

I'll take the bait here and say that I would expect different counts given that we want to count days. The Intck Function counts the number of interval boundaries crossed. An obvious and easy way to solve this is to add 1 to each returned value from the Intck Functions. This makes xday=5 in the third obs, which some Danes would recognize as the number of feriedage they have registered to get a nice and long christmas break 🙂 Obviously it can be done in many other ways and I hope other readers will pitch in.

Again thank you for a great tip and Merry Christmas to all fellow Scandies out there.

Re: Juletip #12 - How to count days across bank holidays using custom intervals

jmic_nyk — Thu, 12 Dec 2019 10:24:00 GMT

We did this - easier for operational use in a large org

proc fcmp outlib=nkfnk.funktion.prod;

function bankdag(Dato,forskydning);

length retur 8;

if forskydning = . then forskydning1=0;

else forskydning1=forskydning;

nyDato=Dato + Forskydning1;

aar = year(nyDato);

if weekday(nyDato) = 7 then retur = 6; /* Lørdag */

else if weekday(nyDato) = 1 then retur = 7; /* Søndag */

else if nyDato = mdy(06,05,aar) then retur = 506; /* Grundlovsdag */

else if nyDato = mdy(12,24,aar) then retur = 2412; /* Juleaftensdag */

else if nyDato = mdy(12,25,aar) then retur = 2512; /* 1. Juledag */

else if nyDato = mdy(12,26,aar) then retur = 2612; /* 2. Juledag */

else if nyDato = mdy(01,01,aar) then retur = 101; /* Nytårsdag */

else if nyDato = mdy(12,31,aar) then retur = 3112; /* Nytårsaftensdag */

else do;

paaske=holiday('easter',aar);

if nyDato = paaske-3 then retur = 201; /*"Skærtorsdag"*/

else if nyDato = paaske-2 then retur = 202; /*"Langfredag"*/

else if nyDato = paaske+1 then retur = 203; /*"2.Påskedag"*/

else if nyDato = paaske+26 then retur = 204; /*"Store bededag"*/

else if nyDato = paaske+39 then retur = 205; /*"Kr. himmelfart"*/

else if nyDato = paaske+40 then retur = 206; /*"Fredag efter Kr. himmelfart"*/

else if nyDato = paaske+50 then retur = 207; /*"2. pinsedag"*/

else retur = 1; /*"Bankdag"*/

end;

return(retur);

endsub;

run;quit;

proc format library=nkfmt;

value bankdagf 1 = 'Bankdag'

7 = 'Søndag'

6 = 'Lørdag'

506 = 'Grundlovsdag'

2412 = 'Juleaftensdag'

2512 = 'Juledag'

2612 = '2. Juledag'

101 = 'Nytårsdag'

3112 = 'Nytårsaftensdag'

201 = 'Skærtorsdag'

202 = 'Langfredag'

203 = '2. Påskedag'

204 = 'Store Bededag'

205 = 'Kr Himmelfart'

206 = 'Fredag efter Kr. Himmelfart'

207 = '2. Pinsedag'

other = '***Fejl***';

run;

/* --- Test data --- */

data aar_2014;

format dato ddmmyy10.

type bankdagf.;

do dato='01jan2014'd to '31dec2014'd by 1;

type=bankdag(dato,0);

output;

end;

run;

title "365 dage i 2014 i Danmark";

proc print data=aar_2014;

run;

proc freq data=aar_2014;

table type /nocum;

run;

title "Fridage i 2014 i Danmark";

proc print data=aar_2014;

where bankdag(dato,0) not in(1,6,7);

run;

title;