Conversion of date (Julian?) variable
Locked
 
|
I have got this csv file which basically is an export from a measurement device, where the Time variable is formatted
in the following way: 43111,0001041667 (where comma is the decimal separator, Swedish standard) It has been possible to import the file into Excel, reformat the variable into a yy-mm-dd hh:mm:ss.ss format as it seems
to be a Julian date format, followed by import into SPSS. But is there any way to convert this string into a working Date (and time) format directly? None of my attempts seem to work. Any suggestions? Going via Excel is of course possible, but it is frustrating
not being able to do it directly in SPSS… Robert
Robert Lundqvist
|
Re: Conversion of date (Julian?) variable
|
|
My first (and only) thought is that this is the number of unknown time units since time=0, whenever that might be. If the time unit were days since December 31,
1899 so that January 1, 1900 was day 1, which might be what excel uses, then 43111 divided by 365.25 is 118.03 years, so 2018, about mid-day Jan 11. If that date is plausible, then that’s the computation.
Gene Maguin From: SPSSX(r) Discussion <[hidden email]>
On Behalf Of Robert Lundqvist I have got this csv file which basically is an export from a measurement device, where the Time variable is formatted in the following way:
43111,0001041667 (where comma is the decimal separator, Swedish standard) It has been possible to import the file into Excel, reformat the variable into a yy-mm-dd hh:mm:ss.ss format as it seems to be a Julian date format, followed
by import into SPSS. But is there any way to convert this string into a working Date (and time) format directly? None of my attempts seem to work. Any suggestions? Going via Excel is of course possible, but it is frustrating not being able to do it directly
in SPSS… Robert ===================== 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: Conversion of date (Julian?) variable
Administrator
|
In reply to this post by Robert L
Hello Robert. Your post prompted me to take a look at this syntax file,
which I wrote some time ago: http://www.angelfire.com/wv/bwhomedir/spss/importing_excel_dates.txt I'm not sure why you suspect the date to be Julian. If the part before the comma is stored as an Excel date (with the default 1900 base year), then something like this ought to generate a proper date variable in SPSS: NEW FILE. DATASET CLOSE ALL. DATA LIST LIST / datestr (A20). BEGIN DATA "43111,0001041667" END DATA. COMPUTE #commapos = CHAR.INDEX(datestr,","). COMPUTE date = NUMBER(CHAR.SUBSTR(datestr,1,#commapos-1),F8.0)* 86400 + 10010304000. ALTER TYPE date(DATE11). LIST. OUTPUT: datestr date 43111,0001041667 12-JAN-2018 Is 12-Jan-2018 the date you're getting using Excel? HTH. Robert L wrote > I have got this csv file which basically is an export from a measurement > device, where the Time variable is formatted in the following way: > > 43111,0001041667 (where comma is the decimal separator, Swedish standard) > > It has been possible to import the file into Excel, reformat the variable > into a yy-mm-dd hh:mm:ss.ss format as it seems to be a Julian date format, > followed by import into SPSS. But is there any way to convert this string > into a working Date (and time) format directly? None of my attempts seem > to work. Any suggestions? Going via Excel is of course possible, but it is > frustrating not being able to do it directly in SPSS... > > Robert > > ===================== > 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 ----- -- 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. -- Sent from: http://spssx-discussion.1045642.n5.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
--
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: Conversion of date (Julian?) variable
Administrator
|
As a general practice, I try to avoid 'magic numbers'.
We can deduce from the following that Bruce's magic number "10010304000" is the beginning of the Gregorian calender. NEW FILE. DATASET CLOSE ALL. DATA LIST LIST / x (F20). BEGIN DATA 10010304000 END DATA. COMPUTE X=CTIME.DAYS(10010304000). FORMATS x (DATE11). LIST. x 15-OCT-1582 I also prefer to use TIME.DAYS(1) rather than the constant 86400. In this case might as well use the Excel Offset DATE.MDY(12,31,1899) and add the value of TIME.DAYS(stuff before comma). My question is what is the stuff AFTER the comma? BTW: When I read it into Open Office and Format as date after changing , to . I get back Jan 11, 2018 so we seem to be off by 1. Hard to tell. Maybe we need to subtract one day? NEW FILE. DATASET CLOSE ALL. DATA LIST LIST / datestr (A20). BEGIN DATA "43111,0001041667" END DATA. COMPUTE #comma = NUMBER(CHAR.SUBSTR(datestr,1,CHAR.INDEX(datestr,",")-1),F8.0) . COMPUTE date = SUM(DATE.MDY(12,31,1899),TIME.DAYS(#comma)). ALTER TYPE date(DATE11). LIST. datestr date 43111,0001041667 12-JAN-2018 Number of cases read: 1 Number of cases listed: 1 This version subtracts one day to achieve agreement with Open Office/Excel NEW FILE. DATASET CLOSE ALL. DATA LIST LIST / datestr (A20). BEGIN DATA "43111,0001041667" END DATA. COMPUTE #comma = NUMBER(CHAR.SUBSTR(datestr,1,CHAR.INDEX(datestr,",")-1),F8.0) . COMPUTE date = SUM(DATE.MDY(12,31,1899),TIME.DAYS(#comma))-TIME.DAYS(1). ALTER TYPE date(DATE11). LIST. datestr date 43111,0001041667 11-JAN-2018 Finally, we might as well skip the parsing and jump right to the jugular -read two variables and just use the first one in the calculations. NEW FILE. DATASET CLOSE ALL. DATA LIST LIST / datestr junk. BEGIN DATA 43111,0001041667 END DATA. COMPUTE date = SUM(DATE.MDY(12,31,1899),TIME.DAYS(datestr))-TIME.DAYS(1). ALTER TYPE date(DATE11). LIST. ------------------------------------------------------------------------ Bruce Weaver wrote > Hello Robert. Your post prompted me to take a look at this syntax file, > which I wrote some time ago: > > http://www.angelfire.com/wv/bwhomedir/spss/importing_excel_dates.txt > > I'm not sure why you suspect the date to be Julian. If the part before > the > comma is stored as an Excel date (with the default 1900 base year), then > something like this ought to generate a proper date variable in SPSS: > > NEW FILE. > DATASET CLOSE ALL. > DATA LIST LIST / datestr (A20). > BEGIN DATA > "43111,0001041667" > END DATA. > > COMPUTE #commapos = CHAR.INDEX(datestr,","). > COMPUTE date = NUMBER(CHAR.SUBSTR(datestr,1,#commapos-1),F8.0)* 86400 + > 10010304000. > ALTER TYPE date(DATE11). > LIST. > > OUTPUT: > > datestr date > 43111,0001041667 12-JAN-2018 > > Is 12-Jan-2018 the date you're getting using Excel? > > HTH. > > > > Robert L wrote >> I have got this csv file which basically is an export from a measurement >> device, where the Time variable is formatted in the following way: >> >> 43111,0001041667 (where comma is the decimal separator, Swedish standard) >> >> It has been possible to import the file into Excel, reformat the variable >> into a yy-mm-dd hh:mm:ss.ss format as it seems to be a Julian date >> format, >> followed by import into SPSS. But is there any way to convert this string >> into a working Date (and time) format directly? None of my attempts seem >> to work. Any suggestions? Going via Excel is of course possible, but it >> is >> frustrating not being able to do it directly in SPSS... >> >> Robert >> >> ===================== >> 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 > > > > > > ----- > -- > Bruce Weaver > bweaver@ > 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. > > -- > Sent from: http://spssx-discussion.1045642.n5.nabble.com/ > > ===================== > 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?" -- Sent from: http://spssx-discussion.1045642.n5.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
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?" |
|
|
The Mac and Windows versions of Excel differ in their base dates by one day. On Fri, Jan 25, 2019 at 10:24 AM David Marso <[hidden email]> wrote: As a general practice, I try to avoid 'magic numbers'. |
Re: Conversion of date (Julian?) variable
|
Administrator
|
In reply to this post by David Marso
I like that last one. "Very nice", as Borat would say. ;-)
David Marso wrote > --- snip --- > > Finally, we might as well skip the parsing and jump right to the jugular > -read two variables and just use the first one in the calculations. > > NEW FILE. > DATASET CLOSE ALL. > DATA LIST LIST / datestr junk. > BEGIN DATA > 43111,0001041667 > END DATA. > COMPUTE date = SUM(DATE.MDY(12,31,1899),TIME.DAYS(datestr))-TIME.DAYS(1). > ALTER TYPE date(DATE11). > LIST. ----- -- 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. -- Sent from: http://spssx-discussion.1045642.n5.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
--
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/). |
|
|
In reply to this post by Robert L
Many thanks for your suggestions, great support as always. The part after the comma which David asked about is actually time (hours, minutes and seconds down to tenths of seconds). So I simply added some parts:
NEW FILE. DATASET CLOSE ALL. DATA LIST LIST / datestr (A20). BEGIN DATA '43104,0031076389' END DATA. COMPUTE #comma = NUMBER(CHAR.SUBSTR(datestr,1,CHAR.INDEX(datestr,",")-1),F8.0) . COMPUTE date = SUM(DATE.DMY(30,12,1899),TIME.DAYS(#comma)). COMPUTE #rem=24*(NUMBER(datestr,F16.10)-#comma) /*Number of hours remaining after date component has been extracted*/. COMPUTE #time0=3600*#rem /*The number of seconds*/. COMPUTE time=date+#time0. EXECUTE. FORMATS date (DATE11) time(DATETIME24.1). And as David spotted, for some reason it was necessary to subtract one day off the date. Thanks again! Robert -----Ursprungligt meddelande----- Från: SPSSX(r) Discussion [mailto:[hidden email]] För David Marso Skickat: den 25 januari 2019 18:24 Till: [hidden email] Ämne: Re: Conversion of date (Julian?) variable As a general practice, I try to avoid 'magic numbers'. We can deduce from the following that Bruce's magic number "10010304000" is the beginning of the Gregorian calender. NEW FILE. DATASET CLOSE ALL. DATA LIST LIST / x (F20). BEGIN DATA 10010304000 END DATA. COMPUTE X=CTIME.DAYS(10010304000). FORMATS x (DATE11). LIST. x 15-OCT-1582 I also prefer to use TIME.DAYS(1) rather than the constant 86400. In this case might as well use the Excel Offset DATE.MDY(12,31,1899) and add the value of TIME.DAYS(stuff before comma). My question is what is the stuff AFTER the comma? BTW: When I read it into Open Office and Format as date after changing , to . I get back Jan 11, 2018 so we seem to be off by 1. Hard to tell. Maybe we need to subtract one day? NEW FILE. DATASET CLOSE ALL. DATA LIST LIST / datestr (A20). BEGIN DATA "43111,0001041667" END DATA. COMPUTE #comma = NUMBER(CHAR.SUBSTR(datestr,1,CHAR.INDEX(datestr,",")-1),F8.0) . COMPUTE date = SUM(DATE.MDY(12,31,1899),TIME.DAYS(#comma)). ALTER TYPE date(DATE11). LIST. datestr date 43111,0001041667 12-JAN-2018 Number of cases read: 1 Number of cases listed: 1 This version subtracts one day to achieve agreement with Open Office/Excel NEW FILE. DATASET CLOSE ALL. DATA LIST LIST / datestr (A20). BEGIN DATA "43111,0001041667" END DATA. COMPUTE #comma = NUMBER(CHAR.SUBSTR(datestr,1,CHAR.INDEX(datestr,",")-1),F8.0) . COMPUTE date = SUM(DATE.MDY(12,31,1899),TIME.DAYS(#comma))-TIME.DAYS(1). ALTER TYPE date(DATE11). LIST. datestr date 43111,0001041667 11-JAN-2018 Finally, we might as well skip the parsing and jump right to the jugular -read two variables and just use the first one in the calculations. NEW FILE. DATASET CLOSE ALL. DATA LIST LIST / datestr junk. BEGIN DATA 43111,0001041667 END DATA. COMPUTE date = SUM(DATE.MDY(12,31,1899),TIME.DAYS(datestr))-TIME.DAYS(1). ALTER TYPE date(DATE11). LIST. ------------------------------------------------------------------------ Bruce Weaver wrote > Hello Robert. Your post prompted me to take a look at this syntax > file, which I wrote some time ago: > > http://www.angelfire.com/wv/bwhomedir/spss/importing_excel_dates.txt > > I'm not sure why you suspect the date to be Julian. If the part > before the comma is stored as an Excel date (with the default 1900 > base year), then something like this ought to generate a proper date > variable in SPSS: > > NEW FILE. > DATASET CLOSE ALL. > DATA LIST LIST / datestr (A20). > BEGIN DATA > "43111,0001041667" > END DATA. > > COMPUTE #commapos = CHAR.INDEX(datestr,","). > COMPUTE date = NUMBER(CHAR.SUBSTR(datestr,1,#commapos-1),F8.0)* 86400 > + 10010304000. > ALTER TYPE date(DATE11). > LIST. > > OUTPUT: > > datestr date > 43111,0001041667 12-JAN-2018 > > Is 12-Jan-2018 the date you're getting using Excel? > > HTH. > > > > Robert L wrote >> I have got this csv file which basically is an export from a >> measurement device, where the Time variable is formatted in the following way: >> >> 43111,0001041667 (where comma is the decimal separator, Swedish >> standard) >> >> It has been possible to import the file into Excel, reformat the >> variable into a yy-mm-dd hh:mm:ss.ss format as it seems to be a >> Julian date format, followed by import into SPSS. But is there any >> way to convert this string into a working Date (and time) format >> directly? None of my attempts seem to work. Any suggestions? Going >> via Excel is of course possible, but it is frustrating not being able >> to do it directly in SPSS... >> >> Robert >> >> ===================== >> 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 > > > > > > ----- > -- > Bruce Weaver > bweaver@ > 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. > > -- > Sent from: http://spssx-discussion.1045642.n5.nabble.com/ > > ===================== > 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?" -- Sent from: http://spssx-discussion.1045642.n5.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
Robert Lundqvist
|
|
You have actually stumbled on a long term ignored bug in Excel's date
handling. 1900 is handled as a leap year, but was not, as only centennials evenly divided by 400 should be a leap year, according to the gregorian calendar. That is the explanation to the 1 day difference between SPSS and Excel. According to Microsoft documentation the date base is identical in Windows and Mac versions, but I do not know if the Mac version handles the year 1900 differently. There was an option (Apple, IIRC), to set the date base to 1904 jan 1 to avoid leap year troubles. I guess very few Excel users handle old dates nowadays, or demand time resolution below years. Excel dates are based on integer values where DMY.DATE(1, 1 , 1900) is day 1 (day 0 is undefined...) and DMY.DATE(2, 1, 1900) is day 2. Time values are fractions of a day where 0 is midnight and 0.5 is 12 PM. See https://en.wikipedia.org/wiki/Year_1900_problem https://support.office.com/en-us/article/date-function-e36c0c8c-4104-49da-ab83-82328b832349 https://support.office.com/en-us/article/time-function-9a5aff99-8f7d-4611-845e-747d0b8d5457 My version on this conversion follows. As Excel datevalue fractions are part of a day no further handling is necessary. SPSS will interpret the resolution correctly. Also remember there is a way to temporary handle decimal values with a foreign decimal sign... /PR *------------------------------------. NEW FILE. PRESERVE. * Handle the decimal number with a comma! SET DECIMAL = COMMA. DATA LIST LIST / xlDateValue (F15.10). BEGIN DATA 43111,0001041667 0 1 60 39448 END DATA. RESTORE. COMPUTE #xlStartDate = DATE.DMY(31, 12, 1899). COMPUTE spssDate = #xlStartDate + (xlDateValue - (xlDateValue GT 60)) * TIME.DAYS(1). * spss handles 1900 leap day correctly. COMPUTE spss1900ly = DATE.DMY(29, 2, 1900). EXECUTE. FORMATS spssDate spss1900ly (YMDHMS40.5). LIST xlDateValue spssDate . *------------------------------------. xlDateValue spssDate 43111,0001041667 2018-01-11 00:00:09.00000 0,0000000000 1899-12-31 00:00:00.00000 1,0000000000 1900-01-01 00:00:00.00000 60,0000000000 1900-03-01 00:00:00.00000 39448,0000000000 2008-01-01 00:00:00.00000 Number of cases read: 5 Number of cases listed: 5 *------------------------------------. -- Sent from: http://spssx-discussion.1045642.n5.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 |
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |