Tabulate at least 10 variables having same value labels in one table, displaying value labels percent on colums

[Stephane Kipre]

Hello dear experienced Stata users. I am a new Stata user. I have been using it for about 5 months and I work with Stata 14.
For the moment I only rub shoulders with data management commands. And I must admit that it makes me more productive with commands like foreach, gen, sort, list…

My problem of the day is related to an output that I cannot produce despite my best efforts. Indeed I have about ten variables in my dataset (mult_i_d1_n1_1 , mult_i_d1_n1_2 ...); and these have the particularity of having the same value labels ("valide", "acceptable" and "non valide" ).
So my wish is to produce an output similar to the one in the "wish" pic. So I used the "tabulate" command which shows me all the elements I need but not in the layout I want (pic "output").

I would like to display the common labels in columns and all the variables in rows and display the percentage of occurrence there.
I've spent the last 4 hours trying to figure out if command options like "tabulate" or "summarize" could get me the layout I want but to no avail.

I hope that your expertise in this area can easily overcome my issu.

Thank you in advance for taking the time to look into my problem.

* Example generated by -dataex-. For more info, type help dataex
clear
input str34 ecole float(mult_i_d1_n1_1 mult_i_d1_n1_2 mult_i_d1_n1_3 mult_i_d1_n1_4 mult_i_d1_n1_5 mult_i_d1_n1_6 mult_i_d1_n1_7 mult_i_d1_n2_1)
"CPC DE DADJORDOUO"   100 100 100 100 100 100 100 100
"CPC DE DARITEON"      50   0 100 100 100 100 100 100
"CPC DE DIHINTEDOUO"   50   0 100 100 100 100 100 100
"CPC DE DIKOTEDOUO"    50   0 100 100 100 100 100 100
"CPC DE KERMANTEDOUO" 100   0 100 100 100 100 100 100
"CPC DE KONTODOUO"     50   0 100 100 100 100 100 100
"CPC DE SEPIDOUO"     100   0 100 100 100 100 100 100
"CPC DE YOUNDOUO"     100   0 100   0 100 100 100 100
"CPC DE NIONA"        100 100 100 100 100 100 100 100
"CPC DE GANSE"        100  25 100 100 100 100 100  50
end
label values mult_i_d1_n1_1 indic_label
label values mult_i_d1_n1_2 indic_label
label values mult_i_d1_n1_3 indic_label
label values mult_i_d1_n1_4 indic_label
label values mult_i_d1_n1_5 indic_label
label values mult_i_d1_n1_6 indic_label
label values mult_i_d1_n1_7 indic_label
label values mult_i_d1_n2_1 indic_label
label def indic_label 50 "acceptable", modify
label def indic_label 100 "valide", modify
label def indic_label 0 "non valide", modify
label def indic_label 25 "acceptable", modify
label var ecole "ECOLE" 
label var mult_i_d1_n1_1 "Distance maximale à parcourir par chaque enfant " 
label var mult_i_d1_n1_2 "Trajet sécurisant pour les élèves " 
label var mult_i_d1_n1_3 "Haute tension située sur le site" 
label var mult_i_d1_n1_4 "Lignes électriques situées sur le site" 
label var mult_i_d1_n1_5 "Risque d’inondation sur le site" 
label var mult_i_d1_n1_6 "Rivières, ravins et falaises situés sur le site" 
label var mult_i_d1_n1_7 "Trafics lourds situés sur le site" 
label var mult_i_d1_n2_1 "Age officiel pour la grande section"

[William Lisowski]

Perhaps this example code using your example data will start you in a useful direction. I cannot go further because the syntax for the table command changed substantially in Stata 16 and I have not been able to remember, or find documentation for, the syntax and all the options in the earlier versions like yours.

rename mult_* v_=
reshape long v_, i(ecole) j(varname) string
recode v_ (100=1) (25 50=2) (0=3), generate(outcome)
label define OUTCOME 1 "valide" 2 "acceptable" 3 "non valide"
label values outcome OUTCOME
label variable outcome "Outcome"
table varname outcome

---------------------------------------------------
               |              Outcome              
       varname |     valide  acceptable  non valide
---------------+-----------------------------------
mult_i_d1_n1_1 |          6           4            
mult_i_d1_n1_2 |          2           1           7
mult_i_d1_n1_3 |         10                        
mult_i_d1_n1_4 |          9                       1
mult_i_d1_n1_5 |         10                        
mult_i_d1_n1_6 |         10                        
mult_i_d1_n1_7 |         10                        
mult_i_d1_n2_1 |          9           1            
---------------------------------------------------

[William Lisowski]

Accidentally duplicated post.

[Stephane Kipre]

