[R] SAS and RODBC

[Daniel Nordlund]

> -----Original Message-----
> From: Prof Brian Ripley [mailto:ripley at stats.ox.ac.uk]
> Sent: Thursday, February 11, 2010 1:25 AM
> To: Daniel Nordlund
> Cc: r-help at r-project.org
> Subject: Re: [R] SAS and RODBC
> 
> On Thu, 11 Feb 2010, Daniel Nordlund wrote:
> 
> > I am using R-2.10.1 binary from CRAN on a WinXP Pro system.  I also use
> SAS v9.2 on the same box.  I just started using the SAS ODBC driver that
> comes with version 9 of SAS.  I have been able to set up an ODBC source
> for SAS datasets using the driver, and then with RODBC I am able to read a
> sample SAS dataset.
> >
> >> library(RODBC)
> >> ch <- odbcConnect('sasodbc', believeNRows=FALSE)
> >> df <- sqlQuery(ch, 'select * from sasodbc.class', as.is=TRUE)


<<<snip>>>

> > However, I am not able to save a dataframe back to a SAS dataset.  When
> I try to use sqlSave I get the following error.
> >
> >> sqlSave(ch, df, tablename='sasodbc.class2')
> > Error in sqlSave(ch, df, tablename = "sasodbc.class2") : 00000 0
> > [RODBC] ERROR: Could not SQLExecDirect 'CREATE TABLE sasodbc.class2
> ("rownames" varchar(255), "Name" varchar(255), "Sex" varchar(255), "Age"
> num, "Height" num, "Weight" num)'
> >
> >
> > Can anyone suggest how I might go about tracking down the problem?
> > I don't know much about ODBC in general or what RODBC is doing under
> > the hood, or how the SAS ODBC driver is written, but it would be
> > useful if I could get R to write SAS datasets, and not just read
> > them.
> 
> What 'RODBC is doing under the hood' is in that error message: sending
> an SQL query to create a table.  Unfortunately, the SAS ODBC driver is
> not returning a useful error message, and this really becomes a
> question for SAS support.  Maybe that function is not supported, the
> connection is read-only, the SAS dialect requires different quotes
> (although RODBC asked the driver about quoting) ... only someone
> familiar with the ODBC driver will know.
> 
> This is really off-topic here: r-sig-db would be more appropriate but
> only marginally so,
> 
> > Thanks,
> >
> > Dan
> >
> > Daniel Nordlund
> > Bothell, WA USA
> 
> --
> Brian D. Ripley,                  ripley at 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

This is just a quick follow-up to my previous post.  Based on Prof. Ripley's response I went back and looked at the SAS log file and reread the RODBC help pages.  The problem of writing a SAS dataset was solved by setting colQuote=NULL in the call to the odbcConnect() function.

ch <- odbcConnect('sasodbc', believeNRows=FALSE, colQuote=NULL) 

I hope this will be useful to others who may have the SAS BASE product and want to do graphics or statistical analyses with their SAS data, but can't afford the high licensing fees for the SAS STAT and GRAPH modules.  Thanks to Prof. Ripley for the fine RODBC package.

Dan

Daniel Nordlund
Bothell, WA USA