topic Re: proc http - loading one record per call in SAS Enterprise Guide

proc http - loading one record per call

alepage — Tue, 09 May 2023 17:55:03 GMT

Hello,

I am using a proc http procedure to upload a contact list via an api web site call.

But the issue, I am facing is: it is like the api web site is not able to upload many contacts at the same time. I have upload 10 contacts, one by one so, 10 proc http call.

%do j=1 %to 1 /*&cie_count.*/;
		    
			%chkifpathexist(dest&j.);
			%if &FEXIST. = 1 %then 
				%do;
					%put the pathname dest&j. exist;
					%put %sysfunc(quote(%sysfunc(pathname(dest&j.))));
					%put &&DirectoryId&j.;
					%put "uploading the consent information for the company : &&cie&j." ;

                     /* https://api.qualtrics.com/3f3d5290d19c2-create-directory-contact*/
					
					filename respp&j. "%sysfunc(getoption(WORK))/Consentinfo&j..json";
					PROC HTTP
						METHOD="POST"
    					URL= "&url_./directories/&&DirectoryId&j./contacts"
    					OUT=respp&j.
    					in=dest&j.;
    					headers
   						'x-api-token'= &Api_Token.
   						'Content-Type'='application/json';   
					run;
					
					libname respp&j. json "%sysfunc(getoption(WORK))/Consentinfo&j..json";
					proc datasets lib=respp&j.;
					run;

					data TrasactionContactsImportSummary&j.;
					set respp&j..alldata;
					run;

					data result&j.;
					set respp&j..result;
					call symputx("ContactId&j.",id,'g');
					run;
					%put "ContactId&j. = &&Contactid&j";
					%end;
					/* Number of Records per Minutes*/


				
			%else
				%do;
					%put the pathname does not exist;
				%end;
		%end;
	%end;

The sample json file look like that:

{"firstName" :"Clint1", "lastName" :"Eastwood1", "email" :"Clint1.Eastwood1@hotmail.com", "extRef" :"AAAAA1",
"embeddedData" : { "Brand" :" Insurance", "Consent Date" :"15JUL2020:14:29:52",
"Dataset Creation Timestamp" :"28MAR2023:22:01:07.758540" }, "unsubscribed" :"true" },
{ "firstName" :"Clint2", "lastName" :"Eastwood2", "email" :"Clint2.Eastwood2@hotmail.com", "extRef" :"AAAAA2",
"embeddedData" : { "Brand" :" Insurance", "Consent Date" :"29AUG2022:08:25:57",
"Dataset Creation Timestamp" :"28MAR2023:22:01:07.758540" }, "unsubscribed" :"true" },
{ "firstName" :"Clint3", "lastName" :"Eastwood3", "email" :"Clint3.Eastwood3@hotmail.com", "extRef" :"AAAAA3",
"embeddedData" : { "Brand" :" Insurance", "Consent Date" :"29JUL2020:16:21:59",
"Dataset Creation Timestamp" :"28MAR2023:22:01:07.758540" }, "unsubscribed" :"true" },
{ "firstName" :"Clint4", "lastName" :"Eastwood4", "email" :"Clint4.Eastwood4@hotmail.com", "extRef" :"AAAAA4",
"embeddedData" : { "Brand" :" Insurance", "Consent Date" :"07NOV2019:04:24:10",
"Dataset Creation Timestamp" :"28MAR2023:22:01:07.758540" }, "unsubscribed" :"true" },
{ "firstName" :"Clint5", "lastName" :"Eastwood5", "email" :"Clint5.Eastwood5@hotmail.com", "extRef" :"AAAAA5",
"embeddedData" : { "Brand" :" Insurance", "Consent Date" :"14JUL2020:18:35:37",
"Dataset Creation Timestamp" :"28MAR2023:22:01:07.758540" }, "unsubscribed" :"true" },
{ "firstName" :"Clint6", "lastName" :"Eastwood6", "email" :"Clint6.Eastwood6@hotmail.com", "extRef" :"AAAAA6",
"embeddedData" : { "Brand" :" Insurance", "Consent Date" :"26JAN2022:17:13:50",
"Dataset Creation Timestamp" :"28MAR2023:22:01:07.758540" }, "unsubscribed" :"true" },
{ "firstName" :"Clint7", "lastName" :"Eastwood7", "email" :"Clint7.Eastwood7@hotmail.com", "extRef" :"AAAAA7",
"embeddedData" : { "Brand" :" Insurance", "Consent Date" :"13AUG2021:06:53:31",
"Dataset Creation Timestamp" :"28MAR2023:22:01:07.758540" }, "unsubscribed" :"true" },
{ "firstName" :"Clint8", "lastName" :"Eastwood8", "email" :"Clint8.Eastwood8@hotmail.com", "extRef" :"AAAAA8",
"embeddedData" : { "Brand" :" Insurance", "Consent Date" :"03FEB2021:14:02:29",
"Dataset Creation Timestamp" :"28MAR2023:22:01:07.758540" }, "unsubscribed" :"true" },
{ "firstName" :"Clint9", "lastName" :"Eastwood9", "email" :"Clint9.Eastwood9@hotmail.com", "extRef" :"AAAAA9",
"embeddedData" : { "Brand" :" Insurance", "Consent Date" :"24MAR2022:20:19:40",
"Dataset Creation Timestamp" :"28MAR2023:22:01:07.758540" }, "unsubscribed" :"true" },
{ "firstName" :"Clint10", "lastName" :"Eastwood10", "email" :"Clint10.Eastwood10@hotmail.com", "extRef" :"AAAA10",
"embeddedData" : { "Brand" :" Insurance", "Consent Date" :"14SEP2022:14:58:17",
"Dataset Creation Timestamp" :"28MAR2023:22:01:07.758540" }, "unsubscribed" :"true" }

