How to test PROC HTTP and the JSON library engine

By Chris Hemedinger on The SAS Dummy January 23, 2018 Topics | Programming Tips

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.

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 echo response, which the program parses using the JSON library engine. I have successfully run this program from my local SAS on Windows 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       */
/* Also works on SAS OnDemand for Academics */
 
filename resp "%sysfunc(getoption(WORK))/echo.json";
proc http
 url="https://httpbin.org/stream/1"
 method="GET"
 out=resp;
run;
 
/* Special macro vars supported with SAS 9.4 Maint 5 */
%put HTTP Status code = &SYS_PROCHTTP_STATUS_CODE. : &SYS_PROCHTTP_STATUS_PHRASE.; 
 
/* JSONPP function supported with SAS 9.4 Maint 6 */ 
data _null_;
 rc = jsonpp('resp','log');
run;
 
/* Tell SAS to parse the JSON response */
libname stream JSON fileref=resp;
 
title "JSON library structure";
proc datasets lib=stream;
quit;
 
/* interpret the various header fields */
data values(drop=ord:);
 merge stream.root stream.headers; 
 by ordinal_root;
run;
 
title "Headers and URL data"; 
proc print data=values;
run;
 
libname stream clear;
filename resp clear;

About Author

Chris Hemedinger
Director, SAS User Engagement

+Chris Hemedinger is the Director of SAS User Engagement, which includes our SAS Communities and SAS User Groups. Since 1993, Chris has worked for SAS as an author, a software developer, an R&D manager and a consultant. Inexplicably, Chris is still coasting on the limited fame he earned as an author of SAS For Dummies.

20 Comments

Quentin on January 24, 2018 9:25 am

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.
- Chris Hemedinger on January 24, 2018 9:42 am
  
  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
Mike on July 26, 2019 3:05 pm

Chris, thanks for the sample code. however, "https://now.httpbin.org/" is not available. Is there another site we can substitute?
- Chris Hemedinger on July 26, 2019 3:32 pm
  
  I guess the developer deprecated that. Try this:
  
  https://httpbin.org/stream/1
  
  Generates a simple JSON response.
Laura McGhghy on May 14, 2021 10:37 am

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
- Chris Hemedinger on May 17, 2021 6:12 pm
  
  Yes, if using HTTPS and a Linux-based SAS, the certs need to be in the path to use it.
vindya on December 2, 2021 12:43 pm

Thanks Chris, this code is helpful. When I run this I get the below error:

ERROR: The JSON engine cannot be found.
ERROR: Error in the LIBNAME statement.

I am running this script on SAS 9.4M7
- Chris Hemedinger on December 2, 2021 12:59 pm
  
  That doesn't seem right. This should work fine in M7. You might have an incomplete installation or bad configuration. Run:
  
  proc product_status; run;
  
  to verify your version.
vindya on December 2, 2021 1:39 pm

I did run proc product_status and proc setinit as well. Here are the results:

For Base SAS Software ...
Custom version information: 9.4_M7
Image version information: 9.04.01M7P080520
For SAS/STAT ...
Custom version information: 15.2
For SAS/GRAPH ...
Custom version information: 9.4_M7
For SAS Integration Technologies ...
Custom version information: 9.4_M7
For High Performance Suite ...
Custom version information: 2.2_M8
For SAS/ACCESS Interface to PC Files ...
Custom version information: 9.4_M7
For SAS/ACCESS Interface to ODBC ...
Custom version information: 9.4_M7

Setinit:

