Date: Mon, 12 Dec 2011 16:54:38 +0000
Reply-To: "Poes, Matthew Joseph" <mpoes@UILLINOIS.EDU>
Sender: "SPSSX(r) Discussion" <SPSSX-L@LISTSERV.UGA.EDU>
From: "Poes, Matthew Joseph" <mpoes@UILLINOIS.EDU>
Subject: Re: Truncation of values after Decimal when exporting to Excel
In-Reply-To: <00a401ccb8eb$767e0f20$637a2d60$@buffalo.edu>
Content-Type: text/plain; charset="us-ascii"
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:SPSSX-L@LISTSERV.UGA.EDU] On Behalf Of Gene Maguin
Sent: Monday, December 12, 2011 10:31 AM
To: SPSSX-L@LISTSERV.UGA.EDU
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:SPSSX-L@LISTSERV.UGA.EDU] On Behalf Of Matthew J Poes
Sent: Monday, December 12, 2011 11:19 AM
To: SPSSX-L@LISTSERV.UGA.EDU
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 LISTSERV@LISTSERV.UGA.EDU (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 LISTSERV@LISTSERV.UGA.EDU (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
LISTSERV@LISTSERV.UGA.EDU (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
