Precision issues with rounding of a variable

[Juan Vega]

Hello Statalist,

I am using Stata 15.1 and would like to request advice regarding a precision issue. I have data of the total scores in an assessment and am dividing the total score by the number of responses to the assessment to create an average assessment score. The calculations I produce need to be compared with other calculations produced in Oracle SQL. The issue I am running into is that a value in the average assessment score such as 4.55 when rounded to 1 decimal place is returned as 4.5, instead of 4.6. The value that Oracle SQL produced was 4.6. All columns are stored in double precision.

I read the help precision Stata documentation, and found that a proposed solution was to create a variable as a float-type to avoid these issues (i.e., section “How count x==float(1.1) solves the problem” of 13.12 Precision and problems therein). This approach worked for the first three observations of the sample data set provided in sample code below, but not the rest. Lastly, another proposed solution is to add 0.5 to the average assessment score, multiply by 10, and use the floor function to ensure that the number rounds down, and then divide by 10. This solution works in all cases, but I would like to understand why Stata is not rounding a number such as 4.55 to 4.6 and if there is a way to round to 4.6 without the solution that uses the floor function. In other words, I would prefer not having to perform additional transformations to the data other than rounding. The following is a sample code that recreates the issue. Attached is a snapshot of the data as well. The variable named “benchmark_avg_assessment_score “ is what I need the calculation of average score to match. These differences in values are relevant, and the values for the average assessment score have to match exactly for my purposes. Thank you in advance for any support provided.

set type double, permanently

set obs 6

gen total_assessment_score=.
replace total_assessment_score=364 if _n==1
replace total_assessment_score=189 if _n==2
replace total_assessment_score=159 if _n==3
replace total_assessment_score=82 if _n==4
replace total_assessment_score=123 if _n==5
replace total_assessment_score=123 if _n==6

gen count_of_scores=.
replace count_of_scores=80 if _n==1
replace count_of_scores=60 if _n==2
replace count_of_scores=60 if _n==3
replace count_of_scores=40 if _n==4
replace count_of_scores=60 if _n==5
replace count_of_scores=60 if _n==6

gen benchmark_avg_assessment_score=.
replace benchmark_avg_assessment_score=4.6 if _n==1
replace benchmark_avg_assessment_score=3.2 if _n==2
replace benchmark_avg_assessment_score=2.7 if _n==3
replace benchmark_avg_assessment_score=2.1 if _n==4
replace benchmark_avg_assessment_score=2.1 if _n==5
replace benchmark_avg_assessment_score=2.1 if _n==6

gen avg_assessment_score=total_assessment_score/count_of_scores

gen avg_assessment_score_rounded_1=round(avg_assessmen t_score, 0.1)
gen avg_assessment_score_rounded_2=round(float(avg_ass essment_score), 0.1)
gen avg_assessment_score_rounded_3=floor((10*avg_asses sment_score)+0.5)/10

[Clyde Schechter]

The reason the rounding does not work as you want it to is that the number that you are calling 4.55 does not exist in Stata. Stata stores all numbers as finite-precision binary numbers. There is no finite precision binary number that is exactly equal to 4.55, just as there is no finite precision decimal number that is exactly equal to 1/3. So the number that Stata uses is the nearest finite precision binary number it can find. That number might be, for example 4.5499999999999998 or something like that--which, when rounded to 1 decimal place is, indeed, 4.5. The solution you found that works in all cases is the best way to work around this problem.

Expect to encounter difficulties like this whenever you attempt to compare floating point numbers for exact equality.

[Mike Lacy]

Another possibility here is that Oracle SQL and Stata use a different rounding rule with an underlying identical value. I had always thought that "banker's rounding" was standard, handed down on stone tablets or whatever your religion uses for revelation. (Banker's rounding: When the final digit is 5, round so that the resulting new final digit will be even, thus producing up/down rounding with equal frequency when ties occur.) However, https://en.wikipedia.org/wiki/IEEE_7...ngs_to_nearest reveals that there are a number of options that IEEE considers reasonable. Stata does not use Banker's Rounding, as near as I can tell. (-display round(445,10) = 450-)
I had a similarly odd discussion with some not so knowledgeable collaborators about a problem such as presented here, and it led me to have to learn about the IEEE non-standard.

[Juan Vega]

Thanks for your input!