Converting mm/dd/yyyy hh:mm:ss Excel to SPSS
Locked
 
|
I have a large file with a lot of date columns in excel that are formatted mm/dd/yyyy hh:mm:ss, for example 12/22/2010 7:06:00 PM. SPSS does not seem to read this format, only recognizing dd/mm. How can I import this format easily from Excel into SPSS.
Thanks, David |
Re: Converting mm/dd/yyyy hh:mm:ss Excel to SPSS
|
|
As far as I know, Excel dates of that format
should be correctly converted to datetime variables. The few simple
tests I tried worked correctly.
If it is being interpreted as a date format, but doesn't show both the date and time portions, try changing the format: formats varlist (datetime20) Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: mdspss <[hidden email]> To: [hidden email] Date: 11/25/2014 02:27 PM Subject: Converting mm/dd/yyyy hh:mm:ss Excel to SPSS Sent by: "SPSSX(r) Discussion" <[hidden email]> I have a large file with a lot of date columns in excel that are formatted mm/dd/yyyy hh:mm:ss, for example 12/22/2010 7:06:00 PM. SPSS does not seem to read this format, only recognizing dd/mm. How can I import this format easily from Excel into SPSS. Thanks, David -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Converting-mm-dd-yyyy-hh-mm-ss-Excel-to-SPSS-tp5728030.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 |
Re: Converting mm/dd/yyyy hh:mm:ss Excel to SPSS
|
|
In reply to this post by mdspss
Note that if there are any values the the
date columns in Excel that are not dates, the variables will be converted
to strings so that no data are lost. If you want real dates, you would
then need to convert the string values to real dates. There are a number
of ways to do that.
Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: Rick Oliver/Chicago/IBM To: mdspss <[hidden email]> Cc: [hidden email] Date: 11/25/2014 02:40 PM Subject: Re: Converting mm/dd/yyyy hh:mm:ss Excel to SPSS As far as I know, Excel dates of that format should be correctly converted to datetime variables. The few simple tests I tried worked correctly. If it is being interpreted as a date format, but doesn't show both the date and time portions, try changing the format: formats varlist (datetime20) Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: mdspss <[hidden email]> To: [hidden email] Date: 11/25/2014 02:27 PM Subject: Converting mm/dd/yyyy hh:mm:ss Excel to SPSS Sent by: "SPSSX(r) Discussion" <[hidden email]> I have a large file with a lot of date columns in excel that are formatted mm/dd/yyyy hh:mm:ss, for example 12/22/2010 7:06:00 PM. SPSS does not seem to read this format, only recognizing dd/mm. How can I import this format easily from Excel into SPSS. Thanks, David -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Converting-mm-dd-yyyy-hh-mm-ss-Excel-to-SPSS-tp5728030.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 |
Re: Converting mm/dd/yyyy hh:mm:ss Excel to SPSS
|
|
In reply to this post by Rick Oliver-3
It is also possible that this is actually
a string rather than an Excel date, in which case you would have to convert
it into an SPSS date after importing the file.
Jon Peck (no "h") aka Kim Senior Software Engineer, IBM [hidden email] phone: 720-342-5621 From: Rick Oliver/Chicago/IBM@IBMUS To: [hidden email] Date: 11/25/2014 01:41 PM Subject: Re: [SPSSX-L] Converting mm/dd/yyyy hh:mm:ss Excel to SPSS Sent by: "SPSSX(r) Discussion" <[hidden email]> As far as I know, Excel dates of that format should be correctly converted to datetime variables. The few simple tests I tried worked correctly. If it is being interpreted as a date format, but doesn't show both the date and time portions, try changing the format: formats varlist (datetime20) Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: mdspss <[hidden email]> To: [hidden email] Date: 11/25/2014 02:27 PM Subject: Converting mm/dd/yyyy hh:mm:ss Excel to SPSS Sent by: "SPSSX(r) Discussion" <[hidden email]> I have a large file with a lot of date columns in excel that are formatted mm/dd/yyyy hh:mm:ss, for example 12/22/2010 7:06:00 PM. SPSS does not seem to read this format, only recognizing dd/mm. How can I import this format easily from Excel into SPSS. Thanks, David -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Converting-mm-dd-yyyy-hh-mm-ss-Excel-to-SPSS-tp5728030.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 LISTSERV@... (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 |
|
|
Thanks for your responses. Still having trouble. I've tried converting the date on the excel end into a simple mm/dd/yyyy and then I can import it as ADATE8 without a problem. However, when I leave it in the mm/dd/yyyy hh:mm:ss format (including the time stamp), SPSS does not recognize it as a date, regardless of whether I use ADATE8 or ADATE20.
I am using a .csv excel file by the way, and using a GET DATA command. Thoughts? Thanks. |
Re: Converting mm/dd/yyyy hh:mm:ss Excel to SPSS
|
|
If you are using a CSV file, then you are
using GET DATA to read the data or the Text Wizard. Set the formats of
the variables to DATETIME20. If you are using the Text Wizard, you may
need to paste the syntax and modify the format specifications for the date
variables there.
Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: mdspss <[hidden email]> To: [hidden email] Date: 11/25/2014 03:06 PM Subject: Re: Converting mm/dd/yyyy hh:mm:ss Excel to SPSS Sent by: "SPSSX(r) Discussion" <[hidden email]> Thanks for your responses. Still having trouble. I've tried converting the date on the excel end into a simple mm/dd/yyyy and then I can import it as ADATE8 without a problem. However, when I leave it in the mm/dd/yyyy hh:mm:ss format (including the time stamp), SPSS does not recognize it as a date, regardless of whether I use ADATE8 or ADATE20. I am using a .csv excel file by the way, and using a GET DATA command. Thoughts? Thanks. -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Converting-mm-dd-yyyy-hh-mm-ss-Excel-to-SPSS-tp5728030p5728036.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 |
Re: Converting mm/dd/yyyy hh:mm:ss Excel to SPSS
Administrator
|
In reply to this post by mdspss
David, did Rick's suggestions work for you? I ask, because they were not working for an example I generated (using SPSS 22.0.0.1 for Windoze). In particular, the AM-PM part of your date-time variable was causing problems. To get it work in the end, I had to resort to reading the date-time variable in as a string, pulling it apart, converting the date and time parts separately to ADATE10 and TIME8 variables--but I had to strip off the AM/PM before I could use ALTER TYPE to convert the time string to a TIME8 variable. And finally, where the original string had a PM on it, I had to add 12 hours to make the time correct. See below!
Here is the little CSV file I generated for testing: DT1,Case 12/22/2010 Â 7:06:00 PM,1 12/23/2010 Â 7:06:00 PM,2 12/24/2010 Â 7:06:00 PM,3 12/25/2010 Â 7:06:00 PM,4 12/26/2010 Â 7:06:00 PM,5 12/27/2010 Â 7:06:00 PM,6 12/28/2010 Â 7:06:00 PM,7 12/29/2010 Â 7:06:00 PM,8 12/30/2010 Â 7:06:00 PM,9 12/31/2010 Â 7:06:00 PM,10 12/22/2010 Â 7:06:00 AM,11 12/23/2010 Â 7:06:00 AM,12 12/24/2010 Â 7:06:00 AM,13 12/25/2010 Â 7:06:00 AM,14 12/26/2010 Â 7:06:00 AM,15 12/27/2010 Â 7:06:00 AM,16 12/28/2010 Â 7:06:00 AM,17 12/29/2010 Â 7:06:00 AM,18 12/30/2010 Â 7:06:00 AM,19 12/31/2010 Â 7:06:00 AM,20 1/1/2011 Â 7:06:00 AM,21 1/1/2011 Â 10:06:00 AM,22 And here is the syntax: NEW FILE. DATASET CLOSE all. GET DATA /TYPE=TXT /FILE="C:\Temp\ExcelDateTime.csv" /ENCODING='Locale' /DELCASE=LINE /DELIMITERS="," /ARRANGEMENT=DELIMITED /FIRSTCASE=2 /IMPORTCASE=ALL /VARIABLES= DTstring A23 Case F2.0. CACHE. EXECUTE. * SPSS has read the Date-Time variable as a STRING. * Extract the date and time pieces, then combine them * into a Date-Time variable. STRING date (A10). * Get the position of the space in the date-time string. COMPUTE #space = CHAR.INDEX(DTstring," "). COMPUTE date = CHAR.SUBSTR(DTstring,1,#space-1). * Now remove the DATE portion of DTstring. COMPUTE DTstring = CHAR.SUBSTR(DTstring,#space+2). * Compute flag for PM times. COMPUTE PM = CHAR.INDEX(DTstring,"PM") GT 0. * Recompute the position of the first space. COMPUTE #Space = CHAR.INDEX(DTstring," "). * Strip off the AM or PM. COMPUTE DTstring = CHAR.SUBSTR(DTstring,1,#space-1). * ALTER TYPE to TIME8. ALTER TYPE DTstring(TIME8). RENAME VARIABLES DTstring=Time. ALTER TYPE date (ADATE10). * Compute a new DateTime variable. NUMERIC DateTime(DATETIME22). COMPUTE DateTime = Date+Time. * If time is a PM time, add 12 hours. IF PM DateTime = DATESUM(DateTime,12,"hours"). LIST. And finally, here is the output: Time Case date PM DateTime 7:06:00 1 12/22/2010 1.00 22-DEC-2010 19:06:00 7:06:00 2 12/23/2010 1.00 23-DEC-2010 19:06:00 7:06:00 3 12/24/2010 1.00 24-DEC-2010 19:06:00 7:06:00 4 12/25/2010 1.00 25-DEC-2010 19:06:00 7:06:00 5 12/26/2010 1.00 26-DEC-2010 19:06:00 7:06:00 6 12/27/2010 1.00 27-DEC-2010 19:06:00 7:06:00 7 12/28/2010 1.00 28-DEC-2010 19:06:00 7:06:00 8 12/29/2010 1.00 29-DEC-2010 19:06:00 7:06:00 9 12/30/2010 1.00 30-DEC-2010 19:06:00 7:06:00 10 12/31/2010 1.00 31-DEC-2010 19:06:00 7:06:00 11 12/22/2010 .00 22-DEC-2010 07:06:00 7:06:00 12 12/23/2010 .00 23-DEC-2010 07:06:00 7:06:00 13 12/24/2010 .00 24-DEC-2010 07:06:00 7:06:00 14 12/25/2010 .00 25-DEC-2010 07:06:00 7:06:00 15 12/26/2010 .00 26-DEC-2010 07:06:00 7:06:00 16 12/27/2010 .00 27-DEC-2010 07:06:00 7:06:00 17 12/28/2010 .00 28-DEC-2010 07:06:00 7:06:00 18 12/29/2010 .00 29-DEC-2010 07:06:00 7:06:00 19 12/30/2010 .00 30-DEC-2010 07:06:00 7:06:00 20 12/31/2010 .00 31-DEC-2010 07:06:00 7:06:00 21 01/01/2011 .00 01-JAN-2011 07:06:00 10:06:00 22 01/01/2011 .00 01-JAN-2011 10:06:00 Number of cases read: 22 Number of cases listed: 22 HTH!
--
Bruce Weaver bweaver@lakeheadu.ca http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." PLEASE NOTE THE FOLLOWING: 1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. 2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/). |
Re: Converting mm/dd/yyyy hh:mm:ss Excel to SPSS
|
|
If the dates are read in as a string, and
we can assume two digits for month and day values, i.e., leading zeros
as appropriate, this will do the conversion.
spssinc trans result=truedate /formula "extendedTransforms.strtodatetime(dt1, '%m/%d/%Y %I:%M:%S %p')". format truedate (datetime22). If the leading zeros are not present, a little more code is required, but I won't post that unless it is needed. This requires the Python Essentials and the extendedTransforms.py module from the SPSS Community site. Jon Peck (no "h") aka Kim Senior Software Engineer, IBM [hidden email] phone: 720-342-5621 From: Bruce Weaver <[hidden email]> To: [hidden email] Date: 11/26/2014 01:26 PM Subject: Re: [SPSSX-L] Converting mm/dd/yyyy hh:mm:ss Excel to SPSS Sent by: "SPSSX(r) Discussion" <[hidden email]> David, did Rick's suggestions work for you? I ask, because they were not working for an example I generated (using SPSS 22.0.0.1 for Windoze). In particular, the AM-PM part of your date-time variable was causing problems. To get it work in the end, I had to resort to reading the date-time variable in as a string, pulling it apart, converting the date and time parts separately to ADATE10 and TIME8 variables--but I had to strip off the AM/PM before I could use ALTER TYPE to convert the time string to a TIME8 variable. And finally, where the original string had a PM on it, I had to add 12 hours to make the time correct. See below! Here is the little CSV file I generated for testing: DT1,Case 12/22/2010 7:06:00 PM,1 12/23/2010 7:06:00 PM,2 12/24/2010 7:06:00 PM,3 12/25/2010 7:06:00 PM,4 12/26/2010 7:06:00 PM,5 12/27/2010 7:06:00 PM,6 12/28/2010 7:06:00 PM,7 12/29/2010 7:06:00 PM,8 12/30/2010 7:06:00 PM,9 12/31/2010 7:06:00 PM,10 12/22/2010 7:06:00 AM,11 12/23/2010 7:06:00 AM,12 12/24/2010 7:06:00 AM,13 12/25/2010 7:06:00 AM,14 12/26/2010 7:06:00 AM,15 12/27/2010 7:06:00 AM,16 12/28/2010 7:06:00 AM,17 12/29/2010 7:06:00 AM,18 12/30/2010 7:06:00 AM,19 12/31/2010 7:06:00 AM,20 1/1/2011 7:06:00 AM,21 1/1/2011 10:06:00 AM,22 And here is the syntax: NEW FILE. DATASET CLOSE all. GET DATA /TYPE=TXT /FILE="C:\Temp\ExcelDateTime.csv" /ENCODING='Locale' /DELCASE=LINE /DELIMITERS="," /ARRANGEMENT=DELIMITED /FIRSTCASE=2 /IMPORTCASE=ALL /VARIABLES= DTstring A23 Case F2.0. CACHE. EXECUTE. * SPSS has read the Date-Time variable as a STRING. * Extract the date and time pieces, then combine them * into a Date-Time variable. STRING date (A10). * Get the position of the space in the date-time string. COMPUTE #space = CHAR.INDEX(DTstring," "). COMPUTE date = CHAR.SUBSTR(DTstring,1,#space-1). * Now remove the DATE portion of DTstring. COMPUTE DTstring = CHAR.SUBSTR(DTstring,#space+2). * Compute flag for PM times. COMPUTE PM = CHAR.INDEX(DTstring,"PM") GT 0. * Recompute the position of the first space. COMPUTE #Space = CHAR.INDEX(DTstring," "). * Strip off the AM or PM. COMPUTE DTstring = CHAR.SUBSTR(DTstring,1,#space-1). * ALTER TYPE to TIME8. ALTER TYPE DTstring(TIME8). RENAME VARIABLES DTstring=Time. ALTER TYPE date (ADATE10). * Compute a new DateTime variable. NUMERIC DateTime(DATETIME22). COMPUTE DateTime = Date+Time. * If time is a PM time, add 12 hours. IF PM DateTime = DATESUM(DateTime,12,"hours"). LIST. And finally, here is the output: Time Case date PM DateTime 7:06:00 1 12/22/2010 1.00 22-DEC-2010 19:06:00 7:06:00 2 12/23/2010 1.00 23-DEC-2010 19:06:00 7:06:00 3 12/24/2010 1.00 24-DEC-2010 19:06:00 7:06:00 4 12/25/2010 1.00 25-DEC-2010 19:06:00 7:06:00 5 12/26/2010 1.00 26-DEC-2010 19:06:00 7:06:00 6 12/27/2010 1.00 27-DEC-2010 19:06:00 7:06:00 7 12/28/2010 1.00 28-DEC-2010 19:06:00 7:06:00 8 12/29/2010 1.00 29-DEC-2010 19:06:00 7:06:00 9 12/30/2010 1.00 30-DEC-2010 19:06:00 7:06:00 10 12/31/2010 1.00 31-DEC-2010 19:06:00 7:06:00 11 12/22/2010 .00 22-DEC-2010 07:06:00 7:06:00 12 12/23/2010 .00 23-DEC-2010 07:06:00 7:06:00 13 12/24/2010 .00 24-DEC-2010 07:06:00 7:06:00 14 12/25/2010 .00 25-DEC-2010 07:06:00 7:06:00 15 12/26/2010 .00 26-DEC-2010 07:06:00 7:06:00 16 12/27/2010 .00 27-DEC-2010 07:06:00 7:06:00 17 12/28/2010 .00 28-DEC-2010 07:06:00 7:06:00 18 12/29/2010 .00 29-DEC-2010 07:06:00 7:06:00 19 12/30/2010 .00 30-DEC-2010 07:06:00 7:06:00 20 12/31/2010 .00 31-DEC-2010 07:06:00 7:06:00 21 01/01/2011 .00 01-JAN-2011 07:06:00 10:06:00 22 01/01/2011 .00 01-JAN-2011 10:06:00 Number of cases read: 22 Number of cases listed: 22 HTH! mdspss wrote > Thanks for your responses. Still having trouble. I've tried converting the > date on the excel end into a simple mm/dd/yyyy and then I can import it as > ADATE8 without a problem. However, when I leave it in the mm/dd/yyyy > hh:mm:ss format (including the time stamp), SPSS does not recognize it as > a date, regardless of whether I use ADATE8 or ADATE20. > > I am using a .csv excel file by the way, and using a GET DATA command. > > Thoughts? > > Thanks. ----- -- 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/Converting-mm-dd-yyyy-hh-mm-ss-Excel-to-SPSS-tp5728030p5728051.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 |
Re: Converting mm/dd/yyyy hh:mm:ss Excel to SPSS
Administrator
|
In reply to this post by Bruce Weaver
DATA LIST LIST/D (ADATE) T (TIME) AMPM (A2) Case (F2).
BEGIN DATA 12/22/2010 7:06:00 PM,1 12/23/2010 7:06:00 PM,2 12/24/2010 7:06:00 PM,3 12/25/2010 7:06:00 PM,4 12/26/2010 7:06:00 PM,5 12/27/2010 7:06:00 PM,6 12/28/2010 7:06:00 PM,7 12/29/2010 7:06:00 PM,8 12/30/2010 7:06:00 PM,9 12/31/2010 7:06:00 PM,10 12/22/2010 7:06:00 AM,11 12/23/2010 7:06:00 AM,12 12/24/2010 7:06:00 AM,13 12/25/2010 7:06:00 AM,14 12/26/2010 7:06:00 AM,15 12/27/2010 7:06:00 AM,16 12/28/2010 7:06:00 AM,17 12/29/2010 7:06:00 AM,18 12/30/2010 7:06:00 AM,19 12/31/2010 7:06:00 AM,20 1/1/2011 7:06:00 AM,21 1/1/2011 10:06:00 AM,22 END DATA. COMPUTE DT=D+T + (AMPM EQ "PM")*12 * 60 * 60. FORMATS DT (DATETIME20). LIST. ;-)
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. --- "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" |
Re: Converting mm/dd/yyyy hh:mm:ss Excel to SPSS
|
Administrator
|
Yeah, I considered importing the bits of the DateTime variable in separate chunks. But I didn't know if we could count on (OP) David's CSV file not having other variables with embedded spaces (e.g., an address field with a variable number of spaces). If there are no such variables, then this seems like the best approach.
p.s. - Happy Thanksgiving to all you folks from the USA.
--
Bruce Weaver bweaver@lakeheadu.ca http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." PLEASE NOTE THE FOLLOWING: 1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. 2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/). |
Re: Converting mm/dd/yyyy hh:mm:ss Excel to SPSS
|
Administrator
|
/* Determine AM or PM *.,
COMPUTE #PM=CHAR.INDEX(dt1,"PM") > 0. /*Strip AM/PM */. COMPUTE dt1=CHAR.SUBSTR(dt1,1,CHAR.RINDEX(dt1," ")-1). /* Calculate Date + Time + PM offset */. COMPUTE datetime=NUMBER(CHAR.SUBSTR(dt1,1,CHAR.INDEX(dt1," ")-1),ADATE) + NUMBER(LTRIM(CHAR.SUBSTR(dt1,CHAR.INDEX(dt1," ")+1)),TIME) + #PM * 43200. FORMATS datetime (Datetime20).
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. --- "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" |
|
|
In reply to this post by Bruce Weaver
Hi thank you all for your suggestions. Unfortunately, still no major resolution. Rick's suggestion did not work for me. Bruce's did not quite work for two reasons, I believe. One is the format of the excel date/time, i.e. 9/5/11 1:36 AM is slightly different in that it uses a two digit year and no leading zeros in the dates. Secondly, even if I was savvy enough to tweak Bruce's coding (which I'm not) it would be far too cumbersome as I have about 25 non-consecutive date columns to convert from excel to SPSS. I unfortunately am not familiar with Python so that suggestion seems simpler but does not help.
Any other thoughts? What am I missing, this doesn't seem like it should be so complicated... |
Re: Converting mm/dd/yyyy hh:mm:ss Excel to SPSS
|
Administrator
|
Study this and prosper grasshopper!
Perhaps attach a sample of your actual data IFF this doesn't work for you! DATA LIST LIST (",")/ DT1 (A22) Case (F2), DT2 (A22), XX (F2). BEGIN DATA 12/22/10 7:06:00 PM,1, 12/31/10 7:06:00 AM,20 12/23/10 7:06:00 PM,2, 12/31/10 7:06:00 AM,20 12/24/10 7:06:00 PM,3, 12/31/10 7:06:00 AM,20 12/25/10 7:06:00 PM,4, 12/31/10 7:06:00 AM,20 12/26/10 7:06:00 PM,5, 12/31/10 7:06:00 AM,20 12/27/10 7:06:00 PM,6, 12/31/10 7:06:00 AM,20 12/28/10 7:06:00 PM,7, 12/31/10 7:06:00 AM,20 12/29/10 7:06:00 PM,8, 12/31/10 7:06:00 AM,20 12/30/10 7:06:00 PM,9, 12/31/10 7:06:00 AM,20 12/31/10 7:06:00 PM,10, 12/31/10 7:06:00 AM,20 12/22/10 7:06:00 AM,11, 12/31/10 7:06:00 AM,20 12/23/10 7:06:00 AM,12, 12/31/10 7:06:00 AM,20 12/24/10 7:06:00 AM,13, 12/31/10 7:06:00 AM,20 12/25/10 7:06:00 AM,14, 12/31/10 7:06:00 AM,20 12/26/10 7:06:00 AM,15, 12/31/10 7:06:00 AM,20 12/27/10 7:06:00 AM,16, 12/31/10 7:06:00 AM,20 12/28/10 7:06:00 AM,17, 12/31/10 7:06:00 AM,20 12/29/10 7:06:00 AM,18, 12/31/10 7:06:00 AM,20 12/30/10 7:06:00 AM,19, 12/31/10 7:06:00 AM,20 12/31/10 7:06:00 AM,20, 12/31/10 7:06:00 AM,20 1/1/11 7:06:00 AM,21, 12/31/10 7:06:00 AM,20 1/1/11 10:06:00 AM,22, 12/31/10 7:06:00 AM,20 END DATA. STRING # (A22). DO REPEAT DT_IN=DT1 DT2 / DT_Out=DateTime_1 DateTime_2. /* Determine AM or PM *. COMPUTE #PM=CHAR.INDEX(DT_IN,"PM") > 0. /*Strip AM/PM */. COMPUTE #=LTRIM(CHAR.SUBSTR(DT_IN,1,CHAR.RINDEX(DT_IN," ")-1)). /* Calculate Date + Time + PM offset */. COMPUTE DT_Out = NUMBER(CHAR.SUBSTR(#,1,CHAR.INDEX(#," ")-1),ADATE) + NUMBER(LTRIM(CHAR.SUBSTR(#,CHAR.INDEX(#," ")+1)),TIME) + #PM * 43200. FORMATS DT_Out (Datetime20). END REPEAT. LIST.
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. --- "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" |
Re: Converting mm/dd/yyyy hh:mm:ss Excel to SPSS
|
In reply to this post by mdspss
You do not need to know python. you have to know how to copy-and-paste.
in the email message select and highlight the python code. Copy it. Open your spss data file. <file> <new><syntax> Paste the syntax from the email message int the syntax window. Run it.
Art Kendall
Social Research Consultants |
Re: Converting mm/dd/yyyy hh:mm:ss Excel to SPSS
|
Administrator
|
In reply to this post by David Marso
But as OP David is reading the data from a CSV file, he'll want to change the DATA LIST, and get rid of BEGIN DATA, END DATA and all the lines in between! The DATA LIST should look something like this (with the path and file name modified as necessaryj, and the variable names & formats modified, of course):
DATA LIST FILE="C:\TEMP\DTdata.csv" LIST (",")/ DT1 (A22) Case (F2), DT2 (A22), XX (F2). EXECUTE. After that, include the rest of DM's syntax, but modify it to make it work for 25 variables rather than 2! NPR--no Python required! ;-) HTH.
--
Bruce Weaver bweaver@lakeheadu.ca http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." PLEASE NOTE THE FOLLOWING: 1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. 2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/). |
Re: Converting mm/dd/yyyy hh:mm:ss Excel to SPSS
|
In reply to this post by David Marso
At 07:08 PM 11/26/2014, David Marso wrote, at the end of code for
parsing a date-time string with "AM/PM": >COMPUTE DT=D+T + (AMPM EQ "PM")*12 * 60 * 60. >FORMATS DT (DATETIME20). That COMPUTE will give incorrect answers for times between midnight and 1:00 AM, and between noon and 1:00 PM. You want, DO IF AMPM = "AM". . IF T GE TIME.HMS(12) T = T - TIME.HMS(12). ELSE IF AMPM = "PM". . IF T LT TIME.HMS(12) T = T + TIME.HMS(12). ELSE. .... insert error-handling code ..... END IF. COMPUTE DT = D + T. The later solution using SUBSTR has the same error. ===================== 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 |
Re: Converting mm/dd/yyyy hh:mm:ss Excel to SPSS
|
Administrator
|
Great catch Richard!
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. --- "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" |
Re: Converting mm/dd/yyyy hh:mm:ss Excel to SPSS
|
Administrator
|
I think I was the one who posted the faulty version in the first place. So yes, great catch.
--
Bruce Weaver bweaver@lakeheadu.ca http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." PLEASE NOTE THE FOLLOWING: 1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. 2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/). |
Re: Converting mm/dd/yyyy hh:mm:ss Excel to SPSS
|
Administrator
|
Yeah Bruce,
Your bad. My faulty code was more or less inspired by yours ;-)
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. --- "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" |
Re: Converting mm/dd/yyyy hh:mm:ss Excel to SPSS
|
|
In reply to this post by mdspss
Did this problem ever get fully resolved?
At 10:38 PM 11/26/2014, mdspss wrote: >Bruce's [solution] did not quite work for two reasons, I believe. >One is the format of the excel date/time, i.e. 9/5/11 1:36 AM is >slightly different in that it uses a two digit year and no leading >zeros in the dates. Bruce's parsing solution can probably be modified quite easily for this case. If you're still having a problem, would you post 20 lines or so of your actual data? (I'm assuming there's nothing too confidential to be posted, or that you can remove anything that is.) >Secondly, even if I was savvy enough to tweak Bruce's coding (which >I'm not) it would be far too cumbersome as I have about 25 >non-consecutive date columns to convert from excel to SPSS. There are a number of ways to handle this, but probably the easiest (as David Marso suggested) is to put whatever conversion logic you use -- Bruce's, David's, or whatever -- in a DO REPEAT loop, looping over the set of date variables. That entails very little more code than is needed to convert a single date-time variable. By the way, for future reference: A date that has been written from Excel to a .CSV file is no longer an 'Excel date'. Excel stores dates in its own internal form; if you are reading an Excel spreadsheet into SPSS, SPSS usually recognizes that form and reads the dates as proper SPSS dates. But Excel writes dates to .CSV files as string-form dates, as you've noticed, and those have to be read into SPSS *as* strings, and converted -- as the discussion has illustrated. ===================== 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 |
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |