Creating customized -table- (or perhaps -collect combine-?-)

[Stephen Jenkins]

I'm using Stata 18 with a large confidential admin dataset inside a Trusted Research Environment and, to get output cleared and released to the outside world, I need to produce spreadsheets of, on this occasion, summary statistics and the raw counts used to calculate them, I'm trying to format those statistics and counts using -table- followed by -export excel- but am unable to produce what I want. I'm also wondering whether multiple -collect- followed by a -collect combine- and then -export excel- is the way to go ... but can't see how. Here follows an example with made-up data. Think of the "a_s_t" variables as Actual transition probabilities between time s and time s+1, and the "p_s_s+1" variables as corresponding Predicted probabilities. I show you what I've produced so far, and also an example of what I'm trying to achieve. Output first (with do-file code at the end should you wish to play). Thanks for any help/suggestions!

PS in my TRE dataset, there are 59 sets of transition probabilities (s = 1,...,59), and 3 types of transition probability (entry, exit, stay) with predicted and actual for each; the simplified example below has only s = 1, ..., 3), and 1 type of transition probability (actual and predicted). count(!missing(id)) is 100s of thousands of individual workers. But I figure that if the basic principles can be cracked, the code can be straightwardly scaled.

. cscript
-------------------------------------------------------------------------BEGIN

.
. input  id       a1_2       a2_3       a3_4       p1_2       p2_3       p3_4

            id       a1_2       a2_3       a3_4       p1_2       p2_3       p3_
> 4
  1.         1   .3488717    .875991   .6950234   .7459667   .1663648   .919626
> 2
  2.         2   .2668857   .2047095   .6866152   .4961259   .7437958   .693453
> 3
  3.         3   .1366463   .8927587   .9319346   .7167162   .9805113   .215402
> 6
  4.         4   .0285569   .5844658   .4548882    .859742   .7295772   .828588
> 8
  5.         5   .8689333   .3697791   .0674011   .1340756   .9011049   .044215
> 4
  6.         6   .3508549   .8506309   .3379889   .4884419   .2643649   .863037
> 8
  7.         7   .0711051   .3913819   .9748848   .8712187   .8856509   .352604
> 6
  8.         8    .323368   .1196613   .7264384   .7664683    .882112   .772039
> 9
  9.         9   .5551032   .7542434   .0454151   .2512555    .748933   .586119
> 9
 10.
. end

. * clist, noobs
.
. * format not wanted
. table (result), statistic(mean a*) statistic(count a*) ///
>                                 statistic(mean p*) statistic(count p*)

----------------------------------------------------------------------------------------------
                            |      a1_2       a2_3       a3_4       p1_2       p2_3       p3_4
----------------------------+-----------------------------------------------------------------
Mean                        |  .3278139   .5604024   .5467322   .5922234   .7002683   .5861209
Number of nonmissing values |         9          9          9          9          9          9
----------------------------------------------------------------------------------------------

.
. * better, but I want 3 rows of output and 4 columns (not 6 rows and 2 cols)
. table () (result), statistic(mean a*) statistic(count a*) ///
>                                 statistic(mean p*) statistic(count p*)

----------------------------------------------
     |      Mean   Number of nonmissing values
-----+----------------------------------------
a1_2 |  .3278139                             9
a2_3 |  .5604024                             9
a3_4 |  .5467322                             9
p1_2 |  .5922234                             9
p2_3 |  .7002683                             9
p3_4 |  .5861209                             9
----------------------------------------------

.
. * desired = something like the following
. /*
> ----------------------------------------------------------------------------
> Actual  |     Mean(a)  Count(nonmissing)   Pred    Mean(b)  Count(nonmissing)
> --------+-------------------------------------------------------------------
> a1_2    |  .3278139                  9       p1_2  .5922234             9
> a2_3    |  .5604024                  9       p2_3  .7002683             9
> a3_4    |  .5467322                  9       p3_4  .5861209             9
>
>
> */
.
. collect clear

. table () (result), statistic(mean a*) statistic(count a*) name(c1)

----------------------------------------------
     |      Mean   Number of nonmissing values
-----+----------------------------------------
a1_2 |  .3278139                             9
a2_3 |  .5604024                             9
a3_4 |  .5467322                             9
----------------------------------------------

. table () (result), statistic(mean p*) statistic(count p*) name(c2)

----------------------------------------------
     |      Mean   Number of nonmissing values
-----+----------------------------------------
p1_2 |  .5922234                             9
p2_3 |  .7002683                             9
p3_4 |  .5861209                             9
----------------------------------------------

.
. collect combine c3 = c1 c2 // does this do horizontal concatenation?
(current collection is c3)

. collect query layout

Collection: c3
      Rows: var
   Columns: result
   Table 1: 3 x 2

. collect dims

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

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

. collect levelsof result

