Include a correlation matrix that includes * in an Excel file

[Francois Durant]

Hi,

I would like to include a correlation matrix in an existing Excel file (not .csv). I would like to statistical significance * to be reported as well. The format I would like is the following:

sysuse auto.dta, clear

estpost correlate weight length gear_ratio price, matrix listwise
esttab using test.csv, replace unstack not noobs compress ///
title(Panel B: Pairwise Pearsons Correlation) ///
nonumbers ///
addnote("Note: Correlation Matrix")

Thank you for your help!

[Francois Durant]

I forgot to mention that I'm open to use a command that would not be estpost/esttab to achieve that goal. Thanks a lot again!

[Francois Durant]

I also enclose the Excel file as exemple. Thanks again!

[Jorrit Gosens]

esttab can deal with this, as logn as it is supplied this info in whatever it is you have just stored.
pwcorr rather than correlate has an option to add stars for significant correlations (http://www.stata.com/manuals13/rcorrelate.pdf), but as far as i can tell you can only specify one level of significance with symbol, not several levels.
I guess you could run pwcorr three times with different levels starred, but I do not know enough about Stata coding to know if or how it is possible to then combine these three results into a single set of estimates stored for esttab to put out into a single matrix.

[Francois Durant]

Thanks for your help Jorrit! Does anyone know how to code that? Thanks!

[Oded Mcdossi]

I think csv files are good enough. It is easy to save it as .xls file within MS Excel. However, if you want to automate all the process, the only way I can think about is to save the matrix, import it back to Stata and export it to Excel.

sysuse auto, clear

estpost correlate weight length gear_ratio price, matrix listwise
esttab using test.tab, not unstack compress noobs nolines replace title(Panel B: Pairwise Pearsons Correlation) nonumbers addnote("Note: Correlation Matrix")

preserve
import delimited test1.tab, delimiter(space, collapse) clear
export excel using "results.xls", sheet("correlation") sheetmodify
restore

[Francois Durant]

That is great! Also, how do you export these results with 2 decimal place? I have tried "format" before export but it did not work. Any suggestion? Thanks again!

[Oded Mcdossi]

Under the esttab command you can format the matrix as you wish. Read the help file for more details.