Stacked tables of row percentages

[Lars Egner]

Hello,

I'm looking to create several tables concerning 1 target variable (eg. e-scooter use) , and about 4 static variables (eg. age, city, gender). Each row should represent the categories in all static variables. Each cell in each row should display the percentage of responses for the total in that group, almost like tabulate x y, row. However, I cannot use tabulate as I need several variables stacked vertically for each row. That is probably confusing, so I will illustrate my target output below:

	Use a lot	Use a little	No use
Male	33%	33%	33%
Female	50%	50%	50%
Oslo	20%	30%	50%
Berlin	10%	5%	85%

While this can be created manually, I need it in a loop, because the "target variable" will vary across about 100 variables, making 100 tables. The tables will be in the appendix of a report. Once I get it working I can generate the foreach loop myself.

I have tried using the collect and table function, but struggle in "stacking" the layers. This is my current "best functioning" code: It produces almost what I want, but does not stack the variables in the first parenthesis, it simply lists the last data collected by "collect:"

collect clear

collect: table (y23gender) (y23use) if y23gender < 3, statistic(percent, across(y23use))
collect: table (y23city) (y23use) if y23gender < 3, statistic(percent, across(y23use))

collect layout (y23gender[1 2] y23city) (y23use) (result[percent])

The above code almost works, but it does not use y23gender AND y23city as row variables, it simply use whatever "collect:" last registered, in this case gender, so the output is similar to this:

	Use a lot	Use a little	No use
Male	33%	33%	33%
Female	50%	50%	50%

Sorry about no datex example, but the dataset is a 1038 variable nightmare with Norwegian labels.

Any help that would help me stack the rows would be appreciated, thank you. I feel like I'm close, but the collect function is as tricky as it is comprehensive

Best,
Lars Even Egner

[Andrew Musau]

Sorry about no datex example, but the dataset is a 1038 variable nightmare with Norwegian labels.