Collection: c3
 Dimension: result
    Levels: count mean

. collect levelsof statcmd

Collection: c3
 Dimension: statcmd
    Levels: 1 2

. collect levelsof var

Collection: c3
 Dimension: var
    Levels: a1_2 p1_2 a2_3 p2_3 a3_4 p3_4

.
. * collect export table_test.xlsx, replace

do-file code:

cscript

input  id       a1_2       a2_3       a3_4       p1_2       p2_3       p3_4
        1   .3488717    .875991   .6950234   .7459667   .1663648   .9196262
        2   .2668857   .2047095   .6866152   .4961259   .7437958   .6934533
        3   .1366463   .8927587   .9319346   .7167162   .9805113   .2154026
        4   .0285569   .5844658   .4548882    .859742   .7295772   .8285888
        5   .8689333   .3697791   .0674011   .1340756   .9011049   .0442154
        6   .3508549   .8506309   .3379889   .4884419   .2643649   .8630378
        7   .0711051   .3913819   .9748848   .8712187   .8856509   .3526046
        8    .323368   .1196613   .7264384   .7664683    .882112   .7720399
        9   .5551032   .7542434   .0454151   .2512555    .748933   .5861199

end
* clist, noobs

* format not wanted
table (result), statistic(mean a*) statistic(count a*) ///
                statistic(mean p*) statistic(count p*)

* better, but I want 3 rows of output and 4 columns (not 6 rows and 2 cols)
table () (result), statistic(mean a*) statistic(count a*) ///
                statistic(mean p*) statistic(count p*)

* desired = something like the following
/*
----------------------------------------------------------------------------
Actual  |     Mean(a)  Count(nonmissing)   Pred       Mean(b)  Count(nonmissing)
--------+-------------------------------------------------------------------
a1_2    |  .3278139                  9       p1_2  .5922234             9
a2_3    |  .5604024                  9       p2_3  .7002683             9
a3_4    |  .5467322                  9       p3_4  .5861209             9


*/

collect clear
table () (result), statistic(mean a*) statistic(count a*) name(c1)
table () (result), statistic(mean p*) statistic(count p*) name(c2)

collect combine c3 = c1 c2 // does this do horizontal concatenation?
collect query layout
collect dims
collect levelsof result
collect levelsof statcmd
collect levelsof var

* collect export table_test.xlsx, replace

[Andrew Musau]

I think you can avoid problems downstream by restructuring your data. Here is one approach:

cscript

input  id       a1_2       a2_3       a3_4       p1_2       p2_3       p3_4
        1   .3488717    .875991   .6950234   .7459667   .1663648   .9196262
        2   .2668857   .2047095   .6866152   .4961259   .7437958   .6934533
        3   .1366463   .8927587   .9319346   .7167162   .9805113   .2154026
        4   .0285569   .5844658   .4548882    .859742   .7295772   .8285888
        5   .8689333   .3697791   .0674011   .1340756   .9011049   .0442154
        6   .3508549   .8506309   .3379889   .4884419   .2643649   .8630378
        7   .0711051   .3913819   .9748848   .8712187   .8856509   .3526046
        8    .323368   .1196613   .7264384   .7664683    .882112   .7720399
        9   .5551032   .7542434   .0454151   .2512555    .748933   .5861199

end

rename (a1_2 - p3_4) var=
reshape long var@1_2 var@2_3 var@3_4, i(id) j(which) string

* clist, noobs
collect clear
local collections
foreach l in a p{
    collect: table (result) if which=="`l'", statistic(mean var*) statistic(count var*) name(`l')
    local collections `collections' `l"'
}

collect combine all = `collections'
collect label levels collection a "Actual" p "Predicted"
collect label levels result count "Count(nonmissing)", modify
collect style header collection, title(hide)
collect layout (var) (collection#result)

Res.:

.
. collect layout (var) (collection#result)

Collection: all
      Rows: var
   Columns: collection#result
   Table 1: 3 x 4

---------------------------------------------------------------------
       |             Actual                        Predicted        
       |      Mean   Count(nonmissing)       Mean   Count(nonmissing)
-------+-------------------------------------------------------------
var1_2 |  .3278139                   9   .5922234                   9
var2_3 |  .5604024                   9   .7002683                   9
var3_4 |  .5467322                   9   .5861209                   9
---------------------------------------------------------------------

.

You could label var1_2 - var3_4 above differently if you wish.

[Stephen Jenkins]

Thanks, Andrew. I'll try it out. I salute the lateral thinking (wide to long), plus your intimate knowledge of how -collect- works. (My reading of the manual entry has not been very fruitful to date)

[Stephen Jenkins]

Andrew Musau An update. Your proposed solution was neat but turned out to be infeasible in the TRE with my actual data and variables. The problem was the reshape from wide to long. It was running for ~45 minutes without completion before I killed it. Of course it's possible my translation of your code caused the problem, but it might also have been the number of variables and cases, I suppose. My current idea -- to be implemented when I'm next in -- is relatively crude but gets me to what I want, and quickly. (I know the -table- commands work quickly with my TRE data and variables.) It's also extensible -- to add in additional pairs of transition probabilities.

Here's a sketch of the code:

> */
. table () (result), statistic(mean a*) statistic(count a*) ///
>          export(table_test1.xlsx, replace)

----------------------------------------------
     |      Mean   Number of nonmissing values
-----+----------------------------------------
a1_2 |  .3278139                             9
a2_3 |  .5604024                             9
a3_4 |  .5467322                             9
----------------------------------------------
(collection Table exported to file table_test1.xlsx)

. table () (result), statistic(mean p*) statistic(count p*) ///
>          export(table_test2.xlsx, replace)

----------------------------------------------
     |      Mean   Number of nonmissing values
-----+----------------------------------------
p1_2 |  .5922234                             9
p2_3 |  .7002683                             9
p3_4 |  .5861209                             9
----------------------------------------------
(collection Table exported to file table_test2.xlsx)

. 
. import excel table_test1.xlsx, clear firstrow    
(3 vars, 3 obs)

. rename A entry_actual_months

. rename Mean entry_actual_prob

. rename Numberofnonmissingvalues entry_actual_count

. ge month = _n

. save table_test1.dta, replace
file table_test1.dta saved

. import excel table_test2.xlsx, clear firstrow    
(3 vars, 3 obs)

. rename A entry_pred_months

. rename Mean entry_pred_prob

. rename Numberofnonmissingvalues entry_pred_count

. ge month = _n

. merge 1:1 month using table_test1

    Result                      Number of obs
    -----------------------------------------
    Not matched                             0
    Matched                                 3  (_merge==3)
    -----------------------------------------

. drop _merge // could also drop entry_{actual|pred}_months when code works

. list, noobs

  +--------------------------------------------------------------------------+
  | entry_..   entry_p~b   ~d_count   month   entry_..   e~l_prob   ~l_count |
  |--------------------------------------------------------------------------|
  |     p1_2   .59222341          9       1       a1_2   .3278139          9 |
  |     p2_3   .70026832          9       2       a2_3   .5604024          9 |
  |     p3_4   .58612094          9       3       a3_4   .5467322          9 |
  +--------------------------------------------------------------------------+

Thanks again for your suggestions, Andrew

[Andrew Musau]

Andrew Musau An update. Your proposed solution was neat but turned out to be infeasible in the TRE with my actual data and variables. The problem was the reshape from wide to long. It was running for ~45 minutes without completion before I killed it.

You could also achieve #2 by temporarily renaming your variables instead of reshaping. You can try:

cscript

input  id       a1_2       a2_3       a3_4       p1_2       p2_3       p3_4
        1   .3488717    .875991   .6950234   .7459667   .1663648   .9196262
        2   .2668857   .2047095   .6866152   .4961259   .7437958   .6934533
        3   .1366463   .8927587   .9319346   .7167162   .9805113   .2154026
        4   .0285569   .5844658   .4548882    .859742   .7295772   .8285888
        5   .8689333   .3697791   .0674011   .1340756   .9011049   .0442154
        6   .3508549   .8506309   .3379889   .4884419   .2643649   .8630378
        7   .0711051   .3913819   .9748848   .8712187   .8856509   .3526046
        8    .323368   .1196613   .7264384   .7664683    .882112   .7720399
        9   .5551032   .7542434   .0454151   .2512555    .748933   .5861199

end

qui ds id, not
local prefixes = ustrregexra("`r(varlist)'", "([^\d]+)([\d]+[_][\d]+)", "$1")
local prefixes: list uniq prefixes

local collections
foreach p of local prefixes{
    rename (`p'#*_*) (var#*_*)
    collect: table (result), statistic(mean var*) statistic(count var*) name(`p')
    local collections `collections' `p'
    rename (var#*_*) (`p'#*_*)
}
collect combine all = `collections'
collect label levels collection a "Actual" p "Predicted"
collect label levels result count "Count(nonmissing)", modify
collect style header collection, title(hide)
collect layout (var) (collection#result)

Res.:

. collect layout (var) (collection#result)

Collection: all
      Rows: var
   Columns: collection#result
   Table 1: 3 x 4

---------------------------------------------------------------------
       |             Actual                        Predicted        
       |      Mean   Count(nonmissing)       Mean   Count(nonmissing)
-------+-------------------------------------------------------------
var1_2 |  .3278139                   9   .5922234                   9
var2_3 |  .5604024                   9   .7002683                   9
var3_4 |  .5467322                   9   .5861209                   9
---------------------------------------------------------------------

.

[Stephen Jenkins]

thanks again. I'll try that next week when I'm back in the SRS