Minimum and maximum values for five year pre and post period

[Stephen Okiya]

Hi Stata users,
I am using Stata 18 and would like to generate a few variables with minimum and maximum values of the year and sales for a given product and year considering five years before and after the year of consideration. For example, considering product D, the minimum and maximum years are 2014 and 16 respectively while the minimum and maximum sales are 95 and 100 respectively.
Below is an example dataset with the original variables and desired variables

* Example generated by -dataex-. For more info, type help dataex
clear
input str1 product int(year sales min_year max_year) byte min_value int max_value
"A" 2010 120 2010 2017 70 120
"A" 2011 105 2010 2018 70 120
"A" 2013 100 2010 2018 70 120
"A" 2014  95 2010 2018 70 120
"A" 2016  70 2010 2018 70 120
"A" 2017  85 2010 2018 70 120
"A" 2018 100 2011 2018 70 105
"B" 2012  80 2012 2017 80 110
"B" 2013  90 2012 2017 80 110
"B" 2014  95 2012 2017 80 110
"B" 2015 100 2012 2017 80 110
"B" 2016 110 2012 2017 80 110
"B" 2017 105 2012 2017 80 110
"C" 2010  75 2010 2015 75 120
"C" 2011 100 2010 2016 75 120
"C" 2012 120 2010 2017 75 120
"C" 2013 110 2010 2018 75 120
"C" 2014 105 2010 2019 75 120
"C" 2015  90 2010 2020 75 120
"C" 2016  85 2011 2021 85 120
"C" 2017 115 2012 2022 85 120
"C" 2018 100 2013 2023 85 115
"C" 2019  90 2014 2024 85 115
"C" 2020  95 2015 2025 80 115
"C" 2021 105 2016 2025 80 115
"C" 2022 100 2017 2025 80 115
"C" 2023 100 2018 2025 80 105
"C" 2024  90 2019 2025 80 105
"C" 2025  80 2020 2025 80 105
"D" 2014  95 2014 2016 95 100
"D" 2016 100 2014 2016 95 100
end

Thanks in advance!

[Nick Cox]

Your worked example doesn't seem to make sense to me.

I can't get beyond observation 1

"A" 2010 120 2010 2017 70 120

But the minimum after 2010 was in 2016, not 2017, and that's more than 5 years later either way,

Then your product D example seems to change focus to max and min over the entire record, ignoring the fact that there is no previous value for 2014 and no following value for 2016.

Here is some code for the maximum sales 5 years previous, 5 years following. If the maximum occurs in more than one year, I take the first. The code for the minimum is naturally similar.

I use rangerun and rangestat from SSC.

* Example generated by -dataex-. For more info, type help dataex
clear
input str1 product int(year sales min_year max_year) byte min_value int max_value
"A" 2010 120 2010 2017 70 120
"A" 2011 105 2010 2018 70 120
"A" 2013 100 2010 2018 70 120
"A" 2014  95 2010 2018 70 120
"A" 2016  70 2010 2018 70 120
"A" 2017  85 2010 2018 70 120
"A" 2018 100 2011 2018 70 105
"B" 2012  80 2012 2017 80 110
"B" 2013  90 2012 2017 80 110
"B" 2014  95 2012 2017 80 110
"B" 2015 100 2012 2017 80 110
"B" 2016 110 2012 2017 80 110
"B" 2017 105 2012 2017 80 110
"C" 2010  75 2010 2015 75 120
"C" 2011 100 2010 2016 75 120
"C" 2012 120 2010 2017 75 120
"C" 2013 110 2010 2018 75 120
"C" 2014 105 2010 2019 75 120
"C" 2015  90 2010 2020 75 120
"C" 2016  85 2011 2021 85 120
"C" 2017 115 2012 2022 85 120
"C" 2018 100 2013 2023 85 115
"C" 2019  90 2014 2024 85 115
"C" 2020  95 2015 2025 80 115
"C" 2021 105 2016 2025 80 115
"C" 2022 100 2017 2025 80 115
"C" 2023 100 2018 2025 80 105
"C" 2024  90 2019 2025 80 105
"C" 2025  80 2020 2025 80 105
"D" 2014  95 2014 2016 95 100
"D" 2016 100 2014 2016 95 100
end

keep product year sales 

dataex 

program myL 
    su sales, meanonly 
    gen maxL5 = r(max)
    su year if sales == maxL5, meanonly 
    gen yearL5 = r(min)
end 

program myF 
    su sales, meanonly 
    gen maxF5 = r(max)
    su year if sales == maxF5, meanonly 
    gen yearF5 = r(min)
