Truncation of values after Decimal when exporting to Excel
Locked
Truncation of values after Decimal when exporting to Excel
 
|
I'm currently using SPSS 18, and I'm running some syntax for a set of
reports which start by aggregating data which was collected at the person level to a unit level (School or State). The aggregation needs to give a report of the % of certain respondent types, and in order to give a valid percent, I use the PIN (Percent in) for each categorical value of the demographic variable. I then take the saved file, and save it as an excel file. When I do this, it's truncating the values down to only one digit after the decimal place. It also is not rounding correctly, as some values which should be appropriatly rounded up, are being roudned down. For instance, a value of 6.547 becomes 6.5 instead of 6.6 I have tried adjusting the fuzz bits used, but this has not changed the values I get. I can not find any syntax that allows me to adjust how SPSS exports to excel. I've also checked the aggregate SPSS files, and the values are saved correctly there. If possible, I would prefer that SPSS export all decimal places to excel. I know this is possible as I have files run from similar syntax by other people which do have all the decimal places. ===================== 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: Truncation of values after Decimal when exporting to Excel
|
|
Matthew,
6.5 is the correct rounded value of 6.547. Would 6.6 be the correct rounded value of 6.50001? It's not clear how you are doing the export to excel but once you do the aggregation operation, look at the data file and I'll be you will see that the format is f5.1. Look at the documentation for aggregation command in the syntax reference. You are getting what you are supposed to. So change the format! That point aside, the other place to look is at how many decimal digits excel is set to display. The solution to your problem is in spss rather than excel. Gene Maguin -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Matthew J Poes Sent: Monday, December 12, 2011 11:19 AM To: [hidden email] Subject: Truncation of values after Decimal when exporting to Excel I'm currently using SPSS 18, and I'm running some syntax for a set of reports which start by aggregating data which was collected at the person level to a unit level (School or State). The aggregation needs to give a report of the % of certain respondent types, and in order to give a valid percent, I use the PIN (Percent in) for each categorical value of the demographic variable. I then take the saved file, and save it as an excel file. When I do this, it's truncating the values down to only one digit after the decimal place. It also is not rounding correctly, as some values which should be appropriatly rounded up, are being roudned down. For instance, a value of 6.547 becomes 6.5 instead of 6.6 I have tried adjusting the fuzz bits used, but this has not changed the values I get. I can not find any syntax that allows me to adjust how SPSS exports to excel. I've also checked the aggregate SPSS files, and the values are saved correctly there. If possible, I would prefer that SPSS export all decimal places to excel. I know this is possible as I have files run from similar syntax by other people which do have all the decimal places. ===================== 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: Truncation of values after Decimal when exporting to Excel
|
|
Thanks Gene,
I will take a look to see, but where I don't understand this is why previous syntax which did not change the format did not have this problem. As I said, other people here who had previously created the same syntax would get all decimal places when exporting to Excel. Ok so I'm a bit confused by this rounding issue. Online rounding calculators agree with you, but even their explanations would not. That or I'm truly confused. My understanding was that if the number after the point you are rounding to is equal to or greater than 5, you round up. If it's less than 5, you round down. Now obviously in my example, .54 would have 4 less than 5, but the next digit is 7. Why don't you round the 4 up to a 5, based on that 7, and then round the .5 to a .6? I readily admit this is becoming an issue of semantics, if anything. The rounding issue has near zero impact on the reporting, the difference here is not going to change what people are looking at (nobody cares if the % of male respondents is 49.5 vs 49.4%. Matthew J Poes Research Data Specialist Center for Prevention Research and Development University of Illinois -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Gene Maguin Sent: Monday, December 12, 2011 10:31 AM To: [hidden email] Subject: Re: Truncation of values after Decimal when exporting to Excel Matthew, 6.5 is the correct rounded value of 6.547. Would 6.6 be the correct rounded value of 6.50001? It's not clear how you are doing the export to excel but once you do the aggregation operation, look at the data file and I'll be you will see that the format is f5.1. Look at the documentation for aggregation command in the syntax reference. You are getting what you are supposed to. So change the format! That point aside, the other place to look is at how many decimal digits excel is set to display. The solution to your problem is in spss rather than excel. Gene Maguin -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Matthew J Poes Sent: Monday, December 12, 2011 11:19 AM To: [hidden email] Subject: Truncation of values after Decimal when exporting to Excel I'm currently using SPSS 18, and I'm running some syntax for a set of reports which start by aggregating data which was collected at the person level to a unit level (School or State). The aggregation needs to give a report of the % of certain respondent types, and in order to give a valid percent, I use the PIN (Percent in) for each categorical value of the demographic variable. I then take the saved file, and save it as an excel file. When I do this, it's truncating the values down to only one digit after the decimal place. It also is not rounding correctly, as some values which should be appropriatly rounded up, are being roudned down. For instance, a value of 6.547 becomes 6.5 instead of 6.6 I have tried adjusting the fuzz bits used, but this has not changed the values I get. I can not find any syntax that allows me to adjust how SPSS exports to excel. I've also checked the aggregate SPSS files, and the values are saved correctly there. If possible, I would prefer that SPSS export all decimal places to excel. I know this is possible as I have files run from similar syntax by other people which do have all the decimal places. ===================== 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 ===================== 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: Truncation of values after Decimal when exporting to Excel
|
|
[snip, about rounding ".547" ] "Why don't you round the 4 up to a 5, based on that 7, and then round the .5 to a .6? [snip, rest] Because we never intentionally do rounding by steps like that. Because that potentially (like here) generates error. -- Rich Ulrich |
Re: Truncation of values after Decimal when exporting to Excel
Administrator
|
In reply to this post by Poes, Matthew Joseph
"Now obviously in my example, .54 would have 4 less than 5, but the next digit is 7. Why don't you round the 4 up to a 5, based on that 7, and then round the .5 to a .6? "
Ever thought about a career in banking?
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: Truncation of values after Decimal when exporting to Excel
|
|
Comments like this are simply reflecting the imprecision caused by any form of rounding. Rounding was not meant to be the point of my post, it was about what was causing the saving of an SPSS data file to Excel, and having the number of decimal places truncated. The specifics of rounding were unimportant.
I've looked into the issue considering the relevant responses, and found that these are not the issue I'm experiencing. As mentioned, this same program was run on other computers in the office, and found to export all decimal places to excel, regardless of what the SPSS program indicated. The other computer it was run on is running version 19, whereas I'm running version 18, however, that user indicated to me that they were fairly sure such truncating issues had never shown up in the past. We have checked the settings, and nothing appears to be different. The comment about rounding was meant to A) question why 6.547 would become 6.5 instead of 6.6 (that was answered already), B) note that this issue was causing an early rounding problem, but that its impact in this scenario was inconsequential. It was mentioned that it was unclear what form of "exporting" was being used, so here is a response. The data file is aggregated into a new data file at a different level, from person to school. This new data file is translated to excel format using the following syntax: SAVE TRANSLATE OUTFILE='C:Excel_file.xlsx' /TYPE=XLS /VERSION=12 /MAP /REPLACE /FIELDNAMES /CELLS=LABELS. Matthew J Poes Research Data Specialist Center for Prevention Research and Development University of Illinois -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of David Marso Sent: Monday, December 12, 2011 3:54 PM To: [hidden email] Subject: Re: Truncation of values after Decimal when exporting to Excel "Now obviously in my example, .54 would have 4 less than 5, but the next digit is 7. Why don't you round the 4 up to a 5, based on that 7, and then round the .5 to a .6? " Ever thought about a career in banking? -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Truncation-of-values-after-Decimal-when-exporting-to-Excel-tp5068824p5069666.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: Truncation of values after Decimal when exporting to Excel
|
Dear Matthew,
First, let's not talk about any truncation anymore. I haven't observed any form of truncation taking place in any of the posts I've seen on this thread. Numbers have been rounded rather than truncated and this was done correctly. Case closed. Second, some members correctly suggested that the format of your variables in SPSS will affect the number of decimal places that will be exported into EXCEL. So if you have a variable "v1", you could write formats v1(f8.2). in order to see 2 decimals or formats v1(f8.4). in order to see 4 decimals. Apart from any speculations on differences between versions, did you try to modify the variable formats before the SAVE TRANSLATE command? If no, please give it a shot. If yes, please let us know what happened and whether it solves the issue. Best, Ruben > Date: Mon, 12 Dec 2011 22:53:06 +0000 > From: [hidden email] > Subject: Re: Truncation of values after Decimal when exporting to Excel > To: [hidden email] > > Comments like this are simply reflecting the imprecision caused by any form of rounding. Rounding was not meant to be the point of my post, it was about what was causing the saving of an SPSS data file to Excel, and having the number of decimal places truncated. The specifics of rounding were unimportant. > > I've looked into the issue considering the relevant responses, and found that these are not the issue I'm experiencing. As mentioned, this same program was run on other computers in the office, and found to export all decimal places to excel, regardless of what the SPSS program indicated. The other computer it was run on is running version 19, whereas I'm running version 18, however, that user indicated to me that they were fairly sure such truncating issues had never shown up in the past. We have checked the settings, and nothing appears to be different. > > The comment about rounding was meant to A) question why 6.547 would become 6.5 instead of 6.6 (that was answered already), B) note that this issue was causing an early rounding problem, but that its impact in this scenario was inconsequential. > > It was mentioned that it was unclear what form of "exporting" was being used, so here is a response. > The data file is aggregated into a new data file at a different level, from person to school. This new data file is translated to excel format using the following syntax: > > SAVE TRANSLATE OUTFILE='C:Excel_file.xlsx' > /TYPE=XLS > /VERSION=12 > /MAP > /REPLACE > /FIELDNAMES > /CELLS=LABELS. > > Matthew J Poes > Research Data Specialist > Center for Prevention Research and Development > University of Illinois > > > -----Original Message----- > From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of David Marso > Sent: Monday, December 12, 2011 3:54 PM > To: [hidden email] > Subject: Re: Truncation of values after Decimal when exporting to Excel > > "Now obviously in my example, .54 would have 4 less than 5, but the next digit is 7. Why don't you round the 4 up to a 5, based on that 7, and then round the .5 to a .6? " > > Ever thought about a career in banking? > > > -- > View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Truncation-of-values-after-Decimal-when-exporting-to-Excel-tp5068824p5069666.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: Truncation of values after Decimal when exporting to Excel
|
|
hi Matthew,
You didn't post your syntax. What did you mean by 'Excel file'? Simply an .xls file, or a .csv file? Both open MS Excel when you double-click them.
If an excel file is saved as .csv from within excel, the display format is what is saved, ie. the numbers may be rounded. Not sure if something similar happens when spss is used. I'd hope not.
Cheers!!
Albert-Jan ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a fresh water system, and public health, what have the Romans ever done for us? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |