3

My company recently converted to SAS and did not buy the SAS SHARE license so I cannot ODBC into the server. I am not a SAS user, but I am writing a program that needs to query data from the server and I want to have my R script call a .sas program to retrieve the data. I think this is possible using

df <- system("sas -SYSIN path/to/sas/script.sas")

but I can't seem to make it work. I have spent all a few hours on the Googles and decided to ask here.

error message:

running command 'sas -SYSIN  C:/Desktop/test.sas' had status 127

Thanks!

rafa.pereira
  • 13,251
  • 6
  • 71
  • 109
Ben
  • 175
  • 1
  • 3
  • 10
  • what does "can't seem to make it work" mean? what kinds of error messages/failure modes are you getting? – Ben Bolker May 23 '16 at 16:42
  • Thanks, Ben. I added the message I am getting. – Ben May 23 '16 at 17:25
  • You are very unlikely to get an R dataframe using a command like that. – cory May 23 '16 at 17:45
  • don't have time to tackle this right now, but I think the information you need is how to dig in and figure what "status 127" means -- i.e. general troubleshooting tips for `system()`. Can you run this command in a shell/command window and see if you get more information? There may be arguments to `system()` (maybe `intern`?) that will output more diagnostic/debugging info. (Maybe status 127 is actually OK, and you're just not getting the output you think ...) – Ben Bolker May 23 '16 at 17:45
  • Thanks again, Ben. I read the documentation for the `system()` and I didn't get much out of it. I will try running it in the shell window and see what results I get. – Ben May 23 '16 at 17:48
  • cory, I do get a dataframe, but it only has one row equal 127L. Any suggestions other than it won't work? – Ben May 23 '16 at 17:49
  • Perhaps using keep.log=TRUE and log.file= option – IRTFM May 23 '16 at 17:56
  • @Ben In such situations is it better to give the complete path to the SAS-program `system("/sas -SYSIN path/to/sas/script.sas")` – jogo May 23 '16 at 17:57
  • @Ben Is the output from SAS a formatted text string, in which case using some form of `read.table` may be appropriate? I just can't think of a way that this would output an R object with class of data.frame unless you read in text using read.table – cory May 23 '16 at 19:12
  • What does your sas program do? If it generates a table, you could use one of the R packages designed specifically to read sas datasets (Haven for instance). If it generates an external text file (csv, txt...) then use `read.table()` to get the data back into R. – Dominic Comtois May 23 '16 at 23:23

1 Answers1

8

Assuming your sas program generates a sas dataset, you'll need to do two things:

  1. Through shellor system, make SAS run the program, but first cd in the directory containing the sas executable in case the directory isn't in your PATH environment variable.

    setwd("c:\\Program Files\\SASHome 9.4\\SASFoundation\\9.4\\")
    return.code <- shell("sas.exe -SYSIN c:\\temp\\myprogram.sas")

    Note that what this returns is NOT the data itself, but the code issued by the OS telling you if the task succeeded or not. A code 0 means task has succeeded.

    In the sas program, all I did was to create a copy of sashelp.baseball in the c:\temp directory.

  2. Import the generated dataset into R using one of the packages written for that. Haven is the most recent and IMO most reliable one.

    # Install Haven from CRAN:
    install.packages("haven")
    # Import the dataset:
    myData <- read_sas("c:\\temps\\baseball.sas7bdat")

And there you should have it!

Dominic Comtois
  • 10,230
  • 1
  • 39
  • 61