Disaplaying Frequency and % Next to each other in a Table.

[pavan pandey]

Hi Everyone,


I am using Stata 17.0. On a daily basis, I need to create descriptive and comparison tables using Stata. I normally use the following command to generate table and exporting it to word.


table () ( group ) (), nototals statistic(fvrawfrequency agegroup gender bmicat surgery) statistic(fvrawpercent agegroup gender bmicat surgery) nformat(%9.3g) style(table-1)

collect export "xxx", as(docx) replace


This command creates a MS Word file that has frequency in the first few rows followed by % in the next rows. Thereafter, I have to manually edit the table to place the % next to the frequency column. Is there a simpler and automatic way of doing it?


I want the % column placed next to the corresponding 'frequency' column.

[Hemanshu Kumar]

Please provide a data example, as suggested in the Statalist FAQ #12.

[Hemanshu Kumar]

Consider:

sysuse nlsw88, clear

table () ( race ) (), nototals ///
    statistic(fvrawfrequency married union) ///
    statistic(fvrawpercent married union) ///
    nformat(%9.3g)

collect style cell result[fvrawpercent], sformat((%s%%))
collect composite define freqperc = fvrawfrequency fvrawpercent, trim override
collect style header result, level(hide)
collect style cell cell_type[item column-header], halign(center)
collect layout (married union) (race#result[freqperc])

which produces:

. collect preview

-------------------------------------------------------
             |                    Race                
             |      White         Black         Other  
-------------+-----------------------------------------
Married      |                                        
  Single     |   487 (29.7%)    309 (53%)     8 (30.8%)
  Married    |  1150 (70.3%)    274 (47%)    18 (69.2%)
Union worker |                                        
  Nonunion   |  1051 (77.7%)   350 (69.9%)   16 (66.7%)
  Union      |   302 (22.3%)   151 (30.1%)    8 (33.3%)
-------------------------------------------------------

[Maarten Buis]

I will propose an alternative way of solving this problem, but more importantly I will show how to save those step. Since you stated that you needed to do this often and repeatedly that could save you a lot of typing and ensure consistency.


The alternative way of creating the table you want is based on the fact that you can already specify what goes where in your table in the table command. For that you use the var and result keywords:

. table (var) (race result ) , nototals ///
>     statistic(fvrawfrequency married union) ///
>     statistic(fvrawpercent married union) ///
>     nformat(%9.3g)

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> --------------------------------------------------
                      |                                                                                                                 Race                                                            
>                                                  
                      |                                    White                                                                       Black                                                            
>            Other                                  
                      |  Unweighted factor-variable frequency   Unweighted factor-variable percent   Unweighted factor-variable frequency   Unweighted factor-variable percent   Unweighted factor-varia
> ble frequency   Unweighted factor-variable percent
----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> --------------------------------------------------
Married=Single        |                                   487                                 29.7                                    309                                   53                          
>             8                                 30.8
Married=Married       |                                  1150                                 70.3                                    274                                   47                          
>            18                                 69.2
Union worker=Nonunion |                                  1051                                 77.7                                    350                                 69.9                          
>            16                                 66.7
Union worker=Union    |                                   302                                 22.3                                    151                                 30.1                          
>             8                                 33.3
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> --------------------------------------------------

That does not look very pretty because of the long labels for fvrawfrequency and fvrawpercent, so we can change that:

. collect label levels result fvrawfrequency "frequency" ///
>                             fvrawpercent   "percent", modify    

.
. collect preview                                                

----------------------------------------------------------------------------------------
                      |                                Race                            
                      |         White                 Black                 Other      
                      |  frequency   percent   frequency   percent   frequency   percent
----------------------+-----------------------------------------------------------------
Married=Single        |        487      29.7         309        53           8      30.8
Married=Married       |       1150      70.3         274        47          18      69.2
Union worker=Nonunion |       1051      77.7         350      69.9          16      66.7
Union worker=Union    |        302      22.3         151      30.1           8      33.3
----------------------------------------------------------------------------------------

We may want to change the rows, the Married=Single and Married=Married look ugly.

. collect style row stack, nobinder spacer

. collect preview                                

-------------------------------------------------------------------------------
             |                                Race                            
             |         White                 Black                 Other      
             |  frequency   percent   frequency   percent   frequency   percent
-------------+-----------------------------------------------------------------
Married      |                                                                
  Single     |        487      29.7         309        53           8      30.8
  Married    |       1150      70.3         274        47          18      69.2
             |                                                                
Union worker |                                                                
  Nonunion   |       1051      77.7         350      69.9          16      66.7
  Union      |        302      22.3         151      30.1           8      33.3
-------------------------------------------------------------------------------

If you want to recreate Hemanshu Kumar 's look, you use:

. collect style cell result[fvrawpercent], sformat((%s%%))

. collect style header result, level(hide)

. collect preview

-------------------------------------------------------------
             |                       Race                    
             |       White           Black           Other  
-------------+-----------------------------------------------
Married      |                                              
  Single     |   487   (29.7%)   309     (53%)    8   (30.8%)
  Married    |  1150   (70.3%)   274     (47%)   18   (69.2%)
             |                                              
Union worker |                                              
  Nonunion   |  1051   (77.7%)   350   (69.9%)   16   (66.7%)
  Union      |   302   (22.3%)   151   (30.1%)    8   (33.3%)
-------------------------------------------------------------

You can store labels and styles and use them afterwards. So you can store my table's labels and styles like this

. collect style clear

. collect style row stack, nobinder spacer

. collect style save c:\temp\MaartensTable, replace
(style from Table saved to file c:\temp\MaartensTable.stjson)

.
. collect label levels result fvrawfrequency "frequency" ///
>                             fvrawpercent   "percent", modify  

. collect label save c:\temp\MaartensTableLabels, replace
(labels from Table saved to file c:\temp\MaartensTableLabels.stjson)

And if tomorrow you want the same table but now by collgrad, you just type:

. table (var) (collgrad result ) , nototals         ///
>     statistic(fvrawfrequency married union)       ///
>     statistic(fvrawpercent married union)         ///
>     nformat(%9.3g)                                ///
>     style(c:\temp\MaartensTable.stjson, override) /// <-- new
>     label(c:\temp\MaartensTableLabels.stjson)      // <-- new

---------------------------------------------------------
             |               College graduate            
             |    Not college grad        College grad  
             |  frequency   percent   frequency   percent
-------------+-------------------------------------------
Married      |                                          
  Single     |        616      35.9         188      35.3
  Married    |       1098      64.1         344      64.7
             |                                          
Union worker |                                          
  Nonunion   |       1101      77.9         316      68.1
  Union      |        313      22.1         148      31.9
---------------------------------------------------------

Or if you like Hemanshu's design better:

. collect style clear

. collect style row stack, nobinder spacer

. collect style cell result[fvrawpercent], sformat((%s%%))

. collect style header result, level(hide)

. collect style save c:\temp\HemanshusTable, replace
(style from Table saved to file c:\temp\HemanshusTable.stjson)

.
. table (var) (collgrad result ) , nototals         ///
>     statistic(fvrawfrequency married union)       ///
>     statistic(fvrawpercent married union)         ///
>     nformat(%9.3g)                                ///
>     style(c:\temp\HemanshusTable.stjson, override) // <-- new

---------------------------------------------------
             |            College graduate        
             |   Not college grad     College grad
-------------+-------------------------------------
Married      |                                    
  Single     |     616     (35.9%)    188   (35.3%)
  Married    |    1098     (64.1%)    344   (64.7%)
             |                                    
Union worker |                                    
  Nonunion   |    1101     (77.9%)    316   (68.1%)
  Union      |     313     (22.1%)    148   (31.9%)
---------------------------------------------------