Announcement

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

  • How can I use "import excel" to import all data from excel as numeric data?

    How can I use "import excel" to import all data from excel as numeric data and not as "strings"? I'm currently using the following command:
    PHP Code:
    import excel "filename.xls"sheet("Data1'"firstrow case(lowerclear 
    but all data, even those which are formated as "nummeric" under Excel, are imported as "strings".

  • #2
    While it is possible to force -import excel- to import every variable as a string, even when it is actually numeric, the reverse is not true. But, -import excel- is actually pretty smart at recognizing when a column in Excel contains only numeric information and bringing them in as numeric variables. So I wonder if your expectations about your Excel data are wrong. For example, if an otherwise numeric column in Excel contains a few observations with "N/A" or something like that, Stata has no choice but to import that column as a string variable, because, unlike Excel, Stata does not mix strings and numbers in the same structure. So I would wager at least a small amount that your data are not so numeric as you think they are.

    For a quick screen of your data, after your -import excel- command, run:

    Code:
    destring _all, replace
    That will convert every variable to a Stata numeric variable, assuming it contains only valid numbers. If all your variables really contain exclusively numbers, then you are done and should be happy.

    For any variable that contains some non-numeric observations, -destring-'s output will tell you about that. The next step is to identify what the non-number content of those variables is. Suppose -destring- tells you that variable xyz could not be destrung because it contains non-numeric content. Then run:

    Code:
    browse xyz if missing(real(xyz))
    The browser will open up and show you all of the non-numeric entries to be found in variable xyz. At that point you will need to contemplate why your Excel variable has these things when you are expecting only numbers, and what to do about it.

    Comment


    • #3
      Thanks a lot Clyde! To be sure:
      PHP Code:
      destring _allreplace 
      will destring all the variables in the currently open database, if possible?

      Comment


      • #4
        Yes, if they actually all contain numbers, and only numbers, then they will all be converted from string to numeric properly.

        Do read -help destring-.

        That said, since Stata did not import them as numbers in the first place, my hunch is that when you run this you will find out that those variable that were imported as strings in fact contain non-number content, contrary to your expectation. We'll see soon enough.

        Comment


        • #5
          If I apply your
          PHP Code:
          browse xyz if missing(real(xyz)) 
          , I receive a "type mismatch". But nevertheless the data seem to be nummeric now, since a can run e.g. a regression command.

          Comment


          • #6
            Great! Yes, -browse xyz if missing(real(xyz))- was only if -destring- told you that some of your variables could not be converted to numeric. Apparently that didn't happen. So if all your variables are now numeric, (real(xyz)) is a type mismatch, because the argument of real() must be a string variable.

            Comment


            • #7
              I see! I acutally couldn't fine
              PHP Code:
              destring _allreplace 
              in the manual. So thanks a lot for your help !!!

              Comment

              Working...
              X