How do I rename a set of variables in one dataset based on a separate codelist stored as another dataset?

[stewartacraig]

I'm new to Stata (about a week) and I want to rename variables in one dataset based on a metadata codelist stored in another dataset.

e.g. I have a dataset AGE as below:

a b c
GH 2 3
BH 2 5

and I have a metadata codelist META as below:

var var_decode
a initials
b age_years
c age_months

I want to replace the variable names a, b and c in AGE with the var_decodes from META

I think this is probably really simple but if somebody can give me a hand with the syntax or some example code it would be a big help

Thanks in advance.

[Dave Airey]

What about "rename (a b c) (initials age_years age_months)"? See help rename group. I guess your question might have been how do you do that programmatically...can you build local macros from the code list file if you don't want to do a one-off edit in a text file?

[stewartacraig]

Thanks Dave,

I was looking for a program, probably using a local macro variable, to do this as I have over over 3000 variables to rename!

I've been trying to use this sort of code but I need to replace the code on line 2 with something that will 'look-up' the var_decode in my META dataset rather than just impute a lowercase version.

foreach oldname of varlist * {
local newname=lower("`oldname'")
rename `oldname' `newname'
}

Any ideas?

[Joseph Coveney]

Try something like the following:

version 13.1

clear *
set more off

input str1 var str244 var_decode
a initials
b age_years
c age_months
end
quietly save META, replace

drop _all
input str2 a byte(b c)
GH 2 3
BH 2 5
end

*
* Begin here
*
local varlist
foreach var of varlist _all {
    local varlist `varlist' `var'
}

quietly generate str1 var = ""
quietly generate str244 var_decode = ""
append using META

foreach var of varlist `varlist' {
    quietly levelsof var_decode if var == "`var'", local(varname)
    local varname : list clean varname
    rename `var' `varname'
}
quietly keep if missing(var)
drop var var_decode

list, noobs separator(0) abbreviate(20)

exit

[stewartacraig]

Many thanks, Joseph!

I'm able to run the code you provided perfectly with your input data, but when I try and run the same code (from 'begin here' down) with my data I get this syntax error reported.

I've stepped through the code and everything up to this last foreach loop seems to be working as expected.

Any idea what I'm doing wrong here?

. foreach var of varlist `varlist' {
2. quietly levelsof var_code if var_no == "`var'", local(varname)
3. local varname : list clean varname
4. rename `var' `varname'
5. }
syntax error
Syntax is
rename oldname newname [, renumber[(#)] addnumber[(#)] sort ...]
rename (oldnames) (newnames) [, renumber[(#)] addnumber[(#)] sort ...]
rename oldnames , {upper|lower|proper}
r(198);

end of do-file

r(198);

[Sergiy Radyakin]

Many thanks, Joseph!

I'm able to run the code you provided perfectly with your input data, but when I try and run the same code (from 'begin here' down) with my data I get this syntax error reported.

I've stepped through the code and everything up to this last foreach loop seems to be working as expected.

Any idea what I'm doing wrong here?

. foreach var of varlist `varlist' {
2. quietly levelsof var_code if var_no == "`var'", local(varname)
3. local varname : list clean varname
4. rename `var' `varname'
5. }
syntax error
Syntax is
rename oldname newname [, renumber[(#)] addnumber[(#)] sort ...]
rename (oldnames) (newnames) [, renumber[(#)] addnumber[(#)] sort ...]
rename oldnames , {upper|lower|proper}
r(198);

end of do-file

r(198);

it is likely that you ended up with a list that has more than one value. Make sure that you have no duplicates in the META dataset.
The error is consistent with the

sysuse auto, clear
rename mpg foo bar

So type isid var , or , set trace on and run to see which variable is causing troubles.

Best,Sergiy Radyakin.

PS: bug in the forum software requires adding commas to the "So type..." line above, otherwise the spaces are eaten up around the word "or".

[stewartacraig]

Thanks Sergiy. I see what's going on now...

The var_code strings are full of spaces and special characters which are causing problems at the rename stage.

I removed all the spaces and special characters from the first 30 var-codes and the code works perfectly over these

I now need to figure out a quick way to trim and tidy all 3000+ var_code strings or rewrite the rename statement in way that it can handle spaces and special characters...

Again, if anybody has any experience of this it would be great to know

Thanks all.

[Joseph Coveney]

Use can use the local macname : subinstr macro extended function to swap out the illegal character for one that's acceptable in a Stata name. For a list of special characters, you'd use a loop that traverses the list.

local special_characters <your list of special characters>

foreach var of varlist `varlist' {
    quietly levelsof var_decode if var == "`var'", local(varname)
    local varname : list clean varname

    // For spaces
    local varname : subinstr local varname " " "_", all

    // For the others
    foreach char in `special_characters' {
        local varname : subinstr local varname "`char'" "_", all
    }
    rename `var' `varname'
}

You'll need to check that you don't end up with collisions (duplicate names incidentally formed during the substitutions). Also, certain characters are tricky with local macros, for example, double quote mark, single quote mark, grave accent, reverse solidus (escape character).

[Dave Airey]

Here's another way to do it. It assumes the
variables are ordered in the same way in both
datasets.

clear

input str1 var str10 var_decode
a initials
b age_years
c age_months
end

list

forvalues i = 1/`=_N' {
    local mynewvars `mynewvars' `=var_decode[`i']'
}

display "`mynewvars'"

clear

input str2 a byte(b c)
GH 2 3
BH 2 5
end

list

foreach var of varlist * {
    local myoldvars `myoldvars' `var'
}

display "`myoldvars'"

rename (`myoldvars') (`mynewvars')

list

[Roberto Ferrer]

The function strtoname() can be used translate strings with ilegal characters to Stata names. See help strtoname().

[Sergiy Radyakin]

After reviewing the new information posted, I've got the opinion that the META file is just a list of labels, not alternative variable names (since the texts there are not conforming to Stata's variable naming restrictions, contain duplicates, etc). stewartacraig may need to read about the labels and see whether they are the solution to his(her) problem.