varied input dates

[Raffe, Sydelle, SSA]

I have a txt file I'm reading into SPSS. I'm having a problem converting the dates into something more useable. End result would be a new var with mm/dd/yyyy format. Using the Transform date/time wizard works fine except for one type of input variable: Where the day piece is only one digit, spss creates a missing value. It can handle a one digit month but not the day. Can anyone suggest a solution? Thanks. Sample data are below.

input date                result date

1/9/2008 11:08:48   .
1/9/2008 11:08:50   .
8/8/2007 14:35:58   .
8/8/2007 14:35:59   .

3/27/2006 13:39:49  03/27/2006
3/27/2006 16:06:42  03/27/2006
3/27/2006 16:06:43  03/27/2006
3/27/2006 16:06:45  03/27/2006
3/28/2006 14:57:25  03/28/2006

Sydelle Raffe, Alameda County Social Services Agency
Information Services Division, Office of Data Management
e:mail:  [hidden email]
phone: [phone]     fax: [phone]
If you have a request for information, please submit an ODM request form at:  https://alamedasocialservices.org/staff/support_services/statistics_and_reports/odm/index.cfm

 

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

[Maguin, Eugene]

Sydelle,

The natural way to read these data would be using THE datetime format.
EXCEPT that, for God knows why, the existing datetime format requires the
date portion to be structured dd/mm/yyyy. So, spss, why not offer an
ADateTime format to read American style dates? The programming would seem to
be trivial.

I'm probably completely wrong here but it sounds like the wizard thing
doesn't work correctly for this data condition. I'm just curious. What
version are you using?


data list / sdate (a20).
begin data
1/9/2008 11:08:48
1/9/2008 11:08:50
8/8/2007 14:35:58
8/8/2007 14:35:59
3/27/2006 13:39:49
3/27/2006 16:06:42
3/27/2006 16:06:43
3/27/2006 16:06:45
3/28/2006 14:57:25
11/27/2006 16:06:45
12/28/2006 14:57:25
end data.
execute.

compute #z=index(sdate,' ').
compute a=number(substr(sdate,1,(#z-1)),adate10).
compute b=number(substr(sdate,(#z+1),20-#z),time8).
compute c=a+b.
execute.
format a(adate10) b(time8) c(datetime20).

List sdate a b c.

sdate                         a        b                    c
1/9/2008 11:08:48    01/09/2008 11:08:48 09-JAN-2008 11:08:48
1/9/2008 11:08:50    01/09/2008 11:08:50 09-JAN-2008 11:08:50
8/8/2007 14:35:58    08/08/2007 14:35:58 08-AUG-2007 14:35:58
8/8/2007 14:35:59    08/08/2007 14:35:59 08-AUG-2007 14:35:59
3/27/2006 13:39:49   03/27/2006 13:39:49 27-MAR-2006 13:39:49
3/27/2006 16:06:42   03/27/2006 16:06:42 27-MAR-2006 16:06:42
3/27/2006 16:06:43   03/27/2006 16:06:43 27-MAR-2006 16:06:43
3/27/2006 16:06:45   03/27/2006 16:06:45 27-MAR-2006 16:06:45
3/28/2006 14:57:25   03/28/2006 14:57:25 28-MAR-2006 14:57:25
11/27/2006 16:06:45  11/27/2006 16:06:45 27-NOV-2006 16:06:45
12/28/2006 14:57:25  12/28/2006 14:57:25 28-DEC-2006 14:57:25

Number of cases read:  11    Number of cases listed:  11


I think you are only interested in the 'a' variable.

Gene Maguin

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

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Gene Maguin
Sent: Friday, October 31, 2008 8:33 AM
To: [hidden email]
Subject: Re: [SPSSX-L] varied input dates

Sydelle,

The natural way to read these data would be using THE datetime format.
EXCEPT that, for God knows why, the existing datetime format requires the
date portion to be structured dd/mm/yyyy. So, spss, why not offer an
ADateTime format to read American style dates? The programming would seem to
be trivial.
[>>>Peck, Jon] As I have noted before, there are infinitely many permutations on date and time formats.  Pursuing these individually would be hopeless.  And, unfortunately, the current built-in set of 39 formats in SPSS - including all types - cannot be expanded without breaking the compatibility of the system file format with older releases.

Apart from making the d/t wizard smarter, another, more flexible direction is to use a pattern language to describe the format.  The pieces of the pattern can then be combined in any way you like with any separators between fields.  In fact, we have provided access to such a date/time description language in the Python programmability module extendedTransforms available from SPSS Developer Central (www.spss.com/devcentral).

In that module, there are two functions for this:
strtodatetime:            convert a date/time string to an SPSS datetime value using a pattern
datetimetostr:            convert an SPSS date/time value to a string using a pattern

These functions need to be used in a program that passes the data and creates a new variable, but they allow virtually any kind of date/time representation to be handled.

As an illustration, for data such as the following
1/9/2008 11:08:48

here is the program that converts this to an SPSS date variable.

begin program.
import spss, extendedTransforms, spssdata
curs = spssdata.Spssdata(accessType="w")
curs.append(spssdata.vdef("datetime", vfmt=["DATETIME",20]))
curs.commitdict()
for case in curs:
  curs.casevalues([extendedTransforms.strtodatetime(case.dt.rstrip(),
    "%m/%d/%Y %H:%M:%S")])
curs.CClose()
end program.

The pattern specifies month/day/4-digit-year <space>hour(24-hour clock):minute:second.

HTH,
Jon Peck

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