Make first observation the variable name

Question

I currently have a data set with 37 variables. I read in the data with import excel but what should be the variable names were read in as the first observation.

I want to use a loop to move the first observation to the variable names.

This is what I have tried:

foreach var of varlist * {
rename `A' `=`District'[1]'
}

District is what should be the name of the first variable but is read as the first observation.

The error I get is:

syntax error
    Syntax is
        rename  oldname    newname   [, renumber[(#)] addnumber[(#)] sort ...]
        rename (oldnames) (newnames) [, renumber[(#)] addnumber[(#)] sort ...]
        rename  oldnames              , {upper|lower|proper}

but I am not sure how to fix it.

Any ideas?

Answer 1

@Pearly Spencer's comment is the best solution. Just do the import correctly!

Meanwhile, let's fix your loop:

You have

foreach var of varlist * {
    rename `A' `=`District'[1]'
}

Here the local macros A and District aren't (obviously) defined. Even if they were defined, at most this loop would rename one variable by the first value of the same or another variable. The loop would fail second time round, as the variable has already been renamed.

What you were reaching for, I guess, was more like

foreach var of varlist * {
    rename `var' `=`var'[1]'
}

to be followed by

drop in 1 
destring, replace 

if the renames worked.

The revised loop is Stata code for

!!! not Stata 
for each variable { 
    use the value in the first observation as the new name 
}

Note that we are presuming that what's in the first observation is a set of distinct and legal Stata variable names. That is often a strong assumption for data files prepared outside Stata.

This code is more robust to problematic names:

foreach var of varlist * {
    rename `var' `=strtoname(`var'[1])'

}

For more, see help strtoname().

If this isn't the answer you will need to give us a data example.