How to find if the values of a variable is nested in another variable, within subgroup or not?

[Lanna Ng]

Hi,

My data structure as below:

groupid	var1	var2	var3
1	256	123	123
1	123	248
1	125	123
2	125	231	235
2	231	234
2	245	231
2	254	234
2	248
3	124	147	147
3	147	9400
3	9400	235
3	2114	259
3	214

My task is to find out which values of var2 and var3 do not appears in var1, within subgroups. For example, value "248" in group 1, "234" in group 2, "235" and "259" are the values of concerned. How to find these values?

Thank you!

[Andrew Musau]

* Example generated by -dataex-. For more info, type help dataex
clear
input byte groupid int(var1 var2 var3)
1  256  123 123
1  123  248   .
1  125  123   .
2  125  231 235
2  231  234   .
2  245  231   .
2  254  234   .
2  248    .   .
3  124  147 147
3  147 9400   .
3 9400  235   .
3 2114  259   .
3  214    .   .
end

bys groupid: gen pos=_n, after(groupid)
reshape long var, i(groupid pos) j(which)
gen gr1= which==1
bys groupid var (gr1): replace gr1= gr1[_N]
gen wanted= !gr1 & !missing(var)
drop gr1
reshape wide var wanted, i(groupid pos) j(which)
drop wanted1 pos
l, sepby(groupid)

Res.:

. l, sepby(groupid)

     +--------------------------------------------------+
     | groupid   var1   var2   wanted2   var3   wanted3 |
     |--------------------------------------------------|
  1. |       1    256    123         0    123         0 |
  2. |       1    123    248         1      .         0 |
  3. |       1    125    123         0      .         0 |
     |--------------------------------------------------|
  4. |       2    125    231         0    235         1 |
  5. |       2    231    234         1      .         0 |
  6. |       2    245    231         0      .         0 |
  7. |       2    254    234         1      .         0 |
  8. |       2    248      .         0      .         0 |
     |--------------------------------------------------|
  9. |       3    124    147         0    147         0 |
 10. |       3    147   9400         0      .         0 |
 11. |       3   9400    235         1      .         0 |
 12. |       3   2114    259         1      .         0 |
 13. |       3    214      .         0      .         0 |
     +--------------------------------------------------+