Export Correlation Matrix & Statistical Significance to Excel File

[Francois Durant]

Hi,

I'd like to export the following large correlation matrix, statistical significance and # observations to Excel. I managed to import the correlation coefficient to an Excel but I have a hard time to copy Statistical Significance & # observations. Here is the code I have so far:

* Correlation Analysis: - 1 b)
correlate Large_Varlist

putexcel A1=matrix(r(C), names) using Pearson_all.xls, replace

Anyone knows how to do that? Thanks a lot!

[Nick Cox]

The number of observations is, or should be, a single constant. P-values should be recoverable by a single calculation.

[Nick Cox]

You can push the correlation matrix into Mata, work out the P-values and push them back again into Stata. Note that the calculation of the P-values is one line in Mata, although I usually forget to specify all the elementwise operators first time around.

Here I use a dopey small example, so that there are P-values visibly much greater than 0 to check.

 
. sysuse auto, clear
(1978 Automobile Data)

. gen gpm = 100/mpg 

. pwcorr gpm weight length price in 1/10, sig 

             |      gpm   weight   length    price
-------------+------------------------------------
         gpm |   1.0000 
             |
             |
      weight |   0.9484   1.0000 
             |   0.0000
             |
      length |   0.6883   0.8585   1.0000 
             |   0.0278   0.0015
             |
       price |   0.7342   0.7112   0.6129   1.0000 
             |   0.0156   0.0211   0.0596
             |

. corr gpm weight length price in 1/10 
(obs=10)

             |      gpm   weight   length    price
-------------+------------------------------------
         gpm |   1.0000
      weight |   0.9484   1.0000
      length |   0.6883   0.8585   1.0000
       price |   0.7342   0.7112   0.6129   1.0000


. mata 
------------------------------------------------- mata (type end to exit) ---------------
: C = st_matrix("r(C)")

: n = 10 

: st_matrix("P", 2 :* ttail(n -2, abs(C):* sqrt(n-2) :/ sqrt(1 :- C:^2)))

: end 
-----------------------------------------------------------------------------------------

. mat li P 

symmetric P[4,4]
           c1         c2         c3         c4
r1          .
r2  .00002916          .
r3  .02775574   .0014714          .
r4  .01561881  .02111297  .05956039          .

Here is the code in one.

 
sysuse auto, clear
gen gpm = 100/mpg 
pwcorr gpm weight length price in 1/10, sig 
corr gpm weight length price in 1/10 
mata 
C = st_matrix("r(C)")
n = 10 
st_matrix("P", 2 :* ttail(n -2, abs(C):* sqrt(n-2) :/ sqrt(1 :- C:^2)))
end 
mat li P

All that said, P-values for correlations don't seem to be much use in practice. If you can't get a publishable P-value, your sample size is probably too small for publishable results any way.