Rename groups of variables with an increment

[Lucien Godard]

Hello everyone,

I have retrieved databases in Excel of election results. Unfortunately in the Excel databases the name of the variables is common to all candidates. When I import the databases into Stata, the first occurrence of the variables works but then Stata obviously renames the variables already used by letters, so I get this (reduced database for the example):

* Example generated by -dataex-. To install: ssc install dataex
clear
input str9 CityName byte(Total_Registered Total_Voting) str5 Name str8 Surname byte Votes double Vote_Registered str5 M str8 N byte O double P str5 R str8 S byte T double U
"CityName1" 30 28 "Name1" "Surname1" 10 33.33333333333333 "Name2" "Surname2" 15                 50 "Name3" "Surname3" 3                10
"CityName2" 25 19 "Name1" "Surname1"  9                36 "Name2" "Surname2"  5                 20 "Name3" "Surname3" 5                20
"CityName3" 48 33 "Name1" "Surname1" 21             43.75 "Name2" "Surname2"  8 16.666666666666664 "Name3" "Surname3" 4 8.333333333333332
end

I would like to rename the necessary variables to Name Surname Votes Vote_Registred and then add 1 to the end of each variable name for candidate 1, 2 to the end of each variable name for candidate 2, and so on. We would get this:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str9 CityName byte(Total_Registered Total_Voting) str5 Name1 str8 Surname1 byte Votes1 double Vote_Registered1 str5 Name2 str8 Surname2 byte Votes2 double Vote_Registered2 str5 Name3 str8 Surname3 byte Votes3 double Vote_Registered3
"CityName1" 30 28 "Name1" "Surname1" 10 33.33333333333333 "Name2" "Surname2" 15                 50 "Name3" "Surname3" 3                10
"CityName2" 25 19 "Name1" "Surname1"  9                36 "Name2" "Surname2"  5                 20 "Name3" "Surname3" 5                20
"CityName3" 48 33 "Name1" "Surname1" 21             43.75 "Name2" "Surname2"  8 16.666666666666664 "Name3" "Surname3" 4 8.333333333333332
end

Are there any commands to do this? I can't get away with using the rename command to make Stata understand that it should add an increment of one to the names of each variable for each new candidate

Thank you in advance,
Regards

[Clyde Schechter]

I can't get away with using the rename command to make Stata understand that it should add an increment of one to the names of each variable for each new candidate

Yes, you can!

rename (Name M R) Name#, addnumber
rename (Surname N S) Surname#, addnumber
rename (Votes O T) Votes#, addnumber
rename (Vote_Registered P U) Votes_Registered#, addnumber

-help rename group- for more about this and many other amazing things that -rename- can do.

[Lucien Godard]

Addnumber is what I was missing, it works great thanks! I'll definitely dive into the documentation of rename group.

I was wondering, let's say there are hundreds of variables in total, do we have to visually identify the variables to rename and indicate them specifically like here? Or is there a way to do it through a loop, knowing that there is exactly the same number of variables for each candidate and that they are always in the same order?

[Clyde Schechter]

Yes, the idea is to create the local macros containing the names of each of the variables to be dealt with in each of the -rename- commands. The following code will be independent of the number of variables in the data set. The only pre-requesite for the code to work is that after the Total_Voting variable, all of the rest of the variables come in groups of 4, with the same ordering of the variables within each group.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str9 CityName byte(Total_Registered Total_Voting) str5 Name str8 Surname byte Votes double Vote_Registered str5 M str8 N byte O double P str5 R str8 S byte T double U
"CityName1" 30 28 "Name1" "Surname1" 10 33.33333333333333 "Name2" "Surname2" 15                 50 "Name3" "Surname3" 3                10
"CityName2" 25 19 "Name1" "Surname1"  9                36 "Name2" "Surname2"  5                 20 "Name3" "Surname3" 5                20
"CityName3" 48 33 "Name1" "Surname1" 21             43.75 "Name2" "Surname2"  8 16.666666666666664 "Name3" "Surname3" 4 8.333333333333332
end

ds CityName Total_Registered Total_Voting, not
local vbles `r(varlist)'
local n_vbles: word count `vbles'

forvalues i = 1/4 {
    local list`i'
    forvalues j = `i'(4)`n_vbles' {
        local list`i' `list`i'' `:word `j' of `vbles''
    }   
}

rename (`list1') Name#, addnumber
rename (`list2') Surname#, addnumber
rename (`list3') Votes#, addnumber
rename (`list4') Vote_Registered#, addnumber

[Lucien Godard]

This is exactly what I needed thank you very much, I will be able to adapt it for other tasks as well!