So, the issue I am facing here, it is like the API Web site is able to read only one record at the time and that per call. So when a json file containing 10 records is sent, it take only the first records and forgot the others.

I have sent the ten records each individually, it works but I will need to make ten different json files.

Is there a way to loop trough each record and make a call for each record.

Re: proc http - loading one record per call

Reeza — Tue, 09 May 2023 18:55:18 GMT

Where's the source data coming from to create the JSON file? A SAS data set? One row per call?

Re: proc http - loading one record per call

alepage — Tue, 09 May 2023 19:01:49 GMT

In that particular case, it comes from a sas dataset. So I wlll need to create one file per record. Is there a way to read the json file, one record at the time. ex:

Read the first record:

{"firstName" :"Clint1", "lastName" :"Eastwood1", "email" :"Clint1.Eastwood1@hotmail.com", "extRef" :"AAAAA1",
"embeddedData" : { "Brand" :" Insurance", "Consent Date" :"15JUL2020:14:29:52",
"Dataset Creation Timestamp" :"28MAR2023:22:01:07.758540" }, "unsubscribed" :"true" }

and make the proc http call

Read the second record:

{ "firstName" :"Clint2", "lastName" :"Eastwood2", "email" :"Clint2.Eastwood2@hotmail.com", "extRef" :"AAAAA2",
"embeddedData" : { "Brand" :" Insurance", "Consent Date" :"29AUG2022:08:25:57",
"Dataset Creation Timestamp" :"28MAR2023:22:01:07.758540" }, "unsubscribed" :"true" }

and make the proc http call and so on until record 10 ?

Re: proc http - loading one record per call

Reeza — Tue, 09 May 2023 19:24:45 GMT

Of course. How do you create the JSON datafile from the SAS data set?

Basically write a macro that you use CALL EXECUTE to operate on every single row.

Re: proc http - loading one record per call

alepage — Tue, 09 May 2023 19:35:51 GMT

Imagine that my json file is saved on the server as below.

{ "firstName" :"Clint1", "lastName" :"Eastwood1", "email" :"Clint1.Eastwood1@hotmail.com", "extRef" :"235987",
"embeddedData" : { "Brand" :"Anthony Insurance", "Consent Date" :"15JUL2020:14:29:52",
"Dataset Creation Timestamp" :"28MAR2023:22:01:07.758540" }, "unsubscribed" :"true" },
{ "firstName" :"Clint2", "lastName" :"Eastwood2", "email" :"Clint2.Eastwood2@hotmail.com", "extRef" :"236663",
"embeddedData" : { "Brand" :"Anthony Insurance", "Consent Date" :"29AUG2022:08:25:57",
"Dataset Creation Timestamp" :"28MAR2023:22:01:07.758540" }, "unsubscribed" :"true" }

From that point, could you provide an example how to make the http call below but that with each record:

filename respp&j. "%sysfunc(getoption(WORK))/Consentinfo&j..json";
PROC HTTP
METHOD="POST"
URL= "&url_./directories/&&DirectoryId&j./contacts"
OUT=respp&j.
in=dest&j.;
headers
'x-api-token'= &Api_Token.
'Content-Type'='application/json';
run;

libname respp&j. json "%sysfunc(getoption(WORK))/Consentinfo&j..json";
proc datasets lib=respp&j.;
run;

data TrasactionContactsImportSummary&j.;
set respp&j..alldata;
run;

data result&j.;
set respp&j..result;
call symputx("ContactId&j.",id,'g');
run;
%put "ContactId&j. = &&Contactid&j";
%end;

Where the json file : Consentinfo&j..json contains the two records

Re: proc http - loading one record per call

Reeza — Tue, 09 May 2023 19:56:27 GMT

How did that big JSON file get created initially? I'm suggesting you back up a step and create a single file for each record, assuming your data source is a SAS data set. This will ensure that the file is created properly each time. Alternatively, you would read in the JSOn and then parse it line by line....but the first way is easier for sure.

Re: proc http - loading one record per call

Reeza — Tue, 09 May 2023 20:38:47 GMT

Also, that JSON doesn't pass a JSON lint so not sure if that's why it's only reading the first record...

Re: proc http - loading one record per call

Reeza — Wed, 10 May 2023 15:59:32 GMT

