Hello all,
I have a data of employees and vesting periods of stocks from which I have derived 'var'. The var is a year based variable. However, currently var only covers the awards given in the current year and they do not account for the stocks in previous years that may yet to vest in the current year. To account for the previous years, I wanted to generate a weighted average of var across years, such that the var value at year t0 would have greater weighing, while the values at previous years would also be included for the calculation but would be placed a lesser weight. Here is a small sample of my data:
fyear firm_id employee_id var
1998 000000xxxx 30147 3
1998 000000xxxx 30149 3
1998 000000xxxx 490 3
1998 000000xxxx 70638 3
1998 000000xxxx 70640 3
1999 000000xxxx 30147 4
1999 000000xxxx 30149 4
1999 000000xxxx 490 4
1999 000000xxxx 70638 4
1999 000000xxxx 70640 4
2000 000000xxxx 30147 4
2000 000000xxxx 30148 4.462788
2000 000000xxxx 30149 4
2000 000000xxxx 490 4
2000 000000xxxx 494 4.462788
2001 000000xxxx 30148 4
2001 000000xxxx 30149 4
2001 000000xxxx 490 4.611662
2001 000000xxxx 492 4
2001 000000xxxx 494 4
2002 000000xxxx 30148 3
2002 000000xxxx 87472 3
2003 000000xxxx 30148 4
2003 000000xxxx 490 4
2003 000000xxxx 492 4
2003 000000xxxx 494 4
2003 000000xxxx 87472 4
2004 000000xxxx 30148 4
2004 000000xxxx 490 4
2004 000000xxxx 492 4
2004 000000xxxx 494 4
2004 000000xxxx 87472 4
What I want to do is to have, for each employee, a weighted average of var. It would look like this weigh_var2004=[var2004+(var2003-1)+(var2002-2)+(var2001-3).....]/numyear where numyear would represent the number of years the employee is observed in the sample between 1998 and 2004. This weighted variable would also be calculated for each year, such that the same individual would also have weigh_var2003=[var2003+(var2002-1)+(var2001-2)+(var2000-3).....]/numyear where in this case it would represent the number of years the employee is observed in the sample between 1998 and 2003. So it would be a weighted average for each year taking into account the previous years' information.
However, I would like to be able to omit the missing years as there are gaps in years for some firms or individuals. In addition, I would like to omit from the weighted average calculation the values that would be negative, and dynamically change the numyear such that the years where var would be negative in weighting would be dropped.
Any help would be much appreciated. Thank you in advance!
I have a data of employees and vesting periods of stocks from which I have derived 'var'. The var is a year based variable. However, currently var only covers the awards given in the current year and they do not account for the stocks in previous years that may yet to vest in the current year. To account for the previous years, I wanted to generate a weighted average of var across years, such that the var value at year t0 would have greater weighing, while the values at previous years would also be included for the calculation but would be placed a lesser weight. Here is a small sample of my data:
fyear firm_id employee_id var
1998 000000xxxx 30147 3
1998 000000xxxx 30149 3
1998 000000xxxx 490 3
1998 000000xxxx 70638 3
1998 000000xxxx 70640 3
1999 000000xxxx 30147 4
1999 000000xxxx 30149 4
1999 000000xxxx 490 4
1999 000000xxxx 70638 4
1999 000000xxxx 70640 4
2000 000000xxxx 30147 4
2000 000000xxxx 30148 4.462788
2000 000000xxxx 30149 4
2000 000000xxxx 490 4
2000 000000xxxx 494 4.462788
2001 000000xxxx 30148 4
2001 000000xxxx 30149 4
2001 000000xxxx 490 4.611662
2001 000000xxxx 492 4
2001 000000xxxx 494 4
2002 000000xxxx 30148 3
2002 000000xxxx 87472 3
2003 000000xxxx 30148 4
2003 000000xxxx 490 4
2003 000000xxxx 492 4
2003 000000xxxx 494 4
2003 000000xxxx 87472 4
2004 000000xxxx 30148 4
2004 000000xxxx 490 4
2004 000000xxxx 492 4
2004 000000xxxx 494 4
2004 000000xxxx 87472 4
What I want to do is to have, for each employee, a weighted average of var. It would look like this weigh_var2004=[var2004+(var2003-1)+(var2002-2)+(var2001-3).....]/numyear where numyear would represent the number of years the employee is observed in the sample between 1998 and 2004. This weighted variable would also be calculated for each year, such that the same individual would also have weigh_var2003=[var2003+(var2002-1)+(var2001-2)+(var2000-3).....]/numyear where in this case it would represent the number of years the employee is observed in the sample between 1998 and 2003. So it would be a weighted average for each year taking into account the previous years' information.
However, I would like to be able to omit the missing years as there are gaps in years for some firms or individuals. In addition, I would like to omit from the weighted average calculation the values that would be negative, and dynamically change the numyear such that the years where var would be negative in weighting would be dropped.
Any help would be much appreciated. Thank you in advance!
