Drop observations if they have equal values on variable A but different values dummy variable B

[Johannes Pimpl]

Hello everyone,

I would like to drop all observations, that have the same value on variable product_id but different values on dummy variable intermediate.
This should be pretty simple, I guess. But since I'm new to Stata, I cannot figure out how to do it.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 SITC_Rev2 str3 BEC4 float intermediate str4 product_id
"00111" "41"  0 "0011"
"00119" "111" 1 "0011"
"00121" "111" 1 "0012"
"00122" "111" 1 "0012"
"0013"  "111" 1 "0013"
"00141" "111" 1 "0014"
"00149" "111" 1 "0014"
"0015"  "111" 1 "0015"
"0019"  "111" 1 "0019"
"01111" "122" 0 "0111"
"01112" "122" 0 "0111"
"0112"  "122" 0 "0112"
"0113"  "122" 0 "0113"
"0114"  "122" 0 "0114"
"0115"  "121" 1 "0115"
"0116"  "122" 0 "0116"
"01181" "122" 0 "0118"
"01189" "122" 0 "0118"
"0121"  "122" 0 "0121"
"0129"  "122" 0 "0129"
"0141"  "121" 1 "0141"
"0142"  "122" 0 "0142"
"0149"  "122" 0 "0149"
"0223"  "112" 0 "0223"
"02241" "121" 1 "0224"
"02242" "121" 1 "0224"
"02243" "122" 0 "0224"
"02249" "122" 0 "0224"
"0230"  "122" 0 "0230"
"0240"  "122" 0 "0240"
"0251"  "112" 0 "0251"
"0252"  "121" 1 "0252"
"0341"  "112" 0 "0341"
"0342"  "122" 0 "0342"
"0343"  "122" 0 "0343"
"0344"  "122" 0 "0344"
"03501" "121" 1 "0350"
"03502" "122" 0 "0350"
"03503" "112" 0 "0350"
"03504" "122" 0 "0350"
end

Sorry, if the code doesn't come out nicely, I am talking about the last two collumns.

Thanks a lot in advance!
Johannes

[William Lisowski]

Welcome to Statalist, and thank you for the well-presented example of your data.

Here's the approach I would take.

by product_id (intermediate), sort: generate todrop = intermediate[1]!=intermediate[_N]
list, noobs sepby(product_id) abbreviate(12)

. list, noobs sepby(product_id) abbreviate(12)

  +-------------------------------------------------------+
  | SITC_Rev2   BEC4   intermediate   product_id   todrop |
  |-------------------------------------------------------|
  |     00111     41              0         0011        1 |
  |     00119    111              1         0011        1 |
  |-------------------------------------------------------|
  |     00121    111              1         0012        0 |
  |     00122    111              1         0012        0 |
  |-------------------------------------------------------|
  |      0013    111              1         0013        0 |
  |-------------------------------------------------------|
  |     00141    111              1         0014        0 |
  |     00149    111              1         0014        0 |
  |-------------------------------------------------------|
  |      0015    111              1         0015        0 |
  |-------------------------------------------------------|
  |      0019    111              1         0019        0 |
  |-------------------------------------------------------|
  |     01112    122              0         0111        0 |
  |     01111    122              0         0111        0 |
  |-------------------------------------------------------|
  |      0112    122              0         0112        0 |
  |-------------------------------------------------------|
  |      0113    122              0         0113        0 |
  |-------------------------------------------------------|
  |      0114    122              0         0114        0 |
  |-------------------------------------------------------|
  |      0115    121              1         0115        0 |
  |-------------------------------------------------------|
  |      0116    122              0         0116        0 |
  |-------------------------------------------------------|
  |     01189    122              0         0118        0 |
  |     01181    122              0         0118        0 |
  |-------------------------------------------------------|
  |      0121    122              0         0121        0 |
  |-------------------------------------------------------|
  |      0129    122              0         0129        0 |
  |-------------------------------------------------------|
  |      0141    121              1         0141        0 |
  |-------------------------------------------------------|
  |      0142    122              0         0142        0 |
  |-------------------------------------------------------|
  |      0149    122              0         0149        0 |
  |-------------------------------------------------------|
  |      0223    112              0         0223        0 |
  |-------------------------------------------------------|
  |     02249    122              0         0224        1 |
  |     02243    122              0         0224        1 |
  |     02241    121              1         0224        1 |
  |     02242    121              1         0224        1 |
  |-------------------------------------------------------|
  |      0230    122              0         0230        0 |
  |-------------------------------------------------------|
  |      0240    122              0         0240        0 |
  |-------------------------------------------------------|
  |      0251    112              0         0251        0 |
  |-------------------------------------------------------|
  |      0252    121              1         0252        0 |
  |-------------------------------------------------------|
  |      0341    112              0         0341        0 |
  |-------------------------------------------------------|
  |      0342    122              0         0342        0 |
  |-------------------------------------------------------|
  |      0343    122              0         0343        0 |
  |-------------------------------------------------------|
  |      0344    122              0         0344        0 |
  |-------------------------------------------------------|
  |     03502    122              0         0350        1 |
  |     03504    122              0         0350        1 |
  |     03503    112              0         0350        1 |
  |     03501    121              1         0350        1 |
  +-------------------------------------------------------+

So then doing this for real, instead of listing the results, you would

drop if todrop

[Johannes Pimpl]

Thank you so much William Lisowski! This works perfectly. Now I'll try to understand what you did there. =)