Keeping 1 of many duplicate ID Numbers

[Jerome Anderson]

I have a dataset with entries for multiple people in a household. I need to remove all but one of the persons in each household. I generated the iddup variable and produced the following table:

tabulate iddup

iddup | Freq. Percent Cum.
------------+-----------------------------------
0 | 1,624 11.15 11.15
1 | 4,548 31.21 42.36
2 | 5,259 36.09 78.45
3 | 2,368 16.25 94.70
4 | 700 4.80 99.51
5 | 72 0.49 100.00
------------+-----------------------------------
Total | 14,571 100.00

I read the section on removing duplicates in Mitchell's Data Management Using Stata: A Practical Handbook, 2nd Ed., but Mitchell does not tell how to remove the duplicates but retain one of the observations. Are there any commands in Stata to do this?

[Clyde Schechter]

Well, which one of the observations with a duplicate ID do you want to retain?

If the observations with the same ID are all the same on all of the variables, then it doesn't matter which, and in that case, the simplest way to proceed is -duplicates drop-.

But if you have multiple observations with the same ID that disagree on other variables, then it matters which one you keep (and it is possible, depending on what the data mean and what you need to do with it, that none of the observations by itself is suitable for retention and you may need to in some way create a "synthetic" observation that combines results from several observations). If you can describe how you can identify which one to retain, then help can be provided. Otherwise, you have left the problem incompletely described and nothing more can be said.

[Jerome Anderson]

Thanks, Clyde. I thought that would be the answer. The dataset has responses from multiple members of the same household; I only need one response/household. For this exercise, it doesn't matter which one. Your answer confirmed what I thought based on my reading of Mitchell. Thanks for the confirmation. I'll simply use all the responses/household for this exercise.

[Clyde Schechter]

Well, be careful. If the exercise you are doing requires that you have only one observation per household, then using all the responses/household will likely give you incorrect results. So if it is a matter that you must reduce to one observation per household, but it doesn't matter which, then run:

by id, sort: keep if _n == 1

Stata will select one observation per household per household at random for you. (Warning: the random selection Stata makes here will not be reproducible, so if you run the entire exercise multiple times, you may not get the same results of your calculations.)

[Jerome Anderson]

Thank you again, Clyde. In this classroom exercise, the number of responses per household is not an issue; the issue is running a SEM. I was only trying to clean the data a bit, and noticed multiple responses/household. This is for me a practice run for a much larger study I want to do in a year or so w/ a different dataset (which I don't have yet, in part because USAID is now gone). I'm really just experimenting w/ proof of concept for the SEM run. I have ~ 14,000 observations, and when i saw the duplicate household ID #s, I thought i would drop the duplicates to reduce the file size. I'll find an earlier version of my dataset and run the code you showed me in your last post and see what I get. Thanks again.