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, rip...@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
______________________________________________
R-help@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.
