Thanks again, There is still going on something strange, because na.strings="" in sqlQuery clause didn't help. I'm helpless but thanks anyway...
-Lauri 2007/4/3, Prof Brian Ripley <[EMAIL PROTECTED]>: > > On Tue, 3 Apr 2007, Lauri Nikkinen wrote: > > > Thanks Prof Ripley for your answer. In fact, there is no NULL's in these > > varchar variables, that is because one of the software's we use do not > > accept NULL's (that is strange, isn't it?). > > It's not uncommon. (Be grateful you are not on MacOS, where it seems > quite common to accept NULLs but not treat them as different from "".) > > > So, for instance, when I type > > > > select * from table1 > > where var1='' > > > > I get all those rows where the cells of var1 are empty. So, is there any > > possibility to tell R that it should convert these empty cells to NA:s > (when > > importing)? > > Yes, via na.strings="". > > > > > Thanks for your help > > Lauri > > > > > > 2007/4/3, Prof Brian Ripley <[EMAIL PROTECTED]>: > >> > >> 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 > > > > -- > 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 > [[alternative HTML version deleted]] ______________________________________________ [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.
