Stata code for leave one out variable from community level variable

[Chanda Moon]

Dear stata Community,

I am using 2SLS to look for impact of E.coli in water on child health.
I need to create sanitation coverage/improved sanitation as an exogeneous community variable measured by the proportion of households practicing improved sanitation in village excluding the household in consideration. I could create the village level improved sanitation (by ratio). However, I am confused about creating this community variable excluding particular households in consideration.

code for village level variable.

egen vil_imp_san = mean (imp_sanitation), by (cluster)
egen vil_no_toilet = mean (no_toilet), by (cluster)

Thanks!

[Clyde Schechter]

To calculate a mean imp_san for all other households:

by cluster, sort: egen numerator = total(imp_sanitation)
by cluster: egen denominator = count(imp_sanitation)
gen vil_imp_san = (numerator - imp_sanitation)/(denominator - 1)

The code for no_toilet is analogous.

[Nick Cox]

If any value is missing, you need a more complicated recipe to catch cases in which a value is missing but there are non-missing values to compare with. This refinement isn't much use, but it is easy to implement.

by cluster, sort : egen numer = total(x)
by cluster: egen denom = count(x)
gen wanted = (numer - cond(missing(x), 0, x)) / (denom - !missing(x))

[Chanda Moon]

Thank you very much for the kind support. Do I need to drop the missing values which are 20

[Chanda Moon]

Thank you Nick and Clyde,

I used both your mentioned codes. It gives similar number of missing vaules (2508). How can i handle such missing values.

If any value is missing, you need a more complicated recipe to catch cases in which a value is missing but there are non-missing values to compare with. This refinement isn't much use, but it is easy to implement.

by cluster, sort : egen numer = total(x)
by cluster: egen denom = count(x)
gen wanted = (numer - cond(missing(x), 0, x)) / (denom - !missing(x))

[Nick Cox]

You can drop them if you don't want them.

drop if missing(x)

[Chanda Moon]

You can drop them if you don't want them.

drop if missing(x)

Thank you Nick Cox.
Actually I want them and do not want to drop