Back to Homepage


Export R data to SAS

Feng Jiang
Last Updated: 2020-11-18

The Problem

Import data from SAS to R is easy. Library haven has the read_sas() function to import "*.sas7bdat" files to R data.frame. However, the "*.sas7bdat" files create by the write_sas() function mostly won't be able to be read by SAS.

It seems the alternative way of transferring data from R to SAS should be easy: just find a common data format supported by both R and SAS. For example, R can export data to CSV, and SAS can import CSV. Wouldn't that be easy?

Actually, no. Accurately importing CSV in SAS is never an easy task. SAS has to guess the type of data for each column, and weird data cells (quotes, commas, special characters, etc.) screw up the importing process all the time. How about other data formats?

Excel? It could be even worse than CSV.

How about SQL server (MySQL, MS SQL, etc.) then? Do you really want to setup a SQL server just for this?

If SAS supports the CSV format with YAML frontmatter, that will certainly help a lot, but it doesn't.

The Solution

So, the only sane solution is to run R code in SAS. Let the R code read the R data, and then use the ImportDataSetFromR() function to transfer the R data.frame to a SAS dataset. The SAS code would look like this:

proc iml;
  submit / R;
    df = readRDS("path\\to\\example.rds")
  endsubmit;
  run ImportDataSetFromR( "sas_data", "df" );
run;

If you use the CSVY format in R, the 3rd line would be something like this:

    df = data.table::fread("path\\to\\example.csv", yaml = TRUE)

This is a fairly simple solution, and unlike haven::write_sas(), it has a 100% success rate.

Yes, this solution requires the SAS software on the machine. But I guess most people need to use SAS data should already have SAS installed on their machines.

The R Workflow Solution

One thing I don't like the above solution is that it needs to run a SAS script, which disrupts my workflow.

When I need to export R data to SAS, that's because I am working on a R project, and at the end of the process, I need to export my R data products to share with colleagues who only use SAS.

I could open SAS and run the above SAS script. But I don't like that. Since I am already using R, why don't I just run everything in R? It is certainly doable because we can run SAS script in R.

What I will need first is a SAS script, which will have the code from the above solution and will be named as "ImportFromR.sas". And then, I will call this script from R use the following code:

sas_exe = '"C:\\Program Files\\SASHome\\SASFoundation\\9.4\\sas.exe"' 
sas_cfg = '"C:\\Program Files\\SASHome\\SASFoundation\\9.4\\sasv9.cfg"' 
sas_autoexec = '"my\\autoexec.sas"'
sas_script = '"path\\to\\ImportFromR.sas"'
sas_log = '"path\\to\\sas.log"'
sas_command = paste0(sas_exe, 
                     " -CONFIG ", sas_cfg, 
                     " -AUTOEXEC ", sas_autoexec, 
                     " -RLANG",
                     " -NOSPLASH",
                     " -SYSIN ", sas_script,
                     " -LOG ", sas_log)
system(sas_command, intern = TRUE, invisible = FALSE)

Two notes:

In addition, to make the entire process adaptable, you can write a R function which creates the SAS script "ImportFromR.sas" (so that it contains the real path of the R data), and then call this SAS script. Following pseudo code demonstrates the general idea:

ExportToSAS = function(r_data_fn, sas_data_fn)
{
  // build the SAS code into a string, which contains r_data_fn and sas_data_fn

  // use writeLines() to save the SAS code string into "ImportFromR.sas"

  // use system() to run the SAS script "ImportFromR.sas" from command line
}

© 2020 fengjiang.me