Connect to Federation Server from R Studio with ODBC

jarno — Tue, 05 May 2020 12:34:01 GMT

A customer recently asked how they could provide SAS datasets to their R and R Studio users. Remembering they already have SAS Federation Server licensed and in use, the ODBC driver for Federation Server could make this possible.

So, to help my customer I started up a test environment to investigate this. Federation Server was already up and running so didn't need to do any configuration server-side. Also the Federation Server driver for ODBC was installed in my Windows environment. If you don't have it installed, contact your SAS administrator for advice how to get it installed. It should be part of the SAS Federation Server software depot.

The windows client did not yet have the Federation Server data source defined, so I looked into the ODBC DSN administration panel.

IMPORTANT: In Windows there is the 32bit and 64bit DSN administration panel, so if you plan to run 64bit R, then use the 64bit ODBC admin panel, like I did.

Choose a descriptive name for the new data source, I used "FEDODBC" in this example. Make sure to enter correct server hostname and port for your Federation Server. And check in your Federation Server administration console for the correct Fed Server DSN to use. I use "Company" in this example.

Then you can click "Test Connection":

I didn't have R and R Studio installed, so I went to cran for R:

https://cran.r-project.org/bin/windows/

And to rstudio.com for R Studio:

https://rstudio.com/products/rstudio/download/

Installer will as if you want both 32bit or 64bit R Studio installed, I only installed the 64bit binaries.

When you start R Studio the first thing you'll need is the ODBC package.

Fortunately it's simple to add packages in R, I used the following command:

# Install the latest odbc release from CRAN:
> install.packages("odbc")

This started the download of R tools first and then proceeded to load the odbc package (some parts of log have been omitted)

WARNING: Rtools is required to build R packages but is not currently installed. Please download and install the appropriate version of Rtools before proceeding:

https://cran.rstudio.com/bin/windows/Rtools/
also installing the dependencies ‘ellipsis’, ‘digest’, ‘glue’, ‘bit’, ‘vctrs’, ‘pkgconfig’, ‘bit64’, ‘blob’, ‘DBI’, ‘hms’, ‘rlang’, ‘Rcpp’, ‘BH’

There is a binary version available but the source version is later:

binary source needs_compilation
rlang 0.4.5 0.4.6 TRUE

Binaries will be installed
.
.
.

trying URL 'https://cran.rstudio.com/bin/windows/contrib/4.0/odbc_1.2.2.zip'
Content type 'application/zip' length 1297511 bytes (1.2 MB)
downloaded 1.2 MB

package ‘odbc’ successfully unpacked and MD5 sums checked

The downloaded binary packages are in
C:\Users\sas\AppData\Local\Temp\2\Rtmps50ErW\downloaded_packages

Looking at the log, odbc packege was now installed.

Then I proceeded to R Studio Connections panel:

There I can see my "FEDODBC" data source readily available as we already configured that on the Windows side as the first step. Opening that I can see the configuration for that data source:

There you can see the connection string for the FEDODBC connection:

library(DBI)

con <- dbConnect(odbc::odbc(), "FEDODBC", timeout = 10)

Clicking on "Test" we see the following:

Success! So we know the connection from R Studio to the ODBC client works and we can open up a data source on the Federation Server:

I opened a test table named "ORION_PROFIT" under the data source Company/SASData/Finance and I can readily see the data in the R Studio data panel. Your path and data are of course what you have defined in your Federation Server administration console.

That's all! It really is this easy 👍

Let me know if you end up trying this in the comments below. As a SAS employee I of course recommend the use of SAS for all your analytics needs, but realize the R is very popular in many organizations and often the need to make use of SAS datasets in R comes up. There are other way to share data between these tools but this is by far the easiest way if you already have SAS Federation Server available.

topic Connect to Federation Server from R Studio with ODBC in SAS Community Nordic

Connect to Federation Server from R Studio with ODBC