Creating a table with mean, SD, and correlations

[Chris Martin]

Hello
I'm trying to build a table in which the mean, SD, and correlations between a group of continuous variables are all displayed. I need the variable name, mean (1 decimal place), SD (1 decimal place), and correlation table (two decimal places, with stars to to denote .10, .05, and .01 significance).

I found a post from 2019 with this code, which I thought I would use as a base. I modified the 2019 code so that the count is no longer displayed

sysuse auto, clear
matrix drop _all

** Set variables used in Summary and Correlation
local variables length weight displacement price

** Descriptive statistics
estpost summarize `variables'
matrix table = ( e(mean) \ e(sd) )
matrix rownames table = mean sd
matrix list table

** Correlation matrix
correlate `variables'
matrix C = r(C)
local k = colsof(C)
matrix C = C[1..`=`k'-1',.]
local corr : rownames C
matrix table = ( table \ C )
matrix list table

estadd matrix table = table

local cells table[table[mean](fmt(2) label(Mean)) table[sd](fmt(2) label(Standard Deviation))
local drop
foreach row of local corr {
    local drop `drop' `row'
    local cells `cells' table[`row'](fmt(2) drop(`drop'))
}
display "`cells'"

esttab using Report.rtf, ///
        replace ///
        noobs ///
        nonumbers ///
        compress ///
        cells("`cells'")

However, this code produces an empty table. Does anyone have suggestions for how to fix this code so it works in State 16, and also if it's feasible, how to add the asterisks for statistical significance? Thanks.

Chris

[William Lisowski]

Thanks for the very reproducible code.

For me, running your code ended with the following 5 lines in Stata's results window, so it is no surprise the report was empty.

invalid latent variable specification;
nothing found where a level variable name was expected
invalid latent variable specification;
nothing found where a level variable name was expected
(output written to ~/Documents/Report.rtf)

so it is no surprise the output was empty.

Change

local cells table[table[mean](fmt(2) label(Mean)) table[sd](fmt(2) label(Standard Deviation))

to

local cells table[mean](fmt(2) label(Mean)) table[sd](fmt(2) label(Standard Deviation))

and it produces a RTF file with a decent-looking table that I think is what you hope for.

[Chris Martin]

Thank you! Is there a simple way to have asterisks added for statistical significance in the correlation table, or it that something I should do manually?

[William Lisowski]

In theory it is possible. In practice I do not use esttab/estout enough to be able to make it happen.

In the same way that correlate produces a table of correlations that are returned in r(C), pwcorr produces a table of pairwise correlations (which will be the same as those from correlate if you have no missing values in your data) and additionally returns the significance level of each in r(sig). Those presumably could be used in conjunction with various esttab/estout options to append stars to each correlation.

. sysuse auto, clear
(1978 Automobile Data)

. local variables length weight displacement price

. pwcorr `variables', sig

             |   length   weight displa~t    price
-------------+------------------------------------
      length |   1.0000 
             |
             |
      weight |   0.9460   1.0000 
             |   0.0000
             |
displacement |   0.8351   0.8949   1.0000 
             |   0.0000   0.0000
             |
       price |   0.4318   0.5386   0.4949   1.0000 
             |   0.0001   0.0000   0.0000
             |

. 
. matrix list r(C)

symmetric r(C)[4,4]
                    length        weight  displacement         price
      length             1
      weight     .94600864             1
displacement     .83514003     .89489577             1
       price     .43183124     .53861146     .49494338             1

. matrix list r(sig)

symmetric r(sig)[4,4]
                    length        weight  displacement         price
      length             0
      weight     5.862e-37             0
displacement     2.268e-20     6.165e-27             0
       price      .0001222     7.416e-07     7.369e-06             0

See the output of help pwcorr for more details.

If you can't get esttab to do what you need, I suggest a fresh post to a new topic with a title like "adding significance stars to a custom esttab report" and include your corrected example code, updated to use pwcorr. That may draw the attention of esttab experts here. Or else, as you suggest, if this is a one-time operation, put them in by hand. I will add that in the areas I work in, I don't commonly see significance stars on correlation tables.