How to make API requests with loop in SAS

I'm trying to geocode entities (company) through the Brazilian zip code respectively, using a public API called BrasilAPI.

This is a fictional sample of the data:

data AMOSTRA_ENTIDADE;
infile datalines delimiter=',';
length  CODEMPRESA 3. 
        NOMEEMPRESA $9
        CEP $8;

input   CODEMPRESA 
        NOMEEMPRESA $
        CEP;
datalines;
1,EMPRESA A,71577090
2,EMPRESA B,01026010
10,EMPRESA C,01000000
100,EMPRESA D,92300000
110,EMPRESA E,29010580
;
run;

fictional sample of the data

I recently discovered the http SAS proc for this task, I don't know if there is another one. With that I tried to make the following code snippet to perform the request in the API following these examples I found:

example_1
example_2

example_3

%macro get_lat_long(cep);
  %let site="https://brasilapi.com.br/api/cep/v2/&cep.";
  %put NOTE: &site.;
  filename resp temp;

  proc http
     url = &site
     method = get
     out = resp;

     debug level=1;
  run;

  libname resp json;

  data _null_;
     infile resp;
     input;
     put _infile_;
  run;

%mend get_lat_long;

data COORDENADAS_ENTIDADE;
   set AMOSTRA_ENTIDADE;
   length macro_call $200;

   macro_call  = cats('%consulta_lat_long(',CEP,')');
   call execute(macro_call);
   rc = dosubl(macro_call);
run;

return from the code above:

My question is, how to make these requests in SAS to return the JSON and other information coming from the request in the API?

What I have input:

What I'm looking for in the output:

Please, if possible, I would like examples to run on both SAS Enterprise Guide and SAS Viya. Thank you in advance for your help!

Solution

Good start but you weren't reading in the JSON at all. This should give you a better idea of how to loop this, but it currently does not handle the case of a CEP not being found - I'll leave you to figure out that logic. Otherwise it does work to create a table with CEP + Coordinates you can merge. Rather than the JSON text, I suggest storing the ALLDATA from the JSON and reformat that to the required structured.

data AMOSTRA_ENTIDADE;
infile datalines delimiter=',';
length  CODEMPRESA 3. 
        NOMEEMPRESA $9
        CEP $8;

input   CODEMPRESA 
        NOMEEMPRESA $
        CEP;
datalines;
1,EMPRESA A,71577090
2,EMPRESA B,01026010
10,EMPRESA C,01000000
100,EMPRESA D,92300000
110,EMPRESA E,29010580
;
run;

proc sql;
drop table master_locations;
drop table master_allData;
quit;

%macro get_lat_long(cep);
  %let site="https://brasilapi.com.br/api/cep/v2/&CEP";
  %put NOTE: &site.;
  filename resp temp;

  proc http
     url = &site
     method = "get"
     out = resp;
  run;

  libname resp json;

  
  *keep all data if desired;
  data fullResponse;
      length Value $200.;
      set resp.allData;
      
  run;
  
  *get location coordinates from RESP library;
  data location;
      set resp.location_coordinates;
      CEP=&CEP.;    
  run;
  
  *append to master file;
  proc append base= master_locations data=location;
  run;
  
  proc append base=master_allData data=fullResponse;
  run;

  *remove temporary tables so you do not get wrong data between loops;  
  proc sql;
  drop table location;
  drop table fullresponse;
  quit;
  
  %mend get_lat_long;

  
  data COORDENADAS_ENTIDADE;
   set AMOSTRA_ENTIDADE;
   length macro_call $200;

   macro_call  = cats('%get_lat_long(',CEP,')');
   call execute(macro_call);
run;