Minimum value of last 3 observations

[Hans Schubert]

Greetings,
I am very new in the world of STATA.
Today I am working on a dataset within STATA where I need to generate a new variable, this new variable based on a formula calculated using other variables from the same dataset.
For clarity I have posted a pic of both how the data looks like both in STATA and EXCEL.
In the new variable 'min_last_3', the minimum value for the 3 previous ratings for that horse will be calculated
Thank you
Hans

[Hans Schubert]

I should add, if there are less than 3 previous rating values for each horse, then the minimum value from the previous 2 or previous 1 rating will be calculated. Sorry for the size of attachment.

[Nick Cox]

Please see FAQ Advice #12 and in passing #18. https://www.statalist.org/forums/help#stata Screenshots are not quite as useful as you hope for showing data.

ssc install rangestat 

rangestat (min) rating, int(horse_obs_no -3 -1) by(horse)

is a guess at what you want. With well behaved data, it is possible that

bysort horse (horse_obs_no) : gen wanted = min(rating[_n-1], rating[_n-2], rating[_n-3])

would work well too.

Also, searching the forum for mentions of rangestat will yield other examples.

[Ken Chui]

Please read and follow the board FAQ (http://www.statalist.org/forums/help) and use -dataex- command to provide some of your data in the form of codes, not image. Code-based code examples allow other users to input the data directly into Stata and test their code, so that we don't have to generate our own sample data (like what I did below). The actual codes are the just the last two lines:

set seed 172
clear
input str5 horse instance
A 1
B 2
C 3
D 5
E 6
end

expand instance
gsort horse
gen horse_obs_no = _n
gen rating = floor(runiform()*100)

rangestat (min) rating, interval(horse_obs_no -3 -1) by(horse)
bysort horse: replace rating_min = . if _n != _N

Output:

     +-------------------------------------------------+
     | horse   instance   horse_~o   rating   rating~n |
     |-------------------------------------------------|
  1. |     A          1          1       10          . |
  2. |     B          2          2       34          . |
  3. |     B          2          3       11         34 |
  4. |     C          3          4       84          . |
  5. |     C          3          5        2          . |
  6. |     C          3          6       87          2 |
  7. |     D          5          7       98          . |
  8. |     D          5          8       67          . |
  9. |     D          5          9       34          . |
 10. |     D          5         10       98          . |
 11. |     D          5         11       25         34 |
 12. |     E          6         12       60          . |
 13. |     E          6         13       62          . |
 14. |     E          6         14       74          . |
 15. |     E          6         15       87          . |
 16. |     E          6         16       66          . |
 17. |     E          6         17       65         66 |
     +-------------------------------------------------+

[Hans Schubert]

Thank you very much gentlemen, the solutions provided have all worked. I have added the help link to my favorites, and will refer to it before posting again.
Regards,
Hans