manage date formats

[progster]

hi I received a file with an odd date format. I actually received a string that is a mix of a date and a number format: of course in origin it was only dates, but I guess they did something wrong importing to SPSS.

the data are like this:


DATA LIST LIST
 / date  (a50).
BEGIN DATA.
38183
38166
38128
37860
31/08/2014 22:00:00
31/08/2014 22:00:00
30/12/2011 1:00:00
END DATA.


I would like to convert all to a date format or to a string format, but displaying actually dates like DD/MM/YYYY.

With a fixed string I would use some CHAR.SUBSTR to extract the months I need
LIST.

[Andy W]

I'm guessing you have excel to blame for that one. Here is my best guess as to the numeric dates, but the logic follows even if this example is not correct -- just use conditional logic to identify the different patterns and convert them to dates.

*************.
DATA LIST LIST
 / date  (a50).
BEGIN DATA.
38183
38166
38128
37860
31/08/2014 22:00:00
31/08/2014 22:00:00
30/12/2011 1:00:00
END DATA.

DO IF LENGTH(RTRIM(date)) <= 5.
  COMPUTE NewDate = DATESUM(DATE.MDY(01,01,1900),NUMBER(date,F5.0)-1,"DAYS").
ELSE.
  COMPUTE NewDate = NUMBER(CHAR.SUBSTR(date,1,10),EDATE10).
END IF.
FORMATS NewDate (ADATE10).
EXECUTE.
**********************.

[Jon K Peck]

Bear in mind that date values in Excel can be defined in different ways depending on the Excel version/platform, and there is an additional off-by-one problem.

See this link for the gory details.
http://www.cpearson.com/excel/datetime.htm


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




From:        Andy W <[hidden email]>
To:        [hidden email]
Date:        12/17/2014 06:24 AM
Subject:        Re: [SPSSX-L] manage date formats
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

I'm guessing you have excel to blame for that one. Here is my best guess as
to the numeric dates, but the logic follows even if this example is not
correct -- just use conditional logic to identify the different patterns and
convert them to dates.

*************.
DATA LIST LIST
/ date  (a50).
BEGIN DATA.
38183
38166
38128
37860
31/08/2014 22:00:00
31/08/2014 22:00:00
30/12/2011 1:00:00
END DATA.

DO IF LENGTH(RTRIM(date)) <= 5.
 COMPUTE NewDate =
DATESUM(DATE.MDY(01,01,1900),NUMBER(date,F5.0)-1,"DAYS").
ELSE.
 COMPUTE NewDate = NUMBER(CHAR.SUBSTR(date,1,10),EDATE10).
END IF.
FORMATS NewDate (ADATE10).
EXECUTE.
**********************.



-----
Andy W
[hidden email]
http://andrewpwheeler.wordpress.com/
--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/manage-date-formats-tp5728212p5728213.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

[Bruce Weaver]

I agree with Andy--it looks like an Excel-related problem.  Here are a few files I put together some time ago that may help.

http://www.angelfire.com/wv/bwhomedir/spss/importing_excel_dates.txt
http://www.angelfire.com/wv/bwhomedir/spss/excel_dates_output.txt
http://www.angelfire.com/wv/bwhomedir/spss/Dates_in_Excel_and_QuattroPro.pdf

HTH.

progster wrote

hi I received a file with an odd date format. I actually received a string that is a mix of a date and a number format: of course in origin it was only dates, but I guess they did something wrong importing to SPSS.

the data are like this:


DATA LIST LIST
 / date  (a50).
BEGIN DATA.
38183
38166
38128
37860
31/08/2014 22:00:00
31/08/2014 22:00:00
30/12/2011 1:00:00
END DATA.


I would like to convert all to a date format or to a string format, but displaying actually dates like DD/MM/YYYY.

With a fixed string I would use some CHAR.SUBSTR to extract the months I need
LIST.

[Albert-Jan Roskam-2]

variation by OS and platform AND an off-by-one error. Brrr. The code below ignores the OBOE, but it converts xls to spss internal dates. It could be used with SPSSINC TRANS, the output variable needs a datetime format (I like SDATE best)


import datetime as dt

def xls2sav_datetime(value, xls_epoch=(1900, 01, 01, 0, 0, 0)):
        sav_epoch = dt.datetime(1582, 10, 14, 0, 0, 0)
        xls_epoch = dt.datetime(*xls_epoch)
        return ((xls_epoch + dt.timedelta(days=value)) - sav_epoch).total_seconds()


 
Regards,

Albert-Jan




~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a

fresh water system, and public health, what have the Romans ever done for us?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~




>________________________________
> From: Jon K Peck <[hidden email]>
>To: [hidden email]
>Sent: Wednesday, December 17, 2014 2:32 PM
>Subject: Re: [SPSSX-L] manage date formats
>
>
>
>Bear in mind that date values in Excel
can be defined in different ways depending on the Excel version/platform,
and there is an additional off-by-one problem.

>
>See this link for the gory details.
>http://www.cpearson.com/excel/datetime.htm 
>
>
>Jon Peck (no "h") aka Kim
>Senior Software Engineer, IBM
>[hidden email]
>phone: [phone]
>
>
>
>
>From:      

 Andy W <[hidden email]>
>To:      
 [hidden email]
>Date:      
 12/17/2014 06:24 AM
>Subject:    
   Re: [SPSSX-L]
manage date formats
>Sent by:    
   "SPSSX(r)
Discussion" <[hidden email]>
>>________________________________
>
>
>
>I'm guessing you have excel to blame for that one.
Here is my best guess as
>to the numeric dates, but the logic follows even if this example is not
>correct -- just use conditional logic to identify the different patterns
and

>convert them to dates.
>
>*************.
>DATA LIST LIST
>/ date  (a50).
>BEGIN DATA.
>38183
>38166
>38128
>37860
>31/08/2014 22:00:00
>31/08/2014 22:00:00
>30/12/2011 1:00:00
>END DATA.
>
>DO IF LENGTH(RTRIM(date)) <= 5.
> COMPUTE NewDate =
>DATESUM(DATE.MDY(01,01,1900),NUMBER(date,F5.0)-1,"DAYS").
>ELSE.
> COMPUTE NewDate = NUMBER(CHAR.SUBSTR(date,1,10),EDATE10).
>END IF.
>FORMATS NewDate (ADATE10).
>EXECUTE.
>**********************.
>
>
>
>-----
>Andy W
>[hidden email]
>http://andrewpwheeler.wordpress.com/
>--
>View this message in context: http://spssx-discussion.1045642.n5.nabble.com/manage-date-formats-tp5728212p5728213.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
>
>

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