Elapsed WorkDays

[Libardo López Guzmán]

Hi, i need a syntax to compute elapsed workdays, excluding public holidays
since year 2000.
I would appreciate your help.

Libardo

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Richard Ristow]

At 11:40 AM 12/4/2007, Libardo López Guzmán wrote:

>Hi, i need a syntax to compute elapsed workdays,
>excluding public holidays since year 2000.

I don't have a solution, but the question came up
before, and what was described as a partial
solution was posted (based, as is much else, on
code by Raynald Levesque). It may serve your
purpose, since the stated remaining problem is
proper handling of fractional days.

His code is at the end of the second posting, below.

Initial question:

>Date:    Wed, 24 May 2006 12:35:46 -0500
>From:    ariel barak <[hidden email]>
>Subject: Excluding Specific Dates in Length of Stay
>To:      [hidden email]
>
>I am attempting to calculate a length of stay
>for individuals sent to detention facilities
>excluding holiday and weekend days for 2005.
>
>There are 2 data files. The first is about
>10,000 admissions that have variables titled
>AdmitDate and ReleaseDate both in dd-mmm-yyyy
>hh:mm:ss format. This file is titled
>charges.sav. The second data file is a list of
>the holiday and weekend days in 2004 and 2005
>whose time should be subtracted from the length
>of stay also in DATETIME20 format. This file is titled excludeddays.sav.

Follow-up:

>Date:         Thu, 25 May 2006 11:52:58 -0500
>From:    ariel barak <[hidden email]>
>Subject: Re: Excluding Specific Dates in Length of Stay
>To: [hidden email]
>
>After search and a few responses from list
>members, I found syntax that is almost exactly
>what I am looking for. The second data set that
>I spoke about in my first post now may be
>unnecessary. I pasted the syntax below, from
>Raynald's SPSS Tools
><http://www.spsstools.net/SampleSyntax.htm#DatesAndTimes>.
>
>I changed the dummy data to my example and
>inserted a few 2005 holidays. The syntax below
>is to find the number of elapsed weekdays in
>between two dates (see sample data) excluding public holidays.
>
>But I would like to calculate the number of
>elapsed seconds in between two dates
>(01-JAN-2005 12:00:00 to 31-Jan-2005 18:00:00)
>excluding public holidays so that I can convert to hours, minutes, etc.
>
>The correct difference for the two dates I
>provided above should be 1,706,400 seconds or
>19.75 days to account for the weekends as well
>as Martin Luther King Jr. Day on January 17th.
>
>If I use the data entered in DATETIME format as below, the syntax doesn't
>seem to account properly for the holiday i.e. with the ADATE format, the
>output is 20 days and with the DATETIME format, the output is 21 days :
>
>Thanks in advance to anyone who tries to help with this problem.
>
>data list / date1 1-20 (DATETIME20) date2 22-41 (DATETIME20) .
>begin data
>01-JAN-2005 12:00:00 31-JAN-2005 18:00:00
>end data.
>
>* Here is a way to compute elapsed weekdays excluding Public Holidays *.
>* Raynald Levesque Summer 2002.
>
>data list / date1 1-10 (ADATE) date2 12-21 (ADATE) .
>begin data
>01/01/2005 01/31/2005
>end data.
>
>* Just a baseline normal number of days* .
>COMPUTE DAYS =CTIME.DAYS(date2-date1).
>
>*Note dates are stored in seconds, 86400 per day *  .
>COMPUTE WKDAYS=0.
>LOOP #=date1 to date2 BY 86400.
>* You have to add all applicable public holidays in .
>* next ANY command below.                           .
>If RANGE(XDATE.WKDAY(#),2,6) & ~ANY(#,
>          DATE.DMY(1,1,2005),
>          DATE.DMY(1,17,2005),
>          DATE.DMY(25,12,2005)
>          )
>          WKDAYS=WKDAYS +1.
>END LOOP.
>
>list.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Richard Ristow]

