looop through postalcodes in table and get api response

[MADS1]

Dear all,

I have a table called input containing postalcodes, housenumbers, and housenumber additions. I want to look up all these combinations in an API and receive the response back in the input table. I am able to retrieve the result for one row in the table, but not for multiple rows. Additionally, some addresses have house number additions, which means the URL changes. Who can assist me further?

Thank you! 🙂

An example of my input table:

postalCode	streetNumber	streetNumberAddition
1234AB	1	A
2345CD	8

Here is my code:

data _null_;
if 0 then set work.input nobs=nobs; /* deze regel haalt het aantal rijen op */
call symputx('cnt', nobs); /* sla het aantal rijen op in de macrovariabele 'cnt' */
run;

%let api_key = 123456;

/* Stap 3: Definieer de macro voor de API-loop */
%macro APILoop();
%do i = 1 %to &cnt.;
/* Stap 4: Haal de variabelen op voor de huidige iteratie */
data _null_;
set input (obs=&i.); /* haal alleen de huidige rij op */
call symputx('streetNumber', streetNumber);
call symputx('postalCode', postalCode);
run;

/* Stap 5: Bouw de URL op met de juiste variabelen */
%let base_url = %nrstr;
%let new_url = &base_url.&%nrstr(&streetNumber=)&streetNumber&%nrstr(&postalCode=)&postalCode&limit=10&offset=0;

/* Stap 6: Voer de HTTP-aanroep uit */
proc http
method='GET'
url="&new_url"
proxyhost="hidden" proxyport=hidden
out=response;
headers "accept" = "application/json"
"X-API-Key" = "&api_key";
run;

/* Stap 7: Verwerk de HTTP-respons */
data _null_;
infile response;
input;
put _infile_;
run;

libname response json;
proc copy in=response out=work;
run;
%end;
%mend;

/* Stap 8: Voer de API-loop uit */
%APILoop();

[HarrySnart]

Hi, I've done this in the past using DS2 instead of the DataStep. This uses the HTTP package in DS2 instead of PROC HTTP. There's a really nice example with code by Chris Hemedinger here: https://blogs.sas.com/content/sasdummy/2015/09/28/parse-json-from-sas/