end 

rangerun myL, int(year -5 -1) by(product) use(sales year)

rangerun myF, int(year 1 5) by(product) use(sales year)

list, sepby(product)


     +----------------------------------------------------------+
     | product   year   sales   maxL5   yearL5   maxF5   yearF5 |
     |----------------------------------------------------------|
  1. |       A   2010     120       .        .     105     2011 |
  2. |       A   2011     105     120     2010     100     2013 |
  3. |       A   2013     100     120     2010     100     2018 |
  4. |       A   2014      95     120     2010     100     2018 |
  5. |       A   2016      70     105     2011     100     2018 |
  6. |       A   2017      85     100     2013     100     2018 |
  7. |       A   2018     100     100     2013       .        . |
     |----------------------------------------------------------|
  8. |       B   2012      80       .        .     110     2016 |
  9. |       B   2013      90      80     2012     110     2016 |
 10. |       B   2014      95      90     2013     110     2016 |
 11. |       B   2015     100      95     2014     110     2016 |
 12. |       B   2016     110     100     2015     105     2017 |
 13. |       B   2017     105     110     2016       .        . |
     |----------------------------------------------------------|
 14. |       C   2010      75       .        .     120     2012 |
 15. |       C   2011     100      75     2010     120     2012 |
 16. |       C   2012     120     100     2011     115     2017 |
 17. |       C   2013     110     120     2012     115     2017 |
 18. |       C   2014     105     120     2012     115     2017 |
 19. |       C   2015      90     120     2012     115     2017 |
 20. |       C   2016      85     120     2012     115     2017 |
 21. |       C   2017     115     120     2012     105     2021 |
 22. |       C   2018     100     115     2017     105     2021 |
 23. |       C   2019      90     115     2017     105     2021 |
 24. |       C   2020      95     115     2017     105     2021 |
 25. |       C   2021     105     115     2017     100     2022 |
 26. |       C   2022     100     115     2017     100     2023 |
 27. |       C   2023     100     105     2021      90     2024 |
 28. |       C   2024      90     105     2021      80     2025 |
 29. |       C   2025      80     105     2021       .        . |
     |----------------------------------------------------------|
 30. |       D   2014      95       .        .     100     2016 |
 31. |       D   2016     100      95     2014       .        . |
     +----------------------------------------------------------+

For the maximum or minimum in any previous year use int(year . -1) and for any following year use int(year 1 .). Also use different new variable names.

[Stephen Okiya]

Nick Cox You never disappoint Thanks so much for your incredible help!!

[Stephen Okiya]

Sorry for coming back but am interested in the average in the same period. Any help is appreciated.

[Nick Cox]

You should be able to work that out. Just as within the short programs the maximum is pulled out of r(max) after summarize so the mean can be pulled out of r(mean). See

help summarize

* Example generated by -dataex-. For more info, type help dataex
clear
input str1 product int(year sales min_year max_year) byte min_value int max_value
"A" 2010 120 2010 2017 70 120
"A" 2011 105 2010 2018 70 120
"A" 2013 100 2010 2018 70 120
"A" 2014  95 2010 2018 70 120
"A" 2016  70 2010 2018 70 120
"A" 2017  85 2010 2018 70 120
"A" 2018 100 2011 2018 70 105
"B" 2012  80 2012 2017 80 110
"B" 2013  90 2012 2017 80 110
"B" 2014  95 2012 2017 80 110
"B" 2015 100 2012 2017 80 110
"B" 2016 110 2012 2017 80 110
"B" 2017 105 2012 2017 80 110
"C" 2010  75 2010 2015 75 120
"C" 2011 100 2010 2016 75 120
"C" 2012 120 2010 2017 75 120
"C" 2013 110 2010 2018 75 120
"C" 2014 105 2010 2019 75 120
"C" 2015  90 2010 2020 75 120
"C" 2016  85 2011 2021 85 120
"C" 2017 115 2012 2022 85 120
"C" 2018 100 2013 2023 85 115
"C" 2019  90 2014 2024 85 115
"C" 2020  95 2015 2025 80 115
"C" 2021 105 2016 2025 80 115
"C" 2022 100 2017 2025 80 115
"C" 2023 100 2018 2025 80 105
"C" 2024  90 2019 2025 80 105
"C" 2025  80 2020 2025 80 105
"D" 2014  95 2014 2016 95 100
"D" 2016 100 2014 2016 95 100
end

keep product year sales 

dataex 

