Need help to create table which includes API call

[Aexor]

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.

[HB]

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. 

[Tom]

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.