Calculate in a loop the min and max of a series of variables

[Riccardo Busin]

Good morning
I would like to create the following operations in a single cycle (to calculate min and max) currently divided into many different codes (as in the example I report here):

forvalues y=15(1)19 {
egen m=min(ROE20`y')
egen M=max(ROE20`y')
gen ROE20`y'Norm=(ROE20`y'-m)/(M-m)
drop m M
}

forvalues y=15(1)19 {
egen m=min(EBITDAVENDITE20`y')
egen M=max(EBITDAVENDITE20`y')
gen EBITDAVENDITE20`y'Norm=(EBITDAVENDITE20`y'-m)/(M-m)
drop m M
}

forvalues y=15(1)19 {
egen m=min(ROI20`y')
egen M=max(ROI20`y')
gen ROI20`y'Norm=(ROI20`y'-m)/(M-m)
drop m M
}

forvalues y=15(1)19 {
egen m=min(TOTFATTVAR20`y')
egen M=max(TOTFATTVAR20`y')
gen TOTFATTVAR20`y'Norm=(TOTFATTVAR20`y'-m)/(M-m)
drop m M
}

forvalues y=15(1)19 {
egen m=min(LEVERAGE20`y')
egen M=max(LEVERAGE20`y')
gen LEVERAGE20`y'Norm=(LEVERAGE20`y'-m)/(M-m)
drop m M
}

forvalues y=15(1)19 {
egen m=min(DE20`y')
egen M=max(DE20`y')
gen DE20`y'Norm=(DE20`y'-m)/(M-m)
drop m M
}

forvalues y=15(1)19 {
egen m=min(TOTIMMVAR20`y')
egen M=max(TOTIMMVAR20`y')
gen TOTIMMOVAR20`y'Norm=(TOTIMMOVAR20`y'-m)/(M-m)
drop m M
}

forvalues y=15(1)19 {
egen m=min(PFNEBITDA20`y')
egen M=max(PFNEBITDA20`y')
gen PFNEBITDA20`y'Norm=(PFNEBITDA20`y'-m)/(M-m)
drop m M
}

To combine these different operations in a single loop I thought of this code:

local Year1=2013
local YearN=2019

forvalues y=`=`YearN'-4'(1)`YearN' {        
    egen m1=min(ROE`y')
    egen m2=min(EBITDAVENDITE`y')
    egen m3=min(ROI`y')
    egen m4=min(LEVERAGE`y')
    egen m5=min(DE`y')
    egen m6=min(PFNEBITDA`y')
    egen m7=min(TOTFATTVAR`y')
    egen m8=min(TOTIMMVAR`y')
    egen M1=max(ROE`y')
    egen M2=max(EBITDAVENDITE`y')
    egen M3=max(ROI`y')
    egen M4=max(LEVERAGE`y')
    egen M5=max(DE`y')
    egen M6=max(PFNEBITDA`y')
    egen M7=max(TOTFATTVAR`y')
    egen M8=max(TOTIMMVAR`y')
    gen ROE`y'Norm=(ROE`y'-m1)/(M1-m1)
    gen EBITDAVENDITE`y'Norm=(EBITDAVENDITE`y'-m2)/(M2-m2)
    gen ROI`y'Norm=(ROI`y'-m3)/(M3-m3)
    gen LEVERAGE`y'Norm=(LEVERAGE`y'-m4)/(M4-m4)
    gen DE`y'Norm=(DE`y'-m5)/(M5-m5)
    gen PFNEBITDA`y'Norm=(PFNEBITDA`y'-m6)/(M6-m6)
    gen TOTFATTVAR`y'Norm=(TOTFATTVAR`y'-m7)/(M7-m7)
    gen TOTIMMVAR`y'Norm=(TOTIMMVAR`y'-m8)/(M8-m8)
    drop m* M*
}

Second, is it possible to create a more simplified form? avoiding to create several m (m1, m2 ....) and M (M1, M2 ....).
Thanks again!

[Nick Cox]

The first and most crucial piece of advice is to reshape long. Then what you want is obtainable much more simply and directly. This sketch needs some adaptation.

local stubs ROE EBITDAVENDITE ROI

reshape long `stubs' , i(someidentifier) j(year)

foreach v in `stubs' { 
    egen min`v' = min(`v'), by(year)
    egen max`v' = max(`v'), by(year)
    gen scaled`v' = (`v' - min`v') / (max`v' - min`v')
}

[Riccardo Busin]

The first and most crucial piece of advice is to reshape long. Then what you want is obtainable much more simply and directly. This sketch needs some adaptation.

local stubs ROE EBITDAVENDITE ROI

reshape long `stubs' , i(someidentifier) j(year)

foreach v in `stubs' {
egen min`v' = min(`v'), by(year)
egen max`v' = max(`v'), by(year)
gen scaled`v' = (`v' - min`v') / (max`v' - min`v')
}

Thanks!
I wanted to ask you one last thing.
In my entire code I never use reshape.
is it possible to rewrite the code without using the reshape?
Thanks again

[Nick Cox]

Sure, you can write code without the reshape but the reshape is such a good idea that you should learn how to do it. Otherwise, you are just laying up a long campaign of busy work with a data structure not fit for purpose.

[KitBaum]

Although Nick makes a good point about familiarity with reshape, there is no need to do so here, and there is also no need to use egen to create variables to hold the min and max when those are just scalars in your data. Pass the variables to Mata and use colminmax() and the colon operator.

webuse lutkepohl,clear
su dl*
mata
st_view(x=.,.,"dlinvestment dlincome dlconsumption")
cmm = colminmax(x)
cmm
x[,] = (x :- cmm[1,.]) :/ (cmm[2,.] - cmm[1,.])
end
su dl*

. 
. webuse lutkepohl,clear
(Quarterly SA West German macro data, Bil DM, from Lutkepohl 1993 Table E.1)

. su dl*

    Variable |        Obs        Mean    Std. dev.       Min        Max
-------------+---------------------------------------------------------
dlinvestment |         91    .0167964    .0447898  -.1401839   .1935849
    dlincome |         91     .019464    .0119361  -.0288782   .0502319
dlconsumpt~n |         91     .018678    .0109604  -.0129967   .0448313

. mata
------------------------------------------------- mata (type end to exit) -----------------
: st_view(x=.,.,"dlinvestment dlincome dlconsumption")

: cmm = colminmax(x)

: cmm
                  1              2              3
    +----------------------------------------------+
  1 |  -.1401839256    -.028878212   -.0129966736  |
  2 |    .193584919    .0502319336    .0448312759  |
    +----------------------------------------------+

: x[,] = (x :- cmm[1,.]) :/ (cmm[2,.] - cmm[1,.])

: end
-------------------------------------------------------------------------------------------

. su dl*

    Variable |        Obs        Mean    Std. dev.       Min        Max
-------------+---------------------------------------------------------
dlinvestment |         91    .4703264    .1341939          0          1
    dlincome |         91    .6110749    .1508798          0          1
dlconsumpt~n |         91    .5477397    .1895351          0          1

[Nick Cox]

@Kit Baum is right. If you're determined not to reshape long, then you can also do this:

foreach v of var ROE* EBITAVENDITE* ROI* LEVERAGE* DE* PFNEBITDA* TOTFATTVAR* TOTIMMVAR*  { 
    su `v', meanonly 
    gen `v'NORM = (`v' - r(min)) / (r(max) - r(min)) 
}*