"estpost tabulate" inserting " _missing_ " where the decimal period should be

[Rober Martinez-Espineira]

I do not think I have ever experience this issue but it persists across computers, Stata 17 and 18, after updating the estpost command, and with different numerical data formats and different datasets.

Basically, I would like to transfer the tabulation of a continuous variable that includes fractional values through esttab.
The command tabulate itself works fine but estpost inserts _missing_ where a regular decimal period would be, so esttab just perpetuates the error.

Anyone would be able to help?

Thanks so much!!!

++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++++++

webuse auto
(1978 automobile data)

. tab gear_ratio if gear_ratio<2.5

Gear ratio | Freq. Percent Cum.
------------+-----------------------------------
2.19 | 1 7.69 7.69
2.24 | 1 7.69 15.38
2.26 | 1 7.69 23.08
2.28 | 1 7.69 30.77
2.41 | 3 23.08 53.85
2.43 | 1 7.69 61.54
2.47 | 5 38.46 100.00
------------+-----------------------------------
Total | 13 100.00

. estpost tabulate gear_ratio if gear_ratio <2.5

gear_ratio | e(b) e(pct) e(cumpct)
-------------+---------------------------------
2_mi~1900001 | 1 7.692308 7.692308
2_missing_24 | 1 7.692308 15.38462
2_missing_26 | 1 7.692308 23.07692
2_missing_28 | 1 7.692308 30.76923
2_mi~4100001 | 3 23.07692 53.84615
2_mi~4300001 | 1 7.692308 61.53846
2_missing_47 | 5 38.46154 100
-------------+---------------------------------
Total | 13 100

row labels saved in macro e(labels)

. esttab

----------------------------
(1)
gear_ratio
----------------------------
2_mi~1900001 1


2_missing_24 1


2_missing_26 1


2_missing_28 1


2_mi~4100001 3


2_mi~4300001 1


2_missing_47 5


Total 13

----------------------------
N 13
----------------------------
t statistics in parentheses
* p<0.05, ** p<0.01, *** p<0.001

[Andrew Musau]

