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) > dfName 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
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
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. >