Bookmark and Share

Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

st: ODBC misreading or not reading numbers with allstring option


From   "Mills, Michael" <[email protected]>
To   <[email protected]>
Subject   st: ODBC misreading or not reading numbers with allstring option
Date   Thu, 10 May 2012 11:18:04 -0400

I'm importing data from Excel to Stata with ODBC (to which I'm rather
new) and several observations have some variables being read in as "E-2"
rather than the number that exists in the Excel files.

It appears the numbers in Excel appearing as "E-2" are almost always
<0.1 and are repeating (e.g. 0.08333333333333 or 0.066666666666667). I
often have multiple, duplicate, observations and generally this issue
will occur for the same variable within a set of duplicates. However,
there are occasionally observations for which the data has been read in
(0.83333333333).

Any thoughts on why this is occurring and what could be done to read in
the data? Could this be a length of number/string issue? And can I
resolve this without altering the original Excel files (given the number
of files, this would be quite time-consuming)?

Code and sample data are below.
Local macro rawpath is defined previously, as are site, center2, and
wkst2, which are looped over to read in multiple files:

  local source "Excel Files;DBQ=`rawpath'\\`site'\\`center2'.xls"
  clear
  disp _newline(2) "`center2'" _newline(2) "`wkst2'"
  odbc load, dsn("`source'") table("`wkst2'$") clear lowercase allstring

date        var1	             var2                var3
01jan2009   0.354              0.8                 0.384
01jan2009   0.354              0.8                 0.384
01jan2009   0.354              0.8                 0.384
01jan2010   0.18123380952381   0.855712345605472   E-2
01jan2010   0.18123380952381   0.855712345605472   E-2
01jan2010   0.18123380952381   0.855712345605472   E-2
01jan2011   0.1945662721893    0.849710789659      0.13844646461538
01jan2011   0.1945662721893    0.849710789659      0.13844646461538
01jan2011   0.1945662721893    0.849710789659      0.13844646461538

Thanks,

 - Mike Mills

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/


© Copyright 1996–2018 StataCorp LLC   |   Terms of use   |   Privacy   |   Contact us   |   Site index