PROC HTTP use macro variable as IN parameter

[Dauren]

Hello SAS community, I am new in SAS base language.

I have a dataset which I need to send as JSON to API using POST method. I’d like to send every single row separately.
Currently I am using a FOR loop to store every row in a file and send it to API with PROC HTTP. However, this method is really slow, and in order to increase processing speed I’m trying to send as IN parameter in PROC HTTP a JSON string stored in Macro variable. But then I face this error: ERROR 22-322: Syntax error, expecting one of the following: ;, AUTH_ANY, AUTH_BASIC, AUTH_NEGOTIATE, AUTH_NONE, AUTH_NTLM, CLEAR_CACHE, etc.

And I need to write a JSON response from API to a DB table. I use PROC JSON to store JSON response into a file and then pass it to macro variable and finally write to DB table value from macro variable. Is there a way to write JSON response straight into DB table without storing it into a file?

In general I'd like to increase the speed of SAS script and I think writing and reading from the file for every dataset's row in the LOOP takes up lots of time.

What I use now:

%let mvCountWB=0;
        %let k=0;

        proc sql;
            select count(*) into :mvCountWB
            from &mpExpTable._ext;
        quit; 
	    %if %MA_NOT_OK %then %goto ERROR_EXIT; 

      %let FIRST_NAME_W =;
      %let MIDDLE_NAME_W =;
      %let PHONE_NUMBER_1_W =;
      %let PHONE_NUMBER_2_W =;

       %do k=1 %to &mvCountWB;

       filename in_req "/temp/path/In&mvFract._&k..json";
       filename out_resp "/temp/path/Out&mvFract._&k..json";

            data _NULL_;
                p = &k;
                set &mpExpTable._ext point=p;				
				
				call symput ("FIRST_NAME_W", strip (name));
				call symput ("MIDDLE_NAME_W", strip (NAME_MIDDLE));
				call symput ("PHONE_NUMBER_1_W", strip (phone1));
				call symput ("PHONE_NUMBER_2_W", strip (phone2));
		
				stop;
            run;
		%if %MA_NOT_OK %then %goto ERROR_EXIT;            
			
			proc json out=in_req pretty nosastags;
				write open object;
					write values request;
					write open object;
						write values token "token";
						write values commonData;
							write open object;
								write values firstName "&FIRST_NAME_W";
								write values middleName "&MIDDLE_NAME_W";
								write values timeZone 3;
							write close;		
						write values "phonesData";				
							write open object;
								write values phone1 "&PHONE_NUMBER_1_W";
								write values phone2 "&PHONE_NUMBER_2_W";
								write values phone1Type 1;
								write values phone2Type 1;
							write close;						
					write close;
				write close;
			run;

proc http 
	METHOD=POST 
	WEBUSERNAME="Username" 
	WEBPASSWORD="Password"
	url = "url"
	in= in_req
	out = out_resp;
	SSLPARMS "SSLREQCERT"="ALLOW";
	headers "Content-type"="application/json"
			"Accept"="application/json";
quit;

libname test JSON fileref=out_resp;
	%let size=0;
	%let response=;
	
	proc sql;
	   select count(*) into:size from test.alldata;
	quit;
	
	%if &size. eq 5 %then %do;
		%let response='Finished successfull';						
	%end; %else %do;
		%let response='Finished with errors';
	%end;        

	proc sql noprint;
		&ConnectToOra.;						
			execute (
				insert into &db_table f  
				values('value')		
				) by ora;	
			disconnect from ora;
	quit;

run;
quit;

What I tried

data _null_;
	p = &k;
    set &mpExpTable._ext point=p;
	length json $ 10000;
	separator=' ';

	json = '{';
	json = catx(separator, json, '"request": {');
	json = catx(separator, json, '"token": "token",');
	json = catx(separator, json, '"commonData": {');
	json = catt(json, '"firstName": "',firstName,'",');
	json = catt(json, '"middleName": "',middleName,'",');
	json = catt(json, '"timeZone": ',3);
	json = catx(separator, json, '},');
	json = catx(separator, json, '"phonesData": {');
	json = catt(json, '"phone1": "',phone1,'",');
	json = catt(json, '"phone2": "',phone2,'",');
	json = catt(json, '"phone1Type": ',1,',');
	json = catt(json, '"phone2Type": ',1);	
	json = catx(separator, json, '}');
	json = catx(separator, json, '}');
	json = catx(separator, json, '}');
	
	put json;
	call symput('json_payload', json); 
	
	stop;
run;

proc http 
	METHOD=POST 
	WEBUSERNAME="Username" 
	WEBPASSWORD="Password"
	url = "url"
	in = "&json_payload"
	out = out_resp;
	SSLPARMS "SSLREQCERT"="ALLOW";
	headers "Content-type"="application/json"
			"Accept"="application/json";

run;

Any help is appreciated.

[SASJedi]

This problem is probably better approached using DS2 code. Here is some sample code to get you started:

/* Create sample input JSON data */
data apiPutJSON;
	do thisRound="Round 1","Round 2","Round 3";
		inputJSON=cats('{"form": {"',thisRound,'?format=json}}');
		output;
	end;
run;

proc ds2;
data apiPostResults/overwrite=yes;
   dcl package http h();
   dcl varchar(200000) character set utf8 JSONresult;
   method run();
      declare int rc;
      set apiPutJSON;
      /* create a POST method call*/
      h.createPostMethod(strip(inputJSON));
      /* Set URL for POST method */
		h.setURL('http://httpbin.org/post');
      /* POST method requires a BODY with a defined body type */
		h.setRequestBodyAsString(inputJSON);
		h.setRequestContentType('application/json');
      /* execute the POST*/
      h.executeMethod();
      /* retrieve the response result as a string, save to JSONresult */
      h.getResponseBodyAsString(JSONresult, rc);
   end;
enddata;
run;
quit;

proc sql;
select * 
	from apiPostResults
;
quit;

[Dauren]

Thank you @SASJedi.

Although I've never used DS2 code I would try to understand and implement you sample code for my task. Will update soon

[SASJedi]

Excellent! Please let me know how it goes.

[yabwon]

Your JSON text contains double quotes in it and you surround your macrovariable call with double quotes too... it won't work...

For this test data:

%let mpExpTable=abc;
%let k=1;

data &mpExpTable._ext;
  firstName = "John";
  middleName = 'Smith';
  phone1=123;
  phone2=456;
run;

your code

call symput('json_payload', json); 

makes it somethin like that:

{ "request": { "token": "token", "commonData": {"firstName": "John","middleName": "Smith","timeZone":3 }, "phonesData": {"phone1": "123","phone2": "456","phone1Type":1,"phone2Type":1 } } }

(no quotes around the JSON string) and then you do:

in = "&json_payload"

which basiacalll blows up the string structure.

Change your `call symput()` to:

call symput('json_payload', quote(strip(json),"'")); 

so that your macrovariable will contain properly quoted text, like:

'{ "request": { "token": "token", "commonData": {"firstName": "John","middleName": "Smith","timeZone":3 }, "phonesData": {"phone1": "123","phone2": "456","phone1Type":1,"phone2Type":1 } } }'

(see single quotes around JSON text) and then in the proc HTTP just do:

in = &json_payload.

Bart