An unexpected date problem

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

An unexpected date problem

Ron0z
I have a date that SPSS shows as 22nd May at one point then shows it incorrectly later, in fact, not a date at all.

I don’t really have much of a clue with dates. Recently I’ve been forced to try. The data I use has fields held as a string (eg. YYYYMMDD) with numeric characters that represent the date.  It is a text field (A8) and is not a date format. It serves quite well.  I now have to use an additional data set, a spreadsheet, that has a date field and I need to match the data sets and compare dates. In order to do this I opted to convert my string into a date format. I didn’t have a clue, but found on the SPSS menu Transform > Date and Time Wizard and using that facility the following was created by SPSS:

COMPUTE CenDt=date.dmy(number(substr(ltrim(e489),7,2),f2.0), number(substr(ltrim(e489),5,2),f2.0), number(substr(ltrim(e489),1,4),f4.0)).
VARIABLE LEVEL  CenDt (SCALE).
FORMATS CenDt (DATE11).
EXECUTE.

It seemed to work very nicely.  Here’s a demo for a date that is the 22nd day of May, 2017. I’ve inserted the list output at the relevant points.  Later, and this is the problem, I included a SAVE TRANSLATE  command. The output follows and you will see the date format has mysteriously changed.


SELECT IF (E313 EQ 'CIT217070').
LIST VARS = E313 E489.

e313       e489
 
CIT217070  20170522

* Date and Time Wizard: CenDt. For comparing our census date with their payment date.
COMPUTE CenDt=date.dmy(number(substr(ltrim(e489),7,2),f2.0), number(substr(ltrim(e489),5,2),f2.0), number(substr(ltrim(e489),1,4),f4.0)).
VARIABLE LEVEL  CenDt (SCALE).
FORMATS CenDt (DATE11).
EXECUTE.

LIST VARS = E313 E489 CenDt.

e313       e489           CenDt

CIT217070  20170522 22-MAY-2017
 
 

save translate
   /output =  'C:\hepcat\eCAFdata\VLL_eCAF.CSV'
   /type = CSV
   /fieldnames
   /cells = labels
   /replace
   /keep = e313 e307 e354 e489 e490 CenDt.

e313 e307 e354 e489 e490 CenDt
CIT217070 PUA60913 FIRE159 20170522 510 5/22/2017


Note the different date value of CenDt.  
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: An unexpected date problem

Bruce Weaver
Administrator
I think you are suggesting that 22-MAY-2017 and 5/22/2017 represent two different values.  They do not:  They represent the same value, but have different display formats (DATE11 and ADATE10).  I suggest you spend a little time reading about date and time variables in the Universals section of the FM.  Here's a link:

https://www.ibm.com/support/knowledgecenter/SSLVMB_20.0.0/com.ibm.spss.statistics.help/syn_date_and_time_date_time_formats.htm

HTH.


Ron0z wrote
I have a date that SPSS shows as 22nd May at one point then shows it incorrectly later, in fact, not a date at all.

I don’t really have much of a clue with dates. Recently I’ve been forced to try. The data I use has fields held as a string (eg. YYYYMMDD) with numeric characters that represent the date.  It is a text field (A8) and is not a date format. It serves quite well.  I now have to use an additional data set, a spreadsheet, that has a date field and I need to match the data sets and compare dates. In order to do this I opted to convert my string into a date format. I didn’t have a clue, but found on the SPSS menu Transform > Date and Time Wizard and using that facility the following was created by SPSS:

COMPUTE CenDt=date.dmy(number(substr(ltrim(e489),7,2),f2.0), number(substr(ltrim(e489),5,2),f2.0), number(substr(ltrim(e489),1,4),f4.0)).
VARIABLE LEVEL  CenDt (SCALE).
FORMATS CenDt (DATE11).
EXECUTE.

It seemed to work very nicely.  Here’s a demo for a date that is the 22nd day of May, 2017. I’ve inserted the list output at the relevant points.  Later, and this is the problem, I included a SAVE TRANSLATE  command. The output follows and you will see the date format has mysteriously changed.


