How to make API requests with loop in SAS

rmartins 0
Viewed 40

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: output

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: input

What I'm looking for in the output: expected 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!

datastep sas-macro sas api sql
1 Answers

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;
Reeza0
Related