I've made some progress connecting to SAS via ODBC, with several R packages.
library(RODBCDBI)
library(odbc)
library(DBI)
The following 2 connections, using different packages, both work:
rodbc_con <- dbConnect(RODBCDBI::ODBC(), dsn = "my_sas_odbc_dsn", believeNRows=FALSE, colQuote=NULL)
odbc_con <- dbConnect(odbc(), "my_sas_odbc_dsn", believeNRows=FALSE, colQuote = NULL)
After both of these, functions like dbGetInfo() and dbListTables() work.
But while the function dbGetQuery() works with the RODBCDBI::ODBC() connection it does not work with the odbc() connection.
the following results in an error
iris_from_sas <- dbGetQuery(odbc_con, "select * from sasiris.iris")
error
Error: nanodbc/nanodbc.cpp:1611:
'select * from sasiris.iris'
traceback()
9: stop(list(message = "nanodbc/nanodbc.cpp:1611: \n 'select * from sasiris.iris'",
call = NULL, cppstack = NULL))
8: new_result(connection@ptr, statement, immediate)
7: OdbcResult(connection = conn, statement = statement, params = params,
immediate = immediate)
6: .local(conn, statement, ...)
5: dbSendQuery(conn, statement, params = params, ...)
4: dbSendQuery(conn, statement, params = params, ...)
3: .local(conn, statement, ...)
2: dbGetQuery(odbc_con, "select * from sasiris.iris")
1: dbGetQuery(odbc_con, "select * from sasiris.iris")
Another odd thing happens. After the call to connect via odbc(), the Connections pane shows a number of SAS schemas (libraries in SAS), with hierarchy icons on the right. Clicking the arrow to the left of a schema name will expand and show a list of tables, and clicking on a table the same way, will show a list of columns, but clicking on the data view icon results in RStudio opening a small dialog saying: R code execution error
I understand this is not a reprex - but it is fairly easy, if you have SAS and ODBC driver, to create an odbc dsn connection to a folder where the iris.sas7bdat is located, and reproduce these results.
Also, I don't see SAS as a supported database in the RStudio database pages. Is there any plan to support SAS in this way? I frequently use python to access SAS, using the pyodbc library, and do not encounter the types of problems described here.