Dates in A8 format
Locked
 
|
Marso will kill me for asking, as he posted a reply on 23 Feb which may already have answered the question. I have a data set with dates entered as ddmmyyyy for two different variables with identical values. They correlate 0.998 so one of them can be discarded. 22082005 22082005 05092005 05092005 For some reason they are defined as strings, but they are clearly f8. How can I convert them to edate10 format? This all part of the nightmare combining files from 32 waves of the British Social Attitudes survey. I’ve already got a cumulative file for 1983 to 1994, 2009 t0 2014 and am now working on 2000 to 2008 and am down to the last 8 variables with the same name but different formats. Waves 1995 to 1999 are still to process, but I can probably cope with everything except dates. John F Hall (Mr) [Retired academic survey researcher] Email: [hidden email] Website: www.surveyresearch.weebly.com SPSS start page: www.surveyresearch.weebly.com/1-survey-analysis-workshop |
|
Strings are more portable than Gregorian dates. Too bad they're no iso-8601 dates, which are sortable in a useful way.
=====================
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
See Char.substr, Number, date.dmy functions followed by formats var (edate20) or similar. Sdate (s = sortable, or asian, date) is lesser known but looks like iso dates. Alternatively, you can use Number, Mod, Trunc, date.dmy and formats. Date: Thu, 7 Apr 2016 13:02:04 +0200 From: [hidden email] Subject: [SPSSX-L] Dates in A8 format To: [hidden email] Marso will kill me for asking, as he posted a reply on 23 Feb which may already have answered the question.
I have a data set with dates entered as ddmmyyyy for two different variables with identical values. They correlate 0.998 so one of them can be discarded. 22082005 22082005 05092005 05092005 For some reason they are defined as strings, but they are clearly f8. How can I convert them to edate10 format?
This all part of the nightmare combining files from 32 waves of the British Social Attitudes survey. I’ve already got a cumulative file for 1983 to 1994, 2009 t0 2014 and am now working on 2000 to 2008 and am down to the last 8 variables with the same name but different formats.
Waves 1995 to 1999 are still to process, but I can probably cope with everything except dates.
John F Hall (Mr) [Retired academic survey researcher]
Email: johnfhall@... Website: www.surveyresearch.weebly.com SPSS start page: www.surveyresearch.weebly.com/1-survey-analysis-workshop
|
|
|
In reply to this post by John F Hall
With a bit of maths, the FM, no Marso, and treading carefully, I think I’ve solved it. In Data Editor, changed dateint from String to Numeric compute day = trunc (dateint/1000000). formats day (f2.0). desc var day. list var day /cases 5. compute month = trunc (dateint/10000) - (day*100). formats month (f2.0). desc var month. list var month /cases 5. missing values day month (99). freq day month. compute date = DATE.DMY(day,month,year). list date /cases 5. date 05.05.1995 02.05.1995 31.07.1995 12.07.1995 12.07.1995 var lab day 'Day of interview' /month 'Month of interview' /year 'Year of interview' /date 'Date of interview (ddmmyyyy)'. disp dic /var day month year date.
John F Hall (Mr) [Retired academic survey researcher] Email: [hidden email] Website: www.surveyresearch.weebly.com SPSS start page: www.surveyresearch.weebly.com/1-survey-analysis-workshop From: John F Hall [mailto:[hidden email]] Marso will kill me for asking, as he posted a reply on 23 Feb which may already have answered the question. I have a data set with dates entered as ddmmyyyy for two different variables with identical values. They correlate 0.998 so one of them can be discarded. 22082005 22082005 05092005 05092005 For some reason they are defined as strings, but they are clearly f8. How can I convert them to edate10 format? This all part of the nightmare combining files from 32 waves of the British Social Attitudes survey. I’ve already got a cumulative file for 1983 to 1994, 2009 t0 2014 and am now working on 2000 to 2008 and am down to the last 8 variables with the same name but different formats. Waves 1995 to 1999 are still to process, but I can probably cope with everything except dates. John F Hall (Mr) [Retired academic survey researcher] Email: [hidden email] Website: www.surveyresearch.weebly.com SPSS start page: www.surveyresearch.weebly.com/1-survey-analysis-workshop |
|
|
Slightly off topic to this particular thread, I would like to mention that Statistics 24 has introduced a new date/time format conforming to ISO 8601 timestamp format. YMDHMSw provides yyyy-mm-dd hh:mm and YMDHMSw.d provides yyyy-mm-dd hh:mm.ss.s. For example, 1990-06-20 08:03. For this format, the separator between the date and time parts can be a space, an uppercase T, or it can be omitted. Also, the separator is now optional for older date formats. Also, the extendedTransforms.py module, which is included in the Python Essentials in recent versions of Statistics and downloadable for earlier ones, provides functions strtodatetime and datetimetostr for working with other formats that do not fit any of the built-ins. Those functions accept pattern specifications for the date and time components. They can conveniently be used with the SPSSINC TRANS extension command. Here is an example. data list list /ydm(A8). begin data 22082005 22082005 end data. spssinc trans result=thedate /formula "extendedTransforms.strtodatetime(ydm, '%Y%d%m')". format thedate(edate10). On Thu, Apr 7, 2016 at 8:06 AM, John F Hall <[hidden email]> wrote:
|
Administrator
|
In reply to this post by John F Hall
Probably better off leaving it as a String and just parsing as you need.
 DATA LIST FREE / Str_d_m_Y (A8). BEGIN DATA 22082005 05092005 END DATA. STRING Middle_Step (A10). COMPUTE Middle_Step=CONCAT(CHAR.SUBSTR(Str_d_m_Y,3,2),'/',CHAR.SUBSTR(Str_d_m_Y,1,2),'/',CHAR.SUBSTR(Str_d_m_Y,5)). ALTER TYPE Middle_Step (ADATE). * A way which might make your head explode *. COMPUTE Realdate=NUMBER(CONCAT(CHAR.SUBSTR(Str_d_m_Y,3,2),'/',CHAR.SUBSTR(Str_d_m_Y,1,2),'/',CHAR.SUBSTR(Str_d_m_Y,5)),ADATE). FORMATS Realdate(ADATE). * Another way which will make your head explode *. COMPUTE Realdate2=DATE.MDY(NUMBER(CHAR.SUBSTR(Str_d_m_Y,3,2),F2),NUMBER(CHAR.SUBSTR(Str_d_m_Y,1,2),F2),NUMBER(CHAR.SUBSTR(Str_d_m_Y,5),F4)). FORMATS Realdate2(ADATE). LIST. Str_d_m_Y Middle_Step Realdate Realdate2 22082005 08/22/2005 08/22/2005 08/22/2005 05092005 09/05/2005 09/05/2005 09/05/2005
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?" |
Administrator
|
In reply to this post by John F Hall
John, why would you change dateint from string to numeric manually in the data editor when you have ALTER TYPE at your disposal?
DATA LIST free / dateint(a8). BEGIN DATA 05051995 02051995 31071995 12071995 12071995 07042016 END DATA. **************************. ALTER TYPE dateint (F8.0). **************************. COMPUTE day = TRUNC(dateint/1000000). COMPUTE month = TRUNC(dateint/10000) - day*100. COMPUTE year = dateint - day*1000000 - month*10000. COMPUTE date = DATE.DMY(day,month,year). FORMATS day month year (F4.0) / date (EDATE10). LIST /cases 10. OUTPUT: dateint day month year date 5051995 5 5 1995 05.05.1995 2051995 2 5 1995 02.05.1995 31071995 31 7 1995 31.07.1995 12071995 12 7 1995 12.07.1995 12071995 12 7 1995 12.07.1995 7042016 7 4 2016 07.04.2016
--
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/). |
|
|
Short answer is I needed to get it done, there was only one variable type to
change, and I knew how to do the calculations. I didn't get the super-guru solutions in time (thanks both you and David )but I doubt whether I'd have got my head round the syntax. My version is probably easier for newbies to understand and it will go into commentaries and introductions for users of the data. John F Hall (Mr) [Retired academic survey researcher] Email: [hidden email] Website: www.surveyresearch.weebly.com SPSS start page: www.surveyresearch.weebly.com/1-survey-analysis-workshop -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Bruce Weaver Sent: 07 April 2016 17:17 To: [hidden email] Subject: Re: Dates in A8 format John, why would you change dateint from string to numeric manually in the data editor when you have ALTER TYPE at your disposal? DATA LIST free / dateint(a8). BEGIN DATA 05051995 02051995 31071995 12071995 12071995 07042016 END DATA. **************************. ALTER TYPE dateint (F8.0). **************************. COMPUTE day = TRUNC(dateint/1000000). COMPUTE month = TRUNC(dateint/10000) - day*100. COMPUTE year = dateint - day*1000000 - month*10000. COMPUTE date = DATE.DMY(day,month,year). FORMATS day month year (F4.0) / date (EDATE10). LIST /cases 10. OUTPUT: dateint day month year date 5051995 5 5 1995 05.05.1995 2051995 2 5 1995 02.05.1995 31071995 31 7 1995 31.07.1995 12071995 12 7 1995 12.07.1995 12071995 12 7 1995 12.07.1995 7042016 7 4 2016 07.04.2016 John F Hall wrote > With a bit of maths, the FM, no Marso, and treading carefully, I think > I've solved it. > > In Data Editor, changed dateint from String to Numeric > > compute day = trunc (dateint/1000000). > formats day (f2.0). > desc var day. > list var day /cases 5. > > compute month = trunc (dateint/10000) - (day*100). > formats month (f2.0). > desc var month. > list var month /cases 5. > > missing values day month (99). > freq day month. > > compute date = DATE.DMY(day,month,year). > list date /cases 5. > > date > > 05.05.1995 > 02.05.1995 > 31.07.1995 > 12.07.1995 > 12.07.1995 > > var lab day 'Day of interview' > /month 'Month of interview' > /year 'Year of interview' > /date 'Date of interview (ddmmyyyy)'. > disp dic /var day month year date. > > > > Variable > Label > Level > Width > Format > > day > Day of interview > Scale > 10 > F2 > > month > Month of interview > Nominal > 10 > F2 > > year > Year of interview > Scale > 10 > F4 > > date > Date of interview (ddmmyyyy > Scale > 10 > EDATE10 > > John F Hall (Mr) > [Retired academic survey researcher] > > Email: <mailto: > johnfhall@ > > > johnfhall@ > > Website: <http://www.surveyresearch.weebly.com/> > www.surveyresearch.weebly.com > SPSS start page: > <http://surveyresearch.weebly.com/1-survey-analysis-workshop.html&g > t; www.surveyresearch.weebly.com/1-survey-analysis-workshop > > From: John F Hall [mailto: > johnfhall@ > ] > Sent: 07 April 2016 13:02 > To: ' > SPSSX-L@.UGA > ' < > SPSSX-L@.UGA > > > Subject: Dates in A8 format > > Marso will kill me for asking, as he posted a reply on 23 Feb which > may already have answered the question. > > I have a data set with dates entered as ddmmyyyy for two different > variables with identical values. They correlate 0.998 so one of them > can be discarded. > 22082005 22082005 > 05092005 05092005 > For some reason they are defined as strings, but they are clearly f8. > How can I convert them to edate10 format? > > This all part of the nightmare combining files from 32 waves of the > British Social Attitudes survey. I've already got a cumulative file > for 1983 to 1994, 2009 t0 2014 and am now working on 2000 to 2008 and > am down to the last 8 variables with the same name but different > formats. > > Waves 1995 to 1999 are still to process, but I can probably cope with > everything except dates. > > John F Hall (Mr) > [Retired academic survey researcher] > > Email: <mailto: > johnfhall@ > > > johnfhall@ > > Website: <http://www.surveyresearch.weebly.com/> > www.surveyresearch.weebly.com > SPSS start page: > <http://surveyresearch.weebly.com/1-survey-analysis-workshop.html&g > t; www.surveyresearch.weebly.com/1-survey-analysis-workshop > > > > > ===================== > 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. -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Dates-in-A8-format-tp5731886p5 731904.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 |
|
Administrator
|
John, my syntax is based very directly on what you posted. I used ALTER TYPE to convert dateint from string to numeric, and I added a line to compute Year because I didn't see that in your syntax. But apart from those two small changes (and rearranging the order a bit), it is your syntax! ;-)
But perhaps things are easier for newbies if you use NUMBER & CHAR.SUBSTR to extract the MM, DD and YYYY components. * Extract DD, MM and YYYY from dateint. COMPUTE DD = NUMBER( CHAR.SUBSTR(dateint,1,2) ,F2.0). COMPUTE MM = NUMBER( CHAR.SUBSTR(dateint,3,2) ,F2.0). COMPUTE YYYY = NUMBER( CHAR.SUBSTR(dateint,5,4), F4.0). COMPUTE Date = DATE.DMY(DD,MM,YYYY). FORMATS DD MM YYYY (F4.0) / date (EDATE10). LIST /cases 10.
--
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/). |
|
Administrator
|
**Note that this becomes virtually identical to my 3rd solution if one does it inline without computing the 3 extra variables ** In that regard I would use #scratch variables to avoid unnecessary leftovers.
COMPUTE #DD..., #MM..., #YYYY ..., DATE.DMY(#DD,#MM,#YYYY)... <quote author="Bruce Weaver"> John, my syntax is based very directly on what you posted. I used ALTER TYPE to convert dateint from string to numeric, and I added a line to compute Year because I didn't see that in your syntax. But apart from those two small changes (and rearranging the order a bit), it is your syntax! ;-) But perhaps things are easier for newbies if you use NUMBER & CHAR.SUBSTR to extract the MM, DD and YYYY components. * Extract DD, MM and YYYY from dateint. COMPUTE DD = NUMBER( CHAR.SUBSTR(dateint,1,2) ,F2.0). COMPUTE MM = NUMBER( CHAR.SUBSTR(dateint,3,2) ,F2.0). COMPUTE YYYY = NUMBER( CHAR.SUBSTR(dateint,5,4), F4.0). COMPUTE Date = DATE.DMY(DD,MM,YYYY). FORMATS DD MM YYYY (F4.0) / date (EDATE10). LIST /cases 10.
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?" |
| Free forum by Nabble | Edit this page |