[R] RODBC, sqlQuery with NA:s

From: Lauri Nikkinen <lauri.nikkinen_at_iki.fi>
Date: Mon 02 Apr 2007 - 09:41:21 GMT


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]]



R-help@stat.math.ethz.ch 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. Received on Mon Apr 02 19:44:41 2007

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.1.8, at Tue 03 Apr 2007 - 08:30:47 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.