Nested loop and table

[Federica Di Marcantonio]

Dear All,

I am trying to make a table containing :
a) the name of the variables and its subcategories
b) the proportion of observations of each variables (for categorical variables) or the mean (for continuous variables)
c) the p-values of the chi2 test (for categorical variables) or ttest (for continuous variables)

I have tried to modify a bit the commands provide by Nick Cox here without success.
Here my command:

 local Xcat  gender q5_1_7  potential_suc farm_typology farm_size local Zcon age_ crops  local Y dep_var1 WTU  gen Xnamecat = ""  gen Xnamecatsub = "" gen Xnamecon = "" gen Xnameconsub = "" gen Yname = ""  gen double chi2 = .  gen double pvalchi2 = .  gen double mu_1= . gen double mu_2= . gen double pvalttest = . gen cols = .   local i = 1   foreach x of local Xcat {     foreach z of local Zcon {          foreach y of local Y {             tab `x' `y', col chi2            ttest `z', by( `y') unequal             quietly {                   replace Xnamecat= "`x'" in `i'                   replace Xnamecatsub= "`x'" in `i' here i would like the label of the different categories in each variable                  replace Xnamecon= "`x'" in `i'                  replace Xnameconsub= "`x'" in `i' here i would like the label of the different categories in each variable                  replace Yname = "`y'" in `i' it would be nice to have this at the top of the table                   replace chi2 = r(chi2) in `i' for the chi2 test                  replace pvalchi2= r(p) in `i'                  replace mu_1= r(mu_1) in `i' this i would like to appear in coloumn                   replace mu_2= r(mu_2) in `i' this i would like to appear in coloumn                   replace pvalttest= r(p) in `i' this i am not sure it reports the p-values of ttest                  replace cols = r(c) in `i' here I would like the percentage or mean values of the different variable but tab does not store th           }           local ++i       }    } }   list Xname-cols

I would like to be able to construct a table like this. I post a small sample in case someone is willing to help.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte gender float age_manager byte q5_1_7 float(potential_suc crops farm_typology farm_size dep_var1 WTU)
0 32 3 1 20 2 3 3 1
0 38 4 0 0 3 3 2 1
0 36 4 0 11 4 1 2 1
0 22 1 0 2 2 1 3 1
0 53 3 0 5.5 4 3 2 1
0 46 3 0 9 4 3 1 1
0 30 4 0 2 2 2 2 1
0 25 1 0 3 2 2 3 1
0 21 3 1 34 4 3 3 1
0 27 3 0 0 4 2 2 1
0 27 4 1 141 3 4 1 1
0 31 4 2 101 4 4 3 0
0 28 2 1 44 4 3 3 0
0 35 4 1 131 2 4 2 0
0 28 3 0 15 2 2 3 1
0 25 3 1 0 4 3 3 0
0 26 3 0 8 1 3 2 0
0 31 1 0 4 4 2 3 1
0 25 4 0 6 2 2 3 1
0 49 3 0 0 4 4 3 1
0 28 1 1 4 4 2 2 0
0 24 4 2 4 2 3 3 0
1 45 4 0 2 4 3 2 1
1 24 4 1 4 4 2 3 0
1 30 4 0 2 4 2 3 1
1 18 1 0 5 4 3 3 0
1 42 4 0 4 4 3 2 0
0 30 4 1 83 4 3 3 1
0 37 4 0 10 2 3 2 1
0 31 3 1 0 2 3 3 0
0 29 4 1 102 4 3 2 0
0 27 4 0 0 4 1 3 0
0 32 2 0 0 3 3 3 0
0 34 4 0 44 3 4 3 0
0 27 3 0 10 4 2 3 1
0 16 1 0 6 4 2 1 1
0 27 1 0 3 4 3 1 0
1 29 3 0 3 4 1 3 0
1 31 4 0 0 4 1 3 0
0 18 4 0 23 3 3 1 1
0 39 3 0 0 4 4 1 1
0 23 3 0 2 2 3 3 1
0 28 4 0 1 2 3 2 0
0 22 4 1 0 3 3 1 1
0 28 1 0 8 1 4 2 0
0 18 3 2 0 4 3 1 1
0 36 4 2 123 2 4 2 0
0 33 4 2 139 3 4 2 0
0 29 4 2 101 2 4 2 0
0 24 1 0 3 2 2 3 1
0 21 2 0 31 2 3 3 1
0 21 3 2 0 4 3 3 0
1 24 1 1 4 4 3 1 0
0 24 3 0 2 2 1 3 1
0 35 4 0 5 2 3 3 1
0 20 4 0 0 4 3 1 0
0 24 4 1 0 3 3 1 1
0 38 3 0 3.5 3 2 2 1
1 26 3 0 13 4 3 3 0
0 34 4 0 16 2 2 2 1
0 36 4 0 28 3 3 1 0
1 31 4 0 16 4 2 2 1
1 51 4 0 4 4 3 3 1
0 29 4 0 9 3 3 3 1
0 22 2 2 84 3 4 2 1
0 20 3 0 7 4 3 2 1
0 28 4 0 0 3 3 3 1
0 21 3 0 12 4 2 2 1
1 26 3 0 25 4 3 3 1
0 24 3 0 3 2 3 3 0
0 23 4 0 4 2 3 3 1
1 41 3 0 3 4 3 2 0
1 30 4 0 3 2 2 3 0
0 24 2 0 5 3 3 3 1
0 50 4 2 66 2 4 3 0
1 27 3 0 3 2 2 3 0
0 28 2 1 0 3 3 3 1
1 34 4 0 0 2 3 3 0
0 56 4 0 0 2 3 1 1
1 25 1 0 7 4 3 1 1
0 30 4 0 73 4 3 3 1
0 20 4 0 15 3 3 3 0
0 28 4 2 84 3 4 1 0
0 46 3 0 0 4 4 3 1
1 45 4 0 3 4 3 2 1
0 19 2 1 60 2 4 1 1
0 40 3 0 4 2 2 3 1
0 32 3 2 47 4 4 1 1
0 38 3 1 11 3 4 3 0
0 20 3 0 30 4 3 3 1
0 37 5 0 40 2 3 3 0
0 34 3 2 0 4 3 1 0
0 22 3 0 2 2 3 3 1
0 40 3 0 5 4 3 2 0
1 35 3 2 4 1 1 3 0
0 25 3 0 2 2 3 2 0
0 35 3 0 0 4 4 1 1
1 30 4 0 2 4 2 3 0
0 30 3 0 6 4 3 2 1
0 26 4 1 9 4 3 3 1
end
label values gender gender
label def gender 0 "Male", modify
label def gender 1 "Female", modify
label values q5_1_7 q5_1_7
label def q5_1_7 1 "elementary school", modify
label def q5_1_7 2 "middle school", modify
label def q5_1_7 3 "technical school (2-3 years)", modify
label def q5_1_7 4 "high school", modify
label def q5_1_7 5 "university degree and higher", modify
label values farm_typology farm_typology
label def farm_typology 1 "Innovator", modify
label def farm_typology 2 "Diversifier", modify
label def farm_typology 3 "Environmentalist", modify
label def farm_typology 4 "Traditionalist", modify
label values farm_size farmsize
label def farmsize 1 "Small farms: EUR 2 000 – < EUR 8 000", modify
label def farmsize 2 "Medium-sized farms: EUR 8 000 – < EUR 25 000", modify
label def farmsize 3 "Large farms: EUR 25 000 – < EUR 100 000", modify
label def farmsize 4 "Very large farms: ≥ EUR 100 000", modify
label values dep_var1 dep_var1
label def dep_var1 1 "No, dot' know", modify
label def dep_var1 2 "Yes, conditional to successors", modify
label def dep_var1 3 "Yes, no conditional to successor", modify
label values WTU WTU
label def WTU 0 "Induced WTU", modify
label def WTU 1 "Spontaneous WTU", modify


