Creating new variable from several variables with same list of countries

Question

In Stata I have split a variable where up to 20 countries were separated by a comma and now I have twenty different variables (country1 to country20), but the same country is listed in more than one variable country1 to country20.

For instance, Uganda may be in country1, country2 and in country5. Now, I want to create one variable for each country (1 if true, 0 false). So, basically Iwant one variable for each of the twenty countries. I tried this but did not work.

local N = _N
forvalues i = 1/`N' {
    local s1 = Countryies1 [`i']
    local s2 = Countryies2   [`i']   
    local s3 = Countryies3 [`i']
    local s4 = Countryies4   [`i'] 
    local s5 = Countryies5 [`i']
    local s6 = Countryies6   [`i'] 
    local s7 = Countryies7 [`i']
    local s8 = Countryies8   [`i'] 
    local s9 = Countryies9 [`i']
    local s10 = Countryies10   [`i'] 
    local s11 = Countryies11 [`i']
    local s12 = Countryies12   [`i']   
    local s13 = Countryies13 [`i']
    local s14 = Countryies14   [`i'] 
    local s15 = Countryies15 [`i']
    local s16 = Countryies16   [`i'] 
    local s17 = Countryies17 [`i']
    local s18 = Countryies18   [`i'] 
    local s19 = Countryies19 [`i']
    local s20 = Countryies20   [`i'] 
    
    local intersection: list s1 & s2 & s3 & s4 & s5 & s6 & s7 & s8 & s9 & s10 & s11 & s12 & s13 & s14 & s15 & s16 & s17 & s18 & s19 & s20
    replace country ="`intersection'" in `i'
}

Your question isn't consistent about the spelling of your variables. More importantly, see the Stata tag for advice about giving data examples. — Nick Cox, Jun 03 '22 at 16:34
The first reason your code won't work is that string values can't be combined with logical operators. But I don't follow what you thought the code would do. — Nick Cox, Jun 03 '22 at 17:17
Thank you, Nick, I followed the second option and it worked for me. — Shri, Jun 04 '22 at 21:43

Nick Cox · Accepted Answer · 2022-06-03T16:37:46.390

This seems to work -- and does not in any sense rule out other solutions.

clear 
input str42 countries 
"Uganda"
"Uganda, Kenya"
"Uganda, Kenya, Tanzania"
"South Africa"
end 

gen id = _n 
save datasofar, replace 

keep id countries 
split countries, parse(,)
drop countries 
reshape long countries, i(id) j(which)
drop if missing(countries)
replace countries = trim(countries)
gen name = strtoname(countries)
levelsof name, local(names) 

gen new_id = _n 

foreach n of local names { 
    gen is_`n' = name == "`n'" 
    su new_id if is_`n', meanonly 
    label var is_`n' "`=countries[r(min)]'"
    local vars `vars' is_`n' 
}

collapse  (max) `vars', by(id)

merge 1:1 id using datasofar 

     +----------------------------------------------------------------------------------------+
     | id   is_Kenya   is_Sou~a   is_Tan~a   is_Uga~a                 countries        _merge |
     |----------------------------------------------------------------------------------------|
  1. |  1          0          0          0          1                    Uganda   Matched (3) |
  2. |  2          1          0          0          1             Uganda, Kenya   Matched (3) |
  3. |  3          1          0          1          1   Uganda, Kenya, Tanzania   Matched (3) |
  4. |  4          0          1          0          0              South Africa   Matched (3) |
     +----------------------------------------------------------------------------------------+

Another kind of solution is to just to loop over the names, so

foreach c in Uganda Kenya Tanzania "South Africa" { 
     local C = strtoname("`c'") 
     gen is_`C' = strpos(countries, "`c'") > 0 
}

but watch out -- variations in spelling will bite you. They will bite with the earlier code too.

Creating new variable from several variables with same list of countries

1 Answers1