Help in reordering the totals to be at the top and renaming them by the sublevel name using table collect for three hierarchical levels

[Alphonse Okurut]

I have generated the table (table_collect_stata_output) below using the auto dataset and would like assistance in refining the code to obtain the table (Desired Table Output (2. table_collect_desired)) where the totals are moved to the top and are renamed by the sublevel name as indicated in the desired table. (See images attached)

sysuse auto,clear
split make , gen(make_1)
collect layout (foreign make_11) (result#var)
collect style header foreign make_11, title(hide)
table (foreign make_11), statistic(mean price mpg) statistic(sd price mpg)     totals(foreign make_11)

(See Attachements)

[Alphonse Okurut]

The totals should be renamed as the suffix to the sublevel name e.g make_11-Total and placed at the top. A generalization of code for the number of hierarchical sublevels would be helpful

[Andrew Musau]

The totals should be renamed as the suffix to the sublevel name e.g make_11-Total and placed at the top. A generalization of code for the number of hierarchical sublevels would be helpful

For some generalization, you'd need a looping code. But that depends on the variable names and whether one could spell them out in a list. Hopefully, the following simplified code (no loops) gives you an intuition.

1. To hide a title, as in your code above, you need collect style header.

collect style header foreign make_11, title(hide)

2. The total level of a dimension by default is named ".m". If you want to lead with it, just copy the order generated from collect levelsof.
3. To show only some levels of a dimension, you can use collect style autolevels.
4. To rename a level of a dimension, you can use collect label.


Putting all these together, from your example in #1:

sysuse auto,clear
collect clear
split make , gen(make_1)
collect layout (foreign make_11) (result#var)
collect style header foreign make_11, title(hide)
*INITIAL
table (foreign make_11), statistic(mean price mpg) statistic(sd price mpg)     totals(foreign make_11)
qui collect levelsof make_11
qui table (foreign make_11[`s(levels)']), statistic(mean price mpg) statistic(sd price mpg)     totals(foreign make_11)
collect style header make_11, title(hide)
collect label levels foreign 0 "Domestic-total" 1 "Foreign-total", modify
collect label levels make_11 .m "make_11-total", modify
collect style autolevels foreign 0 1
*WANTED
collect preview

Res.:

.
. *INITIAL

.
. table (foreign make_11), statistic(mean price mpg) statistic(sd price mpg)     totals(foreign make_11)

--------------------------------------------------------------------
              |            Mean                Standard deviation  
              |     Price   Mileage (mpg)      Price   Mileage (mpg)
--------------+-----------------------------------------------------
Car origin    |                                                    
  Domestic    |                                                    
    make_11   |                                                    
      AMC     |  4215.667        20.33333   485.6267        2.886751
      Buick   |  6075.286        19.14286   2257.915         3.57904
      Cad.    |  13930.33        16.33333   2313.709        4.041452
      Chev.   |  4372.333              22   911.3045        4.427189
      Dodge   |    5055.5           20.25   1236.392        6.551081
      Ford    |      4288            24.5   142.8356        4.949747
      Linc.   |  12852.33        12.66667   1175.497        1.154701
      Merc.   |  4913.833        17.16667   1239.379        3.371449
      Olds    |  6050.857        19.42857   2486.493        2.507133
      Plym.   |      4820            26.2   955.6874        5.761944
      Pont.   |  4878.833            19.5   582.4852        2.258318
      Total   |  6072.423        19.82692   3097.104        4.743297
  Foreign     |                                                    
    make_11   |                                                    
      Audi    |    7992.5              20   2400.628        4.242641
      BMW     |      9735              25          .               .
      Datsun  |    6006.5           25.75   1573.115        6.291529
      Fiat    |      4296              21          .               .
      Honda   |      5149            26.5   919.2388         2.12132
      Mazda   |      3995              30          .               .
      Peugeot |     12990              14          .               .
      Renault |      3895              26          .               .
      Subaru  |      3798              35          .               .
      Toyota  |      5122        22.33333   1193.318        7.505553
      VW      |      6021            28.5   1166.441        8.386497
      Volvo   |     11995              17          .               .
      Total   |  6384.682        24.77273   2621.915        6.611187
  Total       |                                                    
    make_11   |                                                    
      AMC     |  4215.667        20.33333   485.6267        2.886751
      Audi    |    7992.5              20   2400.628        4.242641
      BMW     |      9735              25          .               .
      Buick   |  6075.286        19.14286   2257.915         3.57904
      Cad.    |  13930.33        16.33333   2313.709        4.041452
      Chev.   |  4372.333              22   911.3045        4.427189
      Datsun  |    6006.5           25.75   1573.115        6.291529
      Dodge   |    5055.5           20.25   1236.392        6.551081
      Fiat    |      4296              21          .               .
      Ford    |      4288            24.5   142.8356        4.949747
      Honda   |      5149            26.5   919.2388         2.12132
      Linc.   |  12852.33        12.66667   1175.497        1.154701
      Mazda   |      3995              30          .               .
      Merc.   |  4913.833        17.16667   1239.379        3.371449
      Olds    |  6050.857        19.42857   2486.493        2.507133
      Peugeot |     12990              14          .               .
      Plym.   |      4820            26.2   955.6874        5.761944
      Pont.   |  4878.833            19.5   582.4852        2.258318
      Renault |      3895              26          .               .
      Subaru  |      3798              35          .               .
      Toyota  |      5122        22.33333   1193.318        7.505553
      VW      |      6021            28.5   1166.441        8.386497
      Volvo   |     11995              17          .               .
--------------------------------------------------------------------

.
. qui collect levelsof make_11

.
. qui table (foreign make_11[`s(levels)']), statistic(mean price mpg) statistic(sd price mpg)     totals(foreign make_11)

.
. collect style header make_11, title(hide)

.
. collect label levels foreign 0 "Domestic-total" 1 "Foreign-total", modify

.
. collect label levels make_11 .m "make_11-total", modify

.
. collect style autolevels foreign 0 1

.
. *WANTED

.
. collect preview

------------------------------------------------------------------------
                  |            Mean                Standard deviation  
                  |     Price   Mileage (mpg)      Price   Mileage (mpg)
------------------+-----------------------------------------------------
Car origin        |                                                    
  Domestic-total  |                                                    
    make_11-total |  6072.423        19.82692   3097.104        4.743297
    AMC           |  4215.667        20.33333   485.6267        2.886751
    Buick         |  6075.286        19.14286   2257.915         3.57904
    Cad.          |  13930.33        16.33333   2313.709        4.041452
    Chev.         |  4372.333              22   911.3045        4.427189
    Dodge         |    5055.5           20.25   1236.392        6.551081
    Ford          |      4288            24.5   142.8356        4.949747
    Linc.         |  12852.33        12.66667   1175.497        1.154701
    Merc.         |  4913.833        17.16667   1239.379        3.371449
    Olds          |  6050.857        19.42857   2486.493        2.507133
    Plym.         |      4820            26.2   955.6874        5.761944
    Pont.         |  4878.833            19.5   582.4852        2.258318
  Foreign-total   |                                                    
    make_11-total |  6384.682        24.77273   2621.915        6.611187
    Audi          |    7992.5              20   2400.628        4.242641
    BMW           |      9735              25          .               .
    Datsun        |    6006.5           25.75   1573.115        6.291529
    Fiat          |      4296              21          .               .
    Honda         |      5149            26.5   919.2388         2.12132
    Mazda         |      3995              30          .               .
    Peugeot       |     12990              14          .               .
    Renault       |      3895              26          .               .
    Subaru        |      3798              35          .               .
    Toyota        |      5122        22.33333   1193.318        7.505553
    VW            |      6021            28.5   1166.441        8.386497
    Volvo         |     11995              17          .               .
------------------------------------------------------------------------

.