Stata programming issue

[nizam khan]

Dear Stata user,
When I run the following program (PROGRAM A) it worked fine. But when run the same program within (See PROGRAM B)

cap prog drop WASH
prog def WASH
....PROGRAM A

it gives me the following error message while reading the line at the end "putexcel A`row'=(variable) B`row'=(b) C`row'=(ll) D`row'=(ul) E`row'=(size) F`row'=(wsize) G`row'=(sd) H`row'=(se) I`row'=(deft)" :

A: invalid cell name
r(198);

Any idea how could I solve this problem?
Thanks for your time.

Nizam

*************************
****PROGRAM A*****
*************************
use "wash_analytical_wgts",clear
svyset [pw=hh_wgt], psu (hhea) strata (project)

global WASH improved_water correct_watertreat boiling bleaching filtering time_water solar_disinfect ///
improved_sanitation open_defecation proper_handwashing

putexcel set "WASH.xlsx", sheet ("COMBINED") replace
putexcel A1=("variable") B1=("mean") C1=("min") D1=("max") E1=("size") F1=("wsize") G1=("sd") H1=("se") I1=("deft")

local row = 2

foreach x of varlist $WASH {
svy: mean `x'
ret list
ereturn list
matlist r(table)
mat basic = r(table)
matlist basic
scalar b= basic[1,1]
scalar se=basic[2,1]
scalar ll=basic[5,1]
scalar ul=basic[6,1]
scalar wsize=round(e(N_pop),1)
scalar size=e(N)
scalar variable=e(varlist)

estat effects, deff deft meff meft
return list
mat deft=r(deft)
scalar deft=deft[1,1]

estat sd
return list
mat sd=r(sd)
scalar sd=sd[1,1]

putexcel A`row'=(variable) B`row'=(b) C`row'=(ll) D`row'=(ul) E`row'=(size) F`row'=(wsize) G`row'=(sd) H`row'=(se) I`row'=(deft)

local row=`row' + 1
scalar drop _all
}

***********************************
**PROGRAM B
*************************************
use "wash_analytical_wgts",clear
svyset [pw=hh_wgt], psu (hhea) strata (project)

global WASH improved_water correct_watertreat boiling bleaching filtering time_water solar_disinfect ///
improved_sanitation open_defecation proper_handwashing

putexcel set "WASH.xlsx", sheet ("COMBINED") replace
putexcel A1=("variable") B1=("mean") C1=("min") D1=("max") E1=("size") F1=("wsize") G1=("sd") H1=("se") I1=("deft")

local row = 2

cap prog drop WASH
prog def WASH
foreach x of varlist $WASH {
svy: mean `x'
ret list
ereturn list
matlist r(table)
mat basic = r(table)
matlist basic
scalar b= basic[1,1]
scalar se=basic[2,1]
scalar ll=basic[5,1]
scalar ul=basic[6,1]
scalar wsize=round(e(N_pop),1)
scalar size=e(N)
scalar variable=e(varlist)

estat effects, deff deft meff meft
return list
mat deft=r(deft)
scalar deft=deft[1,1]

estat sd
return list
mat sd=r(sd)
scalar sd=sd[1,1]

putexcel A`row'=(variable) B`row'=(b) C`row'=(ll) D`row'=(ul) E`row'=(size) F`row'=(wsize) G`row'=(sd) H`row'=(se) I`row'=(deft)

local row=`row' + 1
scalar drop _all
}
end

WASH

[Roger Chu]

Hello Nizam,

Your post is a little hard to follow. Using the CODE tags may help.

But, if I am reading your post correctly, I believe you're running into an issue with local macros. Your error message points to the fact that your putexcel command is only reading "A" as the cell, not "A2." Basically, your local row is not defined within the subprogram WASH.

Local macros are defined only within the program or do file for which you are running (hence "local").

The local row is defined outside of WASH, in program B. However, when you run WASH, WASH looks for a local that exists within WASH. Since row is a local that only exists within program B, WASH does not see anything for local row, and thus prints out nothing in the place of row. So, in essence, your putexcel line becomes

putexcel A=(variable) B=(b) C=(ll) D=(ul) E=(size) F=(wsize) G=(sd) H=(se) I=(deft)

