consistent IDs

[Jenna Kerry]

I am trying to follow the same individuals over the period, I want to find how many individuals exist all over my period like suppose years 2001 to 2021. how to find how many individuals' consistent data I have over the periods?

[Carlo Lazzaro]

Jenna:
you may want to consider the -total- function available from -egen-:

use "https://www.stata-press.com/data/r18/nlswork.dta"
. egen wanted=total(idcode) if idcode==1


. list idcode year wanted if idcode==1

       +------------------------+
       | idcode   year   wanted |
       |------------------------|
    1. |      1     70       12 |
    2. |      1     71       12 |
    3. |      1     72       12 |
    4. |      1     73       12 |
    5. |      1     75       12 |
       |------------------------|
    6. |      1     77       12 |
    7. |      1     78       12 |
    8. |      1     80       12 |
    9. |      1     83       12 |
   10. |      1     85       12 |
       |------------------------|
   11. |      1     87       12 |
   12. |      1     88       12 |
       +------------------------+

.

[Leonardo Guizzetti]

You'll need to provide some more detail about how your data are organized if you'd like a helpful response. Please read the FAQ and use -dataex- to post some snippet example data here.

Some questions that you can help with:
Are you interested in the total number of unique people in the entire study? Are you only interested in the number of people with complete data over the entire study period? Something else?
Are you data split across different files? If so, do they have consistent IDs that can be used to merge on? If not, are the data stored in wide or long format?

[Jenna Kerry]

Thank you for all your comments!

Leonardo Guizzetti , thank you for bringing that up. I am interested both in the total number of unique people in the entire study and the number of people with complete data over the entire study period. Can you help me with that?

I have merged the data set, and made sure they have a consistent Ids over the period of time.

Thank you so much!

[Leonardo Guizzetti]

Thank you for all your comments!

Leonardo Guizzetti , thank you for bringing that up. I am interested both in the total number of unique people in the entire study and the number of people with complete data over the entire study period. Can you help me with that?

I have merged the data set, and made sure they have a consistent Ids over the period of time.

Thank you so much!

Thanks for the additional information. As I mentioned in my previous post, can you post an example of your dataset using the built in -dataex- command? See the Forum FAQ or -help dataex- for more information.

[Jenna Kerry]

Here it is:

* Example generated by -dataex-. For more info, type help dataex
clear
input float(ID year)
 4039 2014
 4039 2016
 4039 2017
 4041 2013
 4041 2015
 4041 2017
 4041 2020
 4042 2019
 4044 2022
 5033 2015
 5033 2017
 5033 2019
 7039 2014
 7040 2013
 7040 2015
 7040 2017
 7041 2015
 7042 2017
 7042 2019
 7043 2019
 7044 2014
 7044 2015
 7044 2017
 7044 2020
 7045 2013
 7045 2015
 7045 2017
 7045 2019
 7046 2014
 7046 2016
 7046 2017
 7046 2019
 7047 2015
 7047 2017
 7047 2019
 7048 2017
 7048 2019
 7049 2017
 7049 2020
 7050 2020
 7050 2021
 7051 2021
 7052 2021
 7206 2018
 7206 2021
10034 2013
10034 2015
10034 2017
10035 2013
10035 2016
10035 2017
10035 2019
14031 2013
14031 2015
14032 2013
14032 2015
14032 2017
14032 2019
14033 2013
14033 2015
14033 2017
14033 2019
16034 2018
16034 2022
16174 2017
16177 2020
18039 2013
18039 2015
18039 2017
18039 2019
18040 2015
18040 2017
18040 2019
18041 2016
18041 2022
18042 2017
18042 2019
18045 2018
18045 2020
18189 2013
18189 2015
18189 2017
18189 2019
22030 2013
22030 2015
22031 2013
22031 2015
22031 2018
22032 2021
22176 2014
22176 2015
22176 2018
22176 2019
23032 2022
39031 2013
39031 2015
39031 2017
39031 2021
39032 2019
40035 2014
end

[Leonardo Guizzetti]

Thank you, Jenna. Here is some code I hope you find helpful.

* sort the data first
sort ID year

* confirm that you have unique pairs of ID and year
isid ID year

* tag first observation for each ID
egen tag_first = tag(ID)

* count how many IDs there are (unique values only)
summ tag_first, meanonly
di r(sum)

* alternatively, get the count in one step but does not create a tagged variable like previous.
quiet levelsof ID
di r(r)

* count how many years are contributed by each ID
gen int start_year = 2001
gen int end_year = 2021
bysort ID (year) : gen int num_years = _N

* count how many IDs have X years of data. You can specify the date range, or number of years, as you like.
summ tag_first if num_years == (end_year - start_year + 1), meanonly
di r(sum)
summ tag_first if num_years == 21, meanonly
di r(sum)

* supposing now you want to see patterns of data availability, here's one way to do this.
xtset ID year
xtdescribe

* but if you want this as a dataset, here's one approach.
qui summ year, meanonly
local first_year = r(min)
local last_year = r(max)

keep ID year
gen byte one = 1
reshape wide one, i(ID) j(year)
rename (one*) (nomiss*)
foreach v of varlist nomiss* {
  qui replace `v' = !mi(`v')
}
collapse (count) freq = ID , by(nomiss*)

label define nomiss 0 "." 1 "Y", modify
label values nomiss* nomiss

foreach v of varlist nomiss* {
  decode `v' , gen(s`v')
  drop `v'
}
egen pattern = concat(snomiss`first_year'-snomiss`last_year')
keep freq pattern
gsort -freq pattern
list, sepby(freq)

if you run this code, you'll see that there are no instances of ID that have data for the entire study period from 2001-2021. That's probably an issue of the sample that is used for -dataex-. I also included some code to look at missingness patterns.Here's selected output:

. list, sepby(freq)

     +-------------------+
     | freq      pattern |
     |-------------------|
  1. |    5   Y.Y.Y.Y... |
     |-------------------|
  2. |    3   ........Y. |
  3. |    3   ......Y... |
  4. |    3   ....Y.Y... |
  5. |    3   ..Y.Y.Y... |
     |-------------------|
  6. |    2   .........Y |
  7. |    2   .Y........ |
  8. |    2   Y.Y....... |
  9. |    2   Y.Y.Y..... |
     |-------------------|
 10. |    1   .......Y.. |
 11. |    1   .......YY. |
 12. |    1   .....Y...Y |
 13. |    1   .....Y..Y. |
 14. |    1   .....Y.Y.. |
 15. |    1   ....Y..... |
 16. |    1   ....Y..Y.. |
 17. |    1   ...Y.....Y |
 18. |    1   ..Y....... |
 19. |    1   .Y.YY..... |
 20. |    1   .Y.YY.Y... |
 21. |    1   .YY..YY... |
 22. |    1   .YY.Y..Y.. |
 23. |    1   Y..YY.Y... |
 24. |    1   Y.Y..Y.... |
 25. |    1   Y.Y.Y...Y. |
 26. |    1   Y.Y.Y..Y.. |
     +-------------------+