Hi everybody,
I have data where each row represents one individual in a given year, working in a given firm. For each individual within the same year and firm, I'd like to compute the mean wage of his coworkers given that the coworker's wage is lower than the individual's own wage. For instance, I have
Person_ID Firm_ID Year Wage
1 200 2005 5
2 200 2005 6
3 200 2005 7
4 200 2005 8
5 200 2005 8
1 200 2007 4
2 200 2007 5
Then, for Person 1 in year 2005, the new variable should be a missing since he is the Person earning the least in that given year and firm. For Person 2 in 2005, it should be 5, for Person 3 in 2005, it should be 5.5, and for Person 5 in 2005 it should be 6 (since Person 4 is not earning less but exactly the same wage).

Any idea how I could realize this? For now, I have

gen wage_peer=0
gen wage_count=0

sum teamsize, det
local hilfsvar = `r(max)'
forvalues i=1/`hilfsvar' {
replace wage_peer= wage_peer + wage[_n+`i'] if team_ident == team_ident[_n+`i'] & ///
wage != wage[_n+`i'] & !missing(team_ident)
replace wage_count= wage_count + 1 if team_ident == team_ident[_n+`i'] & ///
wage != wage[_n+`i'] & !missing(team_ident)
}

Where team_ident is an id variable for distinct firm & year combinations and teamsize gives the number of people within a team. This code works, but is really time-consuming, so I am looking for an easier solution.

Thank you!