Putexcel loop for one-way tabulation

[Cindy Li]

I have been trying to obtain some descriptive statistics for my project, using one-way tabulations. To save time, I would want to export the results from Stata to excel automatically by using Putexcel loops. However, I am new to Stata, and have no idea how I could do so.

Here is an example of a Putexcel loop I have been trying to run (but in vain):

local workattitude "workattitude_1 workattitude_2 workattitude_3"
putexcel set "Section4_Workattitude_Tab", replace
quietly foreach var in `workattitude' {
tabulate `var' if survey_string == "2f", matcell(results)
putexcel set "Section4_Workattitude_Tab", sheet ("`var'") modify
putexcel A1=("Category") B1=("Freq") C1=("Percent")
putexcel A2=`r(names)'
putexcel B2=`r(freq)'
putexcel C2=`r(freq/r(N))'
}

My goal is to:

• Export the category names, frequencies and percentages into different columns.
• Create column titles in the first row
• Export the table from tabulation for each variable into a separate spreadsheet in the same excel file

A number of errors have come up. Most recently, the errors "B2 not found" and "nothing found where expression expected" keep popping up no matter what I do. I doubt if these are the only issues with my commands though.

Could someone kindly help fix the problems, please?

Millions of thanks in advance!

[William Lisowski]

A subsequent topic addressed alternative approaches to using putexcel.

https://www.statalist.org/forums/for...-to-excel-file

[Cindy Li]

Thanks for that!

But could anyone kindly advise me on the problems with my putexcel codes?

Very much appreciated!

[Roman Mostazir]

Try the following codes. Note the excel file extension ".xlsx" which is of newer extension. I have seen in your another post #3 using ".xls" extension which is from Office 1997-2003. If that is the version of Office you are using, then correct the extension with ".xls" in the code below. -putexcel- supports both versions of Office.

AND please from next time provide data example using -dataex- and use code delimiters (READ the FAQ section).

local workattitude "workattitude_1 workattitude_2 workattitude_3"

quietly foreach var in `workattitude' {
    
    loc row = 2
    
    putexcel set "Section4_Workattitude_Tab.xlsx", sheet ("`var'") modify
    
        tab `var' if survey_string == "2f", matcell(freq) matrow(rname)
        
        loc rows = rowsof(rname)
            
            forval i = 1/`rows' {
            
                loc val = rname[`i', 1]
                loc val_lab = lab (`var') `val'
                loc rfreq = freq[`i', 1]
                loc total = r(N)
                loc percent : di %4.2f (`rfreq'/`total')*100
                
                putexcel A1=("Category") B1=("Levles") C1=("Freq") D1=("Percent")
                putexcel A`row' = "`:var lab `var''"
                putexcel B`row' = "`val_lab'"
                putexcel C`row' = `rfreq'
                putexcel D`row' = `percent'
                
                loc ++row
            }
}

[Cindy Li]

Millions of thanks, Sir!

That helps!