Obtain the first 3 maximum values of the rolling past 8 years?

[River Huang]

Dear All, Suppose I have the following data:

webuse grunfeld, clear

For each company and each year, I wish to construct three variables max1, max2, and max3, which is the (first, second, and third) maximum value of the past 8 years of `invest' variable, respectively. Any suggestions? Thanks.

PS: I think that the maximum (max1) can be obtained as follows (ssc install rangestat):

rangestat (max) max1=invest (count) n1=invest, interval(year -8 -1) by(company)

[Carlo Lazzaro]

River:
I'm probably not on spot with the following code, but being curious about experimenting something I'm not familiar with, I gave it a shot:

use "https://www.stata-press.com/data/r17/grunfeld.dta"
-gsort company year
rolling r(max), window(8) clear: egen wanted=max(invest)

Then it is a matter of selecting the results you're interested in.

[Nick Cox]

webuse grunfeld, clear 

mata: 

mata clear 
real rowvector max3(real colvector X) {
    X = select(X, (X :< .))
    _sort(X, -1)
    if (length(X) < 3)   X = X \ J(3 - length(X), 1,.) 
    return(X[1..3]')
}

end 

rangestat (max3) invest, int(year -7 0) by(company)

format max3* %2.1f 

list company year invest max3* in 1/20 


     +---------------------------------------------------+
     | company   year   invest    max31    max32   max33 |
     |---------------------------------------------------|
  1. |       1   1935    317.6    317.6        .       . |
  2. |       1   1936    391.8    391.8    317.6       . |
  3. |       1   1937    410.6    410.6    391.8   317.6 |
  4. |       1   1938    257.7    410.6    391.8   317.6 |
  5. |       1   1939    330.8    410.6    391.8   330.8 |
     |---------------------------------------------------|
  6. |       1   1940    461.2    461.2    410.6   391.8 |
  7. |       1   1941      512    512.0    461.2   410.6 |
  8. |       1   1942      448    512.0    461.2   448.0 |
  9. |       1   1943    499.6    512.0    499.6   461.2 |
 10. |       1   1944    547.5    547.5    512.0   499.6 |
     |---------------------------------------------------|
 11. |       1   1945    561.2    561.2    547.5   512.0 |
 12. |       1   1946    688.1    688.1    561.2   547.5 |
 13. |       1   1947    568.9    688.1    568.9   561.2 |
 14. |       1   1948    529.2    688.1    568.9   561.2 |
 15. |       1   1949    555.1    688.1    568.9   561.2 |
     |---------------------------------------------------|
 16. |       1   1950    642.9    688.1    642.9   568.9 |
 17. |       1   1951    755.9    755.9    688.1   642.9 |
 18. |       1   1952    891.2    891.2    755.9   688.1 |
 19. |       1   1953   1304.4   1304.4    891.2   755.9 |
 20. |       1   1954   1486.7   1486.7   1304.4   891.2 |
     +---------------------------------------------------+

[Andrew Musau]

You can also use rangerun from SSC.

webuse grunfeld, clear
keep company year invest
drop if missing(invest)

cap prog drop mymax
prog def mymax
bys company (invest): gen max = invest[_N]
bys company (invest): gen secondmax = invest[_N-1]
bys company (invest): gen thirdmax = invest[_N-2]
end

rangerun mymax, interval(year -7 0) by(company)

Res.:

. l if company==1, sep(0)

     +--------------------------------------------------------+
     | company   year   invest      max   second~x   thirdmax |
     |--------------------------------------------------------|
  1. |       1   1935    317.6    317.6          .          . |
  2. |       1   1936    391.8    391.8      317.6          . |
  3. |       1   1937    410.6    410.6      391.8      317.6 |
  4. |       1   1938    257.7    410.6      391.8      317.6 |
  5. |       1   1939    330.8    410.6      391.8      330.8 |
  6. |       1   1940    461.2    461.2      410.6      391.8 |
  7. |       1   1941      512      512      461.2      410.6 |
  8. |       1   1942      448      512      461.2        448 |
  9. |       1   1943    499.6      512      499.6      461.2 |
 10. |       1   1944    547.5    547.5        512      499.6 |
 11. |       1   1945    561.2    561.2      547.5        512 |
 12. |       1   1946    688.1    688.1      561.2      547.5 |
 13. |       1   1947    568.9    688.1      568.9      561.2 |
 14. |       1   1948    529.2    688.1      568.9      561.2 |
 15. |       1   1949    555.1    688.1      568.9      561.2 |
 16. |       1   1950    642.9    688.1      642.9      568.9 |
 17. |       1   1951    755.9    755.9      688.1      642.9 |
 18. |       1   1952    891.2    891.2      755.9      688.1 |
 19. |       1   1953   1304.4   1304.4      891.2      755.9 |
 20. |       1   1954   1486.7   1486.7     1304.4      891.2 |
     +--------------------------------------------------------+

[River Huang]

Dear @Carlo Lazzaro, @Nick Cox, and @Andrew Musau, Thanks a lot for the helpful suggestions

[Attaullah Shah]

As announced in the recent update, max, max2, max3, max4, and max5 are now available in asrol. The post is available here.


There was a bug in the ssc version, so I have uploaded a new version to my site. The updated version will be sent to SSC in a week time.

To install from my site:

net install asrol, from(http://fintechprofessor.com) replace

Here is a working example

 bys company: asrol invest, stat(max max2 max3) window(year 8)

. list company year invest* in 1/20

     +-------------------------------------------------------------+
     | company   year   invest   invest~x8   invest~28   invest~38 |
     |-------------------------------------------------------------|
  1. |       1   1935    317.6   317.60001           .           . |
  2. |       1   1936    391.8   391.79999   317.60001           . |
  3. |       1   1937    410.6   410.60001   391.79999   317.60001 |
  4. |       1   1938    257.7   410.60001   391.79999   317.60001 |
  5. |       1   1939    330.8   410.60001   391.79999   330.79999 |
     |-------------------------------------------------------------|
  6. |       1   1940    461.2   461.20001   410.60001   391.79999 |
  7. |       1   1941      512         512   461.20001   410.60001 |
  8. |       1   1942      448         512   461.20001         448 |
  9. |       1   1943    499.6         512   499.60001   461.20001 |
 10. |       1   1944    547.5       547.5         512   499.60001 |
     |-------------------------------------------------------------|
 11. |       1   1945    561.2   561.20001       547.5         512 |
 12. |       1   1946    688.1   688.09998   561.20001       547.5 |
 13. |       1   1947    568.9   688.09998   568.90002   561.20001 |
 14. |       1   1948    529.2   688.09998   568.90002   561.20001 |
 15. |       1   1949    555.1   688.09998   568.90002   561.20001 |
     |-------------------------------------------------------------|
 16. |       1   1950    642.9   688.09998   642.90002   568.90002 |
 17. |       1   1951    755.9   755.90002   688.09998   642.90002 |
 18. |       1   1952    891.2   891.20001   755.90002   688.09998 |
 19. |       1   1953   1304.4      1304.4   891.20001   755.90002 |
 20. |       1   1954   1486.7      1486.7      1304.4   891.20001 |
     +-------------------------------------------------------------+

More on asrol can found here https://fintechprofessor.com/asrol-f...tics-in-stata/

[River Huang]

Dear Attaullah, Thanks also for the new function of -asrol- command.

[Attaullah Shah]

Dear River, I appreciate your feedback. The ssc version is now up and running. To get it,

ssc install asrol, replace

[Nick Cox]

There is an interesting contrast in coding style here. If the problem is as described, then asrol already has in wired-in functionality and you are good to go once it is installed.

If the problem were a twist on that described -- say someone wants to look at the largest 6 or 7 -- then you could just tweak the code in #3 or #4 and you are better off using a command that is itself extensible through writing a little extra code. Again you need to install rangestat or rangerun first.

Naturally, you could just write your own code and not rely on anything that needs to be installed.

[River Huang]

Dear Attaullah, Got it and thanks.

[River Huang]

Dear Nick, I agree with what you concern. Thanks.