I'm working with some data on NFL players. I want to create a set of variables that captures the influence one player has historically had on a teammate of another position and have these variables show up for that player's current teammates. I realize that is very confusing, so I will attempt to illustrate with an example.

Jared Goff was the most important quarterback (the "QB1") on the Los Angeles Rams in 2018. The second-most important wide receiver on the Los Angeles Rams in 2018, statistically speaking, was Brandin Cooks (he was the "WR2"). In 2017, Brandin Cooks played for the New England Patriots. His top quarterback in 2017 on the Patriots, Tom Brady, threw for 32 touchdowns that season. What I want to do is create a variable called 'wr2_qb1_pass_td_lag' for Jared Goff in 2018 that gives the number of touchdowns Goff's WR2's previous QB1 threw for (i.e. wr2_qb1_pass_td_lag = 32 if (player == "Jared Goff" & year == 2018)).



My best attempt to do this so far has been:
Code:
egen wr2_qb1_pass_td_lag = max(cond(pos_rank_on_team == 2 & position == "WR"), qb1_pass_td_lag, .), by(team year)
, but this only seems to work when the WR2 hasn't changed teams, so I don't think it's really doing what I want it to do.


Can anyone help me out here? I'd really appreciate it!