How to fix the fracmented result table

[Nitikorn Khummasorn]

I ran a correlation command and got a fragmented table. How can I fix it into the same table

[Mead Over]

As you undoubtedly discovered, Stata's command -correlate- has a "wrap" option, but the command -pwcorr- does not. The simple answer would be to just manually cut and paste in a MS Word document. But I assume you would like a solution that could be applied repeatedly.

Here's an answer to your problem that allows me to reference Luke Gallup 's utility -frmttable-, which he describes in this Stata Journal article: Gallup, L., "A programmer's command to build formatted statistical tables," Volume 12 Number 4: pp. 655-673. This program has an extensive set of help files which supplement the journal article. It can be described and installed with the following command from inside Stata:

view net describe sg97_5, from(http://www.stata-journal.com/software/sj12-4)

*    This code recreates the output of -pwcorr- using Luke Gallup's -frmtble-.

*    Load an arbitrary example data set
clear
webuse nlsy80

*    A pairwise-correlation table with 9 variables produced by Stata's -pwcorr-
*    breaks up the output in the results window.  
pwcorr wage hours iq kww educ exper tenure age married, star(0.05) sig

*    The -pwcorr- command returns the following results.
return list

*    Save the two matrices
mat def C = r(C)
mat def sig = r(sig)

*    Define a new matrix which has a column of the -sig- matrix
*    and a blank column after every column of the C matrix
capture mat drop C_with_sig

mat define empty = J(`: colsof C',1,.)
forvalues i = 1/`: colsof C' {
    mat def C_with_sig = nullmat(C_with_sig), C[1...,`i'], sig[1...,`i'] , empty
}
*    This matrix has three times as many columns as rows, three columns for each variable.
*    For each variable, the second column contains the p-value from the matrix -sig-
*    and the third column is blank so that -frmttable- replicates the blank rows
*    between variables in the output from -pwcorr-.
matlist C_with_sig

*    Now, with its option -substat(2)-, -frmttable- produces the -pwcorr-  
*    correlation matrix without wrapping the output in the results window
frmttable, statmat(C_with_sig) substat(2) nobrket

*    With some further code, we can tweak the -frmttable output
*    to resemble more closely the output from -pwcorr-.
*    We want to (a) annotate the correlation coefficients with asterisks
*    and (b) display only the lower triangle of the triangular matrix.

*    In order to annotate the coefficients with stars,
*    -frmttable- needs a matrix containing the number of asterisks
*    to add to each cell of the table.

*    The following code generates the -stars- matrix and also suppresses
*    display of the above-diagonal elements of the triangular correlation matrix.

matrix stars = J(`: rowsof C_with_sig',`: colsof C_with_sig',0)
forvalues i = 1/`: rowsof C' {
    forvalues j = 1/`: colsof C' {
        local j3m2 = `j'*3 - 2
        matrix stars[`i',`j3m2'] = cond(`i'<=`j',0, (sig[`i',`j'] < .05 ) + (sig[`i',`j'] < 0.01) )
        matrix  C_with_sig[`i',`j3m2'] = cond(`i'<`j',., C_with_sig[`i',`j3m2'] )
        local j3m1 = `j'*3 - 1
        matrix  C_with_sig[`i',`j3m1'] = cond(`i'<=`j',., C_with_sig[`i',`j3m1'] )
    }
}

*    With its -using- option, -frmttable- writes the table to a document in MS Word's ".doc" format.
frmttable using temp.doc, statmat(C_with_sig) substat(2) nobrket ///
    annotate(stars) asymbol(*,**) landscape replace   ///
    note("* p<0.05; ** p<0.01" "p-value appears below correlation coefficient")

Here's the result as it appears in Stata's results window:

--------------------------------------------------------------------------------------
            wage   hours     iq      kww     educ    exper   tenure   age    married
--------------------------------------------------------------------------------------
 wage       1.00                                                                    
                                                                                    
                                                                                    
 hours     -0.01    1.00                                                            
            0.77                                                                    
                                                                                    
 iq        0.31**  0.07*    1.00                                                    
            0.00    0.02                                                            
                                                                                    
 kww       0.33**  0.11**  0.41**    1.00                                            
            0.00    0.00    0.00                                                    
                                                                                    
 educ      0.33**  0.09**  0.52**   0.39**   1.00                                    
            0.00    0.01    0.00     0.00                                            
                                                                                    
 exper      0.00   -0.06   -0.22**   0.02   -0.46**   1.00                          
            0.95    0.06    0.00     0.59    0.00                                    
                                                                                    
 tenure    0.13**  -0.06    0.04    0.14**   -0.04   0.24**   1.00                  
            0.00    0.09    0.20     0.00    0.27     0.00                          
                                                                                    
 age       0.16**   0.02    -0.04   0.39**   -0.01   0.50**  0.27**   1.00          
            0.00    0.45    0.18     0.00    0.71     0.00    0.00                  
                                                                                    
 married   0.14**   0.03    -0.01   0.09**   -0.06   0.11**  0.07*   0.11**   1.00  
            0.00    0.32    0.65     0.01    0.07     0.00    0.03    0.00          
                                                                                    
--------------------------------------------------------------------------------------
                                 * p<0.05; ** p<0.01
                    p-value appears below correlation coefficient

The generated MS Word formatted table saved to -temp.doc- looks like this:

Other -frmttable- options can further modify this table.