Hello Statalist,

I am totally new to Stata, and I hope someone can help me on how to handle this task. My data looks like this:
CFOANN CEOANN COMP ID YEAR
CEO 2476.82 1072 2010
915.192 1072 2010
CFO 781.615 1072 2010
698.464 1072 2010
686.657 1072 2010
CEO 1.691.361 1072 2011
CFO 649.659 1072 2011
619.669 1072 2011
549.004 1072 2011
548.804 1072 2011
CFEOANN indicates whether the person is a CFO, and CEO indicates whether the person is CFO. COMP is the pay and ID is the company identifier.

I want to generate a variable that contains for each separate year the CEO's portion of the aggregate pay to the top 5 executives, but not including the CFO in the denominator.
When I have compensation data for more than five executives, use only the five highest paid executives. When I have compensation data for fewer than five executives, use the pay from the lowest paid executive in place of that of the missing executives.

So in the example above the lowest compensations should be taken into consideration twice because the compensation of the CFO should be excluded.

Any ideas on how to handle this? Thanks in advance!