Export correlation coefficient matrix of the selected variables only

[Pankaj Pokhrel]

Hi all, I have a list of x variables, from x1 through x10, and a list of y variables, from y1 through y10. I am interested in reporting correlation coefficients between x1 and y1, x2 and y2, and so on through x10 and y10. I do not require other coefficients. What would be the best way to export the results? It would be wonderful if anyone could create a table with x vars on the top row and y vars on the first column and the coefficients placed diagonally on the matrix table.

You can use the following code to generate data to illustrate the required process.
Thanks!

clear all
set seed 12456789

set obs 5

forvalues i = 1/10{
    gen x`i' = runiform(0, 1)
}

forvalues i =1/10{
    gen y`i' = rpoisson(1)*400
}

[Jeff Pitblado (StataCorp)]

If you have Stata 17 or newer, and you are looking to publish your table/matrix of correlations to a document, you can use the collect suite of commands to post them to a collection, arrange them in a table, and export them to any of the supported document formats.

Here is an example based on your simulated data setup.

clear all

set seed 12456789

set obs 5

local dim 10

forvalues i = 1/`dim' {
    gen x`i' = runiform(0, 1)
}

forvalues i =1/`dim' {
    gen y`i' = rpoisson(1)*400
}

collect clear
matrix C = J(`dim',`dim',.)
forvalues i = 1/`dim'  {
    * compute correlation
    correlate x`i' y`i'
    * collect result and tag it for easy arrangement/layout
    collect get rho = (r(rho)) , tags(X[x`i'] Y[y`i'])
}

* format the correlations
collect style cell result[rho], nformat(%9.3f)
* arrange the values as requested
collect layout (Y) (X) (result[rho])

Here is the resulting table.

----------------------------------------------------------------------
    |    x1     x2    x3    x4    x5     x6     x7     x8    x9    x10
----+-----------------------------------------------------------------
y1  | 0.963
y2  |       -0.093
y3  |              0.022
y4  |                    0.187
y5  |                          0.206
y6  |                                -0.241
y7  |                                       -0.234
y8  |                                              -0.260
y9  |                                                     0.055
y10 |                                                           -0.215
----------------------------------------------------------------------

For more information about collect and the relatively new custom tables
features in Stata, see [Tables].

[Pankaj Pokhrel]

Thank you