estout is from SSC (FAQ Advice #12).You have a point in noting how estpost handles numeric labels with decimal points. But the deeper issue is that tabulating continuous variables is already a logical misstep—tabulations are meant for categorical variables, not real-valued measurements. Numerous problems arise when trying to tabulate continuous data, including precision issues where values such as 23.9999… and 24 are effectively identical but may not appear so in a tabulation. Secondly, binary floating-point numbers can represent only fractions whose denominators are powers of 2. Many decimal values (like 2.19 - see result below) are repeating fractions in binary, so the stored value is only an approximation. So you would not be able to tabulate 2.19 and display it as such. Otherwise, a workaround is to use the -substitute()- option in esttab to restore decimal points, or convert the continuous variable to a string and tabulate that instead. While one might view this as a bug, it originates from venturing into a rarely visited corner of statistical oddities, and may be best left there.

sysuse auto, clear
tabulate gear_ratio if gear_ratio <2.5

*SOLUTION 1: ESTTAB'S SUBSTITUTE OPTION
qui estpost tabulate gear_ratio if gear_ratio <2.5
esttab, cell("b pct cumpct") noobs nonumb mlab(none) ///
collab(Freq Percent Cum., lhs(`:var lab `e(depvar)'')) ///
substitute("_missing_" "." _mi~ ".") varwidth(20)

*SOLUTION 2: TOSTRING THE NUMERICAL VARIABLE
tostring gear_ratio, gen(str_gear_ratio) format(%4.2f) force
estpost tabulate str_gear_ratio if gear_ratio <2.5
esttab, cell("b pct cumpct") noobs nonumb mlab(none) ///
collab(Freq Percent Cum., lhs(`:var lab `e(depvar)'')) ///
varlabels(`e(labels)')

Res.:

. tabulate gear_ratio if gear_ratio <2.5

 Gear ratio |      Freq.     Percent        Cum.
------------+-----------------------------------
       2.19 |          1        7.69        7.69
       2.24 |          1        7.69       15.38
       2.26 |          1        7.69       23.08
       2.28 |          1        7.69       30.77
       2.41 |          3       23.08       53.85
       2.43 |          1        7.69       61.54
       2.47 |          5       38.46      100.00
------------+-----------------------------------
      Total |         13      100.00

. 
. *SOLUTION 1: ESTTAB'S SUBSTITUTE OPTION
. qui estpost tabulate gear_ratio if gear_ratio <2.5

. esttab, cell("b pct cumpct") noobs nonumb mlab(none) ///
> collab(Freq Percent Cum., lhs(`:var lab `e(depvar)'')) ///
> substitute("_missing_" "." _mi~ ".") varwidth(20)

-----------------------------------------------------------
Gear ratio                   Freq      Percent         Cum.
-----------------------------------------------------------
2.1900001               1     7.692308     7.692308
2.24                    1     7.692308     15.38462
2.26                    1     7.692308     23.07692
2.28                    1     7.692308     30.76923
2.4100001               3     23.07692     53.84615
2.4300001               1     7.692308     61.53846
2.47                    5     38.46154          100
Total                          13          100             
-----------------------------------------------------------

. 
. *SOLUTION 2: TOSTRING THE NUMERICAL VARIABLE
. tostring gear_ratio, gen(str_gear_ratio) format(%4.2f) force
str_gear_ratio generated as str4
str_gear_ratio was forced to string; some loss of information

. estpost tabulate str_gear_ratio if gear_ratio <2.5

str_gear_rat |      e(b)     e(pct)  e(cumpct) 
-------------+---------------------------------
           1 |         1   7.692308   7.692308 
           2 |         1   7.692308   15.38462 
           3 |         1   7.692308   23.07692 
           4 |         1   7.692308   30.76923 
           5 |         3   23.07692   53.84615 
           6 |         1   7.692308   61.53846 
           7 |         5   38.46154        100 
-------------+---------------------------------
       Total |        13        100            

row labels saved in macro e(labels)

. esttab, cell("b pct cumpct") noobs nonumb mlab(none) ///
> collab(Freq Percent Cum., lhs(`:var lab `e(depvar)'')) ///
> varlabels(`e(labels)')

---------------------------------------------------
Gear ratio           Freq      Percent         Cum.
---------------------------------------------------
2.19                    1     7.692308     7.692308
2.24                    1     7.692308     15.38462
2.26                    1     7.692308     23.07692
2.28                    1     7.692308     30.76923
2.41                    3     23.07692     53.84615
2.43                    1     7.692308     61.53846
2.47                    5     38.46154          100
Total                  13          100             
---------------------------------------------------

[Rober Martinez-Espineira]

Thanks so much!!! This is very helpful.

I see your point about the advise against tabulating continuous variables but had no idea that binary floating-point numbers can only represent fractions whose denominators are powers of 2.

I was half hoping for some trick that would avoid the workarounds you suggest but, since this does not then a bug with my Stata or an issue with my specific data, I will have to work with that.

Thanks so much again!

Rober

[Andrew Musau]

Also note that you could use the official collect command here, but my point on representation of fractions remains.

sysuse auto, clear
collect clear
tabulate gear_ratio if gear_ratio <2.5, collect
collect layout (cmdset#gear_ratio) (result)

tostring gear_ratio, gen(str_gear_ratio) format(%4.2f) force
collect clear
tabulate str_gear_ratio if gear_ratio <2.5, collect
collect layout (cmdset#str_gear_ratio) (result)
collect export myfile, as(docx) replace

Res.:

. tabulate gear_ratio if gear_ratio <2.5, collect

 Gear ratio |      Freq.     Percent        Cum.
------------+-----------------------------------
       2.19 |          1        7.69        7.69
       2.24 |          1        7.69       15.38
       2.26 |          1        7.69       23.08
       2.28 |          1        7.69       30.77
       2.41 |          3       23.08       53.85
       2.43 |          1        7.69       61.54
       2.47 |          5       38.46      100.00
------------+-----------------------------------
      Total |         13      100.00

. 
. collect layout (cmdset#gear_ratio) (result)

Collection: Tabulate
      Rows: cmdset#gear_ratio
   Columns: result
   Table 1: 9 x 3

--------------------------------------------------------
                        Frequency   Percent   Cumulative
--------------------------------------------------------
Gear ratio                                              
  2.190000057220458984          1      7.69         7.69
  2.240000009536743164          1      7.69        15.38
  2.259999990463256836          1      7.69        23.08
  2.279999971389770508          1      7.69        30.77
  2.410000085830688477          3     23.08        53.85
  2.430000066757202148          1      7.69        61.54
  2.470000028610229492          5     38.46       100.00
  Total                        13    100.00             
--------------------------------------------------------

. 
. 
. 
. tostring gear_ratio, gen(str_gear_ratio) format(%4.2f) force
str_gear_ratio generated as str4
str_gear_ratio was forced to string; some loss of information

. 
. collect clear

. 
. tabulate str_gear_ratio if gear_ratio <2.5, collect

 Gear ratio |      Freq.     Percent        Cum.
------------+-----------------------------------
       2.19 |          1        7.69        7.69
       2.24 |          1        7.69       15.38
       2.26 |          1        7.69       23.08
       2.28 |          1        7.69       30.77
       2.41 |          3       23.08       53.85
       2.43 |          1        7.69       61.54
       2.47 |          5       38.46      100.00
------------+-----------------------------------
      Total |         13      100.00

. 
. collect layout (cmdset#str_gear_ratio) (result)

Collection: Tabulate
      Rows: cmdset#str_gear_ratio
   Columns: result
   Table 1: 9 x 3

--------------------------------------------
            Frequency   Percent   Cumulative
--------------------------------------------
Gear ratio                                  
  2.19              1      7.69         7.69
  2.24              1      7.69        15.38
  2.26              1      7.69        23.08
  2.28              1      7.69        30.77
  2.41              3     23.08        53.85
  2.43              1      7.69        61.54
  2.47              5     38.46       100.00
  Total            13    100.00             
--------------------------------------------

[Rober Martinez-Espineira]

Thanks so much again!

Rober