Shortcut for "if any in variable range is not missing"

[LiMaria Lopez]

Hello,

I am dealing with 11 variables that I want to make into dummy variables. Currently, the variables only have a value of 1 if it applies to the respondent, otherwise it is missing. I want to recode the missing values to 0 if that observation has a 1 in any of the other 10 variables (ie not missing). If all variables are missing for that observation, I want to keep it as missing.

The current code I have is below. This works fine, it is just very cumbersome because I have to do this for all of the variables and switch one variable name out each time. Is there a more efficient way to do this?

replace  AB    = 0 if    (AE    !=. |    AF    !=. |    AG    !=. |    AH    !=. |    AI    !=. |    AJ    !=. |    AK    !=. |    AL    !=. |    AM    !=. |    AN    !=.)    &    AB    ==.

Thanks in advance!

[Nick Cox]

Like this?

* sandbox -- because no data example was provided! 
clear 
set obs 10 
set seed 2803 

local j = 1
foreach v in AB AE AF AG AH AI AJ AK AL AM AN { 
    gen `v' = cond(runiform() > `j'/11, 1, .) 
    local ++j 
} 

list 

* the machinery starts here 
unab all : A* 
foreach v of local all  { 
    local others : list all - v 
    local OTHERS : subinstr local others " " ",", all 
    replace `v' = 0 if missing(`v') & inlist(1, `OTHERS') 
}

list

Before and after listings:

 
     +------------------------------------------------------+
     | AB   AE   AF   AG   AH   AI   AJ   AK   AL   AM   AN |
     |------------------------------------------------------|
  1. |  1    1    1    .    .    .    .    .    1    .    . |
  2. |  1    1    1    1    .    1    .    .    .    .    . |
  3. |  1    .    1    1    .    .    1    1    .    .    . |
  4. |  1    .    .    .    1    .    1    .    1    .    . |
  5. |  1    .    1    1    .    1    .    .    .    .    . |
     |------------------------------------------------------|
  6. |  .    1    1    1    .    1    .    1    .    .    . |
  7. |  1    .    1    1    .    .    1    .    .    .    . |
  8. |  1    .    1    .    1    .    .    .    .    .    . |
  9. |  1    1    1    .    .    1    1    .    .    1    . |
 10. |  1    1    .    1    .    1    .    1    .    .    . |
     +------------------------------------------------------+


     +------------------------------------------------------+
     | AB   AE   AF   AG   AH   AI   AJ   AK   AL   AM   AN |
     |------------------------------------------------------|
  1. |  1    1    1    0    0    0    0    0    1    0    0 |
  2. |  1    1    1    1    0    1    0    0    0    0    0 |
  3. |  1    0    1    1    0    0    1    1    0    0    0 |
  4. |  1    0    0    0    1    0    1    0    1    0    0 |
  5. |  1    0    1    1    0    1    0    0    0    0    0 |
     |------------------------------------------------------|
  6. |  0    1    1    1    0    1    0    1    0    0    0 |
  7. |  1    0    1    1    0    0    1    0    0    0    0 |
  8. |  1    0    1    0    1    0    0    0    0    0    0 |
  9. |  1    1    1    0    0    1    1    0    0    1    0 |
 10. |  1    1    0    1    0    1    0    1    0    0    0 |
     +------------------------------------------------------+

[LiMaria Lopez]

Thank you so much, Nick!

This does work and now I have a similar problem with a different set of variables. This time, I have a list of 14 variables. These I will not make dummy variables, they have values other than 1 if it applies to them. What I want to do now is not mess with the values, but just replace missing values to .a only if that observation has a non-missing value in any of the other variables. What I have tried, based on your prior code, is this:

unab all : AR AS AT AU AV AW AX AY AZ BA BB BC BD BE
foreach v of local all  { 
    local others : list all - v 
    local OTHERS : subinstr local others " " ",", all 
    replace `v' = .a if missing(`v') & inlist(!missing, `OTHERS')
}

However, it is returning the error r(111). I set trace on and it says "missing not found" after that last line of code (replace). I am guessing the problem is when I attempted to do: !missing. Is this not allowed with inlist?

Thanks again for the help.

[Nick Cox]

Indeed. That won't work. !missing would have meaning if missing were a numeric variable or scalar, but it is not.

missing() is a function, but that doesn't mean that missing is kind of wild card. for missing values.

This is a slightly awkward hack:

unab all : AR AS AT AU AV AW AX AY AZ BA BB BC BD BE
foreach v of local all {
local others : list all - v
egen anyOK = rownonmiss(`others')
replace `v' = .a if missing(`v') & anyOK
drop anyOK
}

By the way, my guess is that you have inherited a lot of arbitrary variable names following import from a spreadsheet. It may be worth your while to rename systematically.

[LiMaria Lopez]

Amazing, that worked! Thanks so much. And absolutely, I'm renaming variables.

Appreciate all the help immensely!