Three-year average of a variable

[Sally Ahmed]

Hello all, I want to create a variable which is defined as the annual decile rankings of the three-year average of total loss recognition timeliness. I want to have an average for each year starting from 2002 to 2018.

I tried to create the following command but it says invalid syntax:

egen AVR2002= rmean (Converse Converse [_n-1] Converse [_n-2]) if year==year[_n-1]+1

Would appreciate your help regarding that.

[Øyvind Snilsberg]

do you want the average of 2001, 2002 and 2003 if year is 2002?
if you have data for 2001-2019,

gen mean=.
forvalues i=2002/2018 {
replace mean = (converse[_n-1] + converse + converse[_n+1])/3 if year==`i'
}

if you only have data for 2002-2018 you also need,

replace mean = (converse + converse[_n+1])/2 if year==2002
replace mean = (converse + converse[_n-1])/2 if year==2018

i.e. the average of 2002 and 2003 if year is 2002 and the average of 2017 and 2018 if year is 2018

[Sally Ahmed]

Thank you for the code it now works but I checked the new variable it did not create a variable for the year 2002 it starts from 2004 and ends at year 2017. I tried this code but still the outcome is just from 2004 do you know how can I fix that?

gen mean=.
forvalues i=2000/2018 {
replace mean = (Converse[_n-2] + Converse[_n-1] + Converse)/3 if year==`i'
}

[Nick Cox]

When does the data start? You need all three values to be in the dataset and not missing for the mean to be not missing. If you want the mean to be based on available years you need different code.

Here is a dopey example using rangestat from SSC.

. clear

. set obs 21
Number of observations (_N) was 0, now 21.

. gen year = 1999 + _n

. gen outcome = _n

. list

     +----------------+
     | year   outcome |
     |----------------|
  1. | 2000         1 |
  2. | 2001         2 |
  3. | 2002         3 |
  4. | 2003         4 |
  5. | 2004         5 |
     |----------------|
  6. | 2005         6 |
  7. | 2006         7 |
  8. | 2007         8 |
  9. | 2008         9 |
 10. | 2009        10 |
     |----------------|
 11. | 2010        11 |
 12. | 2011        12 |
 13. | 2012        13 |
 14. | 2013        14 |
 15. | 2014        15 |
     |----------------|
 16. | 2015        16 |
 17. | 2016        17 |
 18. | 2017        18 |
 19. | 2018        19 |
 20. | 2019        20 |
     |----------------|
 21. | 2020        21 |
     +----------------+

. gen your_average = (outcome[_n-2] + outcome[_n-1] + outcome) / 3
(2 missing values generated)

. rangestat (count) other_count=outcome (mean) other_average=outcome, int(year -2 0)

. l

     +-------------------------------------------------+
     | year   outcome   your_a~e   other_~t   other_~e |
     |-------------------------------------------------|
  1. | 2000         1          .          1          1 |
  2. | 2001         2          .          2        1.5 |
  3. | 2002         3          2          3          2 |
  4. | 2003         4          3          3          3 |
  5. | 2004         5          4          3          4 |
     |-------------------------------------------------|
  6. | 2005         6          5          3          5 |
  7. | 2006         7          6          3          6 |
  8. | 2007         8          7          3          7 |
  9. | 2008         9          8          3          8 |
 10. | 2009        10          9          3          9 |
     |-------------------------------------------------|
 11. | 2010        11         10          3         10 |
 12. | 2011        12         11          3         11 |
 13. | 2012        13         12          3         12 |
 14. | 2013        14         13          3         13 |
 15. | 2014        15         14          3         14 |
     |-------------------------------------------------|
 16. | 2015        16         15          3         15 |
 17. | 2016        17         16          3         16 |
 18. | 2017        18         17          3         17 |
 19. | 2018        19         18          3         18 |
 20. | 2019        20         19          3         19 |
     |-------------------------------------------------|
 21. | 2020        21         20          3         20 |
     +-------------------------------------------------+

.

[Øyvind Snilsberg]

if you have data for 2002-2018, then

replace mean = (Converse[_n-2] + Converse[_n-1] + Converse)/3 if year==`i'

will be missing for years less than 2004 because Converse[_n-2] will be less than 2002

[Sally Ahmed]

I have the data from 1999 I am trying now to fix it, Thanks to both of you. I need to now to calculate the second part of the measurement which is annual decile rank of the new variable (mean) I just created which represents the three--year average of Converse. Could you please help me with the code as I tried some and they do not seem to work.

[Nick Cox]

Decile rank sounds like something to get from xtile. We can't comment on what you tried or "does not seem to work". without any details.

[Sally Ahmed]

I tried this code xtile decile= AVR, nq(10) but I am not sure if this is the annual decile rank.

[Nick Cox]

Nothing in your code says anything about the year. What you want depends on your data structure, which I don't think you've explained. For example, if you have panel data you need to do something different.

[Sally Ahmed]

yes it is panel data so how can I consider the year in my code.

[Nick Cox]

I assume you have calculated your three-year means and that you carefully kept calculations separate for each panel. Warning: That won't happen unless you specify it in your code.

Then there are various add-ons for quantile binning with panel data. That most familiar to me is the xtile() function in egenmore from SSC which will work if and only if you install the package;

ssc install egenmore

Here is a trivial example you can reproduce given that:

webuse grunfeld

egen decile = xtile(invest), by(year) nq(10)

tab decile

line decile year , by(company)