Hi dear William how to thank you so much your answer teaches me a lot about Stata beyond solving my concern. -I understood a little more the reshape command (in any case I discovered another application of this command) -I discovered the "table" command which is pretty much like "tabulate" except that "tabulate" displays the totals on line and the zeros. Many thanks dear William for all this. But I'm curious, with the Stata 17 "table" command for example, we could have gone so far as to display the percentages? if so, I would like to know the difference with the Stata 14 “table” command. At the office we have Stata 17 installed so I would like to learn more. Thank you again dear William.

[William Lisowski]

I was able to find documentation for the table command in Stata 14.

rename mult_* v_=
reshape long v_, i(ecole) j(varname) string
recode v_ (100=1) (25 50=2) (0=3), generate(outcome)
label define OUTCOME 1 "valide" 2 "acceptable" 3 "non valide"
label values outcome OUTCOME
label variable outcome "Outcome (percentage)"
label variable varname "Variable"
by varname, sort: egen vtot = count(outcome)
generate vpct = 100/vtot
table varname outcome, contents(sum vpct) format(%9,2f)

produces

---------------------------------------------------
               |        Outcome (percentage)       
      Variable |     valide  acceptable  non valide
---------------+-----------------------------------
mult_i_d1_n1_1 |      60,00       40,00            
mult_i_d1_n1_2 |      20,00       10,00       70,00
mult_i_d1_n1_3 |     100,00                        
mult_i_d1_n1_4 |      90,00                   10,00
mult_i_d1_n1_5 |     100,00                        
mult_i_d1_n1_6 |     100,00                        
mult_i_d1_n1_7 |     100,00                        
mult_i_d1_n2_1 |      90,00       10,00            
---------------------------------------------------

In Stata 17

rename mult_* v_=
reshape long v_, i(ecole) j(varname) string
recode v_ (100=1) (25 50=2) (0=3), generate(outcome)
label define OUTCOME 1 "valide" 2 "acceptable" 3 "non valide"
label values outcome OUTCOME
label variable outcome "Outcome (percentage)"
label variable varname "Variable"
table varname outcome, statistic(percent, across(outcome)) nototals ///
    nformat(%9,2f) sformat(%s%%)

produces

-----------------------------------------------------
                 |         Outcome (percentage)      
                 |   valide   acceptable   non valide
-----------------+-----------------------------------
Variable         |                                   
  mult_i_d1_n1_1 |   60,00%       40,00%             
  mult_i_d1_n1_2 |   20,00%       10,00%       70,00%
  mult_i_d1_n1_3 |  100,00%                          
  mult_i_d1_n1_4 |   90,00%                    10,00%
  mult_i_d1_n1_5 |  100,00%                          
  mult_i_d1_n1_6 |  100,00%                          
  mult_i_d1_n1_7 |  100,00%                          
  mult_i_d1_n2_1 |   90,00%       10,00%             
-----------------------------------------------------

[Stephane Kipre]

Thank you so much dear, it's perfect. Oh I still have a lot to learn.

[Stephane Kipre]

Hi William the syntax for STaTa 14 works perfectly. But when I run your code for STaTa 17 on another machine that has it, it produces an error message. I don't see how to get around it, it's annoying. Here is the message "string variables not allowed in layout; varname is a variable string" Can you help me to solve it ?

[Andrew Musau]

The table command changed substantially in Version 17. One of the issues was its inability to handle string variables, though I am not sure whether this was fixed. You may try updating your installation to find out.

update all

On your immediate question, you can prefix the table command with the older version.

rename mult_* v_=
reshape long v_, i(ecole) j(varname) string
recode v_ (100=1) (25 50=2) (0=3), generate(outcome)
label define OUTCOME 1 "valide" 2 "acceptable" 3 "non valide"
label values outcome OUTCOME
label variable outcome "Outcome (percentage)"
label variable varname "Variable"
by varname, sort: egen vtot = count(outcome)
generate vpct = 100/vtot
version 16: table varname outcome, contents(sum vpct) format(%9,2f)

[William Lisowski]

The version of Stata 17 on the machine you used has apparently not been updated in over a year - since sometime before the 09 Aug 2021 revision that added support for string variables in the layout. Here's how to find out what version is installed.

. about

Stata/SE 17.0 for Mac (Apple Silicon)
Revision 28 Jul 2022
Copyright 1985-2021 StataCorp LLC

Total physical memory: 8.01 GB

Stata license: Single-user  perpetual
Serial number: ............
  Licensed to: William Lisowski
               

.

Updating Stata requires administrator privileges and an internet connection, and is described in Chapter 19 of the Getting Started with Stata PDF appropriate to the system, which is included in the installation and accessible through Stata's Help menu.

As that documentation suggests, it is important to keep Stata updated.

[Stephane Kipre]

Thank you very much Andrew and William.

I understand better what happened.

Thank you for your great advices and explanations.