How do I load a dataset and submit SAS code using it?

[dannytoomey]

I'm coming from R and Python and curious to figure out basic operations in SAS. In my workflow, I would like to write a local .sas file and use SASpy to execute it on a free SAS OnDemand server. I'd like to figure how to: 1) Load a dataset (in this case excel) 2) Load SAS code from a .sas file that references the dataset 3) Execute the code using an SAS OnDemand server

Here's my file tree:

.
├── data_example.xlsx
├── env
├── sascfg_personal.py
├── test_sas.py
└── test_sas.sas

Contents of test_sas.py:

import saspy
sas = saspy.SASsession(cfgfile='sascfg_personal.py',results='HTML')

code = open('./test_sas.sas').read()
result = sas.submit(code)

print(sas.lastlog())

Contents of test_sas.sas

proc import 
    out=new_data
    datafile="./data_example.xlsx"
    dbms=xlsx
    replace;
    getnames=YES;
run;

Contents of sascfg_personal.py:

SAS_config_names=['oda']
oda = {'java' : '/usr/bin/java',
#US Home Region 2
'iomhost' : ['odaws01-usw2-2.oda.sas.com','odaws02-usw2-2.oda.sas.com'],
'iomport' : 8591,
'authkey' : 'oda',
'encoding' : 'utf-8'
}

Last log when executing python test_sas.py:

5                                                          The SAS System                       Tuesday, August  1, 2023 07:41:00 PM

24         ods listing close;ods html5 (id=saspy_internal) file=_tomods1 options(bitmap_mode='inline') device=svg style=HTMLBlue;
24       ! ods graphics on / outputfmt=png;
25         
26         proc import
27             out=new_data
28             datafile="./data_example.xlsx"
29             dbms=xlsx
30             replace;
31             getnames=YES;
32         run;
ERROR: Physical file does not exist, /pbr/biconfig/940/Lev1/SASApp//data_example.
33         
34         
35         ods html5 (id=saspy_internal) close;ods listing;
36         

6                                                          The SAS System                       Tuesday, August  1, 2023 07:41:00 PM

37         
SAS Connection terminated. Subprocess id was 4161

It appears that the server is not able to locate data_example.xlsx. How would I upload the data so that test_sas.sas can be executed on the server?

Thanks in advance for your help!

[tomweber-sas]

Hey, I can help you with this. First, thanks for providing all this useful info! I really appreciate that. This all looks perfectly reasonable, and I think you just need one more line of code to upload that client side file to the SAS server side. in your test_sas.py file (your python/saspy code), simply upload that file prior to submitting the SAS code that's expecting it to be on the SAS server:

import saspy
sas = saspy.SASsession(cfgfile='sascfg_personal.py',results='HTML')

# this is needed to upload your xls file (you can put it where you like, I find the WORK path convenient)
log = sas.upload('./data_example.xlsx', sas.workpath)

code = open('./test_sas.sas').read().format(sas.workpath) # this adds the work directory to the {} in the sas code below.
result = sas.submit(code)

print(sas.lastlog())

You can try this various ways, but as I uploaded the xls file to the work directory, you need to add that path to your SAS code. If you have a fixed path to upload it to, you can just hardcode that in this sas file and not worry about that format() above after the read method:

proc import 
    out=new_data
    datafile="{}data_example.xlsx"
    dbms=xlsx
    replace;
    getnames=YES;
run;

Seems like this is all you need for what you have! Tom

[dannytoomey]

Hi Tom,

Thanks so much for your quick reply! Everything works now :)

One related SAS newbie question - how would I print the output to the console? I've amended sas_test.sas so that it now contains:

proc import 
    out=new_data
    datafile="{}data_example.xlsx"
    dbms=xlsx
    replace;
    getnames=YES;
run;

proc print data=new_data;

When I run python test_sas.py the output is:

SAS Connection established. Subprocess id is 4620

SAS Connection terminated. Subprocess id was 4620

Since there are no errors, I'm guessing the process works fine but the output from the SAS server is not being received by SASpy. How would I amend this so that I can print the new_data object to my local console?

[tomweber-sas]

Are you running this as a batch script? As opposed to interactively? In batch (pytyon batch) you have to wrap most anything in a print() function to get it written out. Interactive python simply displays things when you just submit them.

Maybe the other part of this question is that you assigned the results from the submit to a variable (which you should), but then you need to print that out if you want to see it: results = sas.submit().

print(results['LOG']) # will print the log, and sas.HTML(results['LST']) # will render the HTML output (if there is any.

But using sas.submitLST(code) # will do that for you. check out the API: https://sassoftware.github.io/saspy/api.html#saspy.SASsession.submitLST

Not sure if that's the answer, so let me know if I misunderstood

[dannytoomey]

I see! I was misunderstanding how the API works. Yes, I am executing this as a batch. My goals is to render the HTML object from the results output. However, when I try sas.HTML(results['LST']), I get:

SAS Connection established. Subprocess id is 6797

<IPython.core.display.HTML object>
Traceback (most recent call last):
  File "test_sas.py", line 9, in <module>
    sas.HTML(results['LST'])
TypeError: 'NoneType' object is not subscriptable
SAS Connection terminated. Subprocess id was 6797

So I'm getting an HTML object, which should sounds like it should be rendered with sas.HTML(), but does not appear to render. I'm expecting a print output of a table containing the data in data_example.xlsx. How would I get that output to print? Thanks so much for your help!

Updated contents of test_sas.py:

import saspy
sas = saspy.SASsession(cfgfile='sascfg_personal.py',results='HTML')

log = sas.upload('./data_example.xlsx', sas.workpath)

code = open('./test_sas.sas').read().format(sas.workpath)
results = sas.submitLST(code)

sas.HTML(results['LST'])

Updated contents of test_sas.sas:

proc import 
    out=new_data
    datafile="{}data_example.xlsx"
    dbms=xlsx
    replace;
    getnames=YES;
run;

proc print data=new_data;
run;

[dannytoomey]

Nevermind! I figured it out. The issue was that I specified results = 'HTML' and set my output to LST. Here's the amended code that works as expected, in case anyone else comes across this thread in the future :)

test_sas.py:

import saspy
sas = saspy.SASsession(cfgfile='sascfg_personal.py',results='LST')

log = sas.upload('./data_example.xlsx', sas.workpath)

code = open('./test_sas.sas').read().format(sas.workpath)
results = sas.submitLST(code)

Thanks so much for your help!

[tomweber-sas]

Well, you're on the right path, but still missing some of how this all works. 'LST' isn't a valid value for results. What you are getting instead is 'text'. Results() take one of 3 values, and only applies to tabular results. HTML is still the only way to get plots and graphs and things like that, which SAS produces. You can only render those when running in a way that can do that. You can't render a picture when running in batch; where would it go? If you're running in a notebook, sure, html objects can be rendered in that. If you're running interactively at a command prompt, you're not going to be able to render a picture (HTML object). SASPy supports running in batch, so that you can get these objects returned to you, then you can write then out to files (x.html), which can then be opened and rendered by anything that supports graphics. Take a look at this example which walks you through all of these things. Most of this near the end of it, but the whole notebook will be helpful I'm sure: https://github.com/sassoftware/saspy-examples/blob/main/SAS_contrib/saspy_example_github.ipynb

Glad you're up and running though! That's good news!

[tomweber-sas]

Hey, was there anything else you needed with this? Or can we close it? You can always reach out for anything else too.

Thanks, Tom

[dannytoomey]

I'm all set! Apologies for the delayed reply. Thanks so much for your help!

[tomweber-sas]

cool, no trouble. I'll close this one!