Using the Putexcel command inside a loop

[Riccardo Busin]

Good morning everyone,
I would like to use (if possible) the putexcel command in a loop to create this result in excel (for each province I would like to calculate the r(mean) of EBITDA for each sector):

EBITDA	2020	2019	2018	2017	2016
agriculture	1	2	3	4	5
electronic	5	4	3	2	1
commerce	6	7	8	9	10
finance	10	9	8	7	6

To create this table in excel I wrote this code (which unfortunately doesn't work):

global Year1=2016
global YearN=2020
cd "C:\Users\Ricky\Desktop\Interlocking2020\ISP2021\Dati2021\Andamenti"
global route = "C:\Users\Ricky\Desktop\Interlocking2020\ISP2021\Dati2021\Andamenti"


foreach provinces in "Belluno" "Padua" "Rovigo" "Treviso" "Venice" "Verona" "Vicenza" "Gorizia" "Pordenone" "Trieste" "Udine" "Trento" "Bolzano" {    
foreach sector in "agriculture" "electronic" "commerce" "finance" {
forvalues y=$YearN(1)$Year1 {
forvalues j = 1/4 {
tokenize "B C D E F"
forvalues n = 1/5 {
preserve
keep if Provincia=="`provinces'"
keep if SETTORI=="`sector'"
putexcel set "${route}\Andamento`provinces'.xlsx", sheet("Andamento") modify
summarize EBITDA`y'
return list
putexcel ``n''`=1+`j''= `r(mean)'
restore
}
}
}
}
}

putexcel set "${route}\Andamento`provinces'.xlsx", sheet("Andamento") modify
putexcel A1="EBITDA"
putexcel A2="agriculture"
putexcel A3="electronic"
putexcel A4="commerce"
putexcel A5="finance"
putexcel B1="2020"
putexcel C1="2019"
putexcel D1="2018"
putexcel E1="2017"
putexcel F1="2016"

In your opinion, is it possible to achieve the result I would like?
Thank you for any kind of help.

[Clyde Schechter]

Your code contains several errors. But, more important, this is a bit like trying to hammer a nail by pounding it with a screwdriver. This task is more simply accomplished by reorganizing the data set with -collapse- to resemble the tables you want and then using -export excel-.

keep if inlist(SETTORI, "agriculture", "electronic", "commerce", "finance") ///
    & (inlist(Provincia, "Belluno", "Padua", "Rovigo", "Treviso", "Venice", "Verona") ///
    | inlist(Provincia, "Vicenza", "Gorizia", "Pordenone", "Trieste", "Udine", "Trento", "Bolzano"))

collapse (mean) EBITDA2020 EBITDA2019 EBITDA2018 EBITDA2017 EBITDA2016, by(SETTORI Provincia)

levelsof Provincia, local(provinces)
foreach p of local provinces {
    export excel SETTORI EBITDA* using "${route}/Andamento`p'.xlsx" ///
    if Provincia == "`p'", sheet("Andamento", modify) firstrow(variables)
}

Note: As no example data was provided, this code is untested. It may contain typos or be in some ways incompatible with your data. But I think it shows you the outline of a better general approach.

As an aside, it would be a safer programming practice to put that path into a local macro instead of a global macro.

[Riccardo Busin]

Your code contains several errors. But, more important, this is a bit like trying to hammer a nail by pounding it with a screwdriver. This task is more simply accomplished by reorganizing the data set with -collapse- to resemble the tables you want and then using -export excel-.

keep if inlist(SETTORI, "agriculture", "electronic", "commerce", "finance") ///
& (inlist(Provincia, "Belluno", "Padua", "Rovigo", "Treviso", "Venice", "Verona") ///
| inlist(Provincia, "Vicenza", "Gorizia", "Pordenone", "Trieste", "Udine", "Trento", "Bolzano"))

collapse (mean) EBITDA2020 EBITDA2019 EBITDA2018 EBITDA2017 EBITDA2016, by(SETTORI Provincia)

levelsof Provincia, local(provinces)
foreach p of local provinces {
export excel SETTORI EBITDA* using "${route}/Andamento`p'.xlsx" ///
if Provincia == "`p'", sheet("Andamento", modify) firstrow(variables)
}

Note: As no example data was provided, this code is untested. It may contain typos or be in some ways incompatible with your data. But I think it shows you the outline of a better general approach.

As an aside, it would be a safer programming practice to put that path into a local macro instead of a global macro.

Thanks for your help and for your valuable advice. I'm just asking you one more thing. Does the collapse (mean) command matches with r (mean) which I get using the summarize command?
The use of putexcel derives from the possibility of saving this result (r (mean)) in an excel cell.
Thanks again again.

[Clyde Schechter]

Yes, -collapse (mean)- calculates the mean of each of the variables listed in the same way that the -summarize- command does. The difference is that -collapse- leaves the results as variables in memory, whereas -summarize- leaves the result for one variable in `r(mean)'.