odd string reading behavior after patch V19
Locked
odd string reading behavior after patch V19
 
|
I hope someone knows what to do about this. I'm using V19 and I have a program that's reading an Excel file and using substrings to code variables. So I get 300.4 and code it as Dysthymia. SPSS crashed frequently so a couple of days ago, I installed the patch #2 to see if it would help. When SPSS reads the Excel file, I am now getting 300.39 instead of 300.4, even though SPSS is correctly reading it as a string variable. Needless to say, many of the variables are now being coded incorrectly. I've had this program and the Excel file for years and everything has worked well in the past. I just happened to notice it today, but I do have a printout of the data from right before the patch and everything was OK then. If anybody has any
ideas or fixes, I'd be grateful!
Cathie |
|
I suggest you check Excel file to see if 300.4 is not there as 300.39…or use Round to round up/down your Excel variable to what SPSS understands. Max. From: Cathie Atkinson-2 [via SPSSX Discussion] [mailto:[hidden email]] I hope someone knows what to do about this. I'm using V19 and I have a program that's reading an Excel file and using substrings to code variables. So I get 300.4 and code it as Dysthymia. SPSS crashed frequently so a couple of days ago, I installed the patch #2 to see if it would help. When SPSS reads the Excel file, I am now getting 300.39 instead of 300.4, even though SPSS is correctly reading it as a string variable. Needless to say, many of the variables are now being coded incorrectly. I've had this program and the Excel file for years and everything has worked well in the past. I just happened to notice it today, but I do have a printout of the data from right before the patch and everything was OK then. If anybody has any ideas or fixes, I'd be grateful! Cathie |
Re: odd string reading behavior after patch V19
Administrator
|
In reply to this post by Cathie Atkinson-2
As Max suggested, I would suggest you take a gander at your Excel formats and if you are doing additional mods in SPSS post your syntax!!! If it is reading it as 300.39 as a string then it is likely on the Excel end.
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: odd string reading behavior after patch V19
|
|
In reply to this post by Cathie Atkinson-2
________________________________
>From: Cathie Atkinson <[hidden email]> >To: [hidden email] >Sent: Wednesday, September 12, 2012 7:50 PM >Subject: [SPSSX-L] odd string reading behavior after patch V19 > > >I hope someone knows what to do about this. I'm using V19 and I have a program that's reading an Excel file and using substrings to code variables. So I get 300.4 and code it as Dysthymia. SPSS crashed frequently so a couple of days ago, I installed the patch #2 to see if it would help. When SPSS reads the Excel file, I am now getting 300.39 instead of 300.4, even though SPSS is correctly reading it as a string variable. Needless to say, many of the variables are now being coded incorrectly. I've had this program and the Excel file for years and everything has worked well in the past. I just happened to notice it today, but I do have a printout of the data from right before the patch and everything was OK then. If anybody has any ideas or fixes, I'd be grateful! > >Cathie > I always call this "bossy behavior" of Excel. You could simply change your regional settings (in the control panel in windows) to change the decimal separator to a comma. Another, better, option is to prefix your string values with a single quote: COMPUTE icd_code = CONCAT("'", icd_code). That way, excel treats the values as strings. So for instance any leading zeroes (if present) will not be omitted by annoying excel. By the way, if you're using xlsx files (as opposed to xls), it is highly recommended to install all possible updates (start with the fixpack(s)). I noticed that there were many bug fixes for xlsx files. Not sure if OpenOffice/LibreOffice/GNumeric are also as annoying as MS Excel, but you could also use one of these alternaitves. Regards, Albert-Jan ===================== 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: odd string reading behavior after patch V19
|
|
In reply to this post by David Marso
thanks, I had already checked Excel - everything was fine there and the file had not changed at all from when it was working fine. I was able to get around it by using Type = ODBC instead of Excel and it reads correctly in this case. Unfortunately, I have to go through and rewrite several programs, but at least this works. Thought I'd post this in case anyone else runs into the problem, but the nerd in me wants to know why!
From: David Marso <[hidden email]> To: [hidden email] Sent: Wednesday, September 12, 2012 5:59 PM Subject: Re: odd string reading behavior after patch V19 As Max suggested, I would suggest you take a gander at your Excel formats and if you are doing additional mods in SPSS post your syntax!!! If it is reading it as 300.39 as a string then it is likely on the Excel end. Cathie Atkinson-2 wrote > > I hope someone knows what to do about this. I'm using V19 and I have a > program that's reading an Excel file and using substrings to code > variables. So I get 300.4 and code it as Dysthymia. SPSS crashed > frequently so a couple of days ago, I installed the patch #2 to see if it > would help. When SPSS reads the Excel file, I am now getting 300.39 > instead of 300.4, even though SPSS is correctly reading it as a string > variable. Needless to say, many of the variables are now being coded > incorrectly. I've had this program and the Excel file for years and > everything has worked well in the past. I just happened to notice it > today, but I do have a printout of the data from right before the patch > and everything was OK then. If anybody has any ideas or fixes, I'd be > grateful! >  > Cathie > ----- Please reply to the list and not to my personal email. Those desiring my consulting or training services please feel free to email me. -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/odd-string-reading-behavior-after-patch-V19-tp5715055p5715057.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 -- BEGIN-ANTISPAM-VOTING-LINKS ------------------------------------------------------ Teach CanIt if this mail (ID 1449965471) is spam: Spam: https://antispam.osu.edu/b.php?i=1449965471&m=1fd77dffed9a&c=s Not spam: https://antispam.osu.edu/b.php?i=1449965471&m=1fd77dffed9a&c=n Forget vote: https://antispam.osu.edu/b.php?i=1449965471&m=1fd77dffed9a&c=f ------------------------------------------------------ END-ANTISPAM-VOTING-LINKS |
Re: odd string reading behavior after patch V19
Administrator
|
Cathie, thanks for posting your solution. Too often, people don't do that, and then the rest of us (at least those who have been thinking about the problem) are left hanging. And yes, it would be nice to know why!
--
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: odd string reading behavior after patch V19
|
|
In reply to this post by Cathie Atkinson-2
Somewhere in that column of numbers in
your Excel spreadsheet is a something that is not a number.
When reading the Excel file with GET DATA /TYPE=XLS, all the values are read before determining the data type, and if the the column contains a mix of numbers and non-numbers, it's read as string to preserve all the data (rather than setting the non-numbers to system-missing). When reading the data with GET DATA /TYPE=ODBC, you have to explicitly set the data type. It is not set dynamically. Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: Cathie Atkinson <[hidden email]> To: [hidden email], Date: 09/13/2012 09:50 AM Subject: Re: odd string reading behavior after patch V19 Sent by: "SPSSX(r) Discussion" <[hidden email]> thanks, I had already checked Excel - everything was fine there and the file had not changed at all from when it was working fine. I was able to get around it by using Type = ODBC instead of Excel and it reads correctly in this case. Unfortunately, I have to go through and rewrite several programs, but at least this works. Thought I'd post this in case anyone else runs into the problem, but the nerd in me wants to know why! From: David Marso <[hidden email]> To: [hidden email] Sent: Wednesday, September 12, 2012 5:59 PM Subject: Re: odd string reading behavior after patch V19 As Max suggested, I would suggest you take a gander at your Excel formats and if you are doing additional mods in SPSS post your syntax!!! If it is reading it as 300.39 as a string then it is likely on the Excel end. Cathie Atkinson-2 wrote > > I hope someone knows what to do about this. I'm using V19 and I have a > program that's reading an Excel file and using substrings to code > variables. So I get 300.4 and code it as Dysthymia. SPSS crashed > frequently so a couple of days ago, I installed the patch #2 to see if it > would help. When SPSS reads the Excel file, I am now getting 300.39 > instead of 300.4, even though SPSS is correctly reading it as a string > variable. Needless to say, many of the variables are now being coded > incorrectly. I've had this program and the Excel file for years and > everything has worked well in the past. I just happened to notice it > today, but I do have a printout of the data from right before the patch > and everything was OK then. If anybody has any ideas or fixes, I'd be > grateful! >  > Cathie > ----- Please reply to the list and not to my personal email. Those desiring my consulting or training services please feel free to email me. -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/odd-string-reading-behavior-after-patch-V19-tp5715055p5715057.html Sent from the SPSSX Discussion mailing list archive at Nabble.com. ===================== 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 -- BEGIN-ANTISPAM-VOTING-LINKS ------------------------------------------------------ Teach CanIt if this mail (ID 1449965471) is spam: Spam: https://antispam.osu.edu/b.php?i=1449965471&m=1fd77dffed9a&c=s Not spam: https://antispam.osu.edu/b.php?i=1449965471&m=1fd77dffed9a&c=n Forget vote: https://antispam.osu.edu/b.php?i=1449965471&m=1fd77dffed9a&c=f ------------------------------------------------------ END-ANTISPAM-VOTING-LINKS |
Re: odd string reading behavior after patch V19
|
|
In reply to this post by Cathie Atkinson-2
Oops. I was wrong about GET DATA /TYPE=ODBC.
You cannot specify the data type. I think it's determined by the ODBC driver.
Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] Phone: 312.893.4922 | T/L: 206-4922 From: Rick Oliver/Chicago/IBM To: Cathie Atkinson <[hidden email]>, Cc: [hidden email] Date: 09/13/2012 10:16 AM Subject: Re: odd string reading behavior after patch V19 Somewhere in that column of numbers in your Excel spreadsheet is a something that is not a number. When reading the Excel file with GET DATA /TYPE=XLS, all the values are read before determining the data type, and if the the column contains a mix of numbers and non-numbers, it's read as string to preserve all the data (rather than setting the non-numbers to system-missing). When reading the data with GET DATA /TYPE=ODBC, you have to explicitly set the data type. It is not set dynamically. Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: Cathie Atkinson <[hidden email]> To: [hidden email], Date: 09/13/2012 09:50 AM Subject: Re: odd string reading behavior after patch V19 Sent by: "SPSSX(r) Discussion" <[hidden email]> thanks, I had already checked Excel - everything was fine there and the file had not changed at all from when it was working fine. I was able to get around it by using Type = ODBC instead of Excel and it reads correctly in this case. Unfortunately, I have to go through and rewrite several programs, but at least this works. Thought I'd post this in case anyone else runs into the problem, but the nerd in me wants to know why! From: David Marso <[hidden email]> To: [hidden email] Sent: Wednesday, September 12, 2012 5:59 PM Subject: Re: odd string reading behavior after patch V19 As Max suggested, I would suggest you take a gander at your Excel formats and if you are doing additional mods in SPSS post your syntax!!! If it is reading it as 300.39 as a string then it is likely on the Excel end. Cathie Atkinson-2 wrote > > I hope someone knows what to do about this. I'm using V19 and I have a > program that's reading an Excel file and using substrings to code > variables. So I get 300.4 and code it as Dysthymia. SPSS crashed > frequently so a couple of days ago, I installed the patch #2 to see if it > would help. When SPSS reads the Excel file, I am now getting 300.39 > instead of 300.4, even though SPSS is correctly reading it as a string > variable. Needless to say, many of the variables are now being coded > incorrectly. I've had this program and the Excel file for years and > everything has worked well in the past. I just happened to notice it > today, but I do have a printout of the data from right before the patch > and everything was OK then. If anybody has any ideas or fixes, I'd be > grateful! >  > Cathie > ----- Please reply to the list and not to my personal email. Those desiring my consulting or training services please feel free to email me. -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/odd-string-reading-behavior-after-patch-V19-tp5715055p5715057.html Sent from the SPSSX Discussion mailing list archive at Nabble.com. ===================== 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 -- BEGIN-ANTISPAM-VOTING-LINKS ------------------------------------------------------ Teach CanIt if this mail (ID 1449965471) is spam: Spam: https://antispam.osu.edu/b.php?i=1449965471&m=1fd77dffed9a&c=s Not spam: https://antispam.osu.edu/b.php?i=1449965471&m=1fd77dffed9a&c=n Forget vote: https://antispam.osu.edu/b.php?i=1449965471&m=1fd77dffed9a&c=f ------------------------------------------------------ END-ANTISPAM-VOTING-LINKS |
Re: odd string reading behavior after patch V19
|
|
In reply to this post by Rick Oliver-3
Hi Rick
Yes, I specifically put an x in the column because I want it to be a string. What it's doing after the patch is reading it as a string (correctly) but altering the numbers so that 300.4 in Excel becomes 300.39 in spss. Using the database wizard, the ODBC solution works for the most part. I still need to use alter type to change some things from string to number and vice versa but it's at least reading the actual value as what it is (300.4).
Cathie
From: Rick Oliver <[hidden email]> To: [hidden email] Sent: Thursday, September 13, 2012 11:16 AM Subject: Re: odd string reading behavior after patch V19 Somewhere in that column of numbers in your Excel spreadsheet is a something that is not a number. When reading the Excel file with GET DATA /TYPE=XLS, all the values are read before determining the data type, and if the the column contains a mix of numbers and non-numbers, it's read as string to preserve all the data (rather than setting the non-numbers to system-missing). When reading the data with GET DATA /TYPE=ODBC, you have to explicitly set the data type. It is not set dynamically. Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: Cathie Atkinson <[hidden email]> To: [hidden email], Date: 09/13/2012 09:50 AM Subject: Re: odd string reading behavior after patch V19 Sent by: "SPSSX(r) Discussion" <[hidden email]> thanks, I had already checked Excel - everything was fine there and the file had not changed at all from when it was working fine. I was able to get around it by using Type = ODBC instead of Excel and it reads correctly in this case. Unfortunately, I have to go through and rewrite several programs, but at least this works. Thought I'd post this in case anyone else runs into the problem, but the nerd in me wants to know why! From: David Marso <[hidden email]> To: [hidden email] Sent: Wednesday, September 12, 2012 5:59 PM Subject: Re: odd string reading behavior after patch V19 As Max suggested, I would suggest you take a gander at your Excel formats and if you are doing additional mods in SPSS post your syntax!!! If it is reading it as 300.39 as a string then it is likely on the Excel end. Cathie Atkinson-2 wrote > > I hope someone knows what to do about this. I'm using V19 and I have a > program that's reading an Excel file and using substrings to code > variables. So I get 300.4 and code it as Dysthymia. SPSS crashed > frequently so a couple of days ago, I installed the patch #2 to see if it > would help. When SPSS reads the Excel file, I am now getting 300.39 > instead of 300.4, even though SPSS is correctly reading it as a string > variable. Needless to say, many of the variables are now being coded > incorrectly. I've had this program and the Excel file for years and > everything has worked well in the past. I just happened to notice it > today, but I do have a printout of the data from right before the patch > and everything was OK then. If anybody has any ideas or fixes, I'd be > grateful! >  > Cathie > ----- Please reply to the list and not to my personal email. Those desiring my consulting or training services please feel free to email me. -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/odd-string-reading-behavior-after-patch-V19-tp5715055p5715057.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 -- BEGIN-ANTISPAM-VOTING-LINKS ------------------------------------------------------ Teach CanIt if this mail (ID 1449965471) is spam: Spam: about:blank Not spam: about:blank Forget vote: about:blank ------------------------------------------------------ END-ANTISPAM-VOTING-LINKS |
Re: odd string reading behavior after patch V19
|
Administrator
|
If you open the file in Excel and format the column in question to display 2 decimal points does it appear as 300.40 or as 300.39? My bet is the latter!<
quote author="Cathie Atkinson-2"> Hi Rick Yes, I specifically put an x in the column because I want it to be a string. What it's doing after the patch is reading it as a string (correctly) but altering the numbers so that 300.4 in Excel becomes 300.39 in spss. Using the database wizard, the ODBC solution works for the most part. I still need to use alter type to change some things from string to number and vice versa but it's at least reading the actual value as what it is (300.4). Cathie From: Rick Oliver <[hidden email]> To: [hidden email] Sent: Thursday, September 13, 2012 11:16 AM Subject: Re: odd string reading behavior after patch V19 Somewhere in that column of numbers in your Excel spreadsheet is a something that is not a number. When reading the Excel file with GET DATA /TYPE=XLS, all the values are read before determining the data type, and if the the column contains a mix of numbers and non-numbers, it's read as string to preserve all the data (rather than setting the non-numbers to system-missing). When reading the data with GET DATA /TYPE=ODBC, you have to explicitly set the data type. It is not set dynamically. Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: Cathie Atkinson <[hidden email]> To: [hidden email], Date: 09/13/2012 09:50 AM Subject: Re: odd string reading behavior after patch V19 Sent by: "SPSSX(r) Discussion" <[hidden email]> thanks, I had already checked Excel - everything was fine there and the file had not changed at all from when it was working fine. I was able to get around it by using Type = ODBC instead of Excel and it reads correctly in this case. Unfortunately, I have to go through and rewrite several programs, but at least this works. Thought I'd post this in case anyone else runs into the problem, but the nerd in me wants to know why! From: David Marso <[hidden email]> To: [hidden email] Sent: Wednesday, September 12, 2012 5:59 PM Subject: Re: odd string reading behavior after patch V19 As Max suggested, I would suggest you take a gander at your Excel formats and if you are doing additional mods in SPSS post your syntax!!! If it is reading it as 300.39 as a string then it is likely on the Excel end. Cathie Atkinson-2 wrote > > I hope someone knows what to do about this. I'm using V19 and I have a > program that's reading an Excel file and using substrings to code > variables. So I get 300.4 and code it as Dysthymia. SPSS crashed > frequently so a couple of days ago, I installed the patch #2 to see if it > would help. When SPSS reads the Excel file, I am now getting 300.39 > instead of 300.4, even though SPSS is correctly reading it as a string > variable. Needless to say, many of the variables are now being coded > incorrectly. I've had this program and the Excel file for years and > everything has worked well in the past. I just happened to notice it > today, but I do have a printout of the data from right before the patch > and everything was OK then. If anybody has any ideas or fixes, I'd be > grateful! >  > Cathie > ----- Please reply to the list and not to my personal email. Those desiring my consulting or training services please feel free to email me. -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/odd-string-reading-behavior-after-patch-V19-tp5715055p5715057.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 -- BEGIN-ANTISPAM-VOTING-LINKS ------------------------------------------------------ Teach CanIt if this mail (ID 1449965471) is spam: Spam: about:blank Not spam: about:blank Forget vote: about:blank ------------------------------------------------------ END-ANTISPAM-VOTING-LINKS Spam Not spam Forget previous vote
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?" |
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |