Correlations table with groups of variables in the rows

Giorgio Di Stefano

Join Date: Oct 2021

Posts: 154
#16

04 Oct 2023, 16:12

The code works except for one point of error when trying to sort.
It shows

Code:

sort group variable group not found

Perhaps it was my mistake to make myself clear. I do not have a variable group in the dataset. I am working on a pane with id, year, country, and variables. For group, I mean the desired layout variable classification in the rows. When I run the code without sorting, the code works, but I still get

Code:

collect layout (group#var_group#rowname) (colname#result[show]) (dimension group not found) Collection: within_group Rows: group#var_group#rowname Columns: colname#result[show] Your layout specification does not uniquely match any items. One or more of the following dimensions might help uniquely match items: cmdset, rowname, var_group.

I guess it has to do with sorting. Sorting by group, I get the above missing variable error

Enable GingerCannot connect to Ginger Check your internet connection
or reload the browserDisable in this text fieldRephraseRephrase current sentence4Edit in Ginger×

Last edited by Giorgio Di Stefano; 04 Oct 2023, 16:14.
Comment

Jeff Pitblado (StataCorp)

StataCorp Employee

Join Date: Mar 2014
Posts: 707

#17

04 Oct 2023, 18:42

Here is the sample example, but without using the group variable.

Code:

clear all

* Example generated by -dataex-. For more info, type help dataex
clear
input float(id year) str97 country str1 group float(fd ka ext dom pr la type1 type2 indicator1 decade) byte(decade1 decade2 decade3)
1 1992 "Country1" "A"   .524175   2.321955  .75 .7777778 .071428575        1 0 1       12.2 1990 1 0 0
1 1993 "Country1" "A"  .5846328   2.321955  .75 .7777778 .071428575        1 0 1  17.553352 1990 1 0 0
1 1994 "Country1" "A"  .6478685   2.321955  .75 .9444444 .071428575 .7912164 0 1      19.11 1990 1 0 0
1 2000 "Country1" "A"  .8414965   1.048859  .75        1  .53571427 .7782844 0 1     28.634 2000 0 1 0
1 2001 "Country1" "A"   .873997   1.048859  .75        1   .6190476 .7782844 0 1   26.41307 2000 0 1 0
1 2002 "Country1" "A"  .8985876   1.048859  .75        1   .6190476 .7661784 0 1   5.536341 2000 0 1 0
1 2003 "Country1" "A"  .9015374   1.048859  .75        1   .6190476 .7661784 0 1   5.536341 2000 0 1 0
1 2004 "Country1" "A"  .9041975   1.048859  .75        1   .6190476 .7661784 0 1   7.350179 2000 0 1 0
1 2010 "Country1" "A"   .941813   1.048859 .875        1   .8452381 .7704133 0 1   6.241802 2010 0 0 1
1 2011 "Country1" "A"   .934702   1.048859 .875        1   .8452381 .7704133 0 1      5.068 2010 0 0 1
1 2012 "Country1" "A"  .9079577   1.303478 .875        1   .8452381 .7704133 0 1      5.068 2010 0 0 1
1 2013 "Country1" "A"  .8901507  1.5580974 .875        1   .8452381 .7694158 0 1   7.929047 2010 0 0 1
2 1992 "Country2" "A"   .399736   .6099069 .875 .7777778          0 .5697657 0 1        1.6 1990 1 0 0
2 1993 "Country2" "A"   .461959  2.0673358 .875 .9444444          0 .5697657 0 1   6.996447 1990 1 0 0
2 1994 "Country2" "A"  .4953545   2.321955 .875 .9444444          0 .5697657 0 1    8.69136 1990 1 0 0
2 2000 "Country2" "A"  .7018992   2.321955    1        1   .4404762 .5697657 0 1   2.364474 2000 0 1 0
2 2001 "Country2" "A"  .7035186   2.321955    1        1   .5952381 .5697657 0 1   2.364474 2000 0 1 0
2 2002 "Country2" "A"  .6993845   2.321955    1        1   .5952381 .5457992 0 1  -.7668521 2000 0 1 0
2 2003 "Country2" "A"  .7077955   2.321955    1        1   .5952381 .5457992 0 1 -2.9592714 2000 0 1 0
2 2004 "Country2" "A"   .728983   2.321955    1        1   .5952381 .5457992 0 1  -2.950783 2000 0 1 0
2 2010 "Country2" "A"  .7894757   2.321955    1 .9444444   .9166667 .5718316 0 1      3.891 2010 0 0 1
2 2011 "Country2" "A"  .8007692   2.321955    1 .9444444   .9166667 .5718316 0 1      3.891 2010 0 0 1
2 2012 "Country2" "A"  .7593077   2.321955    1 .9444444   .9166667 .5718316 0 1 -2.5410414 2010 0 0 1
2 2013 "Country2" "A"  .7435991   2.321955    1 .9444444   .9166667 .5718316 0 1  -6.333579 2010 0 0 1
3 1992 "Country3" "B"  .5840359   2.321955    1 .7777778  .08333334 .6281998 0 1   .3835026 1990 1 0 0
3 1993 "Country3" "B"  .6147648   2.321955    1 .8888889  .08333334 .6281998 0 1   .3835026 1990 1 0 0
3 1994 "Country3" "B"   .632942   2.321955    1 .8888889  .08333334 .6260909 0 1   .8169867 1990 1 0 0
3 2000 "Country3" "B"  .7692201   2.321955    1 .8888889        .75 .6297867 0 1  1.9049623 2000 0 1 0
3 2001 "Country3" "B"  .7502941   2.321955    1 .8888889        .75 .6297867 0 1  1.9049622 2000 0 1 0
3 2002 "Country3" "B"  .7244223   2.321955    1 .8888889   .8333333 .6297867 0 1   1.283737 2000 0 1 0
3 2003 "Country3" "B"  .7401305   2.321955    1 .8888889   .8333333 .6297867 0 1 -1.3254085 2000 0 1 0
3 2004 "Country3" "B"  .7518873   2.321955    1 .8888889   .8333333 .6547069 0 1 -1.3254085 2000 0 1 0
3 2010 "Country3" "B"  .7356597   2.321955    1 .9444444          1 .6547069 0 1  1.1606808 2010 0 0 1
3 2011 "Country3" "B"  .7201834   2.321955    1 .9444444          1 .6547069 0 1  1.1606808 2010 0 0 1
3 2012 "Country3" "B"  .7283527   2.321955    1 .9444444          1 .6547069 0 1  1.1606808 2010 0 0 1
3 2013 "Country3" "B"  .7077386   2.321955    1 .9444444          1 .6547069 0 1  1.1165322 2010 0 0 1
4 1992 "Country4" "B"  .3452282   .6099069 .875 .5555556 .071428575 .6121462 0 1   2.615249 1990 1 0 0
4 1993 "Country4" "B"  .3790323  2.0673358    1 .7222222 .071428575 .6121462 0 1  1.4437795 1990 1 0 0
4 1994 "Country5" "C"  .4175321   2.321955    1 .7222222 .071428575 .6121462 0 1  4.0116615 1990 1 0 0
4 2000 "Country5" "C"   .759881   2.321955    1 .8888889   .7619048 .6121462 0 1    5.97681 2000 0 1 0
4 2001 "Country5" "C"  .7410694   2.321955    1 .8888889   .7619048 .6121462 0 1   7.402936 2000 0 1 0
4 2002 "Country5" "C"  .7281089   2.321955    1 .8888889   .8333333 .6121462 0 1      8.534 2000 0 1 0
4 2003 "Country5" "C"   .735869   2.321955    1 .8888889   .8333333 .6263371 0 1      8.534 2000 0 1 0
4 2004 "Country5" "C"  .7334849   2.321955    1 .8888889   .8333333 .6263371 0 1      8.534 2000 0 1 0
4 2010 "Country5" "C"  .7798166   2.321955    1 .9444444   .9166667 .6263371 0 1     -16.25 2010 0 0 1
4 2011 "Country5" "C"  .7637033   2.321955    1 .9444444   .9166667 .6263371 0 1 -14.330358 2010 0 0 1
4 2012 "Country5" "C"  .7608096   2.321955    1 .9444444   .9166667 .6263371 0 1          0 2010 0 0 1
4 2013 "Country5" "C"  .7455279   2.321955    1        1   .9166667  .607849 0 1   .6806767 2010 0 0 1
5 1992 "Country6" "C"  .1757301          .    .        .          .        . 0 1          . 1990 1 0 0
5 1993 "Country6" "C"  .1761862          .    .        .          .        . 1 0          . 1990 1 0 0
5 1994 "Country6" "C"  .1562944          .    .        .          .        . 1 0          . 1990 1 0 0
5 2000 "Country6" "C"  .2008834 -1.2261546    .        .          .        . 1 0          . 2000 0 1 0
5 2001 "Country6" "C" .17261477 -1.2261546    .        .          .        . 1 0          . 2000 0 1 0
5 2002 "Country6" "C" .21249928 -1.2261546    .        .          .        . 1 0          . 2000 0 1 0
5 2003 "Country6" "C"  .2206576 -.02334493    .        .          .        . 1 0          . 2000 0 1 0
5 2004 "Country6" "C" .23541167 -.02334493    .        .          .        . 1 0          . 2000 0 1 0
5 2010 "Country6" "C" .27683625   1.249751    .        .          .        . 0 1          . 2010 0 0 1
5 2011 "Country6" "C" .27487376   1.249751    .        .          .        . 0 1          . 2010 0 0 1
5 2012 "Country6" "C" .27767062   1.249751    .        .          .        . 0 1          . 2010 0 0 1
5 2013 "Country6" "C"  .2884341   1.249751    .        .          .        . 0 1          . 2010 0 0 1
6 1992 "Country7" "C" .25534174  -.1539507 .625       .5          0 .8271327 1 0          . 1990 1 0 0
6 1993 "Country7" "C" .28078705  -.1539507 .625       .5  .16666667 .8271327 1 0          . 1990 1 0 0
6 1994 "Country7" "C"  .3567602  -.1539507 .625 .5555556  .16666667 .8271327 1 0          . 1990 1 0 0
6 2000 "Country7" "C"  .4190661 -1.2261546 .625 .6111111  .23809524 .8271327 0 1          . 2000 0 1 0
6 2001 "Country7" "C"  .3738855 -1.2261546 .625 .6666667   .3928571 .8271327 0 1          . 2000 0 1 0
6 2002 "Country7" "C"   .404098 -1.2261546 .625 .6666667   .3928571 .8271327 0 1          . 2000 0 1 0
6 2003 "Country7" "C"  .4157713 -1.2261546 .875 .6666667   .3928571 .7653575 0 1          . 2000 0 1 0
6 2004 "Country7" "C"  .4085605 -1.2261546 .875 .6666667  .53571427 .7653575 0 1          . 2000 0 1 0
6 2010 "Country7" "C"  .4888775 -.02334493 .875 .6666667   .7619048 .7653575 0 1          . 2010 0 0 1
6 2011 "Country7" "C"  .4789392 -.02334493 .875 .6666667   .7619048 .7653575 0 1          . 2010 0 0 1
6 2012 "Country7" "C"  .4922497 -.02334493 .875 .6666667   .7619048 .7653575 0 1          . 2010 0 0 1
6 2013 "Country7" "C"   .508882 -.02334493  .75 .6666667   .7619048 .7653575 0 1          . 2010 0 0 1
7 1992 "Country8" "D" .56565696   2.321955    1 .9444444   .7738096 .8009957 0 1  13.403038 1990 1 0 0
7 1993 "Country8" "D"  .6403931   2.321955    1 .9444444   .7738096 .8070084 0 1       12.1 1990 1 0 0
7 1994 "Country8" "D"  .6921942   2.321955    1 .9444444   .8452381 .8070084 0 1       12.1 1990 1 0 0
7 2000 "Country8" "D"  .8552948   2.321955    1        1   .9285714 .8314556 0 1      1.806 2000 0 1 0
7 2001 "Country8" "D"  .8805222   2.321955    1        1   .9285714 .8314556 0 1   1.958802 2000 0 1 0
7 2002 "Country8" "D"  .8265579   2.321955    1        1   .9285714 .8314556 0 1      2.076 2000 0 1 0
7 2003 "Country8" "D"  .8580942   2.321955    1        1          1 .8314556 0 1      2.076 2000 0 1 0
7 2004 "Country8" "D"  .8708485   2.321955    1        1          1 .8314556 0 1      2.076 2000 0 1 0
7 2010 "Country8" "D"  .8683383   2.321955    1        1          1 .8314556 0 1   3.564247 2010 0 0 1
7 2011 "Country8" "D"  .8672714   2.321955    1 .9444444          1 .8314556 0 1   5.454876 2010 0 0 1
7 2012 "Country8" "D"  .8684435   2.321955    1 .8888889          1 .8429364 0 1   5.454876 2010 0 0 1
7 2013 "Country8" "D"  .8866873   2.321955    1 .9444444          1 .8429364 0 1   5.454876 2010 0 0 1
8 1992 "Country8" "D"  .5748482   2.321955    1 .8888889  .52380955 .9702252 0 1       14.9 1990 1 0 0
8 1993 "Country8" "D"  .6702072   2.321955    1 .8888889  .52380955 .9702252 0 1   .0375137 1990 1 0 0
8 1994 "Country8" "D"  .6812267   2.321955    1 .8888889  .52380955 .9702252 0 1      -.781 1990 1 0 0
8 2000 "Country8" "D"  .8790284   2.321955    1        1        .75 .9702252 0 1      1.463 2000 0 1 0
8 2001 "Country8" "D"   .889005   2.321955    1        1        .75 .9702252 0 1   5.542341 2000 0 1 0
8 2002 "Country8" "D"  .8849987   2.321955    1        1        .75 .9702252 0 1      5.767 2000 0 1 0
8 2003 "Country8" "D"  .8916724   2.321955    1        1        .75 .9702252 0 1      5.767 2000 0 1 0
8 2004 "Country8" "D"  .8910962   2.321955    1        1        .75 .9702252 0 1      5.767 2000 0 1 0
8 2010 "Country8" "D"  .8951228   2.321955    1        1   .8333333 .9702252 0 1       -.36 2010 0 0 1
8 2011 "Country8" "D"  .8785759   2.321955    1        1   .8333333 .9702252 0 1       -.36 2010 0 0 1
8 2012 "Country8" "D"  .8894278   2.321955    1        1   .8333333 .9702252 0 1       -.36 2010 0 0 1
8 2013 "Country8" "D"  .8901961   2.321955    1        1   .8333333 .9702252 0 1   1.298654 2010 0 0 1
end

program both_cpcorr, rclass byable(recall)
    // use -cpcorr- to post both CORRelations and COVariances;
    // ensure posted p-values matrix has the same matrix stripes as
    // the corr/cov matrices
    version 17
    syntax anything [if] [in]
    marksample touse
    cpcorr `anything' if `touse'
    tempname C p
    matrix `C' = r(C)
    matrix `p' = r(p)
    matrix colname `p' = `:colfullname `C', quote'
    matrix rowname `p' = `:rowfullname `C', quote'
    return scalar N = r(N)
    return matrix corr = `C'
    return matrix p = `p'
    cpcorr `anything' if `touse', covariance
    matrix `C' = r(C)
    return matrix cov = `C'
end

collect create wide, replace
local common type1 type2 indicator1 decade1 decade2 decade3

* Group A variables
collect, tags(var_group[A]) : both_cpcorr fd ka \ `common'
* Group B variables
collect, tags(var_group[B]) : both_cpcorr ext dom \ `common'
* Group C variables
collect, tags(var_group[C]) : both_cpcorr pr la \ `common'

* label the var_group levels
collect label levels var_group ///
    A "Whatever A" ///
    B "Whatever B" ///
    C "Whatever C"

* apply some style choices on the results of interest
collect style cell result[cov], sformat("(%s)")
collect style cell result[corr cov] , nformat(%12.2f)
collect style cell result[p] , nformat(%5.3f)
* define composite result named show
collect composite define show = corr cov p, trim
* hide result names from the header
collect style header result, level(hide)
* add note detailing the cell contents
collect notes "Correlations, (covariances), and p-values."

collect layout (var_group#rowname) (colname#result[show])

Here is the resulting table.

Code:

--------------------------------------------------------------------------------------------------------------------------
           |          type1          type2          indicator1             decade1             decade2             decade3
-----------+--------------------------------------------------------------------------------------------------------------
Whatever A |                                                                                                              
  fd       | . (0.00) 1.000 . (0.00) 1.000 -0.04 (-0.04) 0.727 -0.80 (-0.05) 0.000   0.34 (0.02) 0.003   0.38 (0.03) 0.001
  ka       | . (0.00) 1.000 . (0.00) 1.000 -0.29 (-0.97) 0.012 -0.02 (-0.00) 0.875 -0.02 (-0.00) 0.893   0.03 (0.01) 0.775
Whatever B |                                                                                                              
  ext      | . (0.00) 1.000 . (0.00) 1.000 -0.58 (-0.34) 0.000 -0.19 (-0.01) 0.105 -0.00 (-0.00) 1.000   0.18 (0.01) 0.137
  dom      | . (0.00) 1.000 . (0.00) 1.000 -0.01 (-0.01) 0.910 -0.62 (-0.02) 0.000   0.29 (0.01) 0.013   0.26 (0.01) 0.026
Whatever C |                                                                                                              
  pr       | . (0.00) 1.000 . (0.00) 1.000 -0.25 (-0.54) 0.034 -0.79 (-0.11) 0.000   0.18 (0.03) 0.140   0.54 (0.08) 0.000
  la       | . (0.00) 1.000 . (0.00) 1.000   0.29 (0.30) 0.013   0.06 (0.00) 0.639 -0.05 (-0.00) 0.697 -0.00 (-0.00) 0.981
--------------------------------------------------------------------------------------------------------------------------
Correlations, (covariances), and p-values.

Comment

Giorgio Di Stefano

Join Date: Oct 2021

Posts: 154
#18

05 Oct 2023, 16:42

Thank you very much Jeff Pitblado (StataCorp) !This one really works, and it is in the desired form. Apart from two small details, I have five "Whatever" classifications of the variables in the rows ("WhateverA", "WhateverB", "WhateverC", "WhateverD" , "WhateverE") and only the first three are printed. The other two are not. The variables are fine. The second one is that I would like to have the table printed in a wide left anticlockwise orientation, that can be imported into latex document . The rest looks fine!

Additionally, to that, I also need a similar summary statistics table in the same way, but this time with the inclusion of a factor variable, not included in this data example. In order to keep the threat, I have stated a new thread here.

https://www.statalist.org/forums/for...ws#post1729278

Thank you for any additional help you can provide.

Giorgio!
Comment

Announcement

Comment

Comment

Comment