Importing .dta (Stata format) files from one row below of the variable names in a dataset

[Pabitra Chowdhury]

Hi,

I have a .dta file dataset and I want to read the data from row 1 as row 1 variable names. The dataset looks like the below and it's in .dta format. The A, B , C,..... are the variable names saved in the dataset, but I want to read the .dta file from row 1 as variable names. The use "..\..dta" , clear does not serve the purpose. Thanks!

A B C D E F G H I J K L M
SCHCD AC_YEAR C1_TB C2_TB C3_TB C4_TB C5_TB C6_TB C7_TB C8_TB C9_TB C10_TB C11_TB
35010103801 2017-18 0 0 0 0 0 0 0 0 0 0 0
35010201201 2017-18 0 0 0 0 0 0 0 0 0 0 0
35020200201 2017-18 4 1 5 4 4 1 4 1 0 0 0
35020300404 2017-18 1 0 2 1 0 0 0 0 0 0 0
35010104003 2017-18 0 0 0 0 1 0 0 0 0 0 0

[Nick Cox]

There is nothing to be done when specifying use.

This all arises presumably because someone read some data in from a spreadsheet and didn't push metadata where it belongs. The surgery needed has this flavour.

* Example generated by -dataex-. For more info, type help dataex
clear
input str11 A str7 B str5(C D E F G H I J K) str6(L M)
"SCHCD"       "AC_YEAR" "C1_TB" "C2_TB" "C3_TB" "C4_TB" "C5_TB" "C6_TB" "C7_TB" "C8_TB" "C9_TB" "C10_TB" "C11_TB"
"35010103801" "2017-18" "0"     "0"     "0"     "0"     "0"     "0"     "0"     "0"     "0"     "0"      "0"    
"35010201201" "2017-18" "0"     "0"     "0"     "0"     "0"     "0"     "0"     "0"     "0"     "0"      "0"    
"35020200201" "2017-18" "4"     "1"     "5"     "4"     "4"     "1"     "4"     "1"     "0"     "0"      "0"    
"35020300404" "2017-18" "1"     "0"     "2"     "1"     "0"     "0"     "0"     "0"     "0"     "0"      "0"    
"35010104003" "2017-18" "0"     "0"     "0"     "0"     "1"     "0"     "0"     "0"     "0"     "0"      "0"    
end

* you start here

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

drop in 1
destring, replace

format SCHCD %12.0f

list

[Pabitra Chowdhury]

Thanks so much Nick, the following code works but shows an "syntax error" in the following

     foreach v of varlist * {
  2.     rename `v' `=`v'[1]'
  3. }
syntax error
    Syntax is
        rename  oldname    newname   [, renumber[(#)] addnumber[(#)] sort ...]
        rename (oldnames) (newnames) [, renumber[(#)] addnumber[(#)] sort ...]
        rename  oldnames              , {upper|lower|proper}

[Clyde Schechter]

I can't reproduce the error you are reporting. In my setup, it runs without error messages and produces correct results.

What version of Stata are you running? (N.B. If you are not running the current version of Stata, which as of this writing is 18, you are supposed to say so in your post so that you do not waste your time and others running and composing code that has no chance of working.)

[Nick Cox]

We need a data example please. It sounds as if your data are not like the example in #2, but we need to know more about what they are (like).

[Daniel Schaefer]

Does one of your columns have the empty string in observation one? The last column here will cause rename to raise the syntax error:

* Example generated by -dataex-. For more info, type help dataex
clear
input str11 A str7 B str5(C D E F G H I J K) str6(L M N)
"SCHCD"       "AC_YEAR" "C1_TB" "C2_TB" "C3_TB" "C4_TB" "C5_TB" "C6_TB" "C7_TB" "C8_TB" "C9_TB" "C10_TB" "C11_TB" ""
"35010103801" "2017-18" "0"     "0"     "0"     "0"     "0"     "0"     "0"     "0"     "0"     "0"      "0"    "1"
"35010201201" "2017-18" "0"     "0"     "0"     "0"     "0"     "0"     "0"     "0"     "0"     "0"      "0"    "2"
"35020200201" "2017-18" "4"     "1"     "5"     "4"     "4"     "1"     "4"     "1"     "0"     "0"      "0"    "3"
"35020300404" "2017-18" "1"     "0"     "2"     "1"     "0"     "0"     "0"     "0"     "0"     "0"      "0"    "4"
"35010104003" "2017-18" "0"     "0"     "0"     "0"     "1"     "0"     "0"     "0"     "0"     "0"      "0"    "5"
end

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

drop in 1
destring, replace

format SCHCD %12.0f

list

. foreach v of var * {
  2.     rename `v' `=`v'[1]'
  3. }
syntax error
    Syntax is
        rename  oldname    newname   [, renumber[(#)] addnumber[(#)] sort ...]
        rename (oldnames) (newnames) [, renumber[(#)] addnumber[(#)] sort ...]
        rename  oldnames              , {upper|lower|proper}
r(198);

[Nick Cox]

Daniel Schaefer's guess is mine too. The empty value of N[1] won't serve as a variable name. You need to go

replace N = "whatever" in 1

before the other stuff, where whatever can be replaced by something with more meaning to you.