Exporting multiple correlation coefficient tables simultaneously to Excel File

[Florence Ijagbone]

Hello Statalist,

Please, how do I export multiple correlation coefficient tables to Excel file? I have used the command below to generate correlation coefficients for six zones, but I have not been able to export the output to Excel file using asdocx as the attempt to install asdocx was not successful (r601).

by zone, sort: pwcorr loglastpay mainactivitysector1 mainactivitysector2 mainactivitysector3 mainactivitysector4 mainactivitysector5 mainactivitysector6 mainactivitysector7 mainactivitysector8 mainactivitysector9 mainactivitysector10 mainactivitysector11 mainactivitysector12 mainactivitysector13 mainactivitysector14, star(5)

Thank you.

Florence Ijagbone

[Jeff Pitblado (StataCorp)]

If you have Stata 17 or newer, you can use the new collect commands to get results into a collect, style and arrange the collected results into a table, and publish (export) the table to an Excel file.

Here is an example using some publicly available data.

webuse nhanes2l

* collect works with the -by- prefix;
* collects results from -pwcorr- for each -by- group;
* use function -vech()- ignore the upper triangle (duplicates)
by agegrp, sort : ///
    collect corr=vech(r(C)) p=vech(r(sig)) : ///
    pwcorr vitaminc zinc copper lead, sig

* attach stars to the correlations
collect stars p .05 "*", attach(corr) shownote

* style choices
collect style cell result[corr], nformat(%6.3f)
collect style header agegrp, title(label) level(label)
collect style header result[corr], level(hide)
collect style column, dups(center)

