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.
... View more
