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

[Richard Palmer-Jones]

Thanks again Andrew. Very helpful.

[Richard Palmer-Jones]

Dear Readers

Now I want to add a column to my table containing text discussing the values in the preceding rows .

"collect get" gets the results of a Stata command. I would just like to add come text under a title such as:

"comments", with entries: "differences due to .... [see text]".

Thanks for further help.

Richard

[Andrew Musau]

You can enter results as text, so you can follow the same procedure as before (see highlighted).

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,.)

local comments `" "something""something else" "' 

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 get comment = "`:word `i' of `comments''" , tags(var[var`i'] collection[Comments]) name(both)
    collect recode result rho =mean, fortags(var[var`i'])
    collect recode result comment =mean, fortags(var[var`i'])
}

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

Res.:

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

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

[Richard Palmer-Jones]

Thanks again Andrew. Works a treat.

Something puzzles me however. When I order or reorder the sequence of variable var1 var2 -> var2 var1 I loose the frequency result.

collect layout (var#result[frequency mean sd rho comment]) (collection)
// reordering variables
collect layout (var[var1 var2]#result[frequency mean sd rho comment]) (collection)
collect layout (var[var2 var2]#result[frequency mean sd rho comment]) (collection)

result

. collect layout (var#result[frequency mean sd rho comment]) (collection)
.....
----------------------------------------------
      set1   set2  corr(set1/2)    Comments   
----------------------------------------------
N       100    100                            
var1   0.77   0.75         0.28      something
     (1.00) (1.00)                            
var2   0.08   0.07         0.17 something else
     (1.00) (1.00)                            
----------------------------------------------

. 
. // reordering variables
. collect layout (var[var1 var2]#result[frequency mean sd rho comment]) (collection)

.....
----------------------------------------------
      set1   set2  corr(set1/2)    Comments   
----------------------------------------------
var1   0.77   0.75         0.28      something
     (1.00) (1.00)                            
var2   0.08   0.07         0.17 something else
     (1.00) (1.00)                            
----------------------------------------------

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

......
----------------------------------------------
      set1   set2  corr(set1/2)    Comments   
----------------------------------------------
var2   0.08   0.07         0.17 something else
     (1.00) (1.00)                            
var1   0.77   0.75         0.28      something
     (1.00) (1.00)                            
----------------------------------------------

Another puzzle is how to "bold" headers

// adding styles to headers
collect addtags header[1], fortags(var[var1])
collect addtags header[2], fortags(var[var2])
collect label levels header 1 "var1" 2 "var2"

collect style cell cell_type[header], font(,bold)
collect layout (header#var[var2 var1]#result[frequency mean sd rho comment]) (collection)

result

. collect addtags header[1], fortags(var[var1])
(6 items changed in collection both)

. collect addtags header[2], fortags(var[var2])
(6 items changed in collection both)

. collect label levels header 1 "source1" 2 "source2"

. 
. collect style cell cell_type[header], font(,bold)

. collect layout (header#var[var2 var1]#result[frequency mean sd rho comment]) (collection)

Collection: both
      Rows: header#var[var2 var1]#result[frequency mean sd rho comment]
   Columns: collection
   Table 1: 6 x 4

------------------------------------------------
        set1   set2  corr(set1/2)    Comments   
------------------------------------------------
source1                                            
  var1   0.77   0.75         0.28      something
       (1.00) (1.00)                            
source2                                            
  var2   0.08   0.07         0.17 something else
       (1.00) (1.00)                            
------------------------------------------------

Many thanks again, especially.

Richard

[Andrew Musau]

You are explicitly asking for those two levels with

var[var2 var1]

Either explicitly specify the number of observations in that list

collect layout (var[_N var2 var1]#result[frequency mean sd rho comment]) (collection)

or use collect style autolevels to specify the order and included levels:

collect style autolevels var _N var2 var1
collect layout (var#result[frequency mean sd rho comment]) (collection)

Res.:

. collect style autolevels var _N var2 var1

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

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

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

[Andrew Musau]

Another puzzle is how to "bold" headers

I missed this yesterday. Here is how you can do it:

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,.)

local comments `" "something""something else" "' 

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 get comment = "`:word `i' of `comments''" , tags(var[var`i'] collection[Comments]) name(both)
    collect recode result rho =mean, fortags(var[var`i'])
    collect recode result comment =mean, fortags(var[var`i'])
}

collect style cell result[rho], nformat(%9.3f)
collect layout (var#result[frequency mean sd rho comment]) (collection)
collect style autolevels var _N var2 var1
collect layout (var#result[frequency mean sd rho comment]) (collection)
collect addtags header[.m], fortags(var[_N])
collect addtags header[1], fortags(var[var1])
collect addtags header[2], fortags(var[var2])
collect label levels header .m "Observations" 1 "source1" 2 "source2"
collect style cell header[1 2], font(,bold)
collect style cell var[] header[.m], font(,nobold)
collect style header var[_N], level(hide)
collect style autolevels header .m 2 1
collect layout (header#var#result[frequency mean sd rho comment]) (collection)
collect export myfile, replace as(docx)

[Richard Palmer-Jones]

Thanks Andrew

I 'm sorry to be so clueless, but I can't get the two solutions to work together.

. collect style cell header[], font(,bold)

. collect style cell var[], font(,nobold)

. collect style autolevels var _N var1 var2

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

Collection: both
      Rows: header#var#result[frequency mean sd rho comment]
   Columns: collection
   Table 1: 6 x 4

-------------------------------------------------
         set1   set2  corr(set1/2)    Comments   
-------------------------------------------------
source1                                          
  var1    0.77   0.75         0.28      something
        (1.00) (1.00)                            
source2                                          
  var2    0.08   0.07         0.17 something else
        (1.00) (1.00)                            
-------------------------------------------------

. collect export myfile, replace as(docx)
(collection both exported to file myfile.docx)

"myfile.docx" has the headings in bold but also no N cases. I'm wondering if this is because "Comments" does not have the same N? The other solution explicitly listing _N doesn't work either.

. collect style cell header[], font(,bold)

. collect style cell var[], font(,nobold)

. *collect style autolevels var _N var1 var2
. collect layout (header#var[ _N var1 var2]#result[frequency mean sd rho comment]) (collection)

Collection: both
      Rows: header#var[ _N var1 var2]#result[frequency mean sd rho comment]
   Columns: collection
   Table 1: 6 x 4

-------------------------------------------------
         set1   set2  corr(set1/2)    Comments   
-------------------------------------------------
source1                                          
  var1    0.77   0.75         0.28      something
        (1.00) (1.00)                            
source2                                          
  var2    0.08   0.07         0.17 something else
        (1.00) (1.00)                            
-------------------------------------------------

[Andrew Musau]

Richard Palmer-Jones, I edited my response in #10 after posting. See if that solves your problem.