I'm working with some data on NFL football players. I want to generate a variable that reflects how well players of a certain position do with a certain coach.

A simplified version of my data looks like this:

Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input str14 player int year str2 team str14 coach str2 position byte pos_rank_on_team int yards
"Tom Brady"      2018 "NE" "Bill Belichick" "QB" 1    0
"Brandin Cooks"  2018 "NE" "Bill Belichick" "WR" 1 1000
"Julian Edelman" 2018 "NE" "Bill Belichick" "WR" 2  900
"Tom Brady"      2017 "NE" "Bill Belichick" "QB" 1    0
"Julian Edelman" 2017 "NE" "Bill Belichick" "WR" 1  850
"Chris Hogan"    2017 "NE" "Bill Belichick" "WR" 2  700
"Drew Brees"     2018 "NO" "Sean Payton"    "QB" 1    0
"Michael Thomas" 2018 "NO" "Sean Payton"    "WR" 1 1500
"Ted Ginn"       2018 "NO" "Sean Payton"    "WR" 2  400
"Drew Brees"     2017 "NO" "Sean Payton"       "QB" 1    0
"Michael Thomas" 2017 "NO" "Sean Payton"       "WR" 1 1200
"Brandin Cooks"  2017 "NO" "Sean Payton"       "WR" 2  935
end
-------------

I wanted to create a variable wr1_yards_lag_coach that gives me the yards gained by the WR1 (position = "WR" and pos_rank_on_team == 1) coached by that observation's coach the previous year. (I understand this is very confusing, so I will give examples.)

The first observation is Tom Brady in 2018. Tom Brady's coach in 2018 is Bill Belichick. Bill Belichick's WR1 the prior year (2017) was Julian Edelman. Edelman gained 850 yards in 2017. Thus, for the first observation, wr1_yards_lag_coach = 850.

The second obsevation is Brandin Cooks in 2018. Brandin Cooks' coach in 2018 is Bill Belichick. Bill Belichick's WR1 the prior year (2017) was Julian Edelman. Edelman gained 850 yards in 2017. Thus, for the second observation, wr1_yards_lag_coach = 850.

The third observation is Julian Edelman in 2018. Julian Edelman's coach in 2018 is Bill Belichick. Bill Belichick's WR1 the prior year (2017) was Julian Edelman. Edelman gained 850 yards in 2017. Thus, for the third observation, wr1_yards_lag_coach = 850.

The fourth-to-sixth observations would have no values because there is no 2016 data in this simplified dataset.

The seventh observation is Drew Brees in 2018. Drew Brees' coach in 2018 is Sean Payton. Sean Payton's WR1 the prior year (2017) was Michael Thomas. Thomas gained 1200 yards in 2017. Thus, for the seventh observation wr1_yards_lag_coach = 1200.

The eighth observation is Michael Thomas in 2018. Michael Thomas' coach in 2018 is Sean Payton. Sean Payton's WR1 the prior year (2017) was Michael Thomas. Thomas gained 1200 yards in 2017. Thus, for the eighth observation wr1_yards_lag_coach = 1200.

The ninth observation is Brandin Cooks in 2018. Brandin Cooks' coach in 2018 is Sean Payton. Sean Payton's WR1 the prior year (2017) was Michael Thomas. Thomas gained 1200 yards in 2017. Thus, for the ninth observation wr1_yards_lag_coach = 1200.

The tenth-to-twelfth observations would have no values because there is no 2016 data in this simplified dataset.

I was able to do this with the following code:

Code:
encode player, gen(player_e)

xtset player_e year

encode coach, gen(coach_e)

egen wr1_yards_lag_coach = max(cond(position == "WR" & L.pos_rank_on_team == 1 & coach_e == L.coach_e), L.yards, .), by(coach year)

However, for some reason the same idea doesn't work for the WR2's. The following code just generates missing values:

Code:
egen wr2_yards_lag_coach = max(cond(position == "WR" & L.pos_rank_on_team == 2 & coach_e == L.coach_e), L.yards, .), by(coach year)

Here the values I want are as follows:

The first observation is Tom Brady in 2018. Tom Brady's coach in 2018 is Bill Belichick. Bill Belichick's WR2 the prior year (2017) was Chris Hogan. Hogan gained 700 yards in 2017. Thus, for the first observation, wr2_yards_lag_coach = 700.

Similarly, wr2_yards_lag_coach = 700 for the second and third observations.

4-6 would blank.

The seventh observation is Drew Brees in 2018. Drew Brees' coach in 2018 is Sean Payton. Sean Payton's WR2 the prior year (2017) was Brandin Cooks. Cooks gained 935 yards in 2017. Thus, for the seventh obsevation, wr2_yards_lag_coach = 935. (Similarly for 8 and 9).

10-12 would be blank.



Can anyone help me with this issue? I don't understand why my WR1 idea doesn't work for my WR2 and thus I don't know how to solve my WR2 problem. Any help would be greatly appreciated - thanks!