collect table xtsum + median

[Sebastian Kruk]

Dear Stata-users,

I made a table using the following code:

collect clear

collect style column, dups(center)
collect style cell result[mean sd  min max N sd_b min_b max_b n sd_w min_w max_w Tbar], nformat(%8.2f)

*Obtener resultados de xtsum y ponerlo en una colección usando un bucle para pasar por todas las variables
unab list : ln_co2pc_gr ln_biocap_gr ln_huella_gr ln_co2pc_1 URB_ch ln_pob_tot_gr ln_gdppc_gr ei_ch res_sh_ch ER_p_ch ENR_p_ch ERNC_p_ch ERC_p_ch
foreach var of local list {
        collect, tags(colname[`var']) : xtsum `var'
}

*Lista de las dimensiones capturadas por el prefijo collect
*Notar que la dimension "colname" ahora existe
collect dims

*Si no se hace un bucle la colección solo se carga con los resultados de la última variable
collect layout (colname) (result[mean sd  min max N sd_b min_b max_b n sd_w min_w max_w Tbar])

collect label levels result mean "Media" sd "SD" min "Mínimmo" max "Máximo" N "No. de observaciones" sd_b "SD" min_b "Mínimo" max_b "Máximo" n "No. de paneles" sd_w "SD" min_w "Mínimo" max_w "Máximo" Tbar "promedio de años", modify

But I would like to:

1) add a subtitle "Global" for columns number 1 to 5, "Transversal" for columns number 6 to 8 and "Intrínseco" for three last columns.

2) add medians after mean.

How I can do it?

Thanks in advance,

Sebastián.

[Hemanshu Kumar]

Something like this?

webuse union, clear

collect clear

collect style column, dups(center)
collect style cell result[mean sd  min max N sd_b min_b max_b n sd_w min_w max_w Tbar], nformat(%8.2f)

*Obtener resultados de xtsum y ponerlo en una colección usando un bucle para pasar por todas las variables
unab list : age-black

foreach var of local list {
    collect, tags(col1name[`var']) : xtsum `var'
    collect, tags(col2name[`var']) : sum `var', detail
    collect remap col2name[`var'] = col1name[`var'], fortags(result[p50])
}

collect addtags group[global], fortags(result[mean p50 sd min max N])
collect addtags group[transversal], fortags(result[sd_b min_b max_b n])
collect addtags group[intrin], fortags(result[sd_w min_w max_w Tbar])

collect label levels group global "Global" transversal "Transversal" intrin "Intrínseco", modify

*Lista de las dimensiones capturadas por el prefijo collect
*Notar que la dimension "col1name" ahora existe
collect dims

*Si no se hace un bucle la colección solo se carga con los resultados de la última variable
collect layout (col1name) (group#result[mean p50 sd  min max N sd_b min_b max_b n sd_w min_w max_w Tbar])

collect label levels result mean "Media" p50 "Mediana" sd "SD" min "Mínimmo" max "Máximo" N "No. de observaciones" sd_b "SD" min_b "Mínimo" max_b "Máximo" n "No. de paneles" sd_w "SD" min_w "Mínimo" max_w "Máximo" Tbar "promedio de años", modify
collect preview

which produces:

---------------------------------------------------------------------------------------------------------------------------------------
         |                         Global                                    Transversal                         Intrínseco            
         | Media Mediana   SD Mínimmo Máximo No. de observaciones   SD Mínimo Máximo No. de paneles   SD Mínimo Máximo promedio de años
---------+-----------------------------------------------------------------------------------------------------------------------------
age      | 30.43      31 6.49   16.00  46.00             26200.00 4.76  17.00  45.00        4434.00 5.22  17.68  43.93             5.91
grade    | 12.76      12 2.41    0.00  18.00             26200.00 2.45   0.00  18.00        4434.00 0.48   7.43  18.01             5.91
not_smsa |  0.28       0 0.45    0.00   1.00             26200.00 0.41   0.00   1.00        4434.00 0.18  -0.63   1.20             5.91
south    |  0.41       0 0.49    0.00   1.00             26200.00 0.47   0.00   1.00        4434.00 0.16  -0.50   1.33             5.91
union    |  0.22       0 0.42    0.00   1.00             26200.00 0.31   0.00   1.00        4434.00 0.28  -0.69   1.14             5.91
black    |  0.27       0 0.45    0.00   1.00             26200.00 0.44   0.00   1.00        4434.00 0.00   0.27   0.27             5.91
---------------------------------------------------------------------------------------------------------------------------------------

[Sebastian Kruk]

#2 is perfect. It is what I want! Thank you!

[Sebastian Kruk]

How can be made the same table #2 but without group Transversal and Intrínsico and deleting?

[Hemanshu Kumar]

Change the code after collect dims to the following:

*Si no se hace un bucle la colección solo se carga con los resultados de la última variable
collect layout (col1name) (group[global]#result[mean p50 sd  min max N])

collect style cell result[N] , nformat(%5.0f)
collect style header group, level(hide)
collect label levels result mean "Media" p50 "Mediana" sd "SD" min "Mínimmo" max "Máximo" N "No. de observaciones" sd_b "SD" min_b "Mínimo" max_b "Máximo" n "No. de paneles" sd_w "SD" min_w "Mínimo" max_w "Máximo" Tbar "promedio de años", modify

which produces:

. collect preview

-----------------------------------------------------------------
         | Media Mediana   SD Mínimmo Máximo No. de observaciones
---------+-------------------------------------------------------
age      | 30.43      31 6.49   16.00  46.00                26200
grade    | 12.76      12 2.41    0.00  18.00                26200
not_smsa |  0.28       0 0.45    0.00   1.00                26200
south    |  0.41       0 0.49    0.00   1.00                26200
union    |  0.22       0 0.42    0.00   1.00                26200
black    |  0.27       0 0.45    0.00   1.00                26200
-----------------------------------------------------------------

[Sebastian Kruk]

I find an easier way than #5

webuse union, clear

collect clear

unab list : age-black

foreach var of local list {
    collect, tags(colname[`var']) : sum `var', detail
    }

collect style header model, title(hide)
collect style column, dups(center)
collect layout (colname) (result[mean p50 Var min max N])
collect label levels result mean "Media" p50 "Mediana" Var "Varianza" min "Mínimmo" max "Máximo" N "Observaciones", modify
collect style cell, nformat(%4.2f) border(right, pattern(nil))

Which produce:

 ---------------------------------------------------------------------------                         Media Mediana Varianza Mínimmo Máximo Observaciones --------------------------------------------------------------------------- Age in current year     30.43   31.00    42.11   16.00  46.00      26200.00 Current grade completed 12.76   12.00     5.82    0.00  18.00      26200.00 1 if not SMSA            0.28    0.00     0.20    0.00   1.00      26200.00 1 if south               0.41    0.00     0.24    0.00   1.00      26200.00 1 if union               0.22    0.00     0.17    0.00   1.00      26200.00 Race black               0.27    0.00     0.20    0.00   1.00      26200.00 ---------------------------------------------------------------------------