Drop ID if different observations for that same ID do not vary across another variable

[Lonneke Graas]

Hello,

I am using Stata 14.2 on Windows. This is my first post so I hope I am doing this correctly.

The dataset I am using contains around 100.000 observations with information about buildings.
Each building has an ID number like 344100000000006, followed by an adress, (..some more variables that are not important for the question) and the function (labeled with values 1 - 12).
One building can contain multiple living units, a store on the ground floor etc. These units are all seperate observations with the same building ID (so they will have the same adress and only (if) differ in function). Therefore one building ID can occur for example 16 times.

I want to know which buildings have more than one function, like building with ID 344100000000042, which is used for both function 3 and 12.
I am not interested in buildings with only one function so I want to drop them from the data set.

I believe I need to combine different observations with the same ID into one, and while this is an issue I found many forumusers are struggeling with, I am not experienced enough with Stata to apply suggestions to other problems to my own case. Therefore I sincerely hope someone is willing to help me.

The data looks like this: (I excluded other variables that are not important to the question)

* Example generated by -dataex-. To install: ssc install dataex
clear
input double gebwbagidgetal long gebruiksdoel_n
344100000000006 12
344100000000006 12
344100000000008 12
344100000000008 12
344100000000011 12
344100000000011 12
344100000000011 12
344100000000014 12
344100000000014 12
344100000000014 12
344100000000014 12
344100000000014 12
344100000000014 12
344100000000014 12
344100000000014 12
344100000000014 12
344100000000014 12
344100000000014 12
344100000000014 12
344100000000014 12
344100000000014 12
344100000000014 12
344100000000014 12
344100000000016 12
344100000000016 12
344100000000029 12
344100000000029 12
344100000000029 12
344100000000029 12
344100000000029 12
344100000000039 12
344100000000039 12
344100000000039 12
344100000000039 12
344100000000039 12
344100000000041 12
344100000000041 12
344100000000042 3
344100000000042 12
344100000000053 12
344100000000053 12
344100000000061 3
344100000000061 12
344100000000061 12
344100000000061 12
344100000000061 12
344100000000061 12
344100000000064 12
344100000000064 12
344100000000074 12
344100000000074 12
344100000000074 3
344100000000074 12
344100000000074 12
344100000000074 12
344100000000074 12
344100000000074 12
344100000000074 12
344100000000079 12
344100000000079 12
344100000000079 12
344100000000079 12
344100000000079 12
344100000000082 12
344100000000082 3
344100000000084 12
344100000000084 3
344100000000084 12
344100000000089 12
344100000000089 12
344100000000089 12
344100000000089 12
344100000000089 12
344100000000089 12
344100000000089 12
344100000000090 12
344100000000090 12
344100000000090 12
344100000000091 3
344100000000091 12
344100000000098 3
344100000000098 12
344100000000102 3
344100000000102 12
344100000000106 12
344100000000106 12
344100000000109 3
344100000000109 12
344100000000114 3
344100000000114 3
344100000000116 12
344100000000116 12
344100000000116 12
344100000000116 12
344100000000116 12
344100000000116 12
344100000000116 12
344100000000116 12
344100000000116 12
344100000000116 12
end
label values gebruiksdoel_n gebruiksdoel_n
label def gebruiksdoel_n 3 "gemengd", modify
label def gebruiksdoel_n 12 "woonfunctie", modify
[/CODE]

[William Lisowski]

Welcome to Statalist.

The following code applied to your example data may point you in a useful direction.

format gebwbagidgetal %20.0f
by gebwbagidgetal, sort: egen has3 = max(gebruiksdoel_n==3)
by gebwbagidgetal: egen has12 = max(gebruiksdoel_n==12)
keep if has3 & has12
list, sepby(gebwbagidgetal) abbreviate(20)

. list, sepby(gebwbagidgetal) abbreviate(20)

     +--------------------------------------------------------------------+
     |  gebwbagidgetal   gebruiksdoel_n   multiplebuilding   has3   has12 |
     |--------------------------------------------------------------------|
  1. | 344100000000042          gemengd                  2      1       1 |
  2. | 344100000000042      woonfunctie                  2      1       1 |
     |--------------------------------------------------------------------|
  3. | 344100000000061          gemengd                  6      1       1 |
  4. | 344100000000061      woonfunctie                  6      1       1 |
  5. | 344100000000061      woonfunctie                  6      1       1 |
  6. | 344100000000061      woonfunctie                  6      1       1 |
  7. | 344100000000061      woonfunctie                  6      1       1 |
  8. | 344100000000061      woonfunctie                  6      1       1 |
     |--------------------------------------------------------------------|
  9. | 344100000000074      woonfunctie                  9      1       1 |
 10. | 344100000000074      woonfunctie                  9      1       1 |
 11. | 344100000000074          gemengd                  9      1       1 |
 12. | 344100000000074      woonfunctie                  9      1       1 |
 13. | 344100000000074      woonfunctie                  9      1       1 |
 14. | 344100000000074      woonfunctie                  9      1       1 |
 15. | 344100000000074      woonfunctie                  9      1       1 |
 16. | 344100000000074      woonfunctie                  9      1       1 |
 17. | 344100000000074      woonfunctie                  9      1       1 |
     |--------------------------------------------------------------------|
 18. | 344100000000082      woonfunctie                  2      1       1 |
 19. | 344100000000082          gemengd                  2      1       1 |
     |--------------------------------------------------------------------|
 20. | 344100000000084      woonfunctie                  3      1       1 |
 21. | 344100000000084          gemengd                  3      1       1 |
 22. | 344100000000084      woonfunctie                  3      1       1 |
     |--------------------------------------------------------------------|
 23. | 344100000000091          gemengd                  2      1       1 |
 24. | 344100000000091      woonfunctie                  2      1       1 |
     |--------------------------------------------------------------------|
 25. | 344100000000098          gemengd                  2      1       1 |
 26. | 344100000000098      woonfunctie                  2      1       1 |
     |--------------------------------------------------------------------|
 27. | 344100000000102          gemengd                  2      1       1 |
 28. | 344100000000102      woonfunctie                  2      1       1 |
     |--------------------------------------------------------------------|
 29. | 344100000000109          gemengd                  2      1       1 |
 30. | 344100000000109      woonfunctie                  2      1       1 |
     +--------------------------------------------------------------------+