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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: