Is there way to append results from -collect- to each other

[Stephen Jenkins]

I want to calculate a common set of statistics for each of number of subgroups, append the results to each other, and export them (e.g.) to an Excel worksheet. My attempts using -table- and -collect- have not been successful. Tips please.

Consider the following

. sysuse auto
(1978 automobile data)

. table (foreign), nformat(%3.2f ) stat(percent) stat( mean weight) nototals na
> me(c1)

-------------------------------------
           |  Percent            Mean
           |            Weight (lbs.)
-----------+-------------------------
Car origin |                         
  Domestic |    70.27         3317.12
  Foreign  |    29.73         2315.91
-------------------------------------

. table (rep78), nformat(%3.2f ) stat(percent) stat( mean weight) nototals name
> (c2)

---------------------------------------------
                   |  Percent            Mean
                   |            Weight (lbs.)
-------------------+-------------------------
Repair record 1978 |                         
  1                |     2.90         3100.00
  2                |    11.59         3353.75
  3                |    43.48         3299.00
  4                |    26.09         2870.00
  5                |    15.94         2322.73
---------------------------------------------

. collect preview, name(c1)

-------------------------------------
           |  Percent            Mean
           |            Weight (lbs.)
-----------+-------------------------
Car origin |                         
  Domestic |    70.27         3317.12
  Foreign  |    29.73         2315.91
-------------------------------------

. collect preview, name(c2)

---------------------------------------------
                   |  Percent            Mean
                   |            Weight (lbs.)
-------------------+-------------------------
Repair record 1978 |                         
  1                |     2.90         3100.00
  2                |    11.59         3353.75
  3                |    43.48         3299.00
  4                |    26.09         2870.00
  5                |    15.94         2322.73
---------------------------------------------

. collect combine newcoll = c1 c2
(current collection is newcoll)

. collect preview, name(newcoll)

-------------------------------------
           |  Percent            Mean
           |            Weight (lbs.)
-----------+-------------------------
Car origin |                         
  Domestic |    70.27         3317.12
  Foreign  |    29.73         2315.91
-------------------------------------

I had been hoping that -collect combine- would have combined the 2 original collections (ideally it would have appended collection c2 to collection c1). However the combined collection seems to only pick up the most recently previewed collection

Something like:

-------------------------------------
           |  Percent            Mean
           |            Weight (lbs.)
-----------+-------------------------
Car origin |                         
  Domestic |    70.27         3317.12
  Foreign  |    29.73         2315.91
-------------------------------------
---------------------------------------------
                   |  Percent            Mean
                   |            Weight (lbs.)
-------------------+-------------------------
Repair record 1978 |                         
  1                |     2.90         3100.00
  2                |    11.59         3353.75
  3                |    43.48         3299.00
  4                |    26.09         2870.00
  5                |    15.94         2322.73
---------------------------------------------

or even ...

-------------------------------------
           |  Percent            Mean
           |            Weight (lbs.)
-----------+-------------------------
Car origin |                         
  Domestic |    70.27         3317.12
  Foreign  |    29.73         2315.91

Repair record 1978 |                         
  1                |     2.90         3100.00
  2                |    11.59         3353.75
  3                |    43.48         3299.00
  4                |    26.09         2870.00
  5                |    15.94         2322.73
---------------------------------------------

Any ideas about how to achieve this please? (Cosmetic aspects such as aligning the columns would be nice but not essential.)

I've looked through the help and [TABLES], and may have missed something ...

[Andrew Musau]

The dimension named "across" contains all levels of the rows from the appended table. Here's a way to achieve what you want:

sysuse auto, clear
collect clear
table (foreign), nformat(%3.2f ) stat(percent) stat( mean weight) nototals name(c1)
table (rep78), nformat(%3.2f ) stat(percent) stat( mean weight) nototals name(c2)
collect combine all= c1 c2
collect levelsof across
collect layout (`s(levels)') (var#result)

Res.:

. collect layout (`s(levels)') (var#result)
(dimension _hide not found)

Collection: all
      Rows: _hide foreign rep78
   Columns: var#result
   Table 1: 9 x 2

---------------------------------------------
                   |  Weight (lbs.)   Percent
                   |           Mean          
-------------------+-------------------------
Car origin         |                        
  Domestic         |        3317.12     70.27
  Foreign          |        2315.91     29.73
Repair record 1978 |                        
  1                |        3100.00      2.90
  2                |        3353.75     11.59
  3                |        3299.00     43.48
  4                |        2870.00     26.09
  5                |        2322.73     15.94
---------------------------------------------

[Bjarte Aagnes]

    collect : table (foreign), nformat(%3.2f ) stat(percent) stat( mean weight) nototals
    collect : table (rep78), nformat(%3.2f ) stat(percent) stat( mean weight) nototals append
    collect layout (foreign rep78)(result)

      Rows: foreign rep78
   Columns: result
   Table 1: 9 x 2

---------------------------------------
                   |  Percent      Mean
-------------------+-------------------
Car origin         |                  
  Domestic         |    70.27   3317.12
  Foreign          |    29.73   2315.91
Repair record 1978 |                  
  1                |     2.90   3100.00
  2                |    11.59   3353.75
  3                |    43.48   3299.00
  4                |    26.09   2870.00
  5                |    15.94   2322.73
---------------------------------------

see FAQ: https://www.stata.com/support/faqs/reporting/combine-multiple-tables

[Stephen Jenkins]

Bjarte Aagnes : wonderful -- thank you. I should have asked you about this at the UK Stata Conference this week!

And thank you too to Andrew Musau for the alternative approach!

[Bjarte Aagnes]

seems

collect layout (foreign rep78)(result)

after #2

 collect combine all= c1 c2

also work.

Playing with collect save/use:

collect clear
  
sysuse auto, clear

local opts nformat(%3.2f) stat(percent) stat(mean weight) nototals

qui foreach v in foreign rep78 {
    
    collect: table (`v'), `opts' name(`v')
   
    tempfile `v'
    collect set  `v' 
    collect save ``v''
    
    collect drop `v'
    collect use ``v''
}

collect combine default = foreign rep78

collect layout (foreign rep78)(result)

Collection: default
      Rows: foreign rep78
   Columns: result
   Table 1: 9 x 2

---------------------------------------
                   |  Percent      Mean
-------------------+-------------------
Car origin         |                   
  Domestic         |    70.27   3317.12
  Foreign          |    29.73   2315.91
Repair record 1978 |                   
  1                |     2.90   3100.00
  2                |    11.59   3353.75
  3                |    43.48   3299.00
  4                |    26.09   2870.00
  5                |    15.94   2322.73
---------------------------------------