[R] SAS and RODBC

[Daniel Nordlund]

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.

Thanks,

Dan

Daniel Nordlund
Bothell, WA USA