How to make API requests with loop in SAS

Question

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!

Answer 1

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;