DATEDIFF excluding Public Holidays

[Villegas, Emilio (Bisnode)]

Hi,

 

Do anyone know how to make a syntax that compute (DATEDIFF) weekdays from 2 dates excluding Public Holidays.

 

For example DATEDIFF between date 2013-05-16 and date 2013-05-23. The right answer should be 6 days, not 8 days.

 

BR

Emilio

[Jon K Peck]

I didn't see an answer to this.

Public holidays are, of course, country and year specific.  So you need a table of public holiday dates you want to exclude.  There is a Python function in the extendedTransforms.py file, countWkdays, that counts weekdays between two dates.  You could adapt that code for holidays listed in a secondary dataset.  I suppose this would be a nice enhancement for  this function, so maybe I will do that.


Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: [phone]




From:        "Villegas, Emilio (Bisnode)" <[hidden email]>
To:        [hidden email],
Date:        05/16/2013 01:51 PM
Subject:        [SPSSX-L] DATEDIFF excluding Public Holidays
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Hi,
 
Do anyone know how to make a syntax that compute (DATEDIFF) weekdays from 2 dates excluding Public Holidays.
 
For example DATEDIFF between date 2013-05-16 and date 2013-05-23. The right answer should be 6 days, not 8 days.
 
BR

Emilio

[Jon K Peck]

Here's an example using the SPSSINC TRANS extension command and the updated extendedTransforms.py module.  The function

extendedTransforms.countDaysWExclusions

in initialized by the initial subcommand by passing it the name of a dataset listing the public holidays as SPSS date values and the name of the variable holding these.

/initial  'extendedTransforms.countDaysWExclusions(exclusionds="holidays", exclusionvar="thedate")'

Then the formula subcommand calls this function for each case

/formula 'func(startdate=start, enddate=end)'

with the output variable named in result.

spssinc trans result = daysexclusive

Weekend days or other listed days can also be excluded by adding an argument like

exclude=["sat", "sun]

to the func call in formula.  You can also control whether the ending day is included or excluded.

This requires the latest version of exendedTransforms.py from the SPSS Community website (www.ibm.com/developerworks/spssdevcentral) in the Utilities Collection under Downloads for SPSS Statistics.

You may also need the latest copy of SPSSINC TRANS from the Extension Commands Collection.

These require the Python Essentials also downloadable via the website.

data list list /thedate(adate10).
begin data.
01/01/2013
01/01/2000
01/01/2001
05/27/2013
07/04/2013
end data.
dataset name holidays.

data list list/start (adate10), end(adate10).
begin data.
01/01/2013  12/31/2013
01/01/2000 12/31/2013
01/01/2004 12/31/2004
01/01/2005 12/31/2005
01/01/2001 01/01/2001
01/01/2002 01/01/2002
01/01/2002 01/02/2002
01/02/2002 01/01/2002
05/26/2013 05/26/2013
01/01/2003
.                01/01/2003

end data.
dataset name dates.

dataset activate dates.
spssinc trans result = daysexclusive
    /initial  'extendedTransforms.countDaysWExclusions(exclusionds="holidays", exclusionvar="thedate")'
    /formula 'func(startdate=start, enddate=end)'.


Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: [phone]




From:        "Villegas, Emilio (Bisnode)" <[hidden email]>
To:        [hidden email],
Date:        05/16/2013 01:51 PM
Subject:        [SPSSX-L] DATEDIFF excluding Public Holidays
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Hi,
 
Do anyone know how to make a syntax that compute (DATEDIFF) weekdays from 2 dates excluding Public Holidays.
 
For example DATEDIFF between date 2013-05-16 and date 2013-05-23. The right answer should be 6 days, not 8 days.
 
BR

Emilio