data have;
infile '/home/fkhurshed/CLASS2/example1.json';
length value $500.;
if mod(_n_, 3)=1 then record+1;
input;
value = _infile_;
run;



data clean;
set have;
by record;


name = catt('/home/fkhurshed/CLASS2/contact', put(record, 8. -l), '.json');
file dummy filevar=name ;

if last.record then value = substr(value, 1, length(value)-1);

put value;

if last.record then do;
str = '%import_json(record='|| trim(put(record, 8. -l)) || ', file=' || name || ');';
call execute(str);
end;

run;



%macro import_json(record= , file=);

filename mydata "&file";
libname myjson JSON fileref=mydata;

proc datasets lib=myjson; quit;

data want&record;
set myjson.alldata;
run;

filename mydata;
libname myjson;

%mend;

Now change the %import_json macro to be your call API call/read.

Tested and works for import.

Re: proc http - loading one record per call

alepage — Thu, 11 May 2023 14:17:32 GMT

%do j=1 %to &cie_count.;
		    
			%chkifpathexist(dest&j.);
			%if &FEXIST. = 1 %then 
				%do;
					%put the pathname dest&j. exist;
					%put %sysfunc(quote(%sysfunc(pathname(dest&j.))));
					%put &&DirectoryId&j.;
					%put "uploading the consent information for the company : &&cie&j." ;
                    /* Getting the number of observations in each brand, i.e.
					   j=1, Anthony Insurance, j=2 NBI, j=3 Belairdirect, j=4 intact
					   AIcnt NBIcnt BDcnt Intcnt */

					%if &j=1       %then %let limitmax= &AIcnt.;
					%else %if &j=2 %then %let limitmax= &NBIcnt.;
					%else %if &j=3 %then %let limitmax= &BDcnt.;
					%else %if &j=4 %then %let limitmax= &Intcnt.;

					%put The number of observations in &&cie&j. is : &=limitmax.;

					/* Putting the proc http into a macro function*/

					%macro upload_json(i,j,directoryId,record);
					
					/* call example: %upload_json(i,j,directoryId,record); where i is equal to _n_ into the dataset
   					j is an index for the brand, directoryId=&&DirectoryId&j. and record is a field into the dataset
					https://api.qualtrics.com/3f3d5290d19c2-create-directory-contact
					*/
                    					
					filename res&i. "%sysfunc(getoption(WORK))/Consentinfo&i..json";

					PROC HTTP
						METHOD="POST"
    					URL= "&url_./directories/&directoryId./contacts"
    					OUT=res&i.
    					in=&record.;
    					headers
   						'x-api-token'= &Api_Token.
   						'Content-Type'='application/json';   
					run;

					libname res&i. json "%sysfunc(getoption(WORK))/Consentinfo&i..json";

					proc datasets lib=res&i.;
					run;

                    /* TxnCtxImpSmry= TransactionContactsImportSummary */

					data TxnCtxImpSmry&i.;
					set res&i..alldata;
					run;

					data result&i.;
					set res&i..result;
                    call symputx("ContactId&i.",id,'g');				
					run;
					%put Contactid&i equal &&ContactId&i.;

					proc datasets library=res&i. nolist;
					delete TxnCtxImpSmry&i. result&i.;
					quit;

					libname res&i clear;

					data _null_;
					rc=fdelete("res&i.");
					run;
                  
					proc datasets library=work nolist;
					delete TxnCtxImpSmry&i. result&i.;
					quit;
               
					%mend upload_json;

					/* Now reading the dataset line by line, putting the information into 
					   the variable record, the sending the information to Qualtrics */
                   
					
                    	Data ConsentInfo&j.;
						length record $2048;
						set info_1 ;
						where brand = "&&cie&j.";
						i=_n_;
						record=cats("'", '{ "firstName" : ',' "',firstName,'", ',' "lastName" : ','"',lastName,'", ','"email" :',
                        '"',email,'", ','"extRef" :','"',extRef,'", ','"embeddedData" : ','{ "Brand" :','"',Brand,'", ',
                        '"Consent Date" :','"',put(CONSENT_DT,datetime18.),'" ,','"Dataset Creation Timestamp" :',
                        '"',put(DW_CREATION_TS,datetime21.6),'" },','"unsubscribed" :','"',unsubscribed,'" }',"'");
                        call symputx('NewRecord',record,'g');	
						run;
                        
						/****** Executing record by record ******/
						
						Data _null_;
						length str $ 2048;
						set ConsentInfo&j.;
						call symputx('NewRecord',record,'g');
                        str=cats('%nrstr(%upload_json(',i,',',%NRSTR("&j."),',',%NRSTR("&&DirectoryId&j."),',',%NRSTR("&NewRecord."),'));');
						call symputx('str',str,'g');                  	
                        %put &=j &=NewRecord &&DirectoryId&j &=str.;
						call execute(str);
						run;	
				%end;				
			%else
				%do;
					%put the pathname does not exist;
				%end;
		%end;/* do j statement*/

Here's my SAS code and it works. But I wonder if there is a nice way to gather in a dataset the values of %put Contactid&i equal &&ContactId&i.;