Starting _n count at particular observations

[Ian Curtis]

Hey Stata users,

I have panel data on subjects and am interesting in how their wages change with time. For each subject, I want to see the number of years since wage data became available. If all subjects had wage information in the first year, this would be easy. I could generate a variable by subject ID equal to _n.

However, for many subjects, the wage data may only become available some years after the panel begins. For example subjects 2, 4, and 5 in this example,

ID Year Wages
1 2001 5
1 2002 5
1 2003 7
2 2001 .
2 2002 .
2 2003 5
3 2001 7
4 2002 .
4 2003 3
5 2001 .
5 2002 .
5 2003 4

I want to start counting in the first year Wages is available. Most of what I have tried has started counting in the first observation year and I cannot adjust this. Even when I made a variable for the first year wages was able and tried to temporally sort by this variable it did not work when making my count variable it did not work. Here is my code:

gen wage_avail = 0
replace wage_avail= 1 if wages>0 
replace wage_avail= 0 if wages==.

egen first_wage_year = min(year / (wage_avail== 1)), by(ID)
by ID first_wage_year, sort: gen wage_count = _n

Your informed thoughts are appreciated

[Rich Goldstein]

the following, in two steps will work for your example; I'm sure there is a one-step solution but it eludes me currently

egen start=min(year) if wages<., by(id)
gen count=year-start+1

here is what the result looks like on your example data

. li, clean noo

    id   year   wages   start   count  
     1   2001       5    2001       1  
     1   2002       5    2001       2  
     1   2003       7    2001       3  
     2   2001       .       .       .  
     2   2002       .       .       .  
     2   2003       5    2003       1  
     3   2001       7    2001       1  
     4   2002       .       .       .  
     4   2003       3    2003       1  
     5   2001       .       .       .  
     5   2002       .       .       .  
     5   2003       4    2003       1

[Ian Curtis]

That seemed to do it. Thank you Rich