execute proc http for each row in data step

[evaleah]

I am building a complex json string to feed to an http proc for each row in a dataset.

I have this:

	data iti;
	    guid=uuidgen();
		set &dbconn..&vwname;
		
		jsnstr=strip('{"body": {"ProcessName": "DCRI Rando Enrollment REST","IncomingXML":"'
			||"<?xml version='1.0' encoding='UTF-16'?><Message MessageId='"
			||trim(guid)
			||"' MessageType='Integration Object' 
			||"<Number>"||strip(SITE)||"</Number>"
			||"<Identity>"||strip(INITIALS)||"</Identity>"
			||'</Message>"}}');	

/* 		call the macro to send each participant via API */
		call execute('%pushAPI(jsnstr="'||jsnstr||'")');
	run;

%macro pushAPI(jsnstr);
	/* make the call to Seibel to push the participants */	
	PROC HTTP
		clear_cache
		method="POST"
		url="https://customurl"
		CT= "application/json"
		in=&jsnstr	
		WEBUSERNAME="user" 
		WEBPASSWORD="pass" 
		AUTH_BASIC
		out=results
		headerout=hdrs;
	run;
%mend pushAPI;

The jsnstr is being built 100% correctly but it is not being actually sent. Any ideas if I am doing this correctly?

[jimbarbour]

Have you tried it?  And what if anything is happening?  Any errors or warnings?  Can you post the log?

