Exporting to multiple excel sheets

[Mattia Coppo]

Hi,

I have a table like the one below, with banks from 70 countries.
Every variable name is composed by the bank name plus the iso code of the country (always three characters). All my variable names labels are the iso code.
I want to export to excel creating a sheet for each country, without using reshape.

Is there a way to create a loop for common labels or common ending of variables?

date	WELLSFARGO_USA	BOA_USA	HSBC_GBR	BARCLAYS_GBR	SANTANDER_SPA
1/1/01
2/1/01
3/1/01
4/1/01
5/1/01

Thanks
Mattia

[Clyde Schechter]

So something like this:

local countries USA GBR SPA

foreach c of local countries {
    ds *_`c'
    local `c'_banks `r(varlist)'
    export excel date ``c'_banks' using bankdata.xlsx, ///
        sheetreplace sheet(`c') firstrow(variables)
    }
}

Notes:

1. Not tested as no example data provided. Beware of typos or other errors.
2. You may want to change the -export excel- command to include additional variables or avail yourself of some of its other options.

By the way, why are you averse to using -reshape- here? Having the data in long layout will make it easier to work with in Stata for almost all purposes.

[Mattia Coppo]

Thanks Clyde, your code worked perfectly. As I have 70 countries, is there anyway to capture all the unique endings of variables in the local countries line?


I had the data in the long form and I could use

levelsof country

, but then I had to reshape it in excel. Also in the long form I had over 1.5M rows and the codes where running really slow.
I also need the data in this form to be launched by other programs.

Thanks,
Mattia

[Clyde Schechter]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 date byte(WELLSFARGO_USA BOA_USA HSBC_GBR BARCLAYS_GBR SANTANDER_SPA)
"1/1/01" . . . . .
"2/1/01" . . . . .
"3/1/01" . . . . .
"4/1/01" . . . . .
"5/1/01" . . . . .
end

ds date, not
local vbles `r(varlist)'
preserve
clear
local n_obs: word count `vbles'
set obs `n_obs'
gen vble = ""
forvalues i = 1/`n_obs' {
    replace vble = `"`:word `i' of `vbles''"' in `i'
}
gen country = substr(vble, -3, 3)
levelsof country, local(countries)
restore

and then apply the code from #2 (without it's initial -local countries- command).

In the future, when showing data examples, please use the -dataex- command to do so, as I have in this response. If you are running version 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Mattia Coppo]

I just realized that I did not thank you for this! That is a great solution, thank you again for helping this community!