At 02:17 PM 12/4/2007, Libardo López Guzmán wrote, off-list:

>Hi Richard,
>Thanks for your help. I was working with this
>syntax and it have some miss results in some cases 1 or 2 days.

I'm sorry, but not completely astounded. As I
wrote, the person who posted it wasn't confident
it solved the problem completely. And I didn't test it.

Post (don't just mail it to me) some test data,
each line having start date, end date, the number
of days you expect, and the number of days the
syntax calculated. And include in the posting,
the exact syntax you used. That'll give list
members a chance to look at your problem; and, it's to be hoped fix it.

-Best wishes
  Richard

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Peck, Jon]

This is a fairly hard problem, boosted by the fact that the public holidays will vary by country.  There is a very powerful third-party Python module called dateutil (a separate download) that can handle this and other kinds of complex date calculations.  The details are spelled out in the Python Cookbook (O'Reilly, 2nd edition) recipe 3.6.  The recipe code can be downloaded from the O'Reilly website (http://examples.oreilly.com/pythoncook2/).  The algorithms handle some standard holidays like Christmas and Easter, but you would need to supply other holidays.

I don't have time right now to turn this into a full fledged SPSS module.  Maybe someone would like to volunteer.

HTH,
Jon Peck

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Libardo López Guzmán
Sent: Tuesday, December 04, 2007 9:41 AM
To: [hidden email]
Subject: [SPSSX-L] Elapsed WorkDays

Hi, i need a syntax to compute elapsed workdays, excluding public holidays
since year 2000.
I would appreciate your help.

Libardo

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[ariel barak]

Everyone,

I was the one who asked the list about a similar question earlier. I had
admit and release date and times for individuals at a correctional facility
and needed to count length of stay (LOS) minus holidays and weekends. Here
is some test data and the syntax that I'm using currently. I know it uses
more execute commands than necessary.

Hope it helps.

-Ari

data list / admitdate 1-20 (DATETIME20) releasedate 22-41 (DATETIME20) .
begin data
01-JAN-2005 11:59:00 01-JAN-2005 18:00:00
18-JAN-2005 23:59:00 20-JAN-2005 00:01:00
01-JAN-2005 11:59:00 31-JAN-2005 18:00:00
15-DEC-2004 00:01:00 05-JAN-2005 23:59:00
04-JAN-2005 11:25:00 10-JAN-2005 12:35:00
01-JAN-2004 18:00:00 06-JAN-2004 18:00:00
25-DEC-2004 19:42:31 20-JAN-2005 14:34:25
03-AUG-2005 03:00:00 03-AUG-2005 11:00:00
05-NOV-2006 03:00:00 05-NOV-2006 11:00:00
07-NOV-2006 03:00:00 08-NOV-2006 11:00:00
04-DEC-2007 13:00:00 04-DEC-2007 14:00:00
end data.

* Just a baseline normal number of days* .
COMPUTE DAYS =CTIME.DAYS(releasedate-admitdate).
EXE.

*Note dates are stored in seconds, 86400 per day *.
*Calculate the time UNTIL midnight on admitdate if it is not a weekend or
holiday *.
COMPUTE GAP1=0.
If RANGE(XDATE.WKDAY(admitdate),2,6) & ~ANY(XDATE.DATE(admitdate),
 DATE.DMY(1,1,2005),
 DATE.DMY(17,1,2005),
 DATE.DMY(30,5,2005),
 DATE.DMY(4,7,2005),
 DATE.DMY(5,9,2005),
 DATE.DMY(24,11,2005),
 DATE.DMY(24,12,2005),
 DATE.DMY(25,12,2005),
 DATE.DMY(31,12,2005),
 DATE.DMY(1,1,2006),
 DATE.DMY(16,1,2006),
 DATE.DMY(29,5,2006),
 DATE.DMY(4,7,2006)
 )
GAP1=86400-XDATE.HOUR(admitdate)*3600-XDATE.MINUTE(admitdate)*
60-XDATE.SECOND(admitdate).
EXE.

*Calculate the time SINCE midnight on releasdate if it is not a weekend or
holiday *.
COMPUTE GAP2=0.
IF RANGE(XDATE.WKDAY(releasedate),2,6) &~ANY(XDATE.DATE(releasedate) ,
  DATE.DMY(1,1,2005),
 DATE.DMY(17,1,2005),
 DATE.DMY(30,5,2005),
 DATE.DMY(4,7,2005),
 DATE.DMY(5,9,2005),
 DATE.DMY(24,11,2005),
 DATE.DMY(24,12,2005),
 DATE.DMY(25,12,2005),
 DATE.DMY(31,12,2005),
 DATE.DMY(1,1,2006),
 DATE.DMY(16,1,2006),
 DATE.DMY(29,5,2006),
 DATE.DMY(4,7,2006)
 )
GAP2=XDATE.HOUR
(releasedate)*3600+XDATE.MINUTE(releasedate)*60+XDATE.SECOND(releasedate).
EXE.

*Calculate the number of full days in between the admit and release date
that are not
holidays or weekends*.
COMPUTE GAP=0.
LOOP #=(admitdate + GAP1) to (releasedate - ( 86400 + GAP2 )) BY 86400.
IF RANGE(XDATE.WKDAY(#),2,6) &~ANY(XDATE.DATE(#) ,
 DATE.DMY(1,1,2005),
 DATE.DMY(17,1,2005),
 DATE.DMY(30,5,2005),
 DATE.DMY(4,7,2005),
 DATE.DMY(5,9,2005),
 DATE.DMY(24,11,2005),
 DATE.DMY(24,12,2005),
 DATE.DMY(25,12,2005),
 DATE.DMY(31,12,2005),
 DATE.DMY(1,1,2006),
 DATE.DMY(16,1,2006),
 DATE.DMY(29,5,2006),
 DATE.DMY(4,7,2006)
 )
GAP=GAP+86400.
END LOOP.
EXE.

FORMATS GAP1 to GAP (F16.0).

COMPUTE NoHolidayLOSdays=(Gap1 + Gap2 + Gap)/86400.
EXE.

*The computation of NoHolidayLOSdays above is incorrect if the admit date
and release date
 are the same on a non-weekend or non-holiday - syntax below corrects this.

COMPUTE admitday = XDATE.TDAY(admitdate).
COMPUTE releaseday = XDATE.TDAY(releasedate).
EXE.

IF (admitday = releaseday AND  GAP1 <> GAP2)
 NoHolidayLOSdays = (86400 -((86400-GAP1)+(86400-GAP2)))/86400.
EXE.


On Dec 4, 2007 1:51 PM, Richard Ristow <[hidden email]> wrote:

> At 02:17 PM 12/4/2007, Libardo López Guzmán wrote, off-list:
>
> >Hi Richard,
> >Thanks for your help. I was working with this
> >syntax and it have some miss results in some cases 1 or 2 days.
>
> I'm sorry, but not completely astounded. As I
> wrote, the person who posted it wasn't confident
> it solved the problem completely. And I didn't test it.
>
> Post (don't just mail it to me) some test data,
> each line having start date, end date, the number
> of days you expect, and the number of days the
> syntax calculated. And include in the posting,
> the exact syntax you used. That'll give list
> members a chance to look at your problem; and, it's to be hoped fix it.
>
> -Best wishes
>  Richard
>
> =====================
> To manage your subscription to SPSSX-L, send a message to
> [hidden email] (not to SPSSX-L), with no body text except the
> command. To leave the list, send the command
> SIGNOFF SPSSX-L
> For a list of commands to manage subscriptions, send the command
> INFO REFCARD
>

====================To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Richard Ristow]

Dear Ariel,

At 04:13 PM 12/4/2007, you wrote:

>I was the one who asked the list about a similar question earlier.
>Here is some test data and the syntax that I'm using currently.

Thank you! I appreciate your reading this latest, and catching us up
with what you're using.

-With warm thanks,
  Richard

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[hillel vardi]

Shalom

as Jon Peck point out there is no easy way to determent public holidays
over all calenders .   Jewish and Moslem's that are base on loner year
are even more complicated . the Moslem's Ramadan  holiday
can not be calculated ahead of time and is only decided on the eve of
the holiday .
Here is a syntax to sum all Ramadan 4th of July and labor day since 1988 .

dataset close all.
input program .
do repeat     start ="18/04/1988" "07/04/1989" "28/03/1990" "17/03/1991"
"05/03/1992" "23/02/1993"
                            "12/02/1994" "01/02/1995" "22/01/1996"
"10/01/1997" "20/12/1998" "08/12/1999"
                            "28/11/2000" "17/11/2001" "06/11/2002"
"27/10/2003" "15/10/2004" "04/10/2005" "24/09/2006" /
                   endd="17/05/1988" "06/05/1989" "26/04/1990"
"15/04/1991" "03/04/1992" "24/03/1993"
                            "13/03/1994" "02/03/1995" "20/02/1996"
"08/02/1997" "18/01/1999" "07/01/2000"
                             "27/12/2000" "16/12/2001" "05/12/2002"
"25/11/2003" "13/11/2004" "02/11/2005" "23/10/2006" .
compute        startdate=date.dmy( number(substr(start,1,2) ,f2),
number(substr(start,4,2),f2) ,  number(substr(start,7,4),f4)) .
compute        enddate =date.dmy(  number(substr(endd,1,2),f2),
number(substr(endd,4,2),f2),  number(substr(endd,7,4),f4) ) .
print /   startdate enddate .
compute       seqday=60*60*24 .
compute       numstart=startdate/seqday .
compute       numend=enddate/seqday .
loop           i=numstart to numend .
compute    ramdandate= I * seqday .
end case .
end loop .
end repeat .
end file .
end input program .
compute       seqday=60*60*24 .
compute    ramdandate= I * seqday .
formats   startdate enddate  ramdandate(edate10) .
dataset name  ramadan.
execute .


input program .
compute       seqday=60*60*24 .
leave         seqday .
loop           days=date.dmy(1,1,1988)/seqday  to
date.dmy(31,12,2006)/seqday  .
if            (xdate.month(days*seqday) eq 7) and
(xdate.mday(days*seqday) eq 4 ) holidays=1 .
if            (xdate.month(days*seqday) eq 9) and
(xdate.mday(days*seqday) le 7 ) and (XDATE.WKDAY(days*60*60*24) eq 2 )
                          holidays=1 .
end case .
end loop .
end file .
end input program .
compute   date=days * seqday .
dataset name  dates .
formats   date (edate10) .
execute .

match files    file=dates / table = ramadan/rename(
ramdandate=date)/in=ramadan / by date
    /drop= seqday days startdate enddate numstart numend i .
recode     holidays(1=1)(else=0) .
if      sum(ramadan,holidays) gt 0  sumholidays= sum(sumholidays,1) .
if      sum(ramadan,holidays) eq 0  sumworkdays= sum(sumworkdays,1) .

leave sumholidays sumworkdays.
execute .


Hillel Vardi
BGU


Libardo López Guzmán wrote:

> Hi, i need a syntax to compute elapsed workdays, excluding public holidays
> since year 2000.
> I would appreciate your help.
>
> Libardo
>
> =====================
> To manage your subscription to SPSSX-L, send a message to
> [hidden email] (not to SPSSX-L), with no body text except the
> command. To leave the list, send the command
> SIGNOFF SPSSX-L
> For a list of commands to manage subscriptions, send the command
> INFO REFCARD
>
>

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD