Convert Date extracted in String format to Number Format

[Nandini Rao]

My data is in a text file in which the date field is recorded as a string
variable (A8).  I want to convert this to a numeric field.  This is what I
tried, but it doesn't work.  I appreciate any suggestions.

DATE1 (A8)

STRING DATE2 (A10).
COMPUTE DATE2 = CONCAT(SUBSTR(DATE1,1,2),"/",SUBSTR(DATE1,3,2),"/",SUBSTR
(DATE1,5,4)).
COMPUTE DATE3 = NUMBER(DATE2, ADATE10).
EXECUTE.

This is what the output looks like
DATE1: 04011987
DATE2: 04/01/1987
DATE3: 1.28E+10

with the warning...
">Warning # 1136
>A field to be read under the ADATE format is invalid.  The field must
>contain month, day, and year separated by spaces, dashes, slashes, decimal
>points, or commas.  The result has been set to the system-missing value.

>Command line: 115  Current case: 2094446  Current splitfile group: 1
>Field contents: '  /  /    '

Thanks for your help.
nan

[Melissa Ives]

Nan,

When we have dates stored as text strings, we generally can convert them
to date fields.
If Date1 is what your data look like with no changes, I would use the
conversion to Date2 and then change the format.

You can't do this in syntax, but if you go to the variable view, select
the date and change it to the Date/Time format:
mm/dd/yyyy, it will work fine.  You can't do this with Date1, and I
didn't try with Date3.

Melissa

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Nandini Rao
Sent: Monday, November 20, 2006 10:04 AM
To: [hidden email]
Subject: [SPSSX-L] Convert Date extracted in String format to Number
Format

My data is in a text file in which the date field is recorded as a
string variable (A8).  I want to convert this to a numeric field.  This
is what I tried, but it doesn't work.  I appreciate any suggestions.

DATE1 (A8)

STRING DATE2 (A10).
COMPUTE DATE2 =
CONCAT(SUBSTR(DATE1,1,2),"/",SUBSTR(DATE1,3,2),"/",SUBSTR
(DATE1,5,4)).
COMPUTE DATE3 = NUMBER(DATE2, ADATE10).
EXECUTE.

This is what the output looks like
DATE1: 04011987
DATE2: 04/01/1987
DATE3: 1.28E+10

with the warning...
">Warning # 1136
>A field to be read under the ADATE format is invalid.  The field must
>contain month, day, and year separated by spaces, dashes, slashes,
>decimal points, or commas.  The result has been set to the
system-missing value.

>Command line: 115  Current case: 2094446  Current splitfile group: 1
>Field contents: '  /  /    '

Thanks for your help.
nan



PRIVILEGED AND CONFIDENTIAL INFORMATION
This transmittal and any attachments may contain PRIVILEGED AND
CONFIDENTIAL information and is intended only for the use of the
addressee. If you are not the designated recipient, or an employee
or agent authorized to deliver such transmittals to the designated
recipient, you are hereby notified that any dissemination,
copying or publication of this transmittal is strictly prohibited. If
you have received this transmittal in error, please notify us
immediately by replying to the sender and delete this copy from your
system. You may also call us at [phone] for assistance.

[Richard Ristow]

At 11:04 AM 11/20/2006, Nandini Rao wrote:

>My data is in a text file in which the date field is recorded as a
>string variable (A8).  I want to convert this to a numeric
>field.  This is what I tried [Reformatted, for shorter lines-WRR]:
>
>DATE1 (A8)
>
>STRING DATE2 (A10).
>COMPUTE DATE2 = CONCAT(SUBSTR(DATE1,1,2),"/",
>                        SUBSTR(DATE1,3,2),"/",
>                        SUBSTR(DATE1,5,4)).
>COMPUTE DATE3 = NUMBER(DATE2, ADATE10).
>EXECUTE.

You have two problems. In reverse order,

>>Warning # 1136
>>A field to be read under the ADATE format is invalid.  The field must
>>contain month, day, and year separated by spaces, dashes, slashes,
>>decimal points, or commas.  The result has been set to the
>>system-missing value.
>>
>>Command line: 115  Current case: 2094446  Current splitfile group: 1
>>Field contents: '  /  /    '

That last line is the key; it's the value DATE2 will have if DATE1 is
blank. Your numeric date should be system-missing in that case, so you
could live with it. Or, you could get rid of the error message by
testing, first:

DO IF   DATE1 NE ' '.
.  COMPUTE DATE2 = CONCAT(SUBSTR(DATE1,1,2),"/",
                           SUBSTR(DATE1,3,2),"/",
                           SUBSTR(DATE1,5,4)).
.  COMPUTE DATE3 = NUMBER(DATE2, ADATE10).
END IF.

Your other problem is,
>This is what the output looks like
>DATE1: 04011987
>DATE2: 04/01/1987
>DATE3: 1.28E+10

Your DATE3 is a legitimate SPSS date quantity.  But it doesn't look
like a date unless you print it with a date format. "Internally, all
date format variables are stored as the number of seconds from 0 hours,
0 minutes, and 0 seconds of Oct. 14, 1582." (SPSS 14 Command Syntax
Reference p.69), which is that big number you see. You get the right
answer if you use a date format. See below, which is SPSS draft output
<to myself: no separate code or output file is saved>:


DATA LIST FREE /DATE1 (A8).
BEGIN DATA
04011987
END DATA.

*  .....  Your syntax, as posted   ..... .
STRING DATE2 (A10).
COMPUTE DATE2 = CONCAT(SUBSTR(DATE1,1,2),"/",
                        SUBSTR(DATE1,3,2),"/",
                        SUBSTR(DATE1,5,4)).
COMPUTE DATE3 = NUMBER(DATE2, ADATE10).

*  .....  Added syntax             ..... .
FORMATS DATE3 (E15.5).

COMPUTE DATE4 = DATE3.
FORMATS DATE4 (DATE11).

LIST.
|-----------------------------|---------------------------|
|Output Created               |20-NOV-2006 15:35:24       |
|-----------------------------|---------------------------|
DATE1    DATE2                DATE3       DATE4

04011987 04/01/1987    1.27636E+010 01-APR-1987

Number of cases read:  1    Number of cases listed:  1


*  .....  Source of the big number ..... .
NUMERIC DiffYrs (F5)
        /DiffDys (F8)
        /DiffSec (E15.5).

COMPUTE #StartDt = DATE.DMY(15,10,1582) /* Oct 14, 1582 .

COMPUTE DiffYrs  = DATEDIFF(DATE4,#StartDt,"years").
COMPUTE DiffDys  = DATEDIFF(DATE4,#StartDt,"days").
COMPUTE DiffSec  = DATEDIFF(DATE4,#StartDt,"seconds").

LIST /VARIABLES   DATE4 DiffYrs DiffDys DiffSec DATE3.
|-----------------------------|---------------------------|
|Output Created               |20-NOV-2006 15:35:24       |
|-----------------------------|---------------------------|
       DATE4 DiffYrs  DiffDys         DiffSec           DATE3

01-APR-1987    404    147726    1.27635E+010    1.27636E+010

Number of cases read:  1    Number of cases listed:  1


An SPSS date or date-time is represented as ""