Dear all,

I have an unbalanced panel data. I am trying to calculate the average risk-taking ratio of similarly sized, successful other firms at T. Variable cumRisk5 is the average risk-taking ratio of the focal firm over the past 5 years. Perf_rank and fsize are quintile variables created based on firm profit over the past 5 years (Perf_rank) and firm sales over the past 5 years (fsize).

If the size code of firm j is the same as the focal firm i's, then these two firms are considered as 'similar' firms. If Perf_rank is 5 (top quartile), this firm is considered to be successful. For example, the focal firm's size is 1 (smallest), then I want to calculate the average risk-taking ratio of other successful firms with the same size number (1) and highest perf_rank (5) at t, which is the sum of cumRisk5 of 'other' firms with size 1 and Perf_rank 5 divided by the number of those other firms at T.

I want to calculate this number for each observation. I tried calculating this number manually by dividing the database into 5 parts based on size, but I would like to study if there is any simpler, wiser way of creating this variable (e.g. Loop). I have been learning a lot from Statalist, and I would greatly appreciate it if anyone can give me suggestions or comments.

Thank you in advance for your help!


Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input float firm int year float(cumRisk5 Perf_rank fsize)
 43 2011         1 4 3
 43 2012        .5 4 3
 43 2013  .3333333 4 3
 43 2014      1.25 4 4
 69 2011         1 4 4
 69 2012       1.5 4 4
 69 2013  .8333334 4 4
 79 2007         1 5 4
 79 2008        .5 5 4
 86 2004         1 2 3
 86 2005 1.1666667 2 5
 86 2006  .6666667 2 5
 86 2007 1.4722222 2 5
 86 2008  .8666667 3 5
 86 2011       1.2 3 5
 86 2012        .5 3 5
 86 2013  .3333333 3 5
 86 2014      1.25 3 5
 86 2015        .7 3 4
 89 2006         1 3 3
 89 2007        .5 3 4
 89 2008 1.3333334 3 4
 89 2009 1.0833334 3 4
 89 2010 1.0333333 3 4
 89 2011  .5277778 3 5
 89 2012  .3944445 3 5
 89 2013      1.15 3 5
 89 2014  .8166667 3 5
 89 2015  .4583333 3 5
103 2002  .3333333 1 3
103 2003       .25 1 2
103 2004        .2 1 2
103 2005         0 1 3
103 2006         0 1 3
119 2014         1 1 2
119 2015        .5 1 2
124 1997        .5 5 5
124 1998  .3333333 5 5
124 1999       .25 5 5
124 2000        .2 5 5
124 2001         0 5 5
124 2002         1 5 5
124 2003        .5 5 5
124 2004  .3333333 5 5
124 2005       .25 5 5
124 2006       1.2 5 5
124 2007        .5 5 5
124 2008  .3333333 5 5
124 2009       .25 5 5
124 2010        .2 5 5
124 2011         0 5 5
124 2012         0 5 5
124 2013         0 5 5
124 2014         0 5 5
124 2015         0 5 5
131 2009 1.3333334 2 2
131 2010       .75 3 2
131 2011 .53333336 3 3
131 2012       .25 3 3
131 2013        .2 3 3
138 2015         0 2 2
153 2009         1 4 4
153 2010        .5 4 5
153 2011  .3333333 4 5
153 2012       .25 4 5
153 2013        .2 4 5
153 2014         1 4 5
153 2015        .5 4 5
157 2004         1 3 4
157 2005        .5 3 4
157 2006  .3333333 3 4
157 2007       .25 3 4
157 2008        .2 3 4
168 2015         1 1 2
169 2012         1 5 5
169 2013       1.5 5 5
169 2014  .8333334 5 5
169 2015  .5833334 5 5
190 2008         1 4 4
195 2004         1 2 2
195 2005       1.5 2 2
195 2006  .8333334 2 2
195 2007  .5833334 2 2
195 2008       .45 2 2
198 2014         1 1 3
198 2015        .5 1 3
210 2012 1.3333334 2 3
210 2013       .75 2 3
210 2014 .53333336 2 3
210 2015       .25 2 3
212 2008         1 3 4
212 2009        .5 3 4
212 2010  .3333333 3 4
212 2011      1.25 3 4
212 2012        .7 4 4
236 2007      1.25 3 4
236 2008        .7 3 4
236 2009  .3333333 3 4
236 2010       .25 3 4
236 2011        .2 3 4
end
format %-ty year