[R] Database-conversion

David James dj at research.bell-labs.com
Tue Jul 30 21:27:24 CEST 2002


Stephan Holl wrote:
> Dear R-Users,
> I store my data in an Postgres-table.
> I can connect with Rdbi.PgSQL, that is no the problem.
> The problem is the NA-Values. No-data-values are coded as -9999 in the
> table.
> But when I extrct them with any SELECT-statement, the -9999-values are
> treated as normal vectors.
> How can I tell R not to fetch the datasets containig -9999-values?
> thanks for your help
> stephan
> -- 
> Linux: the operating system with a CLUE...
>          Command Line User Environment.
> --------------------------------------
> Stephan Holl
> GnuPG Key-ID: 11946A09
> ICQ# 117277975 
> --------------------------------------

The convention of encoding NA's as -9999 is particular to your application
(not SQL nor R), so your code (SQL and/or R) will have to account for them.
I'm not sure how you are extracting your data (via a query or support function
in the RPgSQL package), but I'll suggest modifying your SQL query to include
a where clause explicitly specifying that your variable be different from

    select * from foo_table where foo_var != -9999;

Alternatively, in R you can delete those rows that have the -9999
    foo <- foo[foo$var != -9999, ]

You may want to consider using the SQL value NULL instead of -9999
to denote missing data.  


David A. James
Statistics Research, Room 2C-253            Phone:  (908) 582-3082       
Bell Labs, Lucent Technologies              Fax:    (908) 582-3340
Murray Hill, NJ 09794-0636
r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
Send "info", "help", or "[un]subscribe"
(in the "body", not the subject !)  To: r-help-request at stat.math.ethz.ch

More information about the R-help mailing list