program myL 
    su sales, meanonly 
    gen maxL5 = r(max)
    gen minL5 = r(min)
    gen meanL5 = r(mean)
    
    su year if sales == maxL5, meanonly 
    gen yearmaxL5 = r(min)
    
    su year if sales == minL5, meanonly 
    gen yearminL5 = r(min)
end 

program myF 
    su sales, meanonly 
    gen maxF5 = r(max)
    gen minF5 = r(min)
    gen meanF5 = r(mean)
    
    su year if sales == maxF5, meanonly 
    gen yearmaxF5 = r(min)
    
    su year if sales == minF5, meanonly 
    gen yearminF5 = r(min)
end 

rangerun myL, int(year -5 -1) by(product) use(sales year)

rangerun myF, int(year 1 5) by(product) use(sales year)

list, sepby(product)

     +--------------------------------------------------------------------------------------------------------------------------+
     | product   year   sales   maxL5   minL5     meanL5   year~xL5   year~nL5   maxF5   minF5     meanF5   year~xF5   year~nF5 |
     |--------------------------------------------------------------------------------------------------------------------------|
  1. |       A   2010     120       .       .          .          .          .     105      95        100       2011       2014 |
  2. |       A   2011     105     120     120        120       2010       2010     100      70   88.33334       2013       2016 |
  3. |       A   2013     100     120     105      112.5       2010       2011     100      70       87.5       2018       2016 |
  4. |       A   2014      95     120     100   108.3333       2010       2013     100      70         85       2018       2016 |
  5. |       A   2016      70     105      95        100       2011       2014     100      85       92.5       2018       2017 |
  6. |       A   2017      85     100      70   88.33334       2013       2016     100     100        100       2018       2018 |
  7. |       A   2018     100     100      70       87.5       2013       2016       .       .          .          .          . |
     |--------------------------------------------------------------------------------------------------------------------------|
  8. |       B   2012      80       .       .          .          .          .     110      90        100       2016       2013 |
  9. |       B   2013      90      80      80         80       2012       2012     110      95      102.5       2016       2014 |
 10. |       B   2014      95      90      80         85       2013       2012     110     100        105       2016       2015 |
 11. |       B   2015     100      95      80   88.33334       2014       2012     110     105      107.5       2016       2017 |
 12. |       B   2016     110     100      80      91.25       2015       2012     105     105        105       2017       2017 |
 13. |       B   2017     105     110      80         95       2016       2012       .       .          .          .          . |
     |--------------------------------------------------------------------------------------------------------------------------|
 14. |       C   2010      75       .       .          .          .          .     120      90        105       2012       2015 |
 15. |       C   2011     100      75      75         75       2010       2010     120      85        102       2012       2016 |
 16. |       C   2012     120     100      75       87.5       2011       2010     115      85        101       2017       2016 |
 17. |       C   2013     110     120      75   98.33334       2012       2010     115      85         99       2017       2016 |
 18. |       C   2014     105     120      75     101.25       2012       2010     115      85         96       2017       2016 |
 19. |       C   2015      90     120      75        102       2012       2010     115      85         97       2017       2016 |
 20. |       C   2016      85     120      90        105       2012       2015     115      90        101       2017       2019 |
 21. |       C   2017     115     120      85        102       2012       2016     105      90         98       2021       2019 |
 22. |       C   2018     100     115      85        101       2017       2016     105      90         98       2021       2019 |
 23. |       C   2019      90     115      85         99       2017       2016     105      90         98       2021       2024 |
 24. |       C   2020      95     115      85         96       2017       2016     105      80         95       2021       2025 |
 25. |       C   2021     105     115      85         97       2017       2016     100      80       92.5       2022       2025 |
 26. |       C   2022     100     115      90        101       2017       2019     100      80         90       2023       2025 |
 27. |       C   2023     100     105      90         98       2021       2019      90      80         85       2024       2025 |
 28. |       C   2024      90     105      90         98       2021       2019      80      80         80       2025       2025 |
 29. |       C   2025      80     105      90         98       2021       2024       .       .          .          .          . |
     |--------------------------------------------------------------------------------------------------------------------------|
 30. |       D   2014      95       .       .          .          .          .     100     100        100       2016       2016 |
 31. |       D   2016     100      95      95         95       2014       2014       .       .          .          .          . |
     +--------------------------------------------------------------------------------------------------------------------------+

.

You may need first to

program drop myF 
program drop myL

if those programs are in memory

[Stephen Okiya]

Thanks so much Nick Cox for the additional help. I sincerely appreciate.