How can I export different tables to excel using different sheets?

[Rocio Nunez]

Hi, I'm having a hard time trying to export three different tabstats to three different excel sheets using a loop so at the end I get sheet (indicador s_i) with the matrix results from local s_i... and finally the one with local w. The code actually estimates the tables correctly, however, the sheets get overwritten and in the end I only get the last tabstat on the three sheets. What should I do?



use indices_1996-2010
foreach a in s_i i w {
putexcel set indicadores_practica.xls, sheet(indicador `a') modify
local s_i "ind1_96_3 ind2_96_2 ind3_96_8 ind4_96_7 ind4_96_17 ind5_96_4"
local i "ind1_96_3_i ind2_96_2_i ind3_96_8_i ind4_96_7_i ind4_96_17_i ind5_96_4_i"
local w "ind1_96_3_w ind2_96_2_w ind3_96_8_w ind4_96_13_w ind4_96_17_w ind5_96_4_w"
foreach n in "`s_i'" "`i'" "`w'" {
tabstat `n', stat(mean sd) save

matlist r(StatTotal)
matrix results = r(StatTotal)'
matlist results
putexcel A1=matrix(results), names nformat(number_d2)
}
}

[William Lisowski]

I suspect you will find that you have a single worksheet named "indicador" rather than "indicador s_i" and so forth. If so, try the following change.

putexcel set indicadores_practica.xls, sheet("indicador `a'") modify

[Rocio Nunez]

I actually have the three worksheets but I got the same table on each single one and that's just wrong. I Believe that even though STATA can tabstat the locals, it overwrites the sheets so at the end I have the last tabstat on all of worksheets instead of just in the last one where it should belong

[Sven-Kristjan Bormann]

You have to change your code from

use indices_1996-2010
foreach a in s_i i w {
putexcel set indicadores_practica.xls, sheet(indicador `a') modify
local s_i "ind1_96_3 ind2_96_2 ind3_96_8 ind4_96_7 ind4_96_17 ind5_96_4"
local i "ind1_96_3_i ind2_96_2_i ind3_96_8_i ind4_96_7_i ind4_96_17_i ind5_96_4_i"
local w "ind1_96_3_w ind2_96_2_w ind3_96_8_w ind4_96_13_w ind4_96_17_w ind5_96_4_w"
foreach n in "`s_i'" "`i'" "`w'" {
tabstat `n', stat(mean sd) save

matlist r(StatTotal)
matrix results = r(StatTotal)'
matlist results
putexcel A1=matrix(results), names nformat(number_d2)
}
 }

to

use indices_1996-2010
local s_i "ind1_96_3 ind2_96_2 ind3_96_8 ind4_96_7 ind4_96_17 ind5_96_4"
local i "ind1_96_3_i ind2_96_2_i ind3_96_8_i ind4_96_7_i ind4_96_17_i ind5_96_4_i"
local w "ind1_96_3_w ind2_96_2_w ind3_96_8_w ind4_96_13_w ind4_96_17_w ind5_96_4_w"
foreach a in s_i i w {
putexcel set indicadores_practica.xls, sheet(indicador `a') modify

//Removed the second loop
tabstat `a', stat(mean sd) save

matlist r(StatTotal)
matrix results = r(StatTotal)'
matlist results
putexcel A1=matrix(results), names nformat(number_d2)

}

Your current code does exactly what you observe, namely writing the tabstat for "w" into every sheet. The problem is the second loop which is not needed.

[Rocio Nunez]

Thanks, I appreaciate your advice and I tried that, but unfortunatelyI got the same results.

[William Lisowski]

The following code, modified to use a standard Stata sample dataset, works as expected.

sysuse auto, clear

local v1 "price mpg"
local v2 "headroom trunk"
local v3 "weight length"

foreach a in v1 v2 v3 {
putexcel set testing.xls, sheet(indicador `a') modify

tabstat ``a'', stat(mean sd) save

matlist r(StatTotal)
matrix results = r(StatTotal)'
matlist results
putexcel A1=matrix(results), names nformat(number_d2)

}

You will see that I have made a change to the tabstat command from that given in post #4. Without that change, the code does not run.

One other possibility is that the Excel workbook you are looking at is not the Excel workbook being modified by your code. Next time, before running your code, rename or delete the existing workbook. If a new one is not created in the directory you expect to find it in, then you do not have that directory set as Stata's current working directory.

[Sven-Kristjan Bormann]

William Lisowski : You are right about the change. I simplified the code too much and missed the macro expansion in the previous second loop.