topic REST API connection Using SAS in SAS Programming

REST API connection Using SAS

RAmarapuram — Tue, 11 Feb 2025 12:49:56 GMT

Hi Community,

Need your help on resolving an issue. I am using SAS 9.4M8 version.

I am pulling data from REST API and the PROC HTTP works fine for the first call. The API has data in multiple pages and whenever the JRESP is created, it contains the "next" from jresp.links which has the link to next page

( like https://api.xxxxx.io/api/v2/ma?fields%5Bma%5D=user&page%5Boffset%5D=50 ). However, when I read it via SAS it is fetching the "next" link as https://api.xxxxx.io/api/v2/ma?fields%5Bma%5D=user0026page%5Boffset%5D=50.

The user& is getting replaced with user0026, there by my fetch fails.

Can someone please help on this.

Thank you !

Re: REST API connection Using SAS

SASJedi — Tue, 11 Feb 2025 13:14:46 GMT

0026 is the hex value for a UTF-16 representation of the ampersand. It looks like it's some type of URL encoding problem, but it's not consistent - I'd have expected the % characters to also have been replaced by 0025 if that was the case. Can you explain how you are capturing / constructing the URL for the second PROC HTTP call to the API?

Re: REST API connection Using SAS

RAmarapuram — Wed, 12 Feb 2025 10:48:40 GMT

Hi SASJedi,

Thanks for the clarification on character encoding.

I could see that we are currently using UTF-8 encoding option.

I am making the API calls using the config files and tokens. The first time, when I call the URL,

I pass the very first link as nextlink=https://api.xxxxx.io/api/v2/&module./&relns. This link works fine and fetches data which has 50 records in it.

The successful URL built is as below :

proc http url="https://api.xxxxx.io/api/v2/xxxx/?fields[xxxx]=us"
oauth_bearer="xxxxxxxxx" out = outfield;
run;

When the above URL is set, the JRESP.LINKS dataset fetches the next field with link to the next set of 50 records. Using this next field, I am creating a macro variable and pass this URL to create another data pull.

Below is URL which gets constructed and is creating issue :

proc http url="https://api.xxxxx.io/api/v2/xxxx?fields%5Bmass%5D=us0026page%5Boffset%5D=50"
oauth_bearer="xxxx" out = outfield;

run;

I checked the URL in Postman to verify and postman gives me

https://api.xxxx.io/api/v2/xxxx?fields%5Bmass%5D=us&page%5Boffset%5D=50.

Do you think I should overwrite the encoding option or suggest anything better?

Let me know if I wasn't clear in explaining you the situation.

Thank you.

Re: REST API connection Using SAS

SASJedi — Wed, 12 Feb 2025 14:28:56 GMT

I understand the process. What I need is to see the SAS code that creates the macro variable (page) and the SAS code that creates the second URL. YOu explain the process as:

... the JRESP.LINKS dataset fetches the next field with link to the next set of 50 records. Using this next field, I am creating a macro variable and pass this URL to create another data pull.

I need to see the SAS code that performs that process.

Re: REST API connection Using SAS

RAmarapuram — Wed, 12 Feb 2025 17:53:32 GMT

Hi Jedi,

Here is the code. Hope this gives you some understanding now of what I am trying to achieve.

%let nextLink = https://api.xxxxx.io/api/v2/&module./&relns.;

%let batchnum = 1;

/* flag_url is count of records where there is nextlink */

%do %until (&flag_url.=0);

/*Handle special characters section starts*/

filename outfield "&config_root./json_in_files/xxxxx_data.json";

filename out_&mapmod. "&config_root/json_map/out_&mapmod..map";

proc http url="&nextLink."

oauth_bearer="&access_token"

out = outfield;

run;

/*this section handles the special characters in the data that will not come in via JSON */

%sysexec cd &config_root./json_in_files;

/*replaces two types of special char with space in the new file */

%sysexec cat xxxxx_data.json | sed 's/\\u2022//g; s/\\uf0a7//g; s/\\u//g' > xxxxx_data1.json;

/*uses new file for libname */

filename fields "&config_root./json_in_files/xxxxx_data1.json";

filename out_&mapmod. "&config_root/json_map/out_&mapmod..map";

libname jresp json fileref=fields map=out_&mapmod.;

%if &batchnum=1 %then %do;

%if (&module.=maxxxx) %then %do;

data &module._us_data;

set jresp.us_data;

run;

%end;

%else %do;

%if %sysfunc(exist(jresp.&mod._data)) %then %do;

data &module._data&batchnum. ;

set jresp.&mod._data;

run;

%end;

data &module._data;

%if %numobs(&module._data) > 0 %then %do;

set &module._data &module._data&batchnum.;

%end; %else %do;

set &module._data&batchnum.;

%end;

run;

%end;

/*resets nextlink and batchnum */

%let nextLink=;

%let flag_url=;

/*gets the next URL to pull the next 200 records */

data s_ ;

set jresp.links ;

call symputx('nextLink',next);

run;

/*sets count so the do until will stop when no more nextlinks are available */

proc sql ;

select count(*) into: flag_url

from jresp.links

;

quit;

proc print data=jresp.links;run;

%put flag_url=&flag_url.;

%put nextLink=&nextLink.;

/*updates batchnum and clears names for next run */

%let batchnum = %sysevalf(&batchnum. + 1);

%end;/*DO - UNTIL loop*/

Re: REST API connection Using SAS

SASJedi — Fri, 14 Feb 2025 15:55:50 GMT

After the first PROC HTTP run, you appear to be extracting the value for nextLink from the JSON file xxxxx_data1.json. This file was produced by using sed to modify the text in the original xxxxx_data.json file. Is it possible that the sed search and replace is inadvertently modifying the URL in the original text? I would do diff on xxxxx_data.json and xxxxx_data1.json to see if the URL information has been changed.

As an aside, I would personally use a DATA _NULL_ step and SAS functions to make the changes to the xxxxx_data.json file instead of sed. It's easier to troubleshoot and doesn't require X command permissions.

Re: REST API connection Using SAS

ChrisHemedinger — Fri, 14 Feb 2025 18:08:00 GMT

It's unusual that you would need to modify the JSON for processing in SAS. I work with many APIs that provide JSON responses, and if your SAS session is using UTF-8 you should not need to post-process the JSON for interpretation. So...are you using UTF-8 encoding in your SAS session (ENCODING option)? I certainly recommend that if you are working with APIs.

Re: REST API connection Using SAS

RAmarapuram — Tue, 18 Feb 2025 10:10:28 GMT

Hi Chris,

By default, SAS is picking up UTF-8. I tried changing the encoding= option via options and DATA step.

But it wouldn't work.

Do you suggest any other alternative for this ?

Thanks

Rajesh A

Re: REST API connection Using SAS

RAmarapuram — Tue, 18 Feb 2025 10:41:48 GMT

Hi SASJedi,

As suggested, I shall check the .json files and get back to you.

Thanks

Rajesh A