How to fill in missing values by group when there is one nonmissing value per group

[Daniel Schulte]

Hi,
I have a variable that has one nonmissing numeric value per group, and all the other values are missing per group. I want to replace the missing values with the nonmissing value per group. The place where the nonmissing value appears changes.
For instance:

country code	old var1	new var1 (wanted)
1	.	3
1	.	3
1	3	3
1	.	3
2	.	5
2	5	5
2	.	5
2	.	5
2	.	5

I tried the following code but it didn't completely work.

bysort cow: replace var1 = var1[_n-1] if missing(var1)
bysort cow: replace var1 = var1[_n+1] if missing(var1)

Any suggestions would be greatly appreciated. Thank you.

[Daniel Schulte]

I meant to write:
bysort country_code: replace var1 = var1[_n-1] if missing(var1)
bysort country_code: replace var1 = var1[_n+1] if missing(var1

[Ali Atia]

bysort country_code: egen wanted = total(var1)

Works if there is only 1 non-missing. If there's more than one for any group you'll need something different.

[Daniel Schulte]

Great. I think that worked. What does this code do if all the cells in a particular group (country code) value are all missing? Does it leave it missing or change it to zero?

[Ali Atia]

If you specify the missing option, it leaves them as missing. Without the option, missing is treated as 0.

[Joro Kolev]

Pretty much all native egen functions disregard missings, so assuming that you have only one missing in each group, what Ali did works, and can be done with any egen function, min, max, total, mean, etc.

Another way would be:

. bysort countrycode ( oldvar1): replace oldvar1 = oldvar1[_n-1] if missing( oldvar1)

[Raymond Zhang]

 
 bysort country_code: egen wanted = min(var1)

[Nick Cox]

In support of #1 #2 and #6 see https://www.stata.com/support/faqs/d...issing-values/

[Daniel Schulte]

Dear Ali, Joro, Raymond, and Nick, Thank you very much for all your suggestions. These were all very helpful.