Import file excel in stata

[Serena Gallo]

Hi everyone,
I have a problem.
When I import an Excel file in stata, because of the name of variables is too long, stata reports them as letters (A, B, C, D...).
Can I resolve this?

[Clyde Schechter]

I think your problem may be more complicated than what you are describing. The maximum allowable length of a variable name in Stata is 32 characters. When -import excel- encounters something that would be an illegal variable name, before falling back on the names A, B, C, ... etc., it tries to fix the variable name if it can. Specifically if a variable name exceeds the 32 character limit, -import excel- truncates it to the first 32 characters and also tries to remove disallowed characters unless that still won't make the name legal. So I suspect there is some problem with your desired variable names other than just their length. Or, perhaps you forgot to use the -firstrow- option in your -import excel- command?

Even if perhaps an older version of Stata handles these things differently, however, Stata never throws away information without at least warning you. In the case of -import excel-, if Stata changes your variable names, it saves the original as a variable label. So you can do something like this:

foreach v of varlist A-Z { // OR WHATEVER THE RANGE OF NAMES ACTUALLY IS
    local better_name: var label `v'
    local better_name = substr(strtoname(`"`better_name'"'), 1, 32)
    rename `v' `better_name'
}

This will -rename- each variable to the first 32 characters of the variable label after it has been "sanitized" of illegal characters. I fear, however, that there will still be some problem: perhaps the first 32 characters of the names of two or more variables will all be the same: then the -rename-ing is not possible in this way and some more complicated solution will be needed.

[George Ford]

are you using the "firstrow" option? otherwise, with import excel, stata imports all the excel file as data and adds the A, B ... as variable names.

[Serena Gallo]

Thanks very much Clyde. However, now I have the problem that you said. More variables are the same because I have the same variable for different years: for example Net loans USD 2020, Net loans USD 2021 etc...
How Can I keep also the year at the end of variables? Something like this: net_loans_2020 and so on...

[Clyde Schechter]

foreach v of varlist A-Z { // OR WHATEVER THE RANGE OF NAMES ACTUALLY IS
    local better_name: var label `v'
    local better_name = lower(substr(strtoname(`"`better_name'"'), 1, 32))
    local better_name: subinstr local better_name "_usd_" "usd"
    rename `v' `better_name'
}

[George Ford]

You could keep only data for each year, rename out the year, create a year variable, and then append it all. You'll probably reshape anyway and this will do it.

[Serena Gallo]

I have still the same problem "net_loans___advances_to_customer" already defined.

[Serena Gallo]

I have this one:
clear
input str17(net_loans I J K L M)
And I want:net_loans_2020 net_loans_2021 net_loans_2022 and so on

[Clyde Schechter]

There is no simple solution to these issues. You are going to have to just handle each problem as it comes up, since, for the most part, the problems are idiosyncratic and there is no way to automate the process. The -rename- command will have to be used over and over again to resolve these problems.

Now, there are some that can be simplified because there is a pattern you can exploit. For example what you show in #8 can be done as:

ds net_loans-M
local vbles `r(varlist)'
local year 2020

foreach v of varlist `vbles' {
    rename `v' net_loans_`year'
    local ++year
}

[Serena Gallo]

Thanks very much, I made some changes to your suggestions and it works very well! Thanks again and again!

[Serena Gallo]

Sorry, but if I want a descending order of year? From 2020 to 2015?

[Clyde Schechter]

It's almost exactly the same, you just decrease local macro year instead of increasing it in each iteration of the loop:

ds net_loans-M
local vbles `r(varlist)'
local year 2020

foreach v of varlist `vbles' {
    rename `v' net_loans_`year'
    local --year
}