Removing individuals ID's from a Stata dataset if their ID does not show in all 5 years of the dataset

[Charlie Cook]

Hi, below is the first few lines of my data editor for my panel data showing the id and year variables. The panel is 5 years from 2013 to 2017, and each ID should have 5 observations for each 5 years, however some ids are not in the data 5 times, meaning the panel is unbalanced. Is there a command which I can use to remove all IDs (the whole line of data not just the ID) for any ids which are not in all 5 years of the dataset.

Thanks,

id year
22445 2013
22445 2014
22445 2015
22445 2016
22445 2017
29925 2014
29925 2015
29925 2016

[George Ford]

[CODE]
bys id: egen N = nvals(year)
tab N
keep if N == 5
[CODE]

[Charlie Cook]

Hi, thanks for your reply. I can't seem to get that code to work:


unknown egen function nvals()

Any ideas?

[Rich Goldstein]

"nvals" is a function of the -egenmore- package; use search to find, download and install

[George Ford]

Sorry. Do what Rich said.

[Nick Cox]

I wrote nvals() back in the day. But look at the code:

. ssc type _gnvals.ado
*! 1.0.1 NJC 20 November 2000 
*! 1.0.0 NJC 20 July 2000 
program define _gnvals
        version 6
        gettoken type 0 : 0 
        gettoken g 0 : 0
        gettoken eqs 0 : 0

        syntax varlist [if] [in] [, by(varlist) MISSing]
        tempvar touse
        quietly {
                mark `touse' `if' `in'
                if "`missing'" == "" {
                        markout `touse' `varlist', strok 
                }
                sort `touse' `by' `varlist' 
                by `touse' `by' `varlist': gen `type' `g' = _n == 1 if `touse' 
                by `touse' `by' : replace `g' = sum(`g') if `touse' 
                by `touse' `by' : replace `g' = `g'[_N] if `touse' 
                
        }
end

Although nvals() is a convenience, you don't need it:

clear 
input id year
22445 2013
22445 2014
22445 2015
22445 2016
22445 2017
29925 2014
29925 2015
29925 2016
end

bysort id (year) : gen distinct = sum(year != year[_n-1])
by id: replace distinct = distinct[_N]

list, sepby(id)

     +-------------------------+
     |    id   year   distinct |
     |-------------------------|
  1. | 22445   2013          5 |
  2. | 22445   2014          5 |
  3. | 22445   2015          5 |
  4. | 22445   2016          5 |
  5. | 22445   2017          5 |
     |-------------------------|
  6. | 29925   2014          3 |
  7. | 29925   2015          3 |
  8. | 29925   2016          3 |
     +-------------------------+

See also https://www.stata.com/support/faqs/d...-observations/

https://journals.sagepub.com/doi/pdf...867X0800800408