I am able to connect to a sas odbc connection using an odbc named data source on windows folder (containing iris.sas7bdat), but can not access data.
# packages
library(dbplyr)
library(odbc)
library(dplyr)
# connecting works
# odbc connection was created with windows tools, and named 'sas_iris_9'
con <- dbConnect(odbc::odbc(), "sas_iris_9")
# after connection contents of con:
# <OdbcConnection> __5001
# SAS Version: 9.04.01M6P110718
# functions dbGetInfo(), dbListTables(), dbExistsTable(), dbListFields() all work
# BUT anything involving actual data movement fails, such as following command to get iris table:
iris_tbl <- tbl(con, sql("select * from IRIS"))
# error
# Error: nanodbc/nanodbc.cpp:1611:
# <SQL> 'SELECT *
# FROM (select * from IRIS) zzz6
# WHERE (0 = 1)'
# same error, same c++ file, same line, with dbGetQuery()
dbGetQuery(con, "select * from iris")
# Error: nanodbc/nanodbc.cpp:1611:
# <SQL> 'select * from iris'
# the return from dbGetInfo(con) follows:
$dbname [1] ""
$dbms.name [1] "SAS"
$db.version [1] "9.04.01M6P110718"
$username [1] ""
$host [1] ""
$port [1] ""
$sourcename [1] "sas_iris_9"
$servername [1] "__5001"
$drivername [1] "SASDRV32.DLL"
$odbc.version [1] "03.80.0000"
$driver.version [1] "09.04.0000"
$odbcdriver.version [1] "03.52"
$supports.transactions [1] FALSE
$getdata.extensions.any_column [1] TRUE
$getdata.extensions.any_order [1] TRUE attr(,"class") [1] "SAS" "driver_info" "list"
# package, R versions
os Windows 10 x64
dplyr * 0.8.3 2019-07-04 [1] CRAN (R 3.6.1)
dbplyr * 1.4.2 2019-06-17 [1] CRAN (R 3.6.1)
odbc * 1.2.1 2019-12-05 [1] CRAN (R 3.6.1)
as aside, I frequently access sas, via odbc, through other platforms, c#, python, excel, access, etc.