Table of descriptives fwith added Spearman rho

[Richard Palmer-Jones]

Dear Readers (struggling with "collect")

Its been a long time, so please excuse rough code.

I want a table of descriptives with mean and sd stacked and columns for different data sets (of the same or equivalent) variables. I then want to add a column for correlation coefficients between the equivalent variables in the different data sets.

I compute dtable for each of the data sets, and combine them.

//correlation matrix 
mat list M
matrix input M = (1, -.46, .28, -.04 \  -.46, 1, -.06, .17 \ .28, -.06, 1, -.50 \ -.04, .17, -.50, 1)

corr2data var1 var2 var1_1 var2_1, n(100) corr(M) cstorage(full) means(.77, .08, .75, .07) clear
corr *
su *

collect clear
qui dtable var1 var2, nformat(%5.2f mean sd) name(set1) replace
qui dtable var1_1 var2_1, nformat(%5.2f mean sd) name(set2) replace


collect combine both = set1 set2
collect layout (var#result) (collection)
// not what I want
collect recode var  var1_1 = var1
collect recode var var2_1 = var2

collect style header result, level(hide)
*collect style autolevels var var1 var2 var3 var6 _N, clear

collect layout (var#result[frequency mean sd]) (collection)
collect preview

// now add a column for correlation coefficients between var1-4 
collect create rho, replace
collect set rho
local dim 4
matrix C = J(`dim',1,.)
foreach i in 1 2 {
    * compute correlation
    correlate var`i' var`i'_1
    * collect result and tag it for easy arrangement/layout
    collect get rho = (r(rho)) , tags(Y[var`i'] X[var])
}

collect style cell result[rho], nformat(%9.3f)
collect layout (Y) (X) (result[rho])

// now I want to add the column of correlation coefficients (X) to both variables and 
// output a table such as
/*
         set1    set2     corr(set1/2) 
N         100    100      100      
var1 mean  0.77  0.75      0.28  
       sd   (1)  (1)
var2 mean  0.08  0.7       0.17
       sd   (1)  (1)
*/

Thanks

Richard

[Andrew Musau]

You need everything in the same collection if you want to output jointly.

collect clear
//correlation matrix 

matrix input M = (1, -.46, .28, -.04 \  -.46, 1, -.06, .17 \ .28, -.06, 1, -.50 \ -.04, .17, -.50, 1)

mat list M

corr2data var1 var2 var1_1 var2_1, n(100) corr(M) cstorage(full) means(.77, .08, .75, .07) clear
corr *
su *

collect clear
qui dtable var1 var2, nformat(%5.2f mean sd) name(set1) replace
qui dtable var1_1 var2_1, nformat(%5.2f mean sd) name(set2) replace


collect combine both = set1 set2
collect layout (var#result) (collection)
// not what I want
collect recode var  var1_1 = var1
collect recode var var2_1 = var2

collect style header result, level(hide)
*collect style autolevels var var1 var2 var3 var6 _N, clear

collect layout (var#result[frequency mean sd]) (collection)
collect preview



// now add a column for correlation coefficients between var1-4 
local dim 4
matrix C = J(`dim',1,.)
foreach i in 1 2 {
    * compute correlation
    correlate var`i' var`i'_1
    * collect result and tag it for easy arrangement/layout
    collect get rho = (r(rho)) , tags(var[var`i'] collection[corr(set1/2)]) name(both)
    collect recode result rho=mean, fortags(var[var`i'])
}

collect style cell result[rho], nformat(%9.3f)
collect layout (var#result[frequency mean sd rho]) (collection)

Res.:

. collect layout (var#result[frequency mean sd rho]) (collection)

Collection: both
      Rows: var#result[frequency mean sd rho]
   Columns: collection
   Table 1: 5 x 3

-------------------------------
      set1   set2  corr(set1/2)
-------------------------------
N       100    100             
var1   0.77   0.75         0.28
     (1.00) (1.00)             
var2   0.08   0.07         0.17
     (1.00) (1.00)             
-------------------------------

[Richard Palmer-Jones]

Thanks Andrew for the prompt response.

Could you explain where/how I could have discovered the code you modified/added? i.e. I don't understand where the "collection[corr(set1/2)]" phrase comes from (or the "name(both)" although I assume that is how the results are added to the "both" collection (but doesn't seem documented))?

Perhaps add some notes to the changed lines?

Thanks again

Richard

[Andrew Musau]

At the point you create your table, you can use

collect dims

to inspect the dimensions of your current collection. See

help collect dims

Among these dimensions is a dimension named "collection".

collect clear
//correlation matrix

matrix input M = (1, -.46, .28, -.04 \  -.46, 1, -.06, .17 \ .28, -.06, 1, -.50 \ -.04, .17, -.50, 1)

mat list M

corr2data var1 var2 var1_1 var2_1, n(100) corr(M) cstorage(full) means(.77, .08, .75, .07) clear
corr *
su *

collect clear
qui dtable var1 var2, nformat(%5.2f mean sd) name(set1) replace
qui dtable var1_1 var2_1, nformat(%5.2f mean sd) name(set2) replace


collect combine both = set1 set2
collect layout (var#result) (collection)
// not what I want
collect recode var  var1_1 = var1
collect recode var var2_1 = var2

collect style header result, level(hide)
*collect style autolevels var var1 var2 var3 var6 _N, clear

collect layout (var#result[frequency mean sd]) (collection)
collect dims

Res.:

. collect dims

Collection dimensions
Collection: both
-----------------------------------------
                   Dimension   No. levels
-----------------------------------------
Layout, style, header, label
                      cmdset   1        
                  collection   2         
                     colname   4        
                     command   1        
                      result   10        
                     statcmd   3        
                         var   6        

Style only
                border_block   4        
                   cell_type   4        
-----------------------------------------

To view its levels, use

collect levelsof

For this, see

help collect levelsof

It should be apparent that the columns of your table are the levels of this dimension.

collect clear
//correlation matrix

matrix input M = (1, -.46, .28, -.04 \  -.46, 1, -.06, .17 \ .28, -.06, 1, -.50 \ -.04, .17, -.50, 1)

mat list M

corr2data var1 var2 var1_1 var2_1, n(100) corr(M) cstorage(full) means(.77, .08, .75, .07) clear
corr *
su *

collect clear
qui dtable var1 var2, nformat(%5.2f mean sd) name(set1) replace
qui dtable var1_1 var2_1, nformat(%5.2f mean sd) name(set2) replace


collect combine both = set1 set2
collect layout (var#result) (collection)
// not what I want
collect recode var  var1_1 = var1
collect recode var var2_1 = var2

collect style header result, level(hide)
*collect style autolevels var var1 var2 var3 var6 _N, clear

collect layout (var#result[frequency mean sd]) (collection)
collect dims
collect levelsof collection

Res.:

. collect levelsof collection

Collection: both
 Dimension: collection
    Levels: set1 set2

Your desired table adds a new column to this table and therefore a new level to this dimension. I therefore name this new level with your desired name (highlighted).

collect get rho = (r(rho)) , tags(var[var`i'] collection[corr(set1/2)]) name(both)

Finally, I started by saying that you need everything in the same collection to jointly output all columns. The name of your collection at the point you create the first table is "both". So I use the -name()- option to specify the collection into which results will be saved, instead of the current collection. See the -name()- option within

help collect get