Loop to combine mutually exclusive variables

[Garrett Baker]

I have data set up like the below (but with many more variables) where the middle part of the variable name changes when a child ages into a new age group.

So "C23" would be the naming root when children are ages 4-12, and once they turn 13 the variable name changed (for whatever reason) and the root becomes "D22". The letter that follows is what connects them--so in constructing a cohesive panel, you'd want to combine "C23A" and "D22A" into one variable.

I can obviously just brute force this for each of my dozen variables, but I was wondering if anyone could come up with a clever loop that would accomplish this more efficiently?

* Example generated by -dataex-. For more info, type help dataex
clear
input float(year child_id HOME_C23A_ HOME_D22A_ HOME_C23B_ HOME_D22B_)
2000 100 1 . 1 .
2002 100 0 . 1 .
2004 100 . 1 . 0
2006 100 . 1 . 0
2008 100 . 1 . 1
2000 200 1 . 1 .
2002 200 . 1 . 1
2004 200 . 0 . 1
2006 200 . 0 . 0
2008 200 . 1 . 0
end

[Clyde Schechter]

Well, you don't say in what way you want to combine the variables. I'm going to suppose that all of these variables are always either 0, 1, or missing and you want the combined variable to be 1 if any of them are 1, 0 if all are 0 and at least one is not missing, and missing if all are missing. If that's wrong, the code will need to be modified according to what you actually want.

I'm also going to assume that all of the variable names are of the form HOME_whateverX_, where X is a single character. So the variables that need to be combined are those that have the same X in their name. (Here, I assume they are just A and B, as in your example.) I also assume that you already know what all the possible values of X are.

If these assumptions are all correct, then it's pretty simple:

foreach x in A B {
    egen combined_`x' = rowtotal(HOME_*`x'_)
}

If my assumptions are incorrect, then this code may not work properly. If that is the case, please post back with a new data example that exhibits the problems you encounter with it. Also explain in words the extent to which my assumptions are incorrect. Then I will try to troubleshoot.

[Garrett Baker]

Thanks Clyde. The variables are structured such that HOME_C23A_ and HOME_D22A_ are completely exclusive--if one of them has a 0/1 value, the other will always be missing. So indeed, I'd want them to be combined such that the 0/1 is preserved. I believe your code does that, thank you. The solution I had come up with is a little more clunky:

foreach i in A B {
    gen combined_`i' = .
    replace combined_`i' = HOME_C23`i'_ 
    replace combined_`i' = HOME_D22`i'_ if combined_`i' == .
}

[Nick Cox]

The commands within the loop in #3 could be revised as

  gen combined_`i' = cond(HOME_C23`i' < ., HOME_C23`i'_, HOME_D22`i'_)