* arrange the correlations into group-level tables
collect layout (rowname) (roweq#result[corr]) (agegrp)

* publish tables to an Excel file
collect export corr.xlsx, replace

Here are the resulting tables.

Age group = 20–29
---------------------------------------------------------------------------------------------------------
                        | Serum vitamin C (mg/dL) Serum zinc (mcg/dL) Serum copper (mcg/dL) Lead (mcg/dL)
------------------------+--------------------------------------------------------------------------------
Serum vitamin C (mg/dL) |                   1.000                                                        
Serum zinc (mcg/dL)     |                  -0.022               1.000                                    
Serum copper (mcg/dL)   |                  -0.037             -0.231*                 1.000              
Lead (mcg/dL)           |                 -0.149*              0.197*               -0.202*         1.000
---------------------------------------------------------------------------------------------------------
* p<.05

Age group = 30–39
---------------------------------------------------------------------------------------------------------
                        | Serum vitamin C (mg/dL) Serum zinc (mcg/dL) Serum copper (mcg/dL) Lead (mcg/dL)
------------------------+--------------------------------------------------------------------------------
Serum vitamin C (mg/dL) |                   1.000                                                        
Serum zinc (mcg/dL)     |                  -0.045               1.000                                    
Serum copper (mcg/dL)   |                  -0.047             -0.147*                 1.000              
Lead (mcg/dL)           |                 -0.248*              0.129*               -0.121*         1.000
---------------------------------------------------------------------------------------------------------
* p<.05

Age group = 40–49
---------------------------------------------------------------------------------------------------------
                        | Serum vitamin C (mg/dL) Serum zinc (mcg/dL) Serum copper (mcg/dL) Lead (mcg/dL)
------------------------+--------------------------------------------------------------------------------
Serum vitamin C (mg/dL) |                   1.000                                                        
Serum zinc (mcg/dL)     |                   0.014               1.000                                    
Serum copper (mcg/dL)   |                  -0.041             -0.092*                 1.000              
Lead (mcg/dL)           |                 -0.219*              0.141*                -0.046         1.000
---------------------------------------------------------------------------------------------------------
* p<.05

Age group = 50–59
---------------------------------------------------------------------------------------------------------
                        | Serum vitamin C (mg/dL) Serum zinc (mcg/dL) Serum copper (mcg/dL) Lead (mcg/dL)
------------------------+--------------------------------------------------------------------------------
Serum vitamin C (mg/dL) |                   1.000                                                        
Serum zinc (mcg/dL)     |                  -0.007               1.000                                    
Serum copper (mcg/dL)   |                   0.001             -0.084*                 1.000              
Lead (mcg/dL)           |                 -0.161*               0.045                -0.042         1.000
---------------------------------------------------------------------------------------------------------
* p<.05

Age group = 60–69
---------------------------------------------------------------------------------------------------------
                        | Serum vitamin C (mg/dL) Serum zinc (mcg/dL) Serum copper (mcg/dL) Lead (mcg/dL)
------------------------+--------------------------------------------------------------------------------
Serum vitamin C (mg/dL) |                   1.000                                                        
Serum zinc (mcg/dL)     |                  -0.016               1.000                                    
Serum copper (mcg/dL)   |                  -0.003             -0.057*                 1.000              
Lead (mcg/dL)           |                 -0.155*               0.003                -0.008         1.000
---------------------------------------------------------------------------------------------------------
* p<.05

Age group = 70+
---------------------------------------------------------------------------------------------------------
                        | Serum vitamin C (mg/dL) Serum zinc (mcg/dL) Serum copper (mcg/dL) Lead (mcg/dL)
------------------------+--------------------------------------------------------------------------------
Serum vitamin C (mg/dL) |                   1.000                                                        
Serum zinc (mcg/dL)     |                  -0.011               1.000                                    
Serum copper (mcg/dL)   |                  -0.027              -0.010                 1.000              
Lead (mcg/dL)           |                 -0.145*               0.016                 0.047         1.000
---------------------------------------------------------------------------------------------------------
* p<.05

Here is a screen-shot of the spreadsheet using LibreOffice on my Mac. I resized to optimal column widths within LibreOffice.

[Florence Ijagbone]

Thank you Jeff.

I have used the suggested code . It did not group the result, it showed errors in some parts and no result in the Excel sheet. Perhaps, I did not use the code correctly. The result is presented below:


* collect works with the -by- prefix;

.
. * collects results from -pwcorr- for each -by- group;

.
. * use function -vech()- ignore the upper triangle (duplicates)

.
. by zone, sort : ///
/ is not a valid command name
r(199);

.
. collect corr=vech(r(C)) p=vech(r(sig)) : ///
invalid syntax
r(198);

.
. pwcorr loglastpay mainactivitysector1 mainactivitysector2 mainactivitysector3, sig

loglas~y maina~r1 maina~r2 maina~r3

loglastpay 1.0000


mainactiv~r1 0.0380 1.0000
0.1201

mainactiv~r2 0.0601 -0.0185 1.0000
0.0138 0.4490

mainactiv~r3 -0.0872 -0.0731 -0.0142 1.0000
0.0003 0.0027 0.5617


.
.
.
. * attach stars to the correlations

.
. collect stars p .05 "*", attach(corr) shownote

.
.
.
. * style choices

.
. collect style cell result[corr], nformat(%6.3f)

.
. collect style header zone, title(label) level(label)

.
. collect style header result[corr], level(hide)

.
. collect style column, dups(center)

.
.
.
. * arrange the correlations into group-level tables

.
. collect layout (rowname) (roweq#result[corr]) (zone)

Collection: default
Rows: rowname
Columns: roweq#result[corr]
Tables: zone

Your layout specification does not identify any items.

.
.
.
. * publish tables to an Excel file

.
. collect export corr.xlsx, replace
(collection default exported to file corr.xlsx)



I also present below the sample data using dataex:

* Example generated by -dataex-.    For more info, type help    dataex
clear
input byte zone float loglastpay    byte(mainactivitysector1    mainactivitysector2    mainactivitysector3)
1  6.684612 1 0 0
1  9.392662 0 0 0
1  9.615806 0 0 0
1  9.903487 0 0 0
1  9.680344 0 0 0
1 10.757903 0 0 0
1  8.517193 0 0 0
1 10.308952 0 0 0
1  10.12663 0 0 0
1  9.903487 0 0 0
1 11.561716 0 0 0
1  10.83958 0 0 0
1  9.798127 0 0 0
1  11.88449 0 0 0
1  9.903487 0 0 0
1 10.203592 0 0 0
1 11.066638 0 0 0
1  7.090077 0 0 0
1  8.987197 0 0 0
1  8.517193 0 0 0
1  7.600903 0 0 0
1  9.903487 1 0 0
1  9.903487 0 0 0
1 9.2103405 0 0 0
1   10.1849 0 0 0
1 10.308952 0 0 0
1   6.55108 0 0 0
1 10.819778 0 0 0
1 10.463103 0 0 0
1  7.824046 0 0 0
1  10.08581 0 0 0
1  6.684612 0 0 0
1  9.615806 0 0 0
1 9.2103405 0 0 1
1  8.160519 0 0 0
1  7.600903 0 0 0
1  10.91509 0 0 0
1  9.615806 0 0 0
1  8.699514 0 0 0
1  10.25766 0 0 0
1 10.757903 0 0 0
1 10.308952 0 0 0
1  9.903487 0 0 0
1  6.214608 0 0 0
1  9.392662 0 0 0
1 10.645425 0 0 0
1  8.612503 0 0 0
1  9.615806 0 0 0
1 9.2103405 0 0 0
1  7.824046 0 0 0
1  9.903487 0 0 0
1  9.047821 0 0 0
1  8.006368 1 0 0
1   9.92818 0 0 0
1 10.878047 0 0 0
1  9.903487 0 0 0
1  10.08581 0 0 0
1 10.404263 0 0 0
1  9.680344 0 0 0
1  8.517193 0 0 1
1 11.225244 0 0 0
1  10.12663 0 0 0
1 11.314474 0 0 0
1  10.79655 0 0 0
1  9.392662 0 0 0
1 10.596635 1 0 0
1 10.308952 0 0 0
1  9.059518 0 0 0
1 10.106428 1 0 0
1 9.2103405 0 0 0
1 10.645425 0 0 0
1  10.91509 0 0 0
1 10.878047 0 0 0
1  10.12663 0 0 0
1 9.2103405 0 0 0
1  9.305651 0 0 0
1  7.600903 0 0 0
1 11.184422 0 0 0
1  6.214608 0 0 0
1 10.463103 0 0 0
1  8.517193 0 0 0
1 10.308952 0 0 0
1 10.341743 0 0 0
1 11.221517 0 0 0
1  10.89674 0 0 0
1  10.12663 0 0 0
1   10.1105 0 0 0
1  6.214608 0 0 0
1  9.392662 0 0 0
1  9.615806 0 0 0
1  9.615806 0 0 0
1  8.987197 0 0 0
1  7.090077 0 0 0
1 10.714417 0 0 0
1  8.294049 0 0 0
1  9.560997 0 0 0
1 10.645425 0 0 0
1 10.868568 0 0 0
1 10.968198 0 0 0
1  6.214608 0 0 0
end

Kindly help out.

[Attaullah Shah]

@Florence Ijagbone
In #1, you motioned that you installed asdocx but was unsuccessful. It is important to note that asdocx is a premium software available at a cost of $9.99. You can explore more details related to asdocx here. If your installation attempt was through the SSC, please be aware that asdocx is not available on this platform. It can be installed from the asdocx.com once you have made the purchase.

[Jeff Pitblado (StataCorp)]

The code in post #2 should be copied into a do-file, then run that do-file. The line continuation comments "///" cannot be used in the interactive command window.

[Florence Ijagbone]

Thank you Attaullah.

Concerning asdocx, what I posted on Stata and the output are shown below as requested:

. ssc install asdocx
ssc install: "asdocx" not found at SSC, type search asdocx
(To find all packages at SSC that start with a, type ssc describe a)
r(601);

[Florence Ijagbone]

I have noted that I have to buy asdocx to install it in Stata.

Thank you.