------------------ copy up to and including the previous line ------------------

thanks
Federica

[Nick Cox]

Your desired table needs something more versatile than tabulate. Others are much more fluent with the new table command than I am and may be able to help.

[Federica Di Marcantonio]

Hi Nick ,
I have stata 16 and I cannot exploit all the new features of table :-(
F

[Nick Cox]

Noted belatedly, but as you will realise that makes your task even more challenging.

[Federica Di Marcantonio]

I was informed that I will get an upgrade to stata 18 ...but I do not know when. In the meantime any suggestions is appreciated. even a simple list of both % mean chi2-pvalues and ttest p-values would work for the moment. I am trying different options but I have not find a solution so far

[Nick Cox]

I would re-post the code from #1 that got mangled to improve your chances of a good reply from an expert on complicated tables, who won't be me.

[Federica Di Marcantonio]

local Xcat  gender q5_1_7  potential_suc farm_typology farm_size
local Zcon age_ crops  
local Y dep_var1 WTU  

gen Xnamecat = ""  
gen Xnamecatsub = "" 
gen Xnamecon = "" 
gen Xnameconsub = "" 
gen Yname = ""  
gen double chi2 = .  
gen double pvalchi2 = .  
gen double mu_1= . 
gen double mu_2= . 
gen double pvalttest = . 
gen cols = .   

local i = 1   

foreach x of local Xcat {
 
    ​​​​​​foreach z of local Zcon {
     
        foreach y of local Y {
   
            tab `x' `y', col chi2
  
            ttest `z', by( `y') unequal
  
            quietly { 
            replace Xnamecat= "`x'" in `i'replace
            replace Xnamecatsub= "`x'" in `i' ///here i would like the label of the different categories in each variable     
            replace Xnamecon= "`x'" in `i'                  
            replace Xnameconsub= "`x'" in `i' ///here i would like the label of the different categories in each variable                  
            replace Yname = "`y'" in `i' ///it would be nice to have this at the top of the table            
            replace chi2 = r(chi2) in `i' ///for the chi2 test 
            replace pvalchi2= r(p) in `i'                  
            replace mu_1= r(mu_1) in `i' ///this i would like to appear in column                
            replace mu_2= r(mu_2) in `i' ///this i would like to appear in column                
            replace pvalttest= r(p) in `i' ///this i am not sure it reports the p-values of ttest            
            replace cols = r(c) in `i' ///here I would like the percentage or mean values of the different variable but tab does not store them          
             }           
            local ++i      
        }   
    } 
}  
list Xname-cols

[Clyde Schechter]

I think -table1_mc-, by Mark Chatfield, from SSC, may do what you want.

[Federica Di Marcantonio]

Thanks Clyde I will try
F