Replace Missing Date and Census Days Syntax

[DKUKEC]

Dear SPSSrs

I have been untilizing the following syntax posted by others (Richard R, back in 2008) on the site to develop a census days table to help compute average daily population and other case management indicators.  In order to compute the indicators the dataset requires a valid start and end date.  In my case, my initial dataset is missing some end dates - these are later computed to end 12 months after the start date (estimated).  As such, I have used the following syntax to compute the estimated end date.

RENAME VARIABLES (ENDDATE=ENDDATE_OLD).
COMPUTE ENDDATE=ENDDATE_OLD.
FORMATS ENDDATE (DATE11).
VARIABLE WIDTH ENDDATE (11).
EXECUTE.

* ADD 12 MONTHS OR 1 YEAR Date and Time Wizard: 365.25 DAYS (12 MONTHS) .
DO IF MISSING (ENDDATE_OLD).
COMPUTE ENDDATE = DATESUM (STARTDATE, 365.25, "days", 'closest').
END IF.
EXECUTE.

When I run the following census days syntax, the "estimated" end dates are ignored or unrecognized by the  
syntax below... meaning the the ENDDAY indicator will not identify (=0) the last record in the census table for a unique admission.  However, the orginal ENDDATES (actual) are identified in the last row for ENDDAY (=1).  I am not sure what is wrong with my end date "imputation" any suggestions would be greatly appreciated.

NUMERIC TODAY (DATE11)
   /STARTDAY ENDDAY (F2).

VARIABLE LABELS
    TODAY "Date: within program stay"
    STARTDAY "Flag: Participant started this date"
    ENDDAY "Flag: Participant ended this date".
FORMATS TODAY (DATE11).
EXECUTE.

********************************************************************************** .

LOOP  TODAY = STARTDATE to ENDDATE BY TIME.DAYS(1).
  COMPUTE STARTDAY = (TODAY EQ STARTDATE).
  COMPUTE ENDDAY = (TODAY EQ ENDDATE).
  XSAVE OUTFILE='CENSUS'
   /KEEP= ID NAME STARTDATE ENDDATE TODAY STARTDAY ENDDAY OUTCOME  .
END LOOP.
EXECUTE  /* this one is needed */.
GET FILE='CENSUSDAYS'.
DATASET NAME CENSUSDAYS WINDOW=FRONT.
COMPUTE YEAR=XDATE.YEAR (TODAY).
COMPUTE MONTH=XDATE.MONTH (TODAY).
VARIABLE LABELS YEAR "Year" MONTH "Month".
EXECUTE.

Thank you in advance,
Damir

[DKUKEC]

Just wanted to share the solution to my rookie mistake...

DATESUM and the census daily computations (LOOP) did not recognize when I added 365.25 days to missing end dates; however, it worked when I added 365 days.

Cheers,
Damir

[Maguin, Eugene]

I don't think there was an on-list reply to this.

I wonder if the problem might be with the increment value in this statement.

COMPUTE ENDDATE = DATESUM (STARTDATE, 365.25, "days", 'closest').

365.25 days will add 365*24*3600 seconds plus 6*3600 seconds to startdate. I'd expect that "days", "closest" would truncate the number of seconds to the nearest integer calendar day but I don't know that it works that way as I seldom work with dates (so I may be wrong). You'd need to see the enddate value  in a datetime format (mm/dd/yyyy hh:mm:ss.s) to see the whole value. All your working end dates would have the hh:mm:ss.s portion always equals 00:00:00.0. The imputed ones won't, I think. If people can be in the program for four or more years, then the 365.25 increment makes sense but if people are in only a few months then I think it doesn't. The real problem, if you want to be very precise, is whether they are in the program on Feb 29.
Gene Maguin

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of DKUKEC
Sent: Wednesday, December 04, 2013 4:44 PM
To: [hidden email]
Subject: Replace Missing Date and Census Days Syntax

Dear SPSSrs

I have been untilizing the following syntax posted by others (Richard R, back in 2008) on the site to develop a census days table to help compute average daily population and other case management indicators.  In order to compute the indicators the dataset requires a valid start and end date.  In my case, my initial dataset is missing some end dates - these are later computed to end 12 months after the start date (estimated).  As such, I have used the following syntax to compute the estimated end date.

