Luis Rideau Cruz
2005-Jan-12 10:58 UTC
[R] RODBC package -- sqlQuery(channel,.....,nullstring=0) stillgives NA's
There is something strange in R behaviour (perhaps). I have run the same select in Oracle SQL*Plus (version 10.1.0.2.0) and the output comes out with NULLs (which is what it ougth to be). But in R I still get the same result with NAs (no matter I use na.strings or nullstring arguments) An output example follows below: Using na.string="0" and nullstring="0" (sorry by the indents): Length 2003 2002 2001 2000 1999 1998 1997 1996 1995 1 32 NA 1 NA NA NA NA NA 2 NA 2 34 3 NA NA NA NA NA NA 6 NA 3 35 NA NA NA NA 2 NA NA NA NA 4 36 NA 12 NA NA 10 NA NA 1 NA 5 37 3 3 NA NA 4 NA NA 31 NA 6 38 2 4 1 1 12 6 NA 11 NA 7 39 4 13 5 5 34 8 NA 58 13 Length 2003 2002 2001 2000 1999 1998 1997 1996 1995 32 1 2 34 3 6 35 2 36 12 10 1 37 3 3 4 31 38 2 4 1 1 12 6 11 39 4 13 5 5 34 8 58 13 Best, Luis>>> Prof Brian Ripley <ripley at stats.ox.ac.uk> 12/01/2005 09:14:22 >>>On Tue, 11 Jan 2005, Luis Rideau Cruz wrote:> R-help, > > I'm using the RODBC package to retrieve data froma ODBC databasewhich> contain NA's. > > By using the argument nullstring = "0" in sqlQuery() I expect to > coerce them to numeric but still get NA's in my select.You need to read the help page (as the posting guide asks): it says na.strings: character string(s) to be mapped to 'NA' when reading character data. which is the opposite of what you are saying you want to do. An ODBC database cannot contain NA's. It may contain NULLs, and it may contain "NA", so we have no idea what you mean. -- 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
Prof Brian Ripley
2005-Jan-12 11:21 UTC
[R] RODBC package -- sqlQuery(channel,.....,nullstring=0) stillgives NA's
PLEASE do read the help page, which says nullstring: character string to be used when reading 'SQL_NULL_DATA' character items from the database. ^^^^^^^^^^^^^^^ so this does not apply to numeric items. You can of course easily change numeric NAs to 0s, if you want to. On Wed, 12 Jan 2005, Luis Rideau Cruz wrote:> There is something strange in R behaviour (perhaps).Your negative remarks are not appreciated.> I have run the same select in Oracle SQL*Plus (version 10.1.0.2.0) and > the output comes out with NULLs (which is what it ougth to be). > > But in R I still get the same result with NAs (no matter I use > na.strings or nullstring arguments) > An output example follows below: > > Using na.string="0" and nullstring="0" (sorry by the indents): > > Length 2003 2002 2001 2000 1999 1998 1997 1996 1995 > 1 32 NA 1 NA NA NA NA NA 2 NA > 2 34 3 NA NA NA NA NA NA 6 NA > 3 35 NA NA NA NA 2 NA NA NA NA > 4 36 NA 12 NA NA 10 NA NA 1 NA > 5 37 3 3 NA NA 4 NA NA 31 NA > 6 38 2 4 1 1 12 6 NA 11 NA > 7 39 4 13 5 5 34 8 NA 58 13 > > Length 2003 2002 2001 2000 1999 1998 1997 1996 1995 > 32 1 > 2 > 34 3 > 6 > 35 2 > 36 12 10 > 1 > 37 3 3 4 > 31 > 38 2 4 1 1 12 > 6 11 > 39 4 13 5 5 34 > 8 58 13 > > > Best, > Luis > > >>>> Prof Brian Ripley <ripley at stats.ox.ac.uk> 12/01/2005 09:14:22 >>> > On Tue, 11 Jan 2005, Luis Rideau Cruz wrote: > >> R-help, >> >> I'm using the RODBC package to retrieve data froma ODBC database > which >> contain NA's. >> >> By using the argument nullstring = "0" in sqlQuery() I expect to >> coerce them to numeric but still get NA's in my select. > > You need to read the help page (as the posting guide asks): it says > > na.strings: character string(s) to be mapped to 'NA' when reading > character data. > > which is the opposite of what you are saying you want to do. > > An ODBC database cannot contain NA's. It may contain NULLs, and it may > > contain "NA", so we have no idea what you mean. > > -- > 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 > >-- 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
Reasonably Related Threads
- RODBC package -- sqlQuery(channel,.....,nullstring=0)stillgives NA's
- RODBC package -- sqlQuery(channel,.....,nullstring=0) still gives NA's
- RODBC and NULL values
- quoting a table name due to a special character in sqlQuery (RODBC)
- problems with RODBC, sqlQuery