This behavior for macros (automatically printing nothing instead of generating an error when you call an non-existent macro) is built into Stata. It allows for flexibility in programming, but is unforgiving if you ever lose track of your macros.


You should use program arguments to pass your row to your subprogram. See.

help args
help syntax

[Clyde Schechter]

In the first version, you define -local row = 2- and then you use it later on in the same program.

In the second eversion, you define -local row = 2-, but then you call program WASH. Within this (sub) program you never initialize local macro row, so it is undefined when you get to the -putexcel- command. It is important, when using local macros, to remember that they are only defined within their own local scope. When you invoke a program from inside a do-file or another program, that invoked program does not inherit the local macro definitions of the calling code.

So depending on the logic, you either need to move that -local row = 2- command down inside program WASH (if you always want to start at row = 2), or, you need to pass the initial value of row as an argument to program WASH.

Added: crossed with Roger Chu's response, which says essentially the same thing as mine.

[Nick Cox]

To all this excellent advice, I would add

set trace on

One feature there is that you get to see what Stata uses for local macro contents (here precisely nothing, which is the problem).

[nizam khan]

Thanks Roger and Clyde. After I move the -local row = 2- within WASH (as per your advice) it worked.

[nizam khan]

Although now the program is running properly, but I am getting wrong results for -se- when saved in excel. First -se- is what is saved in excel, second -se- is the correct -se- obtained from result results window. What goes wrong?

variable		se	se
improved_water		1.333333	0.012298
correct_watertreat		1.333333	0.012298
boiling		1.333333	0.001771
bleaching		1.333333	0.004497
filtering		1.333333	0.012325
time_water		1.333333	0.019307
solar_disinfect		1.333333
improved_sanitation		1.333333	0.010506
open_defecation		1.333333	0.005942
proper_handwashing		1.333333	0.014666

[Clyde Schechter]

Well, there is nothing obviously wrong with your code. So I experimented with it a bit. I do not have a data set like yours, so I used the on-line nhanes2f data set and modified the contents of global macro WASH to contain variables from that data set. It was a fortunate choice of alternative data set in that it produced the same error you got (although with a different wrong value for se). To make a long story short, you have a name clash between scalar se and some variable in your data set. (In nhanes2f the clashing variable name is sex.) When Stata encounters a name that can be interpreted as either a variable name or a scalar name, it goes with the variable name. That is one of the drawbacks of scalars and is the reason that Stata usually recommends that tempnames be used for scalars.

So there are a few ways you can fix this. 1) You can -drop se*- to remove the variable whose name clashes. 2) You can change the name of the scalar se to something else that doesn't clash with any variable names (using a tempname for this purpose would guarantee that result even if you subsequently change the data set.) Don't forget to also change the reference to scalar se in your -putexcel- command, too. 3) You can override Stata's default interpretation by changing your -putexcel- command to refer to scalar(se) rather than se.

As an aside, though it didn't bite you here, it is not good practice to pass arguments to a program by putting them in a global macro. The reason is that just as scalars are subject to name clashes with variables, global macros are subject to name clashes with global macros defined in other programs that may be in memory (or have recently been in memory)--which you might not even know about! The bugs that that produces can be even more difficult to chase down than this one was. The better procedure is to incorporate a varlist into the syntax of your program statement and then refer to that within the program. So something like this:

capture program drop WASH
program define WASH
    syntax varlist
    foreach v of varlist `varlist' {
        svy:mean `v'
        // etc.
       putexcel //....
   }
   exit
end

WASH  improved_water correct_watertreat boiling bleaching filtering time_water solar_disinfect ///
improved_sanitation open_defecation proper_handwashing

// OR IF YOU NEED TO USE THAT LIST OF VARIABLES FOR OTHER PURPOSES
// AND DON'T WANT TO RETYPE IT EACH TIME

local wash_vars  improved_water correct_watertreat boiling bleaching filtering time_water solar_disinfect ///
improved_sanitation open_defecation proper_handwashing
WASH `wash_vars'

Local macros are safe. Global macros are not and should only be used when there is no alternative.

[nizam khan]

Thanks Clyde and Nick. Your advice were very helpful.