Reading in a date from Excel to SPSS
Locked
Reading in a date from Excel to SPSS
 
|
Hi All I have a data set with start and end times for two tests. The data in Excel is (I think) a combined datestamp and timestamp and looks like this 06/02/2016 09:34:52 When I read it in to SPSS it displays as 1017753:34:52.00 With Type=Date and Width=11. If I convert to Date=”dd-mmm-yyyy hh:mm:ss.ss” in variable view the date displays as “20-Nov-1698 09:34:52.00”.
This is due to the fact that Excel counts dates from 01/01/1900 whilst SPSS counts from 14/10/1582. The first part of the date “1017753:34:52.00” is the number of hours from the starting date in Excel. What I have done is to add the difference between the two start dates 2780616 hours to get the date/hour part up to Feb 2016. So I have converted the “1017753:34:52.00” using Alter Type to string and mucked about that way (the syntax is
below). My problem is how to convert the adjusted string back into the appropriate date format (the final part of the syntax). I confess that I am not fully au fait with the process. Any help would be very much appreciated. David *Change the format read in from Excel (which is a combined DateStamp TimeStamp) from date11 in SPSS containing Hours:Minutes:Seconds:Milliseconds. *Although it comes in as date11 there are actually 16 characters. alter type VR_StartTime (a16) VR_EndTime (a16) NA_StartTime (a16) NA_EndTime (a16). execute. *Breaking up the existing variables into the hour "FirstBit" and the minutes, seconds, milliseconds "LastBit". *I will concatenate the corrected "FirstBit" (adding in the difference in hours between 14/10/1582 which SPSS uses to count dates from *and 01/01/1900 which Excel uses. The time difference is 2,780,616 hours.). string VR_StartFirstBit VR_EndFirstBit NA_StartFirstBit NA_EndFirstBit (a7)/ VR_StartLastBit VR_EndLastBit NA_StartLastBit NA_EndLastBit (a9). execute. *Getting the hours bit. compute VR_StartFirstBit=substr(VR_StartTime,1,7). compute VR_EndFirstBit=substr(VR_EndTime,1,7). compute NA_StartFirstBit=substr(NA_StartTime,1,7). compute NA_EndFirstBit=substr(NA_EndTime,1,7). execute. *Getting minutes, seconds, milliseconds bit. compute VR_StartLastBit=substr(VR_StartTime,8,9). compute VR_EndLastBit=substr(VR_EndTime,8,9). compute NA_StartLastBit=substr(NA_StartTime,8,9). compute NA_EndLastBit=substr(NA_EndTime,8,9). execute. *Converting the hours bit to a numeric variable so I can add 2780616 to it. compute VR_StartFirstBitnum=number(VR_StartFirstBit,f7.0). compute VR_EndFirstBitnum=number(VR_EndFirstBit,f7.0). compute NA_StartFirstBitnum=number(NA_StartFirstBit,f7.0). compute NA_EndFirstBitnum=number(NA_EndFirstBit,f7.0). execute. *Adding the time difference (in hours) between the two dates that Excel and SPSS use to count dates from. compute VR_StartFirstBitnum=VR_StartFirstBitnum+2780616. compute VR_EndFirstBitnum=VR_EndFirstBitnum+2780616. compute NA_StartFirstBitnum=NA_StartFirstBitnum+2780616. compute NA_EndFirstBitnum=NA_EndFirstBitnum+2780616. execute. *Getting rid of the old "wrong" hours variables in string formats so I can reuse the names for the "corrected" hours. delete variables VR_StartFirstBit VR_EndFirstBit NA_StartFirstBit NA_EndFirstBit. execute. *Converting the numeric variables back into text. string VR_StartFirstBit VR_EndFirstBit NA_StartFirstBit NA_EndFirstBit (a7). execute. compute VR_StartFirstBit=string(VR_StartFirstBitnum,f7.0). compute VR_EndFirstBit=string(VR_EndFirstBitnum,f7.0). compute NA_StartFirstBit=string(NA_StartFirstBitnum,f7.0). compute NA_EndFirstBit=string(NA_EndFirstBitnum,f7.0). execute. *Concatenating the new first with with the old last bit. string VR_StartTimeNew (a16) VR_EndTimeNew (a16) NA_StartTimeNew (a16) NA_EndTimeNew (a16). execute. compute VR_StartTimeNew=concat(VR_StartFirstBit,VR_StartLastBit). compute VR_EndTimeNew=concat(VR_EndFirstBit,VR_EndLastBit). compute NA_StartTimeNew=concat(NA_StartFirstBit,NA_StartLastBit). compute NA_EndTimeNew=concat(NA_EndFirstBit,NA_EndLastBit). execute. *Alter type to proper date format. Alter type VR_StartTimeNew (DATETIME16.2) VR_EndTimeNew (DATETIME16.2) NA_StartTimeNew (DATETIME16.2) NA_EndTimeNew (DATETIME16.2). execute. ------------------------------------------- Dr David Millar Research Associate Educational Research Centre St Patrick's College Dublin 9 Tel 01 8065226 |
Re: Reading in a date from Excel to SPSS
Administrator
|
SPSS stores Date and Time internally as SECONDS not Days ;-)
COMPUTE NewDate=MyDate+2780616*60*60. FORMATS NewDate(ADATE). 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?" |
|
|
double check the format in Excel. Statistics should bring it over and convert to the Statistics equivalent automatically. You might need to change the format in Statistics to datetime to see the time portion, but the datetime value comes over correctly when the format in Excel is datetime. On Wed, Feb 24, 2016 at 6:40 AM, David Marso <[hidden email]> wrote: SPSS stores Date and Time internally as SECONDS not Days ;-) |
Re: Reading in a date from Excel to SPSS
Administrator
|
In reply to this post by David Millar
Here's some info from my SPSS page that may be helpful.
Importing date variables from Excel: importing_excel_dates.txt: How to deal with some of the problems that arise The output generated by that syntax A note on dates in Excel, QuattroPro, and SPSS. IBM Technotes on this issue: http://www-01.ibm.com/support/docview.wss?uid=swg21476759 http://www-01.ibm.com/support/docview.wss?uid=swg21489431 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: Reading in a date from Excel to SPSS
|
|
In reply to this post by David Marso
Wow! A little more concise than my effort . . .
Thank you. ________________________________________ From: SPSSX(r) Discussion <[hidden email]> on behalf of David Marso <[hidden email]> Sent: Wednesday, February 24, 2016 1:40 PM To: [hidden email] Subject: Re: Reading in a date from Excel to SPSS SPSS stores Date and Time internally as SECONDS not Days ;-) COMPUTE NewDate=MyDate+2780616*60*60. FORMATS NewDate(ADATE). LIST. David Millar wrote > Hi All > > I have a data set with start and end times for two tests. The data in > Excel is (I think) a combined datestamp and timestamp and looks like this > > 06/02/2016 09:34:52 > > When I read it in to SPSS it displays as > > 1017753:34:52.00 > > With Type=Date and Width=11. If I convert to Date="dd-mmm-yyyy > hh:mm:ss.ss" in variable view the date displays as "20-Nov-1698 > 09:34:52.00". This is due to the fact that Excel counts dates from > 01/01/1900 whilst SPSS counts from 14/10/1582. The first part of the date > "1017753:34:52.00" is the number of hours from the starting date in Excel. > > What I have done is to add the difference between the two start dates > 2780616 hours to get the date/hour part up to Feb 2016. So I have > converted the "1017753:34:52.00" using Alter Type to string and mucked > about that way (the syntax is below). My problem is how to convert the > adjusted string back into the appropriate date format (the final part of > the syntax). I confess that I am not fully au fait with the process. Any > help would be very much appreciated. > > David > > > > *Change the format read in from Excel (which is a combined DateStamp > TimeStamp) from date11 in SPSS containing > Hours:Minutes:Seconds:Milliseconds. > *Although it comes in as date11 there are actually 16 characters. > alter type > VR_StartTime (a16) > VR_EndTime (a16) > NA_StartTime (a16) > NA_EndTime (a16). > execute. > > *Breaking up the existing variables into the hour "FirstBit" and the > minutes, seconds, milliseconds "LastBit". > *I will concatenate the corrected "FirstBit" (adding in the difference in > hours between 14/10/1582 which SPSS uses to count dates from > *and 01/01/1900 which Excel uses. The time difference is 2,780,616 > hours.). > string > VR_StartFirstBit > VR_EndFirstBit > NA_StartFirstBit > NA_EndFirstBit (a7)/ > VR_StartLastBit > VR_EndLastBit > NA_StartLastBit > NA_EndLastBit (a9). > execute. > > *Getting the hours bit. > compute VR_StartFirstBit=substr(VR_StartTime,1,7). > compute VR_EndFirstBit=substr(VR_EndTime,1,7). > compute NA_StartFirstBit=substr(NA_StartTime,1,7). > compute NA_EndFirstBit=substr(NA_EndTime,1,7). > execute. > > *Getting minutes, seconds, milliseconds bit. > compute VR_StartLastBit=substr(VR_StartTime,8,9). > compute VR_EndLastBit=substr(VR_EndTime,8,9). > compute NA_StartLastBit=substr(NA_StartTime,8,9). > compute NA_EndLastBit=substr(NA_EndTime,8,9). > execute. > > > *Converting the hours bit to a numeric variable so I can add 2780616 to > it. > compute VR_StartFirstBitnum=number(VR_StartFirstBit,f7.0). > compute VR_EndFirstBitnum=number(VR_EndFirstBit,f7.0). > compute NA_StartFirstBitnum=number(NA_StartFirstBit,f7.0). > compute NA_EndFirstBitnum=number(NA_EndFirstBit,f7.0). > execute. > > *Adding the time difference (in hours) between the two dates that Excel > and SPSS use to count dates from. > compute VR_StartFirstBitnum=VR_StartFirstBitnum+2780616. > compute VR_EndFirstBitnum=VR_EndFirstBitnum+2780616. > compute NA_StartFirstBitnum=NA_StartFirstBitnum+2780616. > compute NA_EndFirstBitnum=NA_EndFirstBitnum+2780616. > execute. > > *Getting rid of the old "wrong" hours variables in string formats so I can > reuse the names for the "corrected" hours. > delete variables > VR_StartFirstBit > VR_EndFirstBit > NA_StartFirstBit > NA_EndFirstBit. > execute. > > *Converting the numeric variables back into text. > string > VR_StartFirstBit > VR_EndFirstBit > NA_StartFirstBit > NA_EndFirstBit (a7). > execute. > compute VR_StartFirstBit=string(VR_StartFirstBitnum,f7.0). > compute VR_EndFirstBit=string(VR_EndFirstBitnum,f7.0). > compute NA_StartFirstBit=string(NA_StartFirstBitnum,f7.0). > compute NA_EndFirstBit=string(NA_EndFirstBitnum,f7.0). > execute. > > *Concatenating the new first with with the old last bit. > string > VR_StartTimeNew (a16) > VR_EndTimeNew (a16) > NA_StartTimeNew (a16) > NA_EndTimeNew (a16). > execute. > compute VR_StartTimeNew=concat(VR_StartFirstBit,VR_StartLastBit). > compute VR_EndTimeNew=concat(VR_EndFirstBit,VR_EndLastBit). > compute NA_StartTimeNew=concat(NA_StartFirstBit,NA_StartLastBit). > compute NA_EndTimeNew=concat(NA_EndFirstBit,NA_EndLastBit). > execute. > > *Alter type to proper date format. > Alter type > VR_StartTimeNew (DATETIME16.2) > VR_EndTimeNew (DATETIME16.2) > NA_StartTimeNew (DATETIME16.2) > NA_EndTimeNew (DATETIME16.2). > execute. > > > > ------------------------------------------- > > Dr David Millar > Research Associate > Educational Research Centre > St Patrick's College > Dublin 9 > > Tel 01 8065226 > > ===================== > To manage your subscription to SPSSX-L, send a message to > LISTSERV@.UGA > (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 ----- 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?" -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Reading-in-a-date-from-Excel-to-SPSS-tp5731597p5731600.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: Reading in a date from Excel to SPSS
|
|
In reply to this post by Jon Peck
Thanks Jon. Yes, I needed datetime.
All I can say is that SPSS didn't present the data correctly. It presented as HHHHHHH:MM:SS:ss. I was aware, as David Marso said, that SPSS stores dates in seconds since 14/10/1582 BUT it displayed as above. And when I added in the extra 2million-odd hours and manually converted in the variable view it displayed ok. David ________________________________ From: SPSSX(r) Discussion <[hidden email]> on behalf of Jon Peck <[hidden email]> Sent: Wednesday, February 24, 2016 3:27 PM To: [hidden email] Subject: Re: Reading in a date from Excel to SPSS double check the format in Excel. Statistics should bring it over and convert to the Statistics equivalent automatically. You might need to change the format in Statistics to datetime to see the time portion, but the datetime value comes over correctly when the format in Excel is datetime. On Wed, Feb 24, 2016 at 6:40 AM, David Marso <[hidden email]<mailto:[hidden email]>> wrote: SPSS stores Date and Time internally as SECONDS not Days ;-) COMPUTE NewDate=MyDate+2780616*60*60. FORMATS NewDate(ADATE). LIST. David Millar wrote > Hi All > > I have a data set with start and end times for two tests. The data in > Excel is (I think) a combined datestamp and timestamp and looks like this > > 06/02/2016 09:34:52 > > When I read it in to SPSS it displays as > > 1017753:34:52.00 > > With Type=Date and Width=11. If I convert to Date="dd-mmm-yyyy > hh:mm:ss.ss" in variable view the date displays as "20-Nov-1698 > 09:34:52.00". This is due to the fact that Excel counts dates from > 01/01/1900 whilst SPSS counts from 14/10/1582. The first part of the date > "1017753:34:52.00" is the number of hours from the starting date in Excel. > > What I have done is to add the difference between the two start dates > 2780616 hours to get the date/hour part up to Feb 2016. So I have > converted the "1017753:34:52.00" using Alter Type to string and mucked > about that way (the syntax is below). My problem is how to convert the > adjusted string back into the appropriate date format (the final part of > the syntax). I confess that I am not fully au fait with the process. Any > help would be very much appreciated. > > David > > > > *Change the format read in from Excel (which is a combined DateStamp > TimeStamp) from date11 in SPSS containing > Hours:Minutes:Seconds:Milliseconds. > *Although it comes in as date11 there are actually 16 characters. > alter type > VR_StartTime (a16) > VR_EndTime (a16) > NA_StartTime (a16) > NA_EndTime (a16). > execute. > > *Breaking up the existing variables into the hour "FirstBit" and the > minutes, seconds, milliseconds "LastBit". > *I will concatenate the corrected "FirstBit" (adding in the difference in > hours between 14/10/1582 which SPSS uses to count dates from > *and 01/01/1900 which Excel uses. The time difference is 2,780,616 > hours.). > string > VR_StartFirstBit > VR_EndFirstBit > NA_StartFirstBit > NA_EndFirstBit (a7)/ > VR_StartLastBit > VR_EndLastBit > NA_StartLastBit > NA_EndLastBit (a9). > execute. > > *Getting the hours bit. > compute VR_StartFirstBit=substr(VR_StartTime,1,7). > compute VR_EndFirstBit=substr(VR_EndTime,1,7). > compute NA_StartFirstBit=substr(NA_StartTime,1,7). > compute NA_EndFirstBit=substr(NA_EndTime,1,7). > execute. > > *Getting minutes, seconds, milliseconds bit. > compute VR_StartLastBit=substr(VR_StartTime,8,9). > compute VR_EndLastBit=substr(VR_EndTime,8,9). > compute NA_StartLastBit=substr(NA_StartTime,8,9). > compute NA_EndLastBit=substr(NA_EndTime,8,9). > execute. > > > *Converting the hours bit to a numeric variable so I can add 2780616 to > it. > compute VR_StartFirstBitnum=number(VR_StartFirstBit,f7.0). > compute VR_EndFirstBitnum=number(VR_EndFirstBit,f7.0). > compute NA_StartFirstBitnum=number(NA_StartFirstBit,f7.0). > compute NA_EndFirstBitnum=number(NA_EndFirstBit,f7.0). > execute. > > *Adding the time difference (in hours) between the two dates that Excel > and SPSS use to count dates from. > compute VR_StartFirstBitnum=VR_StartFirstBitnum+2780616. > compute VR_EndFirstBitnum=VR_EndFirstBitnum+2780616. > compute NA_StartFirstBitnum=NA_StartFirstBitnum+2780616. > compute NA_EndFirstBitnum=NA_EndFirstBitnum+2780616. > execute. > > *Getting rid of the old "wrong" hours variables in string formats so I can > reuse the names for the "corrected" hours. > delete variables > VR_StartFirstBit > VR_EndFirstBit > NA_StartFirstBit > NA_EndFirstBit. > execute. > > *Converting the numeric variables back into text. > string > VR_StartFirstBit > VR_EndFirstBit > NA_StartFirstBit > NA_EndFirstBit (a7). > execute. > compute VR_StartFirstBit=string(VR_StartFirstBitnum,f7.0). > compute VR_EndFirstBit=string(VR_EndFirstBitnum,f7.0). > compute NA_StartFirstBit=string(NA_StartFirstBitnum,f7.0). > compute NA_EndFirstBit=string(NA_EndFirstBitnum,f7.0). > execute. > > *Concatenating the new first with with the old last bit. > string > VR_StartTimeNew (a16) > VR_EndTimeNew (a16) > NA_StartTimeNew (a16) > NA_EndTimeNew (a16). > execute. > compute VR_StartTimeNew=concat(VR_StartFirstBit,VR_StartLastBit). > compute VR_EndTimeNew=concat(VR_EndFirstBit,VR_EndLastBit). > compute NA_StartTimeNew=concat(NA_StartFirstBit,NA_StartLastBit). > compute NA_EndTimeNew=concat(NA_EndFirstBit,NA_EndLastBit). > execute. > > *Alter type to proper date format. > Alter type > VR_StartTimeNew (DATETIME16.2) > VR_EndTimeNew (DATETIME16.2) > NA_StartTimeNew (DATETIME16.2) > NA_EndTimeNew (DATETIME16.2). > execute. > > > > ------------------------------------------- > > Dr David Millar > Research Associate > Educational Research Centre > St Patrick's College > Dublin 9 > > Tel 01 8065226 > > ===================== > To manage your subscription to SPSSX-L, send a message to > LISTSERV@.UGA > (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 ----- 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?" -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Reading-in-a-date-from-Excel-to-SPSS-tp5731597p5731600.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]<mailto:[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 -- Jon K Peck [hidden email]<mailto:[hidden email]> ===================== To manage your subscription to SPSSX-L, send a message to [hidden email]<mailto:[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 |
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |