topic Re: SAS Access to REDCap using PROC HTTP. in SAS Procedures

SAS Access to REDCap using PROC HTTP.

jbuab — Fri, 12 Aug 2016 15:17:08 GMT

I am running into trouble accessing REDCap data using SAS PROC HTTP. I have followed the instructions presented in the paper found here (http://www.mwsug.org/proceedings/2013/RX/MWSUG-2013-RX02.pdf), changing the directory listed and providing the correct URL and Token.

We get the following error in the redcap_status.txt document:

HTTP/1.1 403 Forbidden
Date: Thu, 11 Aug 2016 19:38:05 GMT
Server: Apache/2.2.22 (Debian)
X-Powered-By: PHP/5.4.45-0+deb7u4
Expires: 0
cache-control: no-store, no-cache, must-revalidate
Pragma: no-cache
Access-Control-Allow-Origin: *
Vary: Accept-Encoding
Content-Length: 49
Content-Type: text/html; charset=utf-8

And the error we get in the redcap_data.csv file is:

ERROR: You do not have permissions to use the API

FYI: A colleague of mine has been able to successfully use Alteryx to access these REDCap data, but also cannot do so by submitting his token with the PROC HTTP statement in SAS.

Is there a setting that may need to be turned on either in REDCap or in SAS to allow us to access these data? What else are we missing?

Re: SAS Access to REDCap using PROC HTTP.

ChrisHemedinger — Fri, 12 Aug 2016 16:14:50 GMT

Looks like HTTPS is required. If running this from SAS on Windows, it should just work. On a Unix system, you might need to set the SSLCALISTLOC option for your local certificates. If using SAS University Edition, the SSL libraries aren't provided so this part can't work. You didn't receive an SSL error, so this might not be the problem at all.

The process for using the REDCap API looks similar to the one for Slack (the messaging app) and I've recently published a blog post about that.

If you want to check your inputs and outputs in the HTTP/S realm, you might try using https://httpbin.org as an endpoint and see what you get. That free site is really nice for debugging HTTP interactions. Obviously the API methods won't work, but it's an effective way to check the mechanisms in your code. I've also included a little macro to help you debug the responses that you recieve.

filename status temp;
filename my_out temp;
filename my_in temp;

%macro echoResp(fn=);
data _null_;
 infile &fn;
 input;
 put _infile_;
run;
%mend;

*** Project- and user-specific token obtained from REDCap ***;
%let mytoken = XXXXXXXXXXXXX;
 /**********************************************************
Step 2. Request all observations (CONTENT=RECORDS) with one
row per record (TYPE=FLAT). Note: Obtain your site-specific
 url from your local REDCap support team.
******************************************************/
*** Create the text file to hold the API parameters. ***;
data _null_ ;
file my_in ;
put "%NRStr(content=record&type=flat&format=csv&token=)&mytoken";
run;
*** PROC HTTP call. Everything except HEADEROUT= is required. ***;
proc http
 in= my_in
 out= my_out
 headerout = status
 url ="https://httpbin.org"
 method="POST";
run; 

%echoResp(fn=status);
%echoResp(fn=my_out);

Re: SAS Access to REDCap using PROC HTTP.

jbuab — Fri, 12 Aug 2016 16:40:16 GMT

The macro you gave me works perfectly, but I'm still getting exactly the same errors I shared in the original post. PROC HTTP is new to me, but I will read the blog entry you posted, as well as work to find out more about using https://httpbin.org as an endpoint, because I'm not sure I quite understand.

Re: SAS Access to REDCap using PROC HTTP.

ChrisHemedinger — Fri, 12 Aug 2016 16:54:09 GMT

The test program I shared is more of an "r u there" test for PROC HTTP. I think the problem that you are having is most likely due to the way you're providing your credential token.

If you're running SAS 9.4 Maint 3, you can simplify a little bit by placing text directly on the IN option:

proc http
 in="content=record%str(&)type=flat%str(&)format=csv%str(&)token=&mytoken"
...

The %NRStr() call was probably provided to prevent warnings about unresolved macro symbols triggered by the ampersands in the IN field. I've adjusted my version to wrap just the ampersands.

Usually this style of parameter setting is provided on the URL itself, not on a POST payload. If that's the case, then you would really want:

proc http
 out= my_out
 headerout = status
 url ="https://redcap.yoursite.org/api?content=record%str(&)type=flat%str(&)format=csv%str(&)token=&mytoken"
 method="POST";
run;

Also make sure that &mytoken contains no extra spaces, and does in fact match the key you were given.

Re: SAS Access to REDCap using PROC HTTP.

jbuab — Fri, 12 Aug 2016 17:07:24 GMT

Ok. I eliminated the lines including the infile and added the information you included in the URL.

Now, I'm getting different error messages, so I at least feel like I might be getting somewhere.

The status error message is:

HTTP/1.1 405 METHOD NOT ALLOWED

Server: nginx

Date: Fri, 12 Aug 2016 16:37:07 GMT

Content-Type: text/html

Content-Length: 178

Connection: keep-alive

Allow: HEAD, OPTIONS, GET

Access-Control-Allow-Origin: *

Access-Control-Allow-Credentials: true

The output file error message is:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">

<title>405 Method Not Allowed</title>

<h1>Method Not Allowed</h1>

<p>The method is not allowed for the requested URL.</p>

And just in case I might have had the token wrong, I tried the program the old way (with an infile), and the error messages I'm now getting are:

status

HTTP/1.1 301 Moved Permanently

Date: Fri, 12 Aug 2016 17:07:31 GMT

Server: Apache/2.2.22 (Debian)

Location: https://testcap.eyes.uab.edu/api/

Vary: Accept-Encoding

Content-Length: 328

Content-Type: text/html; charset=iso-8859-1

outfile

<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">

<title>301 Moved Permanently</title>

</head><body>

<h1>Moved Permanently</h1>

<p>The document has moved <a href="https://testcap.eyes.uab.edu/api/">here</a>.</p>

<hr>

<address>Apache/2.2.22 (Debian) Server at testcap.eyes.uab.edu Port 443</address>

</body></html>

Suggestions?

Re: SAS Access to REDCap using PROC HTTP.

ChrisHemedinger — Fri, 12 Aug 2016 17:15:20 GMT

For the first set of output, you're not still pointing at httpbin.org, right? "POST" isn't supported there, and that's the message I'd expect from it.

For the second set ... well, I don't know. If you have a colleague who is successfully using this endpoint from another tool, we might need to see how that's configured. Is there code (even if it's not SAS code) that you can share? Then we can translate to PROC HTTP.

PROC HTTP is essentially the SAS equivalent of cURL, a programmatic method to access HTTP services. Anything that you can do in another tool can probably also be done with PROC HTTP, as long as you transcribe the methods and parameters properly.

Re: SAS Access to REDCap using PROC HTTP.

jbuab — Fri, 12 Aug 2016 17:25:05 GMT

No, in both cases I was pointing to our test REDCap URL. And my coworker has tried to access the test site using Alteryx, and he's having no difficulty. So, I'm completely confused. All I can wonder is if I've been kicked out of the sandbox...

Re: SAS Access to REDCap using PROC HTTP.

jbuab — Wed, 17 Aug 2016 17:55:01 GMT

First: I can't figure out the reason behind the new error, but it resolved itself when I repasted the URL and my token in my program (and I'd made no substantial changes to either of these elements between the first error and the new error.

Second: It seems that I had most of the important elements for the PROC HTTP command, but the ordering of my command wouldn't work. I don't know why that should matter, especially because the paper I referenced presented the code in the order I used. But here's the code that eventually was successful:

filename my_in "C:\...\api_parameter.txt";

filename my_out "C:\...\redcap_data.csv";

filename status "C:\...\redcap_status.txt";

%let mytoken = XXXXXXX;

data _null_ ;

file my_in ;

put "%NRStr(token=)&mytoken%NRStr(&content=record&type=flat&format=csv)&";

run;

proc http

in= my_in

out= my_out

headerout = status

url ="https://.../api/"

method="post";

run;

Re: SAS Access to REDCap using PROC HTTP.

Christine_Lai — Wed, 13 Jun 2018 00:46:29 GMT

Thank you for the solution. I ran into the same problem for a few days and no clue how to fix it.
I appreciate the sharing of knowledge.
Yet it is for getting data from REDCap.
Any script for loading data to REDCap via API?