Using SAS with REST APIs is fun and rewarding, but it's also complicated. When you're dealing with web services, credentials, data parsing and security, there are a lot of things that can go wrong. It's useful to have a simple program that verifies that the "basic plumbing" is working before you try to push a lot of complex coding through it.
I'm gratified that many of my readers are able to adapt my API examples and achieve similar results. When I receive a message from a frustrated user who can't get things to work, the cause is almost always one of the following:
- Not using a recent enough version of SAS. PROC HTTP was revised and improved in SAS 9.4 Maint 3. The JSON library engine was added in SAS 9.4 Maint 4 (released in 2016). It's time to upgrade!
- Cannot access the web service through a corporate firewall. Use the various PROXY-related options to tell PROC HTTP how to connect through your network gateway, if necessary.
- No SSL support installed/configured (required for HTTPS access). This is rarely an issue on Windows systems, but on UNIX systems an admin must configure SSL certificates with the SSLCALISTLOC= option. If you're using SAS University Edition, you'll need the update from December 2017 or later -- that's where SSL was added. The editions prior to that did not include the SSL support.
The following SAS program is a simple plumbing test. It uses a free HTTP test service (httpbin.org) to verify your Internet connectivity from SAS and your ability to use SSL. The endpoint returns a JSON-formatted collection of timestamps in various formats, which the program parses using the JSON library engine. I have successfully run this program from my local SAS on Windows, from SAS University Edition (Dec 2017 release), and from SAS OnDemand for Academics (using SAS Studio).
If you can run this program successfully from your SAS session, then you're ready to attempt the more complex REST API calls. If you encounter any errors while running this simple test, then you will need to resolve these before moving on to the really useful APIs. (Like maybe checking on who is in space right now...)
/* PROC HTTP and JSON libname test */ /* Requires SAS 9.4m4 or later to run */ /* SAS University Edition Dec 2017 or later */ /* utility macro to echo the JSON out */ %macro echoFile(fn=); data _null_; infile &fn end=_eof; input; put _infile_; run; %mend; filename resp "%sysfunc(getoption(WORK))/now.json"; proc http url="https://now.httpbin.org/" method="GET" out=resp; run; /* Supported with SAS 9.4 Maint 5 */ /* %put HTTP Status code = &SYS_PROCHTTP_STATUS_CODE. : &SYS_PROCHTTP_STATUS_PHRASE.; */ %echoFile(fn=resp); /* Tell SAS to parse the JSON response */ libname time JSON fileref=resp; title "JSON library structure"; proc datasets lib=time; quit; /* interpret the various datetime vals and convert to SAS */ data values (keep=from:); length from_epoch 8 from_iso8601 8 from_rfc2822 8 from_rfc3339 8; /* Apply the DT format to a range of vars */ format from: datetime20.; set time.now; /* epoch = # seconds since 01Jan1970 */ /* SAS datetime is based on 01Jan1960, so */ /* add the offset here for a SAS value */ from_epoch = epoch + '01Jan1970:0:0:0'dt; /* straight conversion to ISO8061 */ from_iso8601 = input(iso8601,is8601dt.); /* trim the leading "day of week" */ from_rfc2822 = input(substr(rfc2822,5),anydtdtm21.); /* allow SAS to figure it out */ from_rfc3339 = input(rfc3339,anydtdtm.); run; title "Raw values from the JSON response"; proc print data=time.now (drop=ord:); run; title "Formatted values as SAS datetime"; proc print data=values; run; libname time clear; filename resp clear; |
7 Comments
Thanks Chris, very helpful to have a simple test case for inspiration. Not surprisingly, my test from PC SAS on 9.4M4 failed with a bunch of "ERROR: Unable to connect to Web server" messages from the PROC HTTP step. I assume because I'm trying from a corporate network, behind a whole bunch of firewalls that keep me from being productive. I assume that means I need to specify some of the proxy-related settings that you linked to, but no clue what to ask of our IT folks. Can you let me know what question I should send to Mordac? I don't want to mention SAS in the question, since they won't know what that is. So can I ask something more generic like "what proxy settings do I need to use to make REST API calls from inside our network?" I confirmed that I can access the site from the a browser, and download the JSON file.
Quentin,
In more general terms, you want to know if there is a proxy server host and port (and potentially credentials) that you can specify so that you can create scripts to automate access to external web resources. cURL, Python, and pretty much any other non-browser tool would need the same. Your web browser is likely configured with a proxy script that your IT folks can administer. You can specify the proxy in PROXYHOST/PROXYPORT options on PROC HTTP, or via a macro variable called PROCHTTP_PROXY.
Pingback: Using SAS to access and update files on Microsoft OneDrive - The SAS Dummy
Chris, thanks for the sample code. however, "https://now.httpbin.org/" is not available. Is there another site we can substitute?
I guess the developer deprecated that. Try this:
https://httpbin.org/stream/1
Generates a simple JSON response.
Thank you for this code - based on what I'm seeing from your notes, this error:
ERROR: The TCP/IP tcpSockEstablishSSL() support routine failed with error 104 (The connection was reset by a peer.).
ERROR: The tcpSockRead call failed. The system error is 'The connection was reset by a peer.'.
ERROR: Call to tcpSockContinueSSL failed.
seems to indicate I need to have an admin configure the SSL certificates with the SSLCALISTLOC=option. Am I correct?
We are running SAS 9.4M5
Yes, if using HTTPS and a Linux-based SAS, the certs need to be in the path to use it.