[R] SAS and RODBC

[Prof Brian Ripley]

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)
>> df
>      Name Sex Age Height Weight
> 1   Alfred   M  14   69.0  112.5
> 2    Alice   F  13   56.5   84.0
> 3  Barbara   F  13   65.3   98.0
> 4    Carol   F  14   62.8  102.5
> 5    Henry   M  14   63.5  102.5
> 6    James   M  12   57.3   83.0
> 7     Jane   F  12   59.8   84.5
> 8    Janet   F  15   62.5  112.5
> 9  Jeffrey   M  13   62.5   84.0
> 10    John   M  12   59.0   99.5
> 11   Joyce   F  11   51.3   50.5
> 12    Judy   F  14   64.3   90.0
> 13  Louise   F  12   56.3   77.0
> 14    Mary   F  15   66.5  112.0
> 15  Philip   M  16   72.0  150.0
> 16  Robert   M  12   64.8  128.0
> 17  Ronald   M  15   67.0  133.0
> 18  Thomas   M  11   57.5   85.0
> 19 William   M  15   66.5  112.0
>
>> str(df)
> 'data.frame':   19 obs. of  5 variables:
> $ Name  : chr  "Alfred" "Alice" "Barbara" "Carol" ...
> $ Sex   : chr  "M" "F" "F" "F" ...
> $ Age   : num  14 13 13 14 14 12 12 15 13 12 ...
> $ Height: num  69 56.5 65.3 62.8 63.5 57.3 59.8 62.5 62.5 59 ...
> $ Weight: num  112 84 98 102 102 ...
>
>
> 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