In my attempts to reproduce this both approaches worked.
There seems to be something going on we cannot see (and I don't believe
NULL in the database is being mapped to " "). For example, might there be
non-breaking spaces (character A0 in Windows 1252, your most likely
character set) in the database?
On Mon, 2 Apr 2007, Lauri Nikkinen wrote:
Hi R-users,
I'm trying to retrieve data from MS SQL database with RODBC's
sqlQuery-function:
temp <- sqlQuery(channel,"select *, (select text from table1 where
koodi='paa' and koodi2=paa) as tempor from table2")
str(temp)
$ var0 : num NA NA 1.6 NA NA 1.4 NA 1 NA NA ...
$ var1 : Factor w/ 45 levels " ","01","01;07",..: 1 1 1 4 38 1 38 7
1 1 ...
$ var2 : Factor w/ 95 levels " ","01","01;02;09",..: 1 1 1 9 72 1 78
13 1 1 ...
$ var3 : Factor w/ 104 levels " ","01","01;02;09",..: 1 1 1 44 82 1 87
14 1 1 ...
Data types of these variables in the MS SQL database are:
var0: float, NULL
var1: varchar, NULL
var2: varchar, NULL
var3: varchar, NULL
As you can see from above, I get variable var0 with NA:s because of the data
type (there is NULL in the database if there is no observation in the
specific cell). How can I get NA:s to these Factor variables also? I have
tried the following with no positive results:
temp <- sqlQuery(channel,"select *, (select text from table1 where
koodi='paa' and koodi2=paa) as tempor from table2", na.strings=" ")
and also afterwards
temp$var1[temp$var1 == " "] <- "NA"
Regards,
Lauri
[[alternative HTML version deleted]]
--
Brian D. Ripley, [EMAIL PROTECTED]
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
______________________________________________
[email protected] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
