Assign a value only if all observations in the group have the same value

[Blake Lee]

Dear all

Please consider this example where I would like to attain the values in overalltype1, overalltype2 and overalltype3:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(id number) str7 type1 str5 type2 str7 type3 str5(overalltype1 overalltype2) str7 overalltype3
1 1 "alpha"   "bravo" ""        "alpha" "bravo" ""       
1 2 "alpha"   "bravo" ""        "alpha" "bravo" ""       
2 1 "alpha"   "alpha" "charlie" ""      "alpha" "charlie"
2 2 "bravo"   "alpha" "charlie" ""      "alpha" "charlie"
2 3 "charlie" "alpha" "charlie" ""      "alpha" "charlie"
3 1 "charlie" "delta" "charlie" ""      "delta" ""       
3 2 "delta"   "delta" ""        ""      "delta" ""       
3 3 "delta"   "delta" ""        ""      "delta" ""       
4 1 ""        "alpha" ""        ""      "alpha" ""       
4 2 ""        "alpha" "alpha"   ""      "alpha" ""       
4 3 ""        "alpha" ""        ""      "alpha" ""       
end

An overalltype should return a value where the value is the same amongst all observations per id (group). Otherwise, it should return a blank. (If the same values are blanks, it should also return a blank.)

I have tried the following code which almost works because it gives me extra values on top of the correct ones:

egen countsku = count(number), by(id)

forval i = 1/3 { 
egen count`i' = count(type`i') if type`i' == type `i', by(id)
sort id type `i'
by id: gen overalltype`i' = type `i'[_N] if count `i' == countsku
}

Thank you.

[Fei Wang]

foreach var of varlist type1-type3 {
    bys id (`var'): gen overall`var' = `var' if `var'[1] == `var'[_N]
}

[Nick Cox]

Thanks for the clear data example and problem statement.

This is (in essence) an FAQ. https://www.stata.com/support/faqs/d...ions-in-group/ -- although as usual finding the right keywords to search on can be hard.

If all values are the same, then after sorting the first and the last value are equal, and not otherwise (both in general and in any particular group of observations). Hence one solution is

forval j = 1/3 {
    bysort id (type`j') : gen wanted`j' = type`j' if type`j'[1] == type`j'[_N]
}

[Blake Lee]

Brilliant. Thank you Fei Wang and Nick Cox . Thank you for especially pointing out the first and last value would be equal upon sorting if all values are the same!