Putexcel with local macro

[Anja Heimann]

Hi,

I was wondering whether anyone could help me out with my code.
I used the svy: proportion method to get some results. These I wrote to matrices and then I would like to export them into 2 excel files - one for each dependent variable.
This is where I am stuck, the do file doesn't give me an error message, but it does not produce any excel files.
I'm new to using macros, so I think it's probably to do with that.

local xtab X Y
local depvar X Y
local indvars ib1.one ib1.two


* Get list of variable names

local ivnames ""
foreach x in `indvars' {
    local indvar "`x'"
    local varname=substr("`indvar'",5,.)
    local ivnames `ivnames' `varname'
}

* Total percentage, SE, confidence intervals

foreach i in `depvar' {
    svy: proportion "'i'"
    matrix result=r(table)

    local n=e(N)

    matrix `depvar'=J(1,9,.)
    matrix colnames `depvar'="Percentage" "SE" "Lower95%" "Upper95%" "Difference" "SE" "Pvalue"     "stars" "Base"
    matrix `depvar'[1,1]=result[1,2]*100
    matrix `depvar'[1,2]=result[2,2]*100
    matrix `depvar'[1,3]=result[5,2]*100
    matrix `depvar'[1,4]=result[6,2]*100
    matrix `depvar'[1,9]=`n'
}

* put into excel
putexcel set `xtab'.xlsx, replace
putexcel A1=matrix(`depvar'),names
local row=10

 foreach x in `ivnames' {
     putexcel A`row'=matrix(`x'),rownames
     local rows=rowsof(`x')    
     local row=`row'+`rows'+1
    }

Many thanks if anyone can spot where I made the mistake!

[Andrew Musau]

Several problems:

svy: proportion "'i'"

should be

svy: proportion `i'

grave accent and drop the double quotes.

matrix `depvar'

depvar is a local with 2 elements. You cannot define a matrix in this way as a matrix should have one name. Therefore,

matrix `i'

putexcel set `xtab'.xlsx

Same goes for the putexcel command as the local xtab holds two names.

Before writing a loop, you should go step by step and check that the individual commands are valid. A loop is just a generalization. Having said that, it appears that your goal is to export results to Excel. I would not bother programming this. Take a look at the community contributed command esttab.

ssc install esttab
help esttab

[Anja Heimann]

Thank you Andrew, when I read your reply it made sense why the code didn't work.
Unfortunately I have to use the putexcel function, so I'll still have to figure this part out. But thanks again for your help.