[R] sqlQuery of variable of type varchar - confusion with "."

[Prof Brian Ripley]

On Thu, 29 Nov 2007, Søren Højsgaard wrote:

> Dear list,

> I have a rather large dataset in SAS which I export to a 
> SQLite database for subsequent use in R.
>
> One of the columns is
> cowidp
> 1881501224.2
> 1881501224.2
>
> and the column is stored as a character in SAS. It becomes a varchar in 
> the database (it should be - it is an identifier; not a number). Reading 
> this into R gives

How?  You haven't actually said, but I am surmising that you are using 
package RODBC with the default options, in which case this is exactly what 
the help page ?sqlQuery says should happen.

>      cowidp
> 1 1881501224
> 2 1881501224
> ....
> - i.e. the ".2" is stripped off - and it becomes a numeric variable
>
> However, if I store replace "." with "_" in my SAS data then the column in my database is
>
> cowidp
> 1881501224_2
> 1881501224_2
>
> Reading into R gives
>        cowidp
> 1 1881501224_2
> 2 1881501224_2
>
> as it "should be" - it becomes a factor
>
> Is the fact that the "."-part does not seem to work a bug in sqlQuery 
> (or somewhere else) or is it the intention that things containing a "." 
> are converted into numeric values if possible?

See the 'as.is' argument to sqlGetResults(), which can be passed from 
sqlQuery().  It is exactly the same as read.table() in this respect.

-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595