SELECT IF (E313 EQ 'CIT217070').
LIST VARS = E313 E489.

e313       e489
 
CIT217070  20170522

* Date and Time Wizard: CenDt. For comparing our census date with their payment date.
COMPUTE CenDt=date.dmy(number(substr(ltrim(e489),7,2),f2.0), number(substr(ltrim(e489),5,2),f2.0), number(substr(ltrim(e489),1,4),f4.0)).
VARIABLE LEVEL  CenDt (SCALE).
FORMATS CenDt (DATE11).
EXECUTE.

LIST VARS = E313 E489 CenDt.

e313       e489           CenDt

CIT217070  20170522 22-MAY-2017
 
 

save translate
   /output =  'C:\hepcat\eCAFdata\VLL_eCAF.CSV'
   /type = CSV
   /fieldnames
   /cells = labels
   /replace
   /keep = e313 e307 e354 e489 e490 CenDt.

e313 e307 e354 e489 e490 CenDt
CIT217070 PUA60913 FIRE159 20170522 510 5/22/2017


Note the different date value of CenDt.
--
Bruce Weaver
bweaver@lakeheadu.ca
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.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: An unexpected date problem

Ron0z
Thank you for the link.  Though, I would expect that once a format was set it would stay put until a new format command was encountered. It seems strange to set format only to have the system change it before the next command is run.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: An unexpected date problem

Ron0z
I’ve been experimenting.  It doesn’t seem to be a format issue in any way that format can be controlled by the user. Unless of course I’m missing something as I’ve never been good with formats.

It seems as though save translate has a predefined format built into it with respect to date, and it doesn’t seem to matter what date format might be input into the command.

A experimented with a variety of date formats and while each one presented different formats with LIST they were always output in the same date format in the save translate output.  I also noticed that depending upon whether type XLS or CSV is selected the format will change to what seems a hard coded internal arrangement. I can’t see any way of controlling that which seems a pity.

For example, when I set the format for each of the formats shown below the date in LIST was displayed as shown in brackets: DATE11 (16-MAY-2017) ADATE11 (05/16/2017) EDATE11 (16.05.2017) JDATE11 (2017136) SDATE11 (2017/05/16), but each time I viewed the Excel output that was created by the following code

save translate
   /output =  'C:\hepcat\eCAFdata\VLL_eCAF.xls'
   /type = XLS
   /fieldnames
   /replace
   /keep = e313 e307 e354 e489 e490 CenDt.

the XLS file always displayed the date as 16-May-2017. It was ignoring my format command.


Then I edited the above code to that shown below (just another experiment)
 
save translate
   /output =  'C:\hepcat\eCAFdata\VLL_eCAF.CSV'
   /type = CSV
   /fieldnames
   /replace
   /keep = e313 e307 e354 e489 e490 CenDt.

and ran it for the same format examples I had tried previously, and on each occasion the CSV output displayed the date as 5/16/2017.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: An unexpected date problem

Rich Ulrich

If you want to rearrange a text-string version of a date to match another text-string,

you can do it by rearranging the characters.  That's a matter of using character functions.

If there is an text-string version that you can achieve with the Date-formats, you /could/

convert back to another text-string, using the appropriate Date-format. Then export that.

What you export is "values" - If Excel picks up the number of decimals in floating-point

numbers (does it?), that is a special feature which would never convert anything to text.


Re-read what Bruce wrote.


When to convert your text to a "date", then you have created a floating-point number.

The "date format" that you tell SPSS is telling SPSS how to write the date with LIST or

other displays.  It has /nothing to do/  with the internal floating-point to be sent to Excel.

When you sent it to Excel, it is a floating point number that Excel will format however

Excel formats its dates. It is /not/  a text string.


--

Rich Ulrich


From: SPSSX(r) Discussion <[hidden email]> on behalf of Ron0z <[hidden email]>
Sent: Tuesday, June 6, 2017 1:33:18 AM
To: [hidden email]
Subject: Re: An unexpected date problem
 
