Renaming columns

[Hannah Evans]

Hi there,

I was wondering if anybody could help. I have 200+ columns that could change when new data is incorporated. I would like to rename the columns with the variable labels (unique for each column) however when I attempt to use the following I come across a syntax error due to the fact that some labels contain illegal characters:

foreach v of varlist del* {

**find out distinct value (level) of del variables
levelsof `v'
** store the values (from the results) into a string local tmp
local tmp `r(levels)'
** rename the keep variables by the string local tmp
rename `v' `tmp'

}

Is there a way to rename the variables within the loop but also automatically ignore illegal characters or shorten labels when required?

Kind Regards,
Hannah

[Andrew Musau]

Pass the labels through the -strtoname()- function. See -help strtoname()-

di strtoname("xZ%1street?")

Res.:

. di strtoname("xZ%1street?")
xZ_1street_

Example:

sysuse auto, clear
foreach var of varlist *{
    rename `var' `=strtoname("`:var lab `var''")'
}
desc

. desc

Contains data from C:\Program Files\Stata16\ado\base/a/auto.dta
  obs:            74                          1978 Automobile Data
 vars:            12                          13 Apr 2018 17:45
                                              (_dta has notes)
------------------------------------------------------------------------------------------
              storage   display    value
variable name   type    format     label      variable label
------------------------------------------------------------------------------------------
Make_and_Model  str18   %-18s                 Make and Model
Price           int     %8.0gc                Price
Mileage__mpg_   int     %8.0g                 Mileage (mpg)
Repair_Rec~1978 int     %8.0g                 Repair Record 1978
Headroom__in__  float   %6.1f                 Headroom (in.)
Trunk_space__~_ int     %8.0g                 Trunk space (cu. ft.)
Weight__lbs__   int     %8.0gc                Weight (lbs.)
Length__in__    int     %8.0g                 Length (in.)
Turn_Circle__~_ int     %8.0g                 Turn Circle (ft.)
Displacement_~_ int     %8.0g                 Displacement (cu. in.)
Gear_Ratio      float   %6.2f                 Gear Ratio
Car_type        byte    %8.0g      origin     Car type
------------------------------------------------------------------------------------------
Sorted by: Car_type
     Note: Dataset has changed since last saved.

[Hannah Evans]

Thank you for this it is really wonderful but I think I may not have explained myself properly. I need to rename the column with the variable label within the columns, so like I have in my example code I need to rename it with the local level label:

foreach var of varlist *{
levelsof `var'
local tmp `r(levels)'
rename `var' `=strtoname("`:var `tmp''")'
}

This does not work...but I need something like this (if possible).

Thank you,
Hannah

[Andrew Musau]

Present a data example using dataex.

[Hannah Evans]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str65(del7 del100)
""                       "Not Specified"
""                       ""             
""                       ""             
""                       ""             
""                       "Not Specified"
""                       ""             
""                       ""             
""                       "Not Specified"
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       "Not Specified"
""                       "Not Specified"
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       "Not Specified"
""                       "Not Specified"
""                       "Not Specified"
"Advice on Legal Issues" ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       "Not Specified"
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       "Not Specified"
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       "Not Specified"
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       "Not Specified"
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       "Not Specified"
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       "Not Specified"
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       "Not Specified"
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
""                       ""             
end

[Nick Cox]

I did this

foreach v of var del* { 
    levelsof `v', local(levels) 
    label var `v' `levels' 
    local name = strtoname(`levels')
    capture rename `v' `name'
}

and got this

. d, fullnames

Contains data
 Observations:           100                  
    Variables:             2                  
------------------------------------------------------------------------------------------------------------------
Variable      Storage   Display    Value
    name         type    format    label      Variable label
------------------------------------------------------------------------------------------------------------------
Advice_on_Legal_Issues
                str65   %65s                  Advice on Legal Issues
Not_Specified   str65   %65s                  Not Specified
------------------------------------------------------------------------------------------------------------------

Does that help?

[Andrew Musau]

Another way:

foreach var of varlist *{
    sort `var'
    cap rename `var' `=strtoname(`var'[_N])'
}

[Hannah Evans]

Thank you both so much for your help!

[Dirk Enzmann]

Very often the less experienced can't appreciate the advantage of having short variable names. You should consider whether to use short variable names (although the names "del7" and "del100" are not very informative) and put more detailed information into the label of the variable. I also wonder why you are using two "variables" that are constant (except for the strings of length zero).

I don't know where your data came from, but it looks as if they are kind of "dummy coded" from a variable "del" with 7 = "Advice on legal issues" and 100 = "Not specified" and subsequently with labeled values converted to string values. If this is the case and you have up to 100 del*-variables, you can reconstruct the variable "del" using the following (may be that there are more efficient ways to achieve the same). Your data:

clear
input str65(del7 del100)
""                       "Not Specified"
""                       ""            
""                       ""            
""                       ""            
""                       "Not Specified"
""                       ""            
""                       ""            
""                       "Not Specified"
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       "Not Specified"
""                       "Not Specified"
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       "Not Specified"
""                       "Not Specified"
""                       "Not Specified"
"Advice on Legal Issues" ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       "Not Specified"
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       "Not Specified"
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       "Not Specified"
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       "Not Specified"
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       "Not Specified"
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       "Not Specified"
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       "Not Specified"
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
""                       ""            
end

Create numeric variable "del" from the string variables "del...":

* Install -elabel- if necessary:
cap which elabel
if _rc ssc install elabel

* encode all string variables starting with "del" to numeric:
forvalues i = 1/100 {
   cap confirm variable del`i'
   if !_rc {
      rename del`i' dummy
      encode dummy, gen(del`i')
      drop dummy
      recode del`i' (1=`i')        // recode 1 to number of variable
      elabel recode del`i' (1=`i') // recode value label accordingly
   }
}

* Create variable "del" from all variables starting with "del"
egen del = rowmax(del?*)  
foreach v of varlist del?* {
   elabel copy `v' del, add       // add value label from variable `v' do del
   drop `v'
}
label values del del
tab del

The result will be:

. tab del

                   del |      Freq.     Percent        Cum.
-----------------------+-----------------------------------
Advice on Legal Issues |          1        6.25        6.25
         Not Specified |         15       93.75      100.00
-----------------------+-----------------------------------
                 Total |         16      100.00