Sharing SAS code that access REDCap using PROC HTTP without disclosing API tokens

[avz]

Hi There!

Our team members are sharing SAS code on a shared drive. We are collecting and storing data on REDCap (https://www.project-redcap.org/) for some of our projects. We want to automate data downloads from multiple REDCap project spaces. We need to securely include the REDCap API calls and authentication tokens in our shared code to ensure anyone can run the code without using or seeing unique API tokens assigned to someone else.

Thanks to this solution , I'm able to download the data using the following syntax:

filename my_in "C:\Users\avanzyl\MY PRIVATE FOLDER\dict_api_parameter.txt";
filename my_out "H:\OUR SHARED FOLDER\OUR_SHARED_data.csv";
filename status " C:\Users\avanzyl\MY PRIVATE FOLDER\redcap_status.txt";

%let mytoken = MYSECRETTOKEN;
data _null_ ;
file my_in ;
put "%NRStr(token=)&mytoken%NRStr(&content=record&rawOrLabel=label&csvDelimiter=,&type=flat&format=csv&fields=record_id)&";
run;

proc http
in= my_in
out= my_out
headerout = status
url ="https://redcap.OURSERVER.org/api/"
method="post";
run;

I don’t know how to conceal my token in SAS. I, therefore, need to keep and run the API syntax on my C drive.

I have more experience using R. The best R solution I found for this problem is for each team member to store their tokens in a text file in the same relative path on their C drives. The R code can then load the tokens using the following syntax:

source(paste(path.expand('~'), "/logins.R", sep = ""))

What is the best SAS solution?

[Kurt_Bremser]

Every user puts a file, readable only by them, in their %USER% directory, and you address it by using that environment variable (as you would do with $HOME in UNIX).

[ChrisHemedinger · Accepted solution]

Your solution in SAS can be very similar to the solution you use in other languages. I've covered what I do in practice in this article: How to secure your REST API credentials in SAS programs.

Example:

/* My path is different for UNIX vs Windows */
%let authpath = %sysfunc(ifc(&SYSSCP. = WIN,
	 \\netshare\root\u\&sysuserid.,
	 /u/&sysuserid.));
 
/* This should be a file that only YOU or trusted group members can read */
/* Use "chmod 0600 filename" in UNIX environment */
/* "dotfile" notation is convention for on UNIX for "hidden" */
filename auth "&authpath./.google_creds.csv";
 
/* Read in the secret account keys from another file */
data _null_;
 infile auth firstobs=2 dsd delimiter=',' termstr=crlf;
 length client_id $ 100 client_secret $ 30 refresh_token $ 60;
 input client_id client_secret refresh_token;
 call symputx('client_id',client_id);
 call symputx('client_secret',client_secret);
 call symputx('refresh_token',refresh_token);
run;

[Tom]

@ChrisHemedinger For this application we should be able to skip creating macro variables.  That will reduce the risk of exposing the token value.

[avz]

Hi @Tom , thank you for the note. I got it working using the guidelines Chris shared. I don't see my API token printed anywhere. Where could the token be exposed when running the shared code?  

I tried to improve my syntax according to your suggestion.  Unfortunately, I'm getting a message that I'm not authorized to access the server. It seems very helpful because if I include all "&fields=" (I only included record_id  in the example) then I have to scroll far to the right. It doesn't seem like there is a way to enable "wrap text" somewhere.

[Tom]

Once you stick the value into a macro variable then it might get displayed in the SAS log via the SYMBOLGEN option or use  of a %PUT _ALL_ or a %PUT _USER_ statement.

Not sure what you talking about in your debugging.  The code I posted was just an example. You would need to supply the actual name of the file you created that contained the token value.

Not sure what scrolling you are talking about but that was actually one of the main reasons for why I told you to break your long string into small strings in your PUT statement.  That way it be much easier for you to edit, no scrolling involved.

If you want to look at the file your data _null_ step is generating just us another data _null_ step to read it and dump its contents to the SAS log where you can see it.  For example if you have written to the file pointed at by the fileref MY_IN then this step will show you what that file contains.  The list statement output will automatically be wrapped. It will include ruler so you can see the length and position of the text.  Any non-printable characters will trigger it to also show the hexcodes for all of the characters in that line.

data _null_;
  infile my_in  ;
  input;
  list;
run;

[avz]

@ChrisHemedinger , thank you. I got it working using your guidelines.

%let authpath =C:\Users\&sysuserid.\Documents\Vaccine Registration;
filename auth "&authpath./RedcapCreds.csv";

data _null_;
 infile auth firstobs=2 dsd delimiter=',' termstr=crlf;
 length client_secret $ 32 ;
 input client_secret;
 call symputx('client_secret',client_secret);
run;

filename my_in "&authpath.\ApiParameter.txt";
filename my_out "P:\SHARED FOLDER\DATA.csv";
filename status "&authpath.\RedcapStatus.txt";

data _null_ ;
file my_in ;
put "%NRStr(token=)&client_secret%NRStr(&content=record&rawOrLabel=label&csvDelimiter=,&type=flat&format=csv&fields=record_id)&";

proc http
in= my_in
out= my_out
headerout = status
url ="https://redcap.OURSERVER.org/api/"
method="post";
run;

[Tom]

Sounds like you just want to modify this step.  The other parts do not appear to be able show the token.

%let mytoken = MYSECRETTOKEN;
data _null_ ;
file my_in ;
put "%NRStr(token=)&mytoken%NRStr(&content=record&rawOrLabel=label&csvDelimiter=,&type=flat&format=csv&fields=record_id)&";
run;

That step is writing something to a file.  But you have typed the token into the program.  So instead read the token from a secure file.  While we are at it let's simplify the SAS code to make it clearer.

Use single quotes and you won't need to worry about the & characters in the strings you are writing being processed by the macro processor, so not need to add in macro function calls.  Write each string separately and it will be easier to change some of the individual settings. For example to convert from comma delimited to pipe delimited.

If you name the variable you use to read the token as TOKEN you can even use named output mode of the PUT statement to have the text TOKEN= automatically written for you.

data _null_ ;
  infile mytoken truncover;
  input token $200. ;
  file my_in ;
  put token= +(-1)
    '&content=record'
    '&rawOrLabel=label'
    '&csvDelimiter=,'
    '&type=flat' 
    '&format=csv'
    '&fields=record_id'
    '&'
  ;
run;

[Delgoffe_MCRI]

I have recently published a macro to export REDCap data into SAS along with formats and a data dictionary. There is also a token cloaking macro that will utilize external files to pull in your token and set options to prevent disclosure.

Please see my paper, presentation, and code by filling out this survey: https://redcap.link/REDCapIsMySASFriend.

You can also find them on lexjansen under SESUG 2022.

Distribution using this method allows me to distribute only the most recent version, so you can check back to this survey in the future as well.