BookmarkSubscribeRSS Feed
MADS1
Calcite | Level 5

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();

 

1 REPLY 1
HarrySnart
SAS Employee

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/ 

sas-innovate-2024.png

📢

ANNOUNCEMENT

The early bird rate has been extended! Register by March 18 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 200 views
  • 0 likes
  • 2 in conversation