How can I send, receive and collect data to and from API in a loop in SAS EG?

Question

I would like to make a GET request to an API using different strings (adresses) to get the corresponding coordinates. The API allows only to send a single string at a time. Therefore, I need to loop through my "adress" variable and would like to collect the received data in a new variable/column.

Since I am new to SAS EG, I am confused about the appropriate method (macros or "do-loops"). I would very much appreciate any help here. I have drafted the following solution which works for a single string based on the reply to a previous question:

filename response temp;
filename headers temp;
url = 'https://api3.geo.admin.ch/rest/services/api/SearchServer?searchText=Bahnhofstrasse 1 Zürich&type=locations'
method='GET'
proxyhost = 'OUR PROXYHOST'
proxyport = *OUR PROXYPORT*
out= response
headerout = headers
ct = "application/json";
run;


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

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

* libref name same as fileref pointing to json content;
libname response json;

proc copy in=response out=work;
run;

I would appreciate any help very much on how to continue from this point and which method to use.

Answer 1

You can use the doSubL function to submit source code in a side session for every row in a data set.

Example:

* control data, a table of addresses to process;

data addresses;
length address $100;
input address $CHAR100.;
infile datalines truncover;
datalines;
Pennsylvania 6-5000 Millertown
Bahnhofstrasse 1 Zürich
;
*
Duebendorfstrasse 223 8051 Zürich
Hohlstrasse 451, Zurich 8048
Rotbuchstrasse 1, Zurich 8006
Froehlichstrasse 37, Zurich 8008
;

/*
 * a macro for 
 * - retrieving the search response,
 * - extracting the lat and lon,
 * - appending to all results table
 */

%macro get_lat_lon(address);
  %local server search_endpoint;

  %let server = https://api3.geo.admin.ch;
  %let search_endpoint = /rest/services/api/SearchServer;

  filename response temp;
  filename headers temp;

  * GET the search response;

  proc http
    url = "&server.&search_endpoint.?type=locations%str(&)searchText=&address"
    method = "get"
/*    proxyhost = ... */
/*    proxyport = ... */
    out = response
    headerout=headers
    ct = "application/json"
  ;
  run;

  libname response json;  /* Note: LIBREF is same as FILEREF */

  * copy data from JSON response to tables as inferred by json library engine;

  proc copy in=response out=sandbox;
  run;

  * add the address (the search term) to the results;

  %if %sysfunc(exist(sandbox.results_attrs)) %then %do;
    data sandbox.lat_lon / view=sandbox.lat_lon;
      length address $100 detail $200;
      set work.results_attrs (keep=detail lat lon);
      address = symget('address');
    run;
  %end;
  %else %do;
    data sandbox.lat_lon;
      length address $100 detail $200;
      address = symget('address');
      call missing(lat, lon);
      detail = 'No detail found for address';
    run;
  %end;

  * append new results (or lack thereof);

  proc append base=address_lookup_results data=sandbox.lat_lon;
  run;

  * clean out the sandbox;

  proc datasets nolist kill lib=sandbox;
  run; quit;

  filename response;
  filename headers;
%mend;

* create new table to contain appended results;

proc sql;
  create table address_lookup_results
  ( address char(100)
  , lat num
  , lon num
  , detail char(200)
  );

* create new folder to contain tables copied from json response;
* sandbox folder is under WORK so it will be automatically deleted when SAS session ends;

%let workpath = %sysfunc(pathname(WORK));
%let rc = %sysfunc(DCREATE(sandbox,&workpath));
libname SANDBOX "&workpath./sandbox";

* process each address in the control data;

data _null_;
  set addresses;

  length macro_call_source_code $200;

  * codegen;
  macro_call_source_code = cats('%get_lat_lon(%str(',address,'))');

  * submit codegen in side session;
  rc = dosubl (macro_call_source_code);
run;

libname sandbox;

Image of results appended together.

• Note the 'not found' detail, and that some search results have multiple details for a given address. Open a new question if you have trouble writing code that must choose a 'best' possible detail correspond to an address.

• 

Contents of intermediate table SANDBOX.RESULTS_ATTRS as copied from response.

• Note that only variables numbered 7, 8, and 11 (lon, detail, lat) are used in the appended results table WORK.ADDRESS_LOOKUP_RESULTS

•