Product expiration dates:
---Base SAS Software 14JUN2022 (CPU A)
---SAS/STAT 14JUN2022 (CPU A)
---SAS/GRAPH 14JUN2022 (CPU A)
---SAS Integration Technologies 14JUN2022 (CPU A)
---SAS/Secure 168-bit 14JUN2022 (CPU A)
---SAS/Secure Windows 14JUN2022 (CPU A)
---SAS Enterprise Guide 14JUN2022 (CPU A)
---SAS/ACCESS Interface to PC Files 14JUN2022 (CPU A)
---SAS/ACCESS Interface to ODBC 14JUN2022 (CPU A)
---SAS Workspace Server for Local Access 14JUN2022 (CPU A)
---High Performance Suite 14JUN2022 (CPU A)
---SAS Add-in for Microsoft Excel 14JUN2022 (CPU A)
---SAS Add-in for Microsoft Outlook 14JUN2022 (CPU A)
---SAS Add-in for Microsoft PowerPoint 14JUN2022 (CPU A)
---SAS Add-in for Microsoft Word 14JUN2022 (CPU A)
- Chris Hemedinger on December 2, 2021 1:45 pm
  
  Okay, that should work so something is wrong with your install. And who knows what else is missing? I'd follow up with SAS Technical Support.
  - vindya on December 2, 2021 1:49 pm
    
    I have a ticket in with SAS support. Will try a re-install as well. Thanks for your help!
Suren on March 22, 2022 4:13 pm

Does proc http support graphql? I tried sending the graphql query in the in= parameter but I only get "400 Bad Request" as the response.
- Chris Hemedinger on March 22, 2022 4:38 pm
  Yes, you can use graphql. I've done this with some projects, such as to get GitHub project vulnerability data.
  filename in "&authpath./github_vuln_graphql2.json"; proc http url="https://api.github.com/graphql" method="POST" in=in out=resp ; headers "Authorization" = "token &AUTH." ; run;
  Where the JSON file I pass in is something like:
```
{"query":"{organization(login:\"my-org-name\") {repositories(first:100){edges{node{owner {id} name vulnerabilityAlerts(first:100){edges{node{  dismissReason dismissedAt securityVulnerability { package {name ecosystem } advisory { identifiers {type value}} severity} vulnerableManifestFilename }}}}}}}}"}
```
  The JSON that comes back isn't exactly neat data sets, so I usually end up using DATA step to pull from the ALLDATA member of the JSON library.
  - Suren on March 23, 2022 10:25 am
    
    Thanks Chris, Here is how my Json that I am passing looks like, do you see any issue here? I always get the "Missing Query Parameter" message
    {
    permissibleValues {
    uri
    label
    nciCode
    nciPreferredTerm
    cdiscSubmissionValue
    }
    }
    - Chris Hemedinger on March 23, 2022 10:57 am
      
      I'm not familiar with your API, but I suspect that you need to include the JSON in a "query" parameter within the input, like:
      
      {"query":"{permissibleValues { uri label nciCode nciPreferredTerm cdiscSubmissionValue }}"}
      - Suren on March 23, 2022 1:00 pm
        
        Thanks for the tip Chris. It worked adding "query" as below
        {"query":" query {permissibleValues { uri label nciCode nciPreferredTerm cdiscSubmissionValue }}"}
RAPHAEL FRANCO CHAVES on October 25, 2022 1:13 pm

Dear Chris, how's it going?
I need your help. I have been trying to setup a graphql query in order to retrive specific token, for instance:

{
"query": " mutation {\r\n signIn(\r\n email: \"test@gmail.com\"\r\n password: \"test_123\"\r\n ) {\r\n token\r\n }\r\n }"
}

Do you have any idea how can I implement this process using proc http?
Thanks in advance.
Rgs,
Raphael
Stephen Mc Cawille on March 28, 2023 7:22 pm

Hi Chris,

Are you able to enlighten me as to hat the root cause of this error is or a possible suggestion?

"ERROR: Windows SSL error -2146893018 (0x80090326) occurred at line 2696, the error message is "The message received was unexpected
or badly formatted. "
ERROR: Secure communications error status 807ff008 description "52.152.96.252: Windows SSL error -2146893018 (0x80090326) occurred
at line 2696, the error message is "The message received was unexpected or badly formatted. ""
ERROR: Encryption run-time execution error
ERROR: Call to tcpSockContinueSSL failed."
- Chris Hemedinger on March 29, 2023 8:30 am
  
  Stephen, my first check would be for a proxy server -- can your SAS environment get to the Internet directly or does it need a PROXYHOST= option on PROC HTTP? Next, depending on the site you are trying to access, are the certificates in place for SSL support. There are SSL* related options that can tell SAS where to look for certs, but usually on Windows it happens without that intervention.

Blogs

Blogs

How to test PROC HTTP and the JSON library engine

About Author

20 Comments