-table- in Stata 17/18: Replacing blanks in cells with no cross-tabulation cases

[Emanuel Agu]

Hi everyone,

I’m trying to produce the same table output using -table- in Stata 16 and 17/18.

In Stata 16, -table, miss- adds dots in the cells with no data. For example,

sysuse auto, clear
qui summ price, d

gen Clase = price >= r(p50)
label define costo 0 Barato 1 Caro
label values Clase costo 

version 16: bys Clase: table turn rep78 foreign if rep78>= 3, c(mean length) f(%5.0f) center miss


---------------------------------------------------------------------------
-> Clase = Barato

------------------------------------------------
Turn      |  Car origin and Repair record 1978  
circle    | --- Domestic ---    ---- Foreign ---
(ft.)     |   3     4     5       3     4     5
----------+-------------------------------------
       31 |  193    .     .       .     .     .
       32 |   .     .     .       .     .    165
       33 |   .    147    .       .    154    .
       34 |  163    .     .      142   149    .
       35 |   .     .    163      .    155   165
       36 |  165    .     .      161    .    164
       37 |  170    .    157      .     .     .
       39 |   .    169    .       .     .     .
       40 |  188    .     .       .     .     .
       41 |  198    .     .       .     .     .
       42 |  199   218    .       .     .     .
       43 |  193    .     .       .     .     .
------------------------------------------------

---------------------------------------------------------------------------
-> Clase = Caro

------------------------------------------------
Turn      |  Car origin and Repair record 1978  
circle    | --- Domestic ---    ---- Foreign ---
(ft.)     |   3     4     5       3     4     5
----------+-------------------------------------
       34 |   .     .     .       .    174    .
       35 |   .     .     .       .    170   155
       36 |   .     .     .      174   164   174
       37 |   .     .     .       .     .    191
       38 |   .     .     .       .    184    .
       41 |  201    .     .       .     .     .
       42 |  199   214    .       .     .     .
       43 |  210   218    .       .     .     .
       44 |  216    .     .       .     .     .
       45 |  202   217    .       .     .     .
       48 |  230   221    .       .     .     .
       51 |  233    .     .       .     .     .
------------------------------------------------

In Stata 17/18, -table, miss- shows potential missing values in variables, but not in the cell with the cross-tabulation. In the cells, we see now blanks instead of dots. For example,

table (turn) (foreign rep78) (Clase) if rep78>= 3, statistic(mean length) nformat(%5.0f mean leng) nototals

Clase = Barato
--------------------------------------------------------------------
| Car origin
| Domestic Foreign
| Repair record 1978 Repair record 1978
| 3 4 5 3 4 5
------------------+-------------------------------------------------
Turn circle (ft.) |
31 | 193
32 | 165
33 | 147 154
34 | 163 142 149
35 | 163 155 165
36 | 165 161 164
37 | 170 157
39 | 169
40 | 188
41 | 198
42 | 199 218
43 | 193
--------------------------------------------------------------------

Clase = Caro
------------------------------------------------------------------
| Car origin
| Domestic Foreign
| Repair record 1978 Repair record 1978
| 3 4 3 4 5
------------------+-----------------------------------------------
Turn circle (ft.) |
34 | 174
35 | 170 155
36 | 174 164 174
37 | 191
38 | 184
41 | 201
42 | 199 214
43 | 210 218
44 | 216
45 | 202 217
48 | 230 221
51 | 233
------------------------------------------------------------------

Then, I wonder whether is it possible to replace the blanks with other characters when running -table- in Stata 17/18.

Thank you!

[Clyde Schechter]

If you want to show 0 instead of blanks for counts of empty cells, use the -zerocounts- option in -table-.

[Emanuel Agu]

Hi Clyde Schechter, thank you for the reference.

I use -zerocounts- to show the zeroes when tabulating frequencies. But, now, I’m tabulating statistics, so the -zerocounts- option doesn’t do anything with respect to “missing statistics”, as the Stata manual describes the cases I’m interested on.

In Stata 16, the -missing- option in -table- shows dots for “missing statistics”. This is exactly what I would like to have using -table- with Stata 17/18.

[Clyde Schechter]

