proc http as API with JSON data push macro variable inside quote

[Rae_]

I am uploading a dataset to a JAVA program that is serving as an intermediary between me and a redcap per the protocol of our IT department.  As such I need to push a JSON dataset through the API (via proc http).  The data inside the JSON file can be CSV but there is still a format for the overall file and since the data will change with each upload I've been trying to use a macro to call the data into the statement but I'm running into errors that I think are related to the quotes.  

The statement below works when I submit it with two observations manually entered where the variables "id" and "study_refusal" are listed only once first and the observations are comma delimited and separated by an escape character \n.  

ods escapechar="\"

proc http url="&myurl." OAUTH_BEARER="&mytoken."
method="POST"
in='{"projectId":"290","data": "id,study_refusal\n100009,0\n104568,1" ,"dataFormat":"csv","serverName":"INTERNAL"}'
ct="application/json" ;
run;
quit;

Once I try to switch out the variable names and the data with macro variables formatted the same way it starts to get caught up in the quotes and I can't get it to execute.  

I've tried to make just the variable names and the data a single macro, I've tried to use %str to mask the quotes so that the macro variables will still resolve.  As I'm trying to troubleshoot I'm still using only a few variables and observations but my final upload could be hundreds of observations a day.  

Here are some of the things I've tried at the line as a substitution for the in statement.

Attempt #1:

%let varnames=id,study_refusal;

%let datvar=\n100009,0\n104568,1;

%let supervar.={"projectId":"290","data": "&varnames&datvar." "dataFormat":"csv","serverName":"INTERNAL"}
in='&supervar'

Attempt #2: to use %str to stop the errors related to the quotation marks and still allow the macro variables to submit.  

in=%str(%'{%"projectId%":%"290%",%"data%": %")&varnames&datvar.%str(%", %"dataFormat%":%"csv%",%"serverName%":%"INTERNAL%"}%')

Any thoughts or suggestions would be appreciated!  Thanks!

[AllanBowe]

The `in=` parameter can be a FILEREF, it doesn't have to be a string

Just create the fileref exactly how you want it and pass it in, eg as follows (untested):

filename example temp;
data _null_;
  file example;
  put '{"projectId":"290","data": "id,study_refusal\n100009,0\n104568,1" ,"dataFormat":"csv","serverName":"INTERNAL"}';
run;

ods escapechar="\"

proc http url="&myurl." OAUTH_BEARER="&mytoken."
method="POST"
in=example
ct="application/json" ;
run;
quit;

[Rae_]

@AllanBowe thanks so much for thinking through this with me!  Unfortunately while I was able to get a successful submission of the statement itself the macro variables did not resolve.  This is the statement as it's showing in the debugging output.  

This is how I created the paypush statment and simply submitted as you'd suggested in=paypush.  I keep thinking that this is because the single quote in the put statement that's stopping the macro variables from resolving.

[Tom]

Macro variable references do not resolve inside of single quotes. The macro processor ignores strings that are bounded by single quotes instead of double quotes.

[Rae_]

Yes this is the issue I'm running into.  Any thoughts on how to get the data resolved knowing this issue.

[Tom]

Please make use of the INSERT CODE button when pasting/editing text blocks.  That will insure the text is displayed as is with a fixed space font.

If I understand the question you want to generate the value of the IN= string in your PROC HTTP call?

in='{"projectId":"290","data": "id,study_refusal\n100009,0\n104568,1" ,"dataFormat":"csv","serverName":"INTERNAL"}'

First question: Why not put the values into a file and use the IN= option of PROC HTTP to point to the file instead of trying to stuff the data into a string?  So create a file that looks like:

{"projectId":"290"
,"data": "id,study_refusal\n100009,0\n104568,1" 
,"dataFormat":"csv"
,"serverName":"INTERNAL"
}

And point a fileref to it, say the fileref is named JSON then in your PROC HTTP call you would use 

in=json

Second question:  What is it that you want in your JSON string?  What are the "variables" you are talking about?  What dataset has these variables? How large is the dataset?

Are you saying that this part of the string in your example is the CSV file?

"data": "id,study_refusal\n100009,0\n104568,1"

So that it is a representation of a file like this:

id,study_refusal
100009,0
104568,1

[Rae_]

@Tom Thank you for your feedback and I'll look for the insert code button next time.  I think I'm needing to clarify that my challenge is implementing the macro variables within the statement.  The statement itself is working with the data manually entered as you've shown.  But what I'd like to do is use a macro variables to insert the data and variable names so that the data doesn't have to be pasted into the statement each day that the code is run.  

First question: I did try the file as @AllanBowe suggested above but again the macro variables didn't resolve and therefor the statement was submitted successfully but the data wasn't actually contained in the statement.  

Second question:  In this example we only have two variables but my actual dataset had 43 variables and can change from 10 observations to over 200 daily depending on how many surveys the group receives.  

Does this help?

[Tom · Accepted solution]

Sounds like you want to do something like this.

Take in two input parameters: the name of a SAS dataset and a list of variable names.

%let dsname=sashelp.class;
%let varnames=name age sex;

Use that to generate a CSV file.

filename csv temp;
data _null_;
  set &dsname;
  file csv dsd ;
  if _n_=1 the put "%sysfunc(translate(&varnames,%str(,),%str( )))";
  put &varnames;
run;

Then generate a JSON file.

filename json temp;
data _null_;
  file json ;
  if _n_=1 then put 
    '{"projectId":"290"'
  / ',"dataFormat":"csv"'
  / ',"serverName":"INTERNAL"'
  / ',"data":"'
    @
  ;
  if eof then put '"' 
    / '}'
  ;
  infile csv end=eof;
  input ;
  _infile_=tranwrd(_infile_,'"','\"');
  put _infile_ @;
  if not eof then put '\n' @;
run;

And then call PROC HTTP and tell it send that file.

proc http .... in=json ... ;

[Rae_]

Thanks @Tom!!! That's exactly what I needed! Can you confirm the tranwrd statement is changing quotation marks for backslashes in the CSV file although I'm not recognizing where the quotations came into being? Sorry for my confusion, because you've already solved my issue but I'd really like to understand the methodology in the event that I need to modify in any way.

[Tom]

That would only change any quotes that happened to make it into the CSV file itself.  

Try it with as value in one your character variables you are sending that contains a comma. In which case you will get a CSV file list this:

ID,LIST
1,1
2,"2,3,4"
3,5

The reason I did it is that you are stuffing that CSV file into a quoted string in the JSON file.  In JSON you normally want use C/Unix style "escape" characters instead of normal SAS syntax of doubling embedded quotes.

In SAS you would include a quote in a string literal like this:

"He said ""Hello"" to me"

But in those systems they want you to use this goofy syntax instead:

"He said \"Hello\" to me"

There are other things you might need to "escape".  For example you might want to turn any existing \ into \\ to prevent the reader of the JSON string from thinking a string like this

i\need

has a newline (linefeed) character in it.  If you change the \ to \\ make sure you do it before changing the " to \".