Replacing missing values with the mean by group via a loop

[Daniel Sutcliffe]

Hello,

I have a large dataset of 59,000,000 observations.

There are a 50 variables each beginning with the word qof. Some of the observations have missing values. I need to replace the missing values with the mean by group (healthgroup), For an individual missing variable I can do this easily enough I think:

egen missqof1 = mean(qof1), by(health_org)
replace qof1 = missqo1 if missing(qof1)

my problem is that I don't want to type this 50 times for each of the 50 variables, so I want to do this in a loop I think:


foreach var of varlist qof* {

replace qof* = mean(qof*) if missing(qof*)
}

I am not familiar with loops - and not sure how to make the code work.

[Nick Cox]

That code is illegal. You can't use wildcards with replace. I don't follow whether healthgroup or health_org is what you want to grouo by, but something like

foreach var of varlist qof* { 
      egen mean = mean(`var'), by(health_group) 
      gen `var'2 = cond(missing(`var'), mean, `var') 
      drop mean
}

is closer to what you ask. Whether imputation of missing values by means is a good idea is a totally different question. I would keep the original variables, as the code implies. Otherwise you have lost almost all scope for assessing the effect of imputation.

[Carlo Lazzaro]

Daniel:
as Nick touched upon, replacing missing values with the mean of the observed values is definitely not a good idea: see, among tons of literature, https://www.lshtm.ac.uk/research/cen...a#introduction.
More substantively, it's my experience that such trivial way of handling missing values is now rejected by an increasing number of technical journals.

[Daniel Sutcliffe]

Thank you, this works great!