Some testing ideas (apologies if you're already doing this):

The first step for me for debugging/testing would be to write a occurrence of the JSON to a file and then try to read it with some type of JSON processor, e.g. Proc JSON.  If the processor can read and process the code correctly, then you've eliminated one possible source of errors.

Second, I would hard code an instance of the Proc HTTP with the values I expect to get from the JSON data step.  If the Proc HTTP works, then you've eliminated another possible source of errors.

Third, I would write the generated output of a CALL EXECUTE to a file just to see if everything is working there.  In other words, instead of actually running a CALL EXECUTE, I would write whatever it is that would have been run in the CALL EXECUTE to a file so that I can see that it's working properly.  You could probably skip this test and actually run a CALL EXECUTE and then loop back to this test if you encounter problems.  I would not skip the first two.

Only after testing the above two would I let one of the CALL EXECUTEs run.  By the way, the use of CALL EXECUTE here is the first thing I thought of when I saw the title of this post, so I think your approach is good.  A DOSUBL might also work, but I think CALL EXECUTE is the most straightforward. 

Jim

[evaleah]

I have done all the breaking down. All the pieces work. I cannot put it all together! I am getting:

ERROR 22-322: Syntax error, expecting one of the following: ;, AUTH_ANY, AUTH_BASIC, AUTH_NEGOTIATE, AUTH_NONE, AUTH_NTLM,
CLEAR_CACHE, CLEAR_CONN_CACHE, CLEAR_COOKIES, CT, EXPECT_100_CONTINUE, FOLLOWLOC, HEADERIN, HEADEROUT,
HEADEROUT_OVERWRITE, HTTP_TOKENAUTH, IN, METHOD, NOFOLLOW, NOFOLLOWLOC, NO_CONN_CACHE, NO_COOKIES, OAUTH_BEARER, OUT,
PASSWORD, PROXYHOST, PROXYPASSWORD, PROXYPORT, PROXYUSERNAME, PROXY_AUTH_BASIC, PROXY_AUTH_NEGOTIATE,
PROXY_AUTH_NONE, PROXY_AUTH_NTLM, TIMEOUT, URL, USERNAME, VERBOSE, WEBAUTHDOMAIN, WEBPASSWORD, WEBUSERNAME.

ERROR 76-322: Syntax error, statement will be ignored.

[jimbarbour]

OK, I think we're making progress here.  Can you set the following option in your code?

OPTION MSGLEVEL=I;

And then re-run.  I'd like to see the log.  The log should display the generated CALL EXECUTE, something like: 

NOTE: CALL EXECUTE generated line.
1         + %Exec_Cmd(%SLEEP(2); SYSTASK COMMAND "SAS -logparm ""rollover=session write=immediate"" -log 
U:\Tools\Utility\DiskSpaceDetail\logs\Disk_Space_Detail_#Y-#m-#d_#H-#M-#s_#p.log -initstmt ""%NRSTR(%SLEEP(200.69300008);)"" 
-sysin U:\Tools\Utility\DiskSpaceDetail\pgm\Disk_Space_Detail.sas -set NoPrompt NOPROMPT -nostatuswin -noterminal 
-nosplash -noicon " NOWAIT);

This may tell us something; it may not, but let's give it a shot.

Jim

[evaleah]

It breaks down the string I have generated and repeats the same error. I would copy and paste it here but it contains sensitive data. I stripped down my example. It is harder to do that when running it.

[ballardw]

Prove that your jnstr variable is created "100% correctly".

Starting on this bit

			||"' MessageType='Integration Object' 
			||"<Number>"||strip(SITE)||"</Number>"

You have unbalanced quotes. The || before <Number> is part of the previous text. The

You should show the code from the LOG with all the log messages.

Removing a data set I don't have submitting code with created values for the variables used in the code:

1      data junk;
2          guid = "abc 123   ";
3          Site ='PDQ';
4          initials='aaaaa';
5          jsnstr=strip('{"body": {"ProcessName": "DCRI Rando Enrollment REST","IncomingXML":"'
6             ||"<?xml version='1.0' encoding='UTF-16'?><Message MessageId='"
7             ||trim(guid)
8             ||"' MessageType='Integration Object'
9             ||"<Number>"||strip(SITE)||"</Number>"
                                           -
                                           22
10            ||"<Identity>"||strip(INITIALS)||"</Identity>"
                                                 -
                                                 22
11            ||'</Message>"}}');
                            -
                            388
                            200
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,
              a numeric constant, a datetime constant, a missing value, INPUT, PUT.

ERROR 388-185: Expecting an arithmetic operator.

ERROR 200-322: The symbol is not recognized and will be ignored.

12
13   /*       call the macro to send each participant via API */
14         call execute('%pushAPI(jsnstr="'||jsnstr||'")');
WARNING: Apparent invocation of macro PUSHAPI not resolved.
14         call execute('%pushAPI(jsnstr="'||jsnstr||'")');
                          -------
                          68
ERROR 68-185: The function PUSHAPI is unknown, or cannot be accessed.

15      run;

So your code as posted is unlikely to do anything from the data step.

[evaleah]

I have proven the string is 100% correct by writing it to a file. The output to the file is spot on. If you note the open quotes, they close later in the concatenation. In other words, the building of the string is not the issue.

[evaleah]

I think what I am really asking here is how do I send a string as a parameter that contains single quotes and double quotes? That is the root of the question which I did not word very well to start.

jsnstr is a JSON string. It contains XML as one of its members. This means there are both double quotes around the JSON member definitions and single quotes around the XML declarations. How do I send a string like that as a parameter to the proc http call?

I tried writing it to a file but since I am looping through a dataset it writes each line to the file. I need to send them one by one. So I am fine with putting it in a file and then running it. But I need to do each one when it is hit, not at the end with all of the JSON in a single file.

[jimbarbour]

---

@evaleah wrote:
I think what I am really asking here is how do I send a string as a parameter that contains single quotes and double quotes? That is the root of the question which I did not word very well to start.

---

Ah!  That narrows it down a bit.  Typically, I do something like this:  """'You can quote me,' said the man."""

You'll have to play with it, but typically if I have quotes inside quotes I have to double or triple (or more) them to get what I want.  Play with that and see what you get.

%LET squote = ';
%LET dquote = ";

The other thing to try is to use %NRSTR(&squote) and %NRSTR(&dquote).  I usually have to play with it a few times before I get it right.

Lastly, once you get the quotes where you want them, the %NRSTR function may still be of benefit.  I think you're OK since you have the macro call in single quotes, but you may want to try something like this:

call execute('%NRSTR(%pushAPI(jsnstr="'||jsnstr||'"))');

You want that %pushAPI macro to execute as part of the CALL EXECUTE and not before.  I think you're OK here and that the problem is probably the quoting, but this is another thing to try.  Sorry I can't be more definitive here; it's been a while since I've done a CALL EXECUTE with generated text.

Jim

[ballardw]

Show us some of the valid values of the JNSTR variable.

Since you are using it as parameter to a macro then you have some limits as to what the string can contain. Commas could mean that the macro thinks you passed more parameters than were defined, % and & characters can trigger other macro parsing problems.

And you may want to consider wrapping that whole macro call in a %nrstr function call because you might be getting the macro triggered.

BTW, the Error messages you post have very little relevance if the code generating them is not included. The entire step or procedure that generates the error should be included, copy the whole mess from the log including all the notes and messages, then paste into a text box opened with the </> icon to preserve the formatting of the text. The message windows will reformat text an may move diagnostic characters that SAS typically provides with the "expects one of " type errors.

[Tom · Accepted solution]

Your posted code has the data step and macro definition in the wrong order.  You cannot call a macro you have not defined yet.

Your posted code does not appear to be generating a closing >  for the <Message> tag.

How long is the string you are generating into the data step variable JSNSTR?

Is it more than 200 bytes?  If so then make sure to define that variable long enough so it is not truncated.

You seem to be adding double quotes around the outside of the value of JSNSTR when using it to generate the value of the macro parameter JSNSTR. Are you sure none of the content already contains double quote characters?  If they do the embedded double quotes need to be doubled. You can use the QUOTE() function to add quotes and it will make sure to double up any embedded quotes so that you generate valid SAS syntax.

Plus if the value of the generated JSNSTR contains macro triggers, & or %, then you really should be quoting it with single quotes instead of double quotes so that the macro processor ignores it.  Otherwise it might accidently replace part of the string with the value of a macro variable, macro function call or macro call.

In general is is MUCH easier to use the PUT statement to write code than it is to use CALL EXECUTE().  Plus then you have an actual file with the generated code that you can review before submitting.  You can copy the macro calls and run them individually to debug the code generation step.

Using the CATS() function to generate your string is much easier that having code strip, time and ||.

I don't think this will work but it should get your closer.

%macro pushAPI(jsnstr);
	/* make the call to Seibel to push the participants */	
	PROC HTTP
		clear_cache
		method="POST"
		url="https://customurl"
		CT= "application/json"
		in=&jsnstr	
		WEBUSERNAME="user" 
		WEBPASSWORD="pass" 
		AUTH_BASIC
		out=results
		headerout=hdrs;
	run;
%mend pushAPI;

filename code temp;

data iti;
  guid=uuidgen();
  set &dbconn..&vwname;
  length jsnstr $32767 ;
  jsnstr=cats
        ('{"body": {"ProcessName": "DCRI Rando Enrollment REST","IncomingXML":"'
        , "<?xml version='1.0' encoding='UTF-16'?>"
        , '<Message MessageId=', quote(strip(guid,"'"))
        , "MessageType='Integration Object' 
        , "<Number>",SITE,"</Number>"
        , "<Identity>",INITIALS,"</Identity>"
        , '</Message>"}}'
         );	

* write one call per observation ;
  file code ;
  put '%pushAPI(' jsnstr = :$quote. ')' ;
run;

* Run the generated code ;
%include code / source2 ;

[Patrick]

As an add-on to @Tom's suggested approach: When it comes to json and the like I find it often easier to use an external template with tokens and then generate the actual json by replacing the tokens. In doing it this way I don't have deal with any of the quoting anymore. Below using datalines for such a tokenized json template - but this could of course also be in an external .txt file.

data json_template;
  input;
  length jsnstr $32767;
  retain jsnstr;
  jsnstr=cats(jsnstr,_infile_);
datalines4;
{"body": {"ProcessName": "DCRI Rando Enrollment REST","IncomingXML":"
<?xml version='1.0' encoding='UTF-16'?>
<Message MessageId='@#@guid@#@'
and so on '@#@token2@#@' and so on         
;;;;
data json_template;
  set json_template end=last;
  if last then output;
run;

data parameters;
  p1='ABCD';
  p2=123;
  output;
  p1='XYZ';
  p2=999;
  output;
run;

data _null_;
  file print;
  if _n_=1 then set json_template;
  set parameters;
  jsnstr_out=jsnstr;
  jsnstr_out=tranwrd(jsnstr_out,'@#@guid@#@',strip(p1));
  jsnstr_out=tranwrd(jsnstr_out,'@#@token2@#@',strip(p2));
  put _n_ ;
  put jsnstr_out;
run;

[evaleah]

Two things in this post were so helpful. Thank you! First, I must have used all the cat functions and why I never went to "cats" I have no idea. That made this easier. The real magic was writing to a file and then executing every line in that file. I did not think of that at all. Thank you!