import excel and treat first row as variable labels

[Chen Samulsion]

Dear Stata Users,

A quick question: can we treat first row of Excel data as variable labels when running -import excel- ? Now we can only treat first row as variable names. That is not very convenient once too often. Thank you very much.

[Andrew Musau]

What do you propose the variable names should be? Do you have a separate row in Excel that holds the variable names?

[George Ford]

import excel will assign the variable name to the label with firstrow.

[Leonardo Guizzetti]

The comments so far have assumed you meant variable names instead of variable labels. To answer your direct question, -import excel- will not allow you to specify where variable labels come from. If you want to take variable labels from your Excel sheet, this must involve some programming on your part. The specific solution depends very much on precisely how your Excel file is structured.

Here is an example that assumes the Excel file has variable names in row 1, variable lables in row 2, and data in subsequent rows. Begin at begin here.

The code works by importing data into one frame, and variable names and labels into another frame. In both cases, default naming is used (which corresponds to the Excel column letter). The code the does a simple walk over the list of default variables, setting variable names and labels to each one.

clear *
cls

* create some test Excel file
input str4(var1 var2)
"lbl1" "lbl2"
"1"    "a"   
"2"    "b"   
"3"    "c"   
end
list
export excel using testexcel.xlsx, firstrow(variable) replace

** start here

* one frame to hold the data
mkf data
cwf data
import excel using testexcel.xlsx, cellrange(A3) clear
list

* another frame to hold variable names and labels
mkf labels
cwf labels
import excel using testexcel.xlsx, allstring cellrange(A1:B2)
list

qui ds
local vlist `r(varlist)'
foreach v in `vlist' {
  local vname `"`=`v'[1]'"'
  local vlab `"`=`v'[2]'"'
  mac list _vlab
  frame data: rename `v' `vname'
  frame data: label var `vname' `"`vlab'"'
}

cwf data
desc
list

[Chen Samulsion]

Dear Leonardo Guizzetti, thank you very much. My data structure is exactly what you describe:

...... an example that assumes the Excel file has variable names in row 1, variable lables in row 2, and data in subsequent rows.

And thank you for your excellent codes, it is what I wanted.