Export list of vars by cumulative frequency of value using putexcel, loops and scalars

[Jasper Tjaden]

Hi,

All I wanted to do is export a list of variables to an excel sheet ordered by their cumulative frequencies. All vars in varlist are codede 0 and 1. I wanted to know which vars have most ones.
My most recent attempt was 'putexcel' using loops and scalars, but I cant make it work. There must be a much easier way to do this.

This is what I would like to do
putexcel A1 = ("Variables with highest number of Errors") using "$output\var_errors.xlsx", sheet(VariabelErrors) modify
foreach var of varlist *_e {
fre `var'
matrix `var'_freq = r(valid)
putexcel A2 = ("`var1'") using "$output\var_es.xlsx", sheet(VariabelErrors) modify
putexcel B2 = ((`var1'_freq)[2,1]) using "$output\var_es.xlsx", sheet(VariabelErrors) modify
putexcel A3 = ("`var2'") using "$output\var_es.xlsx", sheet(VariabelErrors) modify
putexcel B3 = ((`var2'_freq)[2,1]) using "$output\var_es.xlsx", sheet(VariabelErrors) modify
.
.
.
and so forth until all my vars have a row cell.
}

I know I probably should be using a forvalues loop and scalars, but I can't figure it out.
******


Initially, I tried:

foreach var of varlist *_e {
egen `var'_t = total(`var') if `var'==1
}

It seems, I have the correct numbers but I am not sure How to export a list of those variables by their frequency.

I very much appreciate your help!

Jasper

[Nick Cox]

Forgetting about Excel for a moment, you need at most two new variables.

gen count = . 
gen which = ""

local i = 0 
qui foreach var of varlist *_e { 
     local ++i 
     count if `var' == 1 
     replace count = r(N) in `i' 
     replace which = "`var'" in `i' 
} 

gsort -count

list which count if count < .

Now you have a block of results, to export just once.