Re: [R] RODBC, sqlQuery with NA:s

From: Prof Brian Ripley <ripley_at_stats.ox.ac.uk>
Date: Tue 03 Apr 2007 - 08:39:18 GMT

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 <ripley@stats.ox.ac.uk>:
>>
>> 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, ripley@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@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

______________________________________________
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 Tue Apr 03 19:09:31 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 - 09:30:45 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.