The beauty of the new -table- command and the -collect- framework that it works in is their remarkable scope and flexibility, so that pretty much anything you can imagine can be done. On the other hand, the price of all that scope and flexibility is that they are complicated and difficult to learn. What I'm saying is that 1) I'm confident there is a way to do what you want, but 2) I have no idea what it might be and 3) it would probably take me a very long time to figure it out. Fortunately, Jeff Pitblado (StataCorp) , whose knowledge of -collect- and -table- is truly encyclopedic, follows this Forum closely. My guess is that he will see this thread and come to the rescue now that we are past the weekend.

Sorry I can't be more helpful.

[Clyde Schechter]

Wait, it's much simpler than I thought.

. sysuse auto, clear
(1978 automobile data)

.
. fillin rep78 foreign

.
. table rep78 foreign, statistic(count price) statistic(mean price) nformat(%3.2f mean)

---------------------------------------------------------------
                                |           Car origin         
                                |  Domestic   Foreign     Total
--------------------------------+------------------------------
Repair record 1978              |                              
  1                             |                              
    Number of nonmissing values |         2         0         2
    Mean                        |   4564.50         .   4564.50
  2                             |                              
    Number of nonmissing values |         8         0         8
    Mean                        |   5967.62         .   5967.62
  3                             |                              
    Number of nonmissing values |        27         3        30
    Mean                        |   6607.07   4828.67   6429.23
  4                             |                              
    Number of nonmissing values |         9         9        18
    Mean                        |   5881.56   6261.44   6071.50
  5                             |                              
    Number of nonmissing values |         2         9        11
    Mean                        |   4204.50   6292.67   5913.00
  Total                         |                              
    Number of nonmissing values |        48        21        69
    Mean                        |   6179.25   6070.14   6146.04
---------------------------------------------------------------

[Emanuel Agu]

Hi Clyde Schechter , thank you so much for bringing in the idea of using -fillin-. I'll try this out and get back.

[Jeff Pitblado (StataCorp)]

Sorry, I was AFK.

Clyde provides an elegant solution.

The following uses collect to get the same table without having to modify your dataset.

You can provide your own rules for filling empty cells beyond table's zerocounts option with command collect style cell's option empty().

In the following, building on Clyde's auto data example, I show how to use collect commands after table to put a zero "0" for counts in empty cells and dots "." for all other results in empty cells.

sysuse auto

table rep78 foreign, ///
    statistic(count price) ///
    statistic(mean price) ///
    nformat(%3.2f mean)

* make "." the default for empty item cells
collect style cell cell_type[item], empty(".")
* make "0" the default for count results in empty item cells
collect style cell cell_type[item]#result[count], empty("0")
collect preview

You can save yourself some typing by storing these style choices to a file that you can subsequently specify in table's style() option. Here is how that would look in a do-file.

* start a new collection named "myempty"
collect create myempty
collect style clear
* make "." the default for empty item cells
collect style cell cell_type[item], empty(".")
* for these results, make "0" the default for empty item cells
collect style cell cell_type[item]#result[frequency sumw count], empty(0)
* save these style choices to disk, they can be used to override a
* command's default style
collect style save myempty, replace
* put this file in your PERSONAL folder if you want reference it in
* -style()- options without having to specify its path

Here is the "myempty" style in action.

table rep78 foreign, ///
    style(myempty, override) ///
    statistic(count price) ///
    statistic(mean price) ///
    nformat(%3.2f mean)

Here is the resulting table.

---------------------------------------------------------------
                                |           Car origin         
                                |  Domestic   Foreign     Total
--------------------------------+------------------------------
Repair record 1978              |                              
  1                             |                              
    Number of nonmissing values |         2         0         2
    Mean                        |   4564.50         .   4564.50
  2                             |                              
    Number of nonmissing values |         8         0         8
    Mean                        |   5967.62         .   5967.62
  3                             |                              
    Number of nonmissing values |        27         3        30
    Mean                        |   6607.07   4828.67   6429.23
  4                             |                              
    Number of nonmissing values |         9         9        18
    Mean                        |   5881.56   6261.44   6071.50
  5                             |                              
    Number of nonmissing values |         2         9        11
    Mean                        |   4204.50   6292.67   5913.00
  Total                         |                              
    Number of nonmissing values |        48        21        69
    Mean                        |   6179.25   6070.14   6146.04
---------------------------------------------------------------