[R] SAS and RODBC

[Gabor Grothendieck]

There is a SASxport package on CRAN that may be of interest.

On Thu, Feb 11, 2010 at 3:38 AM, Daniel Nordlund <djnordlund at verizon.net> 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.
>
> Thanks,
>
> Dan
>
> Daniel Nordlund
> Bothell, WA USA
>
>
> ______________________________________________
> R-help at r-project.org 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.
>