topic Re: Need help to create table which includes API call in SAS Programming

Need help to create table which includes API call

Aexor — Thu, 27 Jul 2023 13:58:43 GMT

Hi All,

I am new for API calls , wanted to understand how can I create a table

I have table like this

table abc
id market_id
1 100
2 500
4 600
3 300
5 400

I have one more table xyz table, in a different database , where we can do only API call to get data.

table xyz
market_id market_cd
100 12
500 13
300 22
600 55
700 44

I am doing API call like this to get market_cd.

proc sql noprint;
select market_id into :mkt_typ_id
from abc
where id in ('100');
quit;
%put &mkt_typ_id;

proc http
url="&SERVICESBASEURL./&mkt_typ_id"
method="GET"
out=resp2
OAUTH_BEARER = SAS_SERVICES;
run;
libname resp2 json fileref=resp2;

proc sql noprint;
select trim(code) into: market_cd from resp2.root
where lowcase(memberId)= "&mkt_typ_id";
quit;
%put &market_cd;

-- here I am getting only one value for market_cd that is 12.

But I want to create a table like table three as mentioned below.

table three
id market_cd
1 12
2 13
4 55
3 22

Can anyone please help , How to achieve this.

Re: Need help to create table which includes API call

HB — Thu, 27 Jul 2023 15:05:09 GMT

Your initial select

select market_id into :mkt_typ_id
from abc
where id in ('100');

only selects one record.

So I assume your API call only matches one record.

Try deleting the WHERE clause.

Re: Need help to create table which includes API call

Tom — Thu, 27 Jul 2023 15:21:14 GMT

I don't understand.

Is the issue that each API call can only return one value?

Or is the issue that last SQL step that is only pulling one value from the JSON response that the API returned?

If the later then don't use a macro variable to get the data from the JSON file. Just use normal SAS code instead.

If the former then you just need to do it one at a time. You can use PROC APPEND or the PROC SQL INSERT statement to append the results into one dataset.