topic Re: How to send api request every X observation of a dataset in SAS Programming

How to send api request every X observation of a dataset

mitchum — Sun, 06 Aug 2023 12:58:44 GMT

Hi,

I'm try to send to an API a request (json content).
As the maximum number of element of each request is 100 (and my dataset may contain 5000 or more entries) i try to process over a batch of 10 elements (for testing).

I produce the code bellow who's working as expected if i didn't call the macro in the loop but print the content of my concatened_json variable.

But if i try to pass the json in the macro parameter i got an error like macro parameter contains syntax error.
In my mind the "best" way to achieve it is to create a temporary file, pass it to the macro to make the proc http query, reset the content of the file and process again... but i'm struggle to write it

/****** Update product with API PATCH Request *****/
%macro updateProduct(json_in);
 proc http
    method="PATCH"
    url="http://xxx/api/rest/v1/products"
    in=&json_in.
    out=resp;
  headers
    "Authorization" = "Bearer &token."
    "Content-Type"="application/vnd.akeneo.collection+json";
  run;
%mend;

data _null_;
  set temp;
  array json_arr[100] $1000 _temporary_;

  /* Declare the variable to retain the concatenated JSON strings */
  length concatenated_json $32767; /* Adjust the length as needed */
  length json_in_str $32767;
  retain concatenated_json; 

  json_in_str = '{"identifier":"' || compress(sku) || '","enabled":' || put(enabled, 1. -l) || ',"values":{';
  json_arr[0] = ' "availibility_code":[{"scope":null,"locale":null,"data":"' || availibility_code || '"}],';
  json_arr[1] = ' "allowed_cpy":[{"scope":null,"locale":null,"data":"' || compress(allowed_cpy) || '"}],';
  json_arr[2] = ' "libelle":[{"scope":null,"locale":null,"data":"' ||  compbl(libelle) || '"}]';
  do i = 1 to dim(json_arr);
    if not missing(json_arr[i]) then
      json_in_str = catx(' ', json_in_str, json_arr[i]);
  end;

  /* Finish building the JSON payload */
  json_in_str = catx(' ', json_in_str, '}}');

  concatenated_json = catx(' ', concatenated_json, json_in_str);

  /* Loop to print content every 10 iterations */
  if mod(_N_, 10) = 0 then do;
    /*put concatenated_json;*/
    call execute('%updateProduct(' || concatenated_json || ');');
	concatenated_json ="";
  end;
run;

any help is much appreciated.

Also open on suggestion / improvements as i starting with SAS and not a lot of experience...
Thanks

Re: How to send api request every X observation of a dataset

Tom — Sun, 06 Aug 2023 13:58:45 GMT

Look at the syntax for PROC HTTP. Doesn't it support passing the "IN" text via a FILE instead of trying to type it all into the code?

Re: How to send api request every X observation of a dataset

mitchum — Sun, 06 Aug 2023 16:14:17 GMT

Yes I can send a file (that's my goal at the end).

For testing i updated the macro to display the file content.

%macro updateProduct(json_in);
 data _null_;
  infile json_in;
  input;
  *put "*******------******";
  put "DEBUG: > " _infile_;
  *put "*******------******";
run;
%mend;

data _null_;
  set temp;
  array json_arr[100] $1000 _temporary_;
  /* Declare the variable to retain the concatenated JSON strings */
  length concatenated_json $32767; /* Adjust the length as needed */
  length json_in_str $32767;
  retain concatenated_json; 
  filename payload temp;
  *json_in_str = '{"identifier":"' || compress(sku) || '"}';
    json_in_str = '{"identifier":"' || compress(sku) || '","enabled":' || put(enabled, 1. -l) || ',"values":{';
  json_arr[2] = ' "code_mega":[{"scope":null,"locale":null,"data":"' || compress(code_mega) || '"}],';
  json_arr[3] = ' "availibility_code":[{"scope":null,"locale":null,"data":"' || availibility_code || '"}],';
  json_arr[4] = ' "allowed_cpy":[{"scope":null,"locale":null,"data":"' || compress(allowed_cpy) || '"}],';
  json_arr[5] = ' "libelle_digis":[{"scope":null,"locale":null,"data":"' ||  compbl(libelle_digis) || '"}]';
  do i = 1 to dim(json_arr);
    if not missing(json_arr[i]) then
      json_in_str = catx(' ', json_in_str, json_arr[i]);
  end;

  /* Finish building the JSON payload */
  json_in_str = catx(' ', json_in_str, '}}');

  concatenated_json = catx(' ', concatenated_json, json_in_str);

  /* Loop to print content every 10 iterations */
  if mod(_N_, 10) = 0 then do;
    *put concatenated_json;
  /* Step 1: Create and write to the temporary file */
	    file payload;
	    put concatenated_json; 
    /* Step 2: Pass the temporary file as a parameter to the macro */
    call execute('%updateProduct(' || payload || ');');
    /* Step 3: Clear the temporary file after the macro call */
    filename payload clear;
	*put '****';
	concatenated_json ="";
  end;
run;

Almost everything is working as expected, except the file is not "cleared" between each call to the macro.

for example in the first occurence i get 10 items on it, on the second one 20....
I was expecting the the clear will remove the content between each iteration but i should miss something...

Thanks

Re: How to send api request every X observation of a dataset

Tom — Sun, 06 Aug 2023 20:23:42 GMT

Write separate files. Then generate separate HTTP calls for each file.

So something like will make files with 10 records each and a dataset with a list of the file it made.

Just change the DO loop upperbound to use some number other than 10 per file.

%let path=%sysfunc(pathname(work));
data files ;
  fileno+1;
  length $8 inref outref $8 infile outfile $200;
  inref=cats('in',fileno);
  infile=cats("&path/in",fileno,".json");
  rc1 = filename(inref,infile);
  outref=cats('out',fileno);
  outfile=cats("&path/out",fileno,".json");
  rc2=filename(outref,outfile);
  file json filevar=infile;
  do n=1 to 10 until(eof);
     set have end=eof;
/*  stuff that writes the JSON lines text */
  end;
  keep fileno -- rc2;
run;

Then you can use that dataset with the list of files you created to make the PROC HTTP calls.

data _null_;
  set files;
  call execute(catx(' ','proc http method="PATCH"'
    ,'url="http://xxx/api/rest/v1/products"'
    ,'in=',inref
    ,'out=',outref
    ,';headers'
    ,'"Authorization" = "Bearer &token."'
    ,'"Content-Type"="application/vnd.akeneo.collection+json";'
    ,'run;'
  ));
  run;

Re: How to send api request every X observation of a dataset

mitchum — Mon, 07 Aug 2023 07:43:19 GMT

Many thanks! It did the work.
Just have to fine tune with some minor tuning but it's working as expected !!