A trick to avoid variables starting with a number!

[Walid Al-Soneidar]

Dear Stata Experts,

I have a problem with importing data from Excel.

A bunch of variables I have are refer to virus genotypes, so they were entered as numbers. When I choose the option “firstrow”, because Stata does not accept variables starting with a number, it resorts to changing names of variables to the column they correspond to on the spreadsheet. For example, if the column G on Excel has a variable called 157, Stata would import it as variable “G” with a label of “157.”

Changing that for a variable or two is not a problem, but when you have to do it for hundreds of columns it becomes tedious. What I want to do is somehow import these columns and add a letter or two before them. In other words, import variable 157 as b_157 to get around the no-number-policy Stata has!

Any help is much appreciated!

[Richard Williams]

Could you change it in the spreadsheet itself? Insert a row on top, create a formula that takes the value of the cell below and adds a letter before, copy that formula to other cells across the row, and then delete the original row?

[Andrew Musau]

I think it would be easier to address this within Stata. Import without including the -firstrow- option of import excel and then follow the procedure in

https://www.statalist.org/forums/for...of-label-names

[daniel klein]

Here is a short code that renames variables if their label is a number.

// get variables with labels that are numbers
foreach v of varlist * {
    local varlabel : variable label `v'
    capture confirm number `varlabel'
    if ( !_rc ) local oldnames `oldnames' `v'
}

// rename those variables
rename (`oldnames') (b_=)

Best
Daniel

[Walid Al-Soneidar]

Thank you all for your responses. I could not do it with any of the methods you suggested, so I resorted to importing data to R then exported to Stata. Not the most elegant way, but it worked!

Thanks again,
Walid

[Nick Cox]

Daniel Klein's method looks fine to me. In what sense did it not work for you?

[Walid Al-Soneidar]

Thanks Nick! Not sure why it did not work with Daniel's code. But I finally found out how to do it in Stata:

Code:

foreach v of varlist G-AZ {
local varlabel : variable label `v'
label variable `v' "b_`varlabel'"

local x: variable label `v'
rename `v' `x'
}

foreach v of varlist b_* {

local varlabel: variable label `v'
label var `v' ""
}

[daniel klein]

The code in #7 could be simplified to the point of

rename (G-AZ) (b_=)

concerning variable names. The code will fail if variable labels are not valid names or are longer than 30 characters. Concerning variable labels, I never really got the point of using variables names as their labels; some people seem to want that, though.