I’ve been experimenting.  It doesn’t seem to be a format issue in any way
that format can be controlled by the user. Unless of course I’m missing
something as I’ve never been good with formats.

It seems as though save translate has a predefined format built into it with
respect to date, and it doesn’t seem to matter what date format might be
input into the command.

A experimented with a variety of date formats and while each one presented
different formats with LIST they were always output in the same date format
in the save translate output.  I also noticed that depending upon whether
type XLS or CSV is selected the format will change to what seems a hard
coded internal arrangement. I can’t see any way of controlling that which
seems a pity.

For example, when I set the format for each of the formats shown below the
date in LIST was displayed as shown in brackets: DATE11 (16-MAY-2017)
ADATE11 (05/16/2017) EDATE11 (16.05.2017) JDATE11 (2017136) SDATE11
(2017/05/16), but each time I viewed the Excel output that was created by
the following code

save translate
   /output =  'C:\hepcat\eCAFdata\VLL_eCAF.xls'
   /type = XLS
   /fieldnames
   /replace
   /keep = e313 e307 e354 e489 e490 CenDt.

the XLS file always displayed the date as 16-May-2017. It was ignoring my
format command.


Then I edited the above code to that shown below (just another experiment)
 
save translate
   /output =  'C:\hepcat\eCAFdata\VLL_eCAF.CSV'
   /type = CSV
   /fieldnames
   /replace
   /keep = e313 e307 e354 e489 e490 CenDt.

and ran it for the same format examples I had tried previously, and on each
occasion the CSV output displayed the date as 5/16/2017.




--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/An-unexpected-date-problem-tp5734316p5734319.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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: An unexpected date problem

Bruce Weaver
Administrator
In reply to this post by Ron0z
After your SAVE TRANSLATE command, import the data from your CSV file, then issue the following command:

FORMATS CenDt(DATE11).

What do you see after that?  

Ron0z wrote
I’ve been experimenting.  It doesn’t seem to be a format issue in any way that format can be controlled by the user. Unless of course I’m missing something as I’ve never been good with formats.

It seems as though save translate has a predefined format built into it with respect to date, and it doesn’t seem to matter what date format might be input into the command.

A experimented with a variety of date formats and while each one presented different formats with LIST they were always output in the same date format in the save translate output.  I also noticed that depending upon whether type XLS or CSV is selected the format will change to what seems a hard coded internal arrangement. I can’t see any way of controlling that which seems a pity.

For example, when I set the format for each of the formats shown below the date in LIST was displayed as shown in brackets: DATE11 (16-MAY-2017) ADATE11 (05/16/2017) EDATE11 (16.05.2017) JDATE11 (2017136) SDATE11 (2017/05/16), but each time I viewed the Excel output that was created by the following code

save translate
   /output =  'C:\hepcat\eCAFdata\VLL_eCAF.xls'
   /type = XLS
   /fieldnames
   /replace
   /keep = e313 e307 e354 e489 e490 CenDt.

the XLS file always displayed the date as 16-May-2017. It was ignoring my format command.


Then I edited the above code to that shown below (just another experiment)
 
save translate
   /output =  'C:\hepcat\eCAFdata\VLL_eCAF.CSV'
   /type = CSV
   /fieldnames
   /replace
   /keep = e313 e307 e354 e489 e490 CenDt.

and ran it for the same format examples I had tried previously, and on each occasion the CSV output displayed the date as 5/16/2017.
--
Bruce Weaver
bweaver@lakeheadu.ca
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.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: An unexpected date problem

Ron0z
When this data is written to a CSV file, which is opened using Excel, the value 5/11/2007 was seen to have been written. This started in SPSS as a date in May, and seems like the US format for dates. Entering a formula in Excel that will add one day the value becomes 6/11/2017.  The change was confirmed by changing the display format in Excel and the cells showed November.

