BookmarkSubscribeRSS Feed
Dauren
Fluorite | Level 6

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.

4 REPLIES 4
SASJedi
SAS Super FREQ

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;
Check out my Jedi SAS Tricks for SAS Users
Dauren
Fluorite | Level 6

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
SAS Super FREQ
Excellent! Please let me know how it goes.
Check out my Jedi SAS Tricks for SAS Users
yabwon
Onyx | Level 15

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

 

 

 

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1427 views
  • 2 likes
  • 3 in conversation