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

From: Lauri Nikkinen <lauri.nikkinen_at_iki.fi>
Date: Tue 03 Apr 2007 - 08:30:26 GMT

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?). 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)?

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

        [[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 Tue Apr 03 18:34:27 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.