R help - Nov 2007 - sqlQuery of variable of type varchar - confusion with "."

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
      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?
 
Regards
S?ren

On 11/29/2007 7:03 AM, 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
>       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?
If you're using sqlQuery from RODBC, then the as.is argument of 
sqlGetResults is relevant.  It defaults to FALSE, and tries to convert 
things based on how they look.

Duncan Murdoch

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