It wouldn't take you a few minutes to create a reproducible example. We don't necessarily need your real data (see FAQ Advice #12).

webuse citytemp2, clear
collect clear
collect: table (region) (agecat), statistic(percent, across(agecat)) 
collect: table (division) (agecat), statistic(percent, across(agecat)) append
collect layout (region division) (agecat#result)

Res.:

. collect: table (region) (agecat), statistic(percent, across(agecat)) 

-----------------------------------------------
              |           Age category         
              |  19–29   30–34     35+    Total
--------------+--------------------------------
Census region |                                
  NE          |  27.71   50.00   22.29   100.00
  N Cntrl     |  57.04   32.39   10.56   100.00
  South       |  55.60   27.20   17.20   100.00
  West        |  62.50   28.52    8.98   100.00
  Total       |  53.03   33.05   13.91   100.00
-----------------------------------------------

. 
. collect: table (division) (agecat), statistic(percent, across(agecat)) append

-------------------------------------------------
                |           Age category         
                |  19–29   30–34     35+    Total
----------------+--------------------------------
Census division |                                
  N. Eng.       |  31.88   62.32    5.80   100.00
  Mid Atl       |  24.74   41.24   34.02   100.00
  E.N.C.        |  54.37   39.81    5.83   100.00
  W.N.C.        |  64.10   12.82   23.08   100.00
  S. Atl.       |  50.43   13.91   35.65   100.00
  E.S.C.        |  71.74   28.26           100.00
  W.S.C.        |  53.93   43.82    2.25   100.00
  Mountain      |  95.08    4.92           100.00
  Pacific       |  52.31   35.90   11.79   100.00
  Total         |  53.03   33.05   13.91   100.00
-------------------------------------------------

. 
. collect layout (region division) (agecat#result)

Collection: Table
      Rows: region division
   Columns: agecat#result
   Table 1: 17 x 4

--------------------------------------------------------
                |               Age category            
                |    19–29     30–34       35+     Total
                |  Percent   Percent   Percent   Percent
----------------+---------------------------------------
Census region   |                                       
  NE            |    27.71     50.00     22.29    100.00
  N Cntrl       |    57.04     32.39     10.56    100.00
  South         |    55.60     27.20     17.20    100.00
  West          |    62.50     28.52      8.98    100.00
  Total         |    53.03     33.05     13.91    100.00
Census division |                                       
  N. Eng.       |    31.88     62.32      5.80    100.00
  Mid Atl       |    24.74     41.24     34.02    100.00
  E.N.C.        |    54.37     39.81      5.83    100.00
  W.N.C.        |    64.10     12.82     23.08    100.00
  S. Atl.       |    50.43     13.91     35.65    100.00
  E.S.C.        |    71.74     28.26              100.00
  W.S.C.        |    53.93     43.82      2.25    100.00
  Mountain      |    95.08      4.92              100.00
  Pacific       |    52.31     35.90     11.79    100.00
  Total         |    53.03     33.05     13.91    100.00
--------------------------------------------------------

.

[Lars Egner]

Great, thank you. For some reason (agecat#result) did not produce the table, but (agecat) (result[percent]) did. For anyone interested in doing the same thing, I will paste the final code below. Now for combining these documents into a word-fil. Based on MS word's stability, it will not be pleasant.

foreach v of varlist y23aeietilgang y23aleid_sisttur_area y23aleid_formal y23aleid_avstand_start_sisttur y23aleid_sisttur_hjelm y23aleid_sisttur_kombinert y23sisttur_erstatt_all_leid y23apriv_sisttur_area y23apriv_formal y23sisttur_erstatt_all_eid y23apriv_sisttur_hjelm y23aelsyk_sisttur_area y23aelsyk_formal  y23aelsyk_sisttur_hjelm y23apastander_integrasjonn1 y23apastander_integrasjonn2 y23apastander_integrasjonn3 y23apastander_integrasjonn4 y23apastander_integrasjonn5 y23apastander_integrasjonn6 y23apastander_integrasjonn7 y23apastander_integrasjonn8 y23ageof_kjennskap y23ageof_paavirket_1 y23ageof_paavirket_2 y23ageof_paavirket_3 y23agoef_opplevdn1 y23agoef_opplevdn2 y23agoef_opplevdn3 y23agoef_opplevdn4 y23agoef_opplevdn5 y23aregel_aldersgrense y23aregel_hjelm y23aregel_flere y23aregel_promille y23aregel_forsikring y23aregel_parkering y23aregel_parkering_utleie y23anatten_brukt y23anatten_erstattet y23aendringn1 y23aendringn2 y23aendringn3 y23aendringn4 y23aendringn5 y23aendringn6 y23aendringn7 y23aendringn8 y23aendringn9 y23aendringn10 y23aendringn11 y23aendringn12 y23aendringn13 y23abilhold y23abruk_leidn1 y23abruk_leidn2 y23aleid_bruksistemnd y23abruk_privateidn1 y23abruk_privateidn2 y23apriv_bruksistemnd y23atenker_hinder y23amarked_viktign1 y23amarked_viktign2 y23amarked_viktign3 y23amarked_viktign4 y23amarked_viktign5 y23amarked_viktign6 y23amarked_viktign7 y23autvalg_regelverkn1 y23autvalg_regelverkn2 y23autvalg_regelverkn3 y23abruk_annen_deling_eier_2n1 y23abruk_annen_deling_eier_2n2 y23abruk_annen_deling_eier_2n3 y23abruk_annen_deling_eier_2n4 y23abruk_annen_deling_eier_2n5 y23aforsikring_fremtid y23aavstand_kollektiv y23aavgang_kollektiv y23aperiodebillett y23aandretransportmidlern1 y23aandretransportmidlern2 y23aandretransportmidlern3 y23aandretransportmidlern4 y23aandretransportmidlern5 y23aandretransportmidlern6 y23aandretransportmidlern7 y23aandretransportmidlern8 y23aandretransportmidlern9 y23aandretransportmidlern10{

collect clear
qui collect: table (y23akjonn) (`v') if y23akjonn < 3, statistic(percent, across(`v')) 
qui collect: table (Aldersgruppe) (`v'), statistic(percent, across(`v')) append
qui collect: table (Brukergrupper) (`v'), statistic(percent, across(`v')) append
collect: table (Område) (`v'), statistic(percent, across(`v')) append

collect layout (y23akjonn Aldersgruppe Brukergrupper Område) (`v') (result[percent])
collect export "I:\MO-AVD\5009 MikroReg\WP7 Piloter\2021 til 2023 samlet dataset\ekstratabeller samling rapport\ `v'.docx", replace
}

[Maarten Buis]

With putdocx you can automate that process. With a 100 tables that could be worthwhile.