Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • import excel and treat first row as variable labels

    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.

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

    Comment


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

      Comment


      • #4
        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.

        Code:
        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

        Comment


        • #5
          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.

          Comment

          Working...
          X