[Nick Cox]

You can calculate what you ask for but nothing stops it being missing when it is.

[Pandelis Andreou]

It's not really missing.

AEA has the same value for all winners in every year.

Therefore, there is the same lagged value of AEA, for all winners,

I am just looking for a command in Stata to do that, so I would not calculate them one by one manually in Excel.

[Nick Cox]

This may help, although i am struggling to imagine a data generation process that makes sense. Using dataex as we request would have simplified the code (FAQ Advice #12).

clear
input Year str6     SOrder    Age    AEA
2010    First    69    10
2010    Second    58    10
2010    Third    71    10
2011    First    59    15
2011    Second    65    15
2012    First    62    20
2012    Second    62    20
2013    First    61    25
2013    Second    60    25
2013    Third    60    25
2014    First    59    30
2015    First    58    35
2016    First    57    40
2016    Second    57    40
2017    First    56    45
2018    First    55    50
2018    Second    55    50
2019    First    54    55
2019    Second    53    55
2019    Third    53    55
end

label define Order 1 First 2 Second 3 Third
encode SOrder, gen(Order) label(Order)

tsset Order Year

tsfill
bysort Year (AEA) : replace AEA = AEA[1] if missing(AEA)
sort Year Order
list, sepby(Year)

     +------------------------------------+
     | Year   SOrder   Age   AEA    Order |
     |------------------------------------|
  1. | 2010    First    69    10    First |
  2. | 2010   Second    58    10   Second |
  3. | 2010    Third    71    10    Third |
     |------------------------------------|
  4. | 2011    First    59    15    First |
  5. | 2011   Second    65    15   Second |
  6. | 2011              .    15    Third |
     |------------------------------------|
  7. | 2012    First    62    20    First |
  8. | 2012   Second    62    20   Second |
  9. | 2012              .    20    Third |
     |------------------------------------|
 10. | 2013    First    61    25    First |
 11. | 2013   Second    60    25   Second |
 12. | 2013    Third    60    25    Third |
     |------------------------------------|
 13. | 2014    First    59    30    First |
 14. | 2014              .    30   Second |
 15. | 2014              .    30    Third |
     |------------------------------------|
 16. | 2015    First    58    35    First |
 17. | 2015              .    35   Second |
 18. | 2015              .    35    Third |
     |------------------------------------|
 19. | 2016    First    57    40    First |
 20. | 2016   Second    57    40   Second |
 21. | 2016              .    40    Third |
     |------------------------------------|
 22. | 2017    First    56    45    First |
 23. | 2017              .    45   Second |
 24. | 2017              .    45    Third |
     |------------------------------------|
 25. | 2018    First    55    50    First |
 26. | 2018   Second    55    50   Second |
 27. | 2018              .    50    Third |
     |------------------------------------|
 28. | 2019    First    54    55    First |
 29. | 2019   Second    53    55   Second |
 30. | 2019    Third    53    55    Third |
     +------------------------------------+

[Pandelis Andreou]

Nick Cox

Thank you, it does the trick - I have checked the file.

However, when I try to create dAEA = AEA - L1.AEA

it writes "not sorted".

daea is the variable that I should use. By filling the gaps and having lags I thought it would be easy to create it.

[Nick Cox]

You need to tsset or xtset your data for time series operators to work, and a side-effect of that will be that they are sorted to the right order. Then -- assuming that can be done -- first differences can be calculated as (e.g.) D.AEA

[Pandelis Andreou]

Because I did it above, I didn't think to re-write tsset.

Yes, now is fine.

THANKS