RENAME VARIABLES (ENDDATE=ENDDATE_OLD).
COMPUTE ENDDATE=ENDDATE_OLD.
FORMATS ENDDATE (DATE11).
VARIABLE WIDTH ENDDATE (11).
EXECUTE.

* ADD 12 MONTHS OR 1 YEAR Date and Time Wizard: 365.25 DAYS (12 MONTHS) .
DO IF MISSING (ENDDATE_OLD).
COMPUTE ENDDATE = DATESUM (STARTDATE, 365.25, "days", 'closest').
END IF.
EXECUTE.

When I run the following census days syntax, the "estimated" end dates are ignored or unrecognized by the syntax below... meaning the the ENDDAY indicator will not identify (=0) the last record in the census table for a unique admission.  However, the orginal ENDDATES (actual) are identified in the last row for ENDDAY (=1).  I am not sure what is wrong with my end date "imputation" any suggestions would be greatly appreciated.

NUMERIC TODAY (DATE11)
   /STARTDAY ENDDAY (F2).

VARIABLE LABELS
    TODAY "Date: within program stay"
    STARTDAY "Flag: Participant started this date"
    ENDDAY "Flag: Participant ended this date".
FORMATS TODAY (DATE11).
EXECUTE.

**********************************************************************************
.

LOOP  TODAY = STARTDATE to ENDDATE BY TIME.DAYS(1).
  COMPUTE STARTDAY = (TODAY EQ STARTDATE).
  COMPUTE ENDDAY = (TODAY EQ ENDDATE).
  XSAVE OUTFILE='CENSUS'
   /KEEP= ID NAME STARTDATE ENDDATE TODAY STARTDAY ENDDAY OUTCOME  .
END LOOP.
EXECUTE  /* this one is needed */.
GET FILE='CENSUSDAYS'.
DATASET NAME CENSUSDAYS WINDOW=FRONT.
COMPUTE YEAR=XDATE.YEAR (TODAY).
COMPUTE MONTH=XDATE.MONTH (TODAY).
VARIABLE LABELS YEAR "Year" MONTH "Month".
EXECUTE.

Thank you in advance,
Damir



--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Replace-Missing-Date-and-Census-Days-Syntax-tp5723440.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
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]

At 04:44 PM 12/4/2013, DKUKEC wrote:

>I have been utilizing the following syntax posted by others on the
>site to develop a census days table to help compute average daily
>population and other case management indicators.  My initial dataset
>is missing some end dates - these are later
>computed to end 12 months after the start date (estimated).
>
>When I run the following census days syntax, the "estimated" end dates are
>ignored or unrecognized by the syntax below... meaning the the
>ENDDAY indicator will not identify (=0) the last record in the
>census table for a unique admission.  However, the original ENDDATES
>(actual) are identified in the last row for ENDDAY (=1)

Your logic for computing the estimated end date is

>COMPUTE ENDDATE = DATESUM (STARTDATE, 365.25, "days", 'closest').

I've got an old SPSS (release 14), so I can't check this. But if this
DATESUM call produces date-time values that are not exact days (i.e.,
specifying some time other than the midnight that begins the day),
then the test

>   COMPUTE ENDDAY = (TODAY EQ ENDDATE).

will fail -- it looks for exact equality, to the second, of the two values.

If this is the problem, then

COMPUTE ENDDATE = XDATE.DATE(DATESUM (STARTDATE, 365.25, "days", 'closest')).

should solve it -- there's also probably also another solution where
you use a different DATESUM expression.

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

[Bruce Weaver]

Damir posted the message shown below in Nabble earlier today (Dec 05, 2013; 9:58am), but for some reason, it has not yet been forwarded to the mailing list.  With any luck, it will get there via my message!  

DKUKEC wrote

Just wanted to share the solution to my rookie mistake...

DATESUM and the census daily computations (LOOP) did not recognize when I added 365.25 days to missing end dates; however, it worked when I added 365 days.

Cheers,
Damir

