Balancing an unbalanced panel

[WilliamVigil]

I am trying to balance an unbalanced panel in order to create a set of datasets that have the same exact structure. I am aware that one can do calculations in Stata with an unbalanced panel, but that is not my goal.

My unbalance panel looks like this:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str1 country byte hh str6 group int exp byte weight
"x" 1 "shoes"  320  3
"x" 1 "rent"   398  3
"x" 1 "tomato" 100  3
"x" 1 "ham"    217  3
"x" 2 "shoes"  426  4
"x" 2 "rent"   423  4
"x" 2 "tomato" 536  4
"x" 3 "shoes"  372  5
"x" 3 "rent"   141  5
"y" 1 "shoes"  182  3
"y" 1 "rent"   508  3
"y" 2 "shoes"  419  4
"y" 3 "shoes"  586  5
"y" 3 "rent"   207  5
"y" 4 "shoes"  142  6
"y" 4 "rent"   462  6
"y" 4 "tomato" 430  6
"y" 5 "ham"    177 20
end

There are two dimensions: the country and the group. I am seeking to balance the "group" variable for each household ("hh") for each country. For example, in country x, HH1 consumes all the group items that I want every household to have: shoes, rent, tomato and ham. HH 2 in country x; however, consumes all the "group" items except for "ham". I am seeking a way that I can add missing group categories like "ham" in household 2 in country X to all household missing a group in each country. The "exp" variable for this missing group would be 0, but the group would still be there.

This is what I am ultimately after (notice that the number of household is not the same in both countries x and y. This is OK)

* Example generated by -dataex-. To install: ssc install dataex
clear
input str1 country byte hh str6 group int exp byte weight
"x" 1 "shoes"  320  3
"x" 1 "rent"   398  3
"x" 1 "tomato" 100  3
"x" 1 "ham"    217  3
"x" 2 "shoes"  426  4
"x" 2 "rent"   423  4
"x" 2 "tomato" 536  4
"x" 2 "ham"      0  4
"x" 3 "shoes"  372  5
"x" 3 "rent"   141  5
"x" 3 "tomato"   0  5
"x" 3 "ham"      0  5
"y" 1 "shoes"  182  3
"y" 1 "rent"   508  3
"y" 1 "tomato"   0  3
"y" 1 "ham"      0  3
"y" 2 "shoes"  419  4
"y" 2 "rent"     0  4
"y" 2 "tomato"   0  4
"y" 2 "ham"      0  4
"y" 3 "shoes"  586  5
"y" 3 "rent"   207  5
"y" 3 "tomato"   0  5
"y" 3 "ham"      0  5
"y" 4 "shoes"  142  6
"y" 4 "rent"   462  6
"y" 4 "tomato" 430  6
"y" 4 "ham"      0  6
"y" 5 "shoes"    0 20
"y" 5 "rent"     0 20
"y" 5 "tomato"   0 20
"y" 5 "ham"    177 20
end

Any suggestions and/or advice would be of much help. Thank you.

[Nick Cox]

Thanks for the clear example.

egen id = concat(country hh) 
fillin id group 
bysort id (country) : replace country = country[_N] if missing(country)
bysort id (hh): replace hh = hh[1] if missing(hh) 
bysort id: replace weight = weight[1] if missing(weight) 
replace exp = 0 if _fillin 
drop id

[John Pevedia]

This is very helpful, Nick. Thank you.

Just one question: I have two additional variables called "country_name" (a variable that includes not a country code, but a country name and has a 1:1 correspondence to the variable "country") and "group_code" (a variable that has a 1:1 correspondence to "group"; it's the coded version of each group category). For these two cases, would the correct replacement syntax be:

bysort id (country) : replace country_name= country_name[_N] if missing(country_name)
bysort id (country) : replace group_code= group_code[_N] if missing(group_code)

That is, using the "_N" in the replacement rather than [1] and using "bysort" by id and then country.

[Nick Cox]

Empty strings sort to beginning, missing numerics sort to end, so non-missings are at the other extreme.

John Pevedia == William Vigil ?????