stata - Filter specific observations

Question

I have an employer-employee database and need to keep only the individuals that have at least one colleague considering the Firm_id variable, but I don't know how to do this in Stata. My dataset is like:

Id    Firm_id    Year
1        50       2010
1        50       2011
2        50       2010
2        50       2011
3        22       2010
3        22       2011
4        22       2010
4        20       2011

In case above, I would keep only the individuals corresponding to the Id 1 and 2 because they are in the same firm in both of the years in the sample. Individual number 3 in 2011 and Individual 4 in 2011 would be dropped.

The output I'm looking for is like:

Id    Firm_id    Year
1        50       2010
1        50       2011
2        50       2010
2        50       2011
3        22       2010
4        22       2010

question from:https://stackoverflow.com/questions/65557857/filter-specific-observations

Answer 1

This works for your data example:

clear 
input Id    Firm_id    Year
1        50       2010
1        50       2011
2        50       2010
2        50       2011
3        22       2010
3        22       2011
4        22       2010
4        20       2011
end

bysort Year Firm_id : keep if Id[1] != Id[_N]
sort Id Year 
list