Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.

# st: Cautionary tale - single & double

 From "Allan Reese (Cefas)" <[email protected]> To "Allan Reese (Cefas)" <[email protected]>, "[email protected]" <[email protected]> Subject st: Cautionary tale - single & double Date Mon, 21 Oct 2013 13:20:51 +0000

```It's well know that computers work to finite precision, but less obvious how you might detect when this breaks down.  In this example, a column of values copied from an Excel sheet gave a different total in Stata from that shown in Excel. The -table- command, however, gave the same total as Excel.  A difference of 13 in 740 million was just irritating!

The explanation was that the column of values read from Excel had been coerced to double precision, but the generated running total used single precision arithmetic.  This was further obscured by applying a format with no decimal places.

Here's the stripped down example, with the column summed in both single and double precision. The difference becomes meaningful after the first big amount is added in, but it is odd that the sign of diff switches.
Allan

. format  xlvals  %15.3f
. gen singletot = sum(xlvals)
. g double doubletot = sum(xlvals)
. g diff = doubletot - singletot
. format single  double   %13.0f

. desc

Contains data
obs:            33
vars:             4
size:           792
-------------------------------------------------------------
storage  display     value
variable name   type   format      label      variable label
-------------------------------------------------------------
xlvals          double %15.3f
singletot       float  %13.0f
doubletot       double %13.0f
diff            float  %9.0g

. list

+---------------------------------------------------+
|        xlvals   singletot   doubletot        diff |
|---------------------------------------------------|
1. |     69700.461       69700       69700    -.000108 |
2. |      9504.608       79205       79205   -.0011881 |
3. |    134792.627      213998      213998   -.0072725 |
4. |      1036.866      215035      215035    -.000288 |
5. |   1392857.143     1607892     1607892   -.0449309 |
|---------------------------------------------------|
6. |    673963.134     2281855     2281855    .0887097 |
7. |    347350.230     2629205     2629205    .0691244 |
8. |   1241474.654     3870680     3870680   -.0264977 |
9. |  24436866.359    28307546    28307546    .0829493 |
10. |    893087.558    29200634    29200634    -.359447 |
|---------------------------------------------------|
11. |   5073732.719    34274368    34274366   -1.640553 |
12. |     50691.244    34325056    34325058    1.603687 |
13. |     31105.991    34356164    34356164   -.4055299 |
14. |    101612.903    34457776    34457776    .4976959 |
15. |   2540322.581    36998100    36998099    -.921659 |
|---------------------------------------------------|
16. |    472811.060    37470912    37470910   -1.861751 |
17. |    101612.903    37572524    37572523   -.9585254 |
18. |     48387.097    37620912    37620910   -1.861751 |
19. | 677845714.286   715466624   715466624    .4239631 |
20. |     52419.355   715519040   715519044    3.778802 |
|---------------------------------------------------|
21. |   2730184.332   718249216   718249228     12.1106 |
22. |    144470.046   718393728   718393698   -29.84332 |
23. |         0.000   718393728   718393698   -29.84332 |
24. |    892165.899   719285888   719285864    -23.9447 |
25. |    201612.903   719487488   719487477   -11.04147 |
|---------------------------------------------------|
26. |  14594130.184   734081600   734081607    7.142857 |
27. |   6007955.069   740089536   740089562    26.21198 |
28. |     96751.152   740186304   740186313    9.364056 |
29. |     18571.429   740204864   740204885    20.79263 |
30. |      8064.516   740212928   740212949    21.30876 |
|---------------------------------------------------|
31. |     35714.286   740248640   740248664    23.59447 |
32. |      3571.429   740252224   740252235    11.02304 |
33. |     35714.286   740287936   740287949    13.30876 |
+---------------------------------------------------+

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/
```