Then, interestingly, though perhaps not unexpectedly, when the values are written to the XLS file, when opened the value 11-May-2017 is shown, and when a formula to add one day is used the new value is 12-May-2017.

So, what we have here is an SPSS date variable having a value that changes depending upon whether it’s written to a CSV file or a XLS file. The issue would seem to be related to the way save translate writes CSV files.

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: An unexpected date problem

Bruce Weaver
Administrator
It is sometimes wise, I think, to store dates as 3 separate variables (day of the month, month and year) when moving them between programs that store date variables differently.  In your original .SAV file, you can compute those 3 variables using the appropriate XDATE functions.  E.g.,

COMPUTE CenDtYear = XDATE.YEAR(CenDt).
COMPUTE CenDtMonth = XDATE.MONTH(CenDt).
COMPUTE CenDtDay = XDATE.MDAY(CenDt).
FORMATS CenDtYear (F4.0) / CenDtMonth CenDtDay (F2.0).
EXECUTE.

Then include those 3 variables when you export to CSV.  Whoever imports the CSV file using whatever program should be able to combine them to form a proper date variable if it is needed.  E.g., in SPSS you would use a function like DATE.DMY(D,M,Y).  In Excel, use the DATE function:  =DATE(Y,M,D).  This approach pretty much eliminates any confusion of day with month.  

HTH.

Ron0z wrote
When this data is written to a CSV file, which is opened using Excel, the value 5/11/2007 was seen to have been written. This started in SPSS as a date in May, and seems like the US format for dates. Entering a formula in Excel that will add one day the value becomes 6/11/2017.  The change was confirmed by changing the display format in Excel and the cells showed November.

Then, interestingly, though perhaps not unexpectedly, when the values are written to the XLS file, when opened the value 11-May-2017 is shown, and when a formula to add one day is used the new value is 12-May-2017.

So, what we have here is an SPSS date variable having a value that changes depending upon whether it’s written to a CSV file or a XLS file. The issue would seem to be related to the way save translate writes CSV files.
--
Bruce Weaver
bweaver@lakeheadu.ca
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.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: An unexpected date problem

Ron0z
Good idea.

In this exercise, I was writing the CSV file so that I might distribute it to staff, as a report.  It has been a useful experience. If I have to work with dates in future (in combination with save translate) I might consider turning dates into a string field first.

Thank you to everyone who took the time to hear me out.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: An unexpected date problem

Bruce Weaver
Administrator
In reply to this post by Ron0z
While cleaning up my hotmail inbox, I noticed that Rick Oliver Cc-ed me on something he posted to the list.  For some reason, Rick's post is not (yet) appearing in the Nabble archive.  Therefore, I'll paste his comments here, in case the OP (RonOz) has not seen them.  (I hope you don't mind, Rick.)

---- Start of Rick's comments ----

Dates are exported to Excel in dd-mmm-yyyy format, regardless of the display format in SPSS. AFAIK, there is no way to change that behavior – but like SPSS, it’s just a display format that can be changed in Excel.
 
For CSV format, by default dates are exported in mm/dd/yyyy format, regardless of the display format in SPSS – but you can preserve the display format by including /TEXTOPTIONS FORMAT=VARIABLE in the SAVE TRANSLATE command.

---- End of Rick's comments ----

Ron0z wrote
When this data is written to a CSV file, which is opened using Excel, the value 5/11/2007 was seen to have been written. This started in SPSS as a date in May, and seems like the US format for dates. Entering a formula in Excel that will add one day the value becomes 6/11/2017.  The change was confirmed by changing the display format in Excel and the cells showed November.

Then, interestingly, though perhaps not unexpectedly, when the values are written to the XLS file, when opened the value 11-May-2017 is shown, and when a formula to add one day is used the new value is 12-May-2017.

So, what we have here is an SPSS date variable having a value that changes depending upon whether it’s written to a CSV file or a XLS file. The issue would seem to be related to the way save translate writes CSV files.
--
Bruce Weaver
bweaver@lakeheadu.ca
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.
Loading...