[Jon K Peck]

Extract from the DATESUM doc...
DATESUM(datevar, value, "unit", "method").

value is a positive or negative number. For variable-length units (years, quarters, months), fractional values are truncated to integers.

"method" is an optional specification for variable-length units (years, quarters, months) enclosed in quotes. The method can be "rollover" or "closest". The rollover method advances excess days into the next month. The closest method uses the closest legitimate date within the month. This is the default.

and

• The rollover and closest methods yield the same result when incrementing February 28, 2004, by one year: February 28, 2005.
• Using the rollover method, incrementing February 29, 2004, by one year returns a value of March 1, 2005. Since there is no February 29, 2005, the excess day is rolled over to the next month.
• Using the closest method, incrementing February 29, 2004, by one year returns a value of February 28, 2005, which is the closest day in the same month of the following year.
  
  I designed this feature.  Dates are tricky.
  
  
  Jon Peck (no "h") aka Kim
  Senior Software Engineer, IBM
  [hidden email]
  phone: [phone]
  
  

From:        Bruce Weaver <[hidden email]>
To:        [hidden email],
Date:        12/05/2013 01:13 PM
Subject:        Re: [SPSSX-L] Replace Missing Date and Census Days Syntax
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Damir posted the message shown below in Nabble earlier today (Dec 05, 2013;
9:58am), but for some reason, it has not yet been forwarded to the mailing
list.  With any luck, it will get there via my message!


DKUKEC wrote
> Just wanted to share the solution to my rookie mistake...
>
> DATESUM and the census daily computations (LOOP) did not recognize when I
> added 365.25 days to missing end dates; however, it worked when I added
> 365 days.
>
> Cheers,
> Damir





-----
--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Replace-Missing-Date-and-Census-Days-Syntax-tp5723440p5723476.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

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

[Rick Oliver-3]

The documentation contains some examples:

http://pic.dhe.ibm.com/infocenter/spssstat/v22r0m0/topic/com.ibm.spss.statistics.reference/spss/base/syn_date_and_time_date_increments.htm

Rick Oliver
Senior Information Developer
IBM Business Analytics (SPSS)
E-mail: [hidden email]



From:        Jon K Peck/Chicago/IBM@IBMUS
To:        [hidden email],
Date:        12/05/2013 02:26 PM
Subject:        Re: Replace Missing Date and Census Days Syntax
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Extract from the DATESUM doc...
DATESUM(datevar, value, "unit", "method").

value is a positive or negative number. For variable-length units (years, quarters, months), fractional values are truncated to integers.

"method" is an optional specification for variable-length units (years, quarters, months) enclosed in quotes. The method can be "rollover" or "closest". The rollover method advances excess days into the next month. The closest method uses the closest legitimate date within the month. This is the default.

and

• The rollover and closest methods yield the same result when incrementing February 28, 2004, by one year: February 28, 2005.
• Using the rollover method, incrementing February 29, 2004, by one year returns a value of March 1, 2005. Since there is no February 29, 2005, the excess day is rolled over to the next month.
• Using the closest method, incrementing February 29, 2004, by one year returns a value of February 28, 2005, which is the closest day in the same month of the following year.
  
  I designed this feature.  Dates are tricky.
  
  
  Jon Peck (no "h") aka Kim
  Senior Software Engineer, IBM
  [hidden email]
  phone: [phone]
  

From:        Bruce Weaver <[hidden email]>
To:        [hidden email],
Date:        12/05/2013 01:13 PM
Subject:        Re: [SPSSX-L] Replace Missing Date and Census Days Syntax
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Damir posted the message shown below in Nabble earlier today (Dec 05, 2013;
9:58am), but for some reason, it has not yet been forwarded to the mailing
list.  With any luck, it will get there via my message!


DKUKEC wrote
> Just wanted to share the solution to my rookie mistake...
>
> DATESUM and the census daily computations (LOOP) did not recognize when I
> added 365.25 days to missing end dates; however, it worked when I added
> 365 days.
>
> Cheers,
> Damir





-----
--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Replace-Missing-Date-and-Census-Days-Syntax-tp5723440p5723476.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

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