topic Re: Reading JSON file with JSON LIBNAME engine in SAS Programming

Reading JSON file with JSON LIBNAME engine

Pierre_L — Thu, 11 Mar 2021 00:33:24 GMT

Hello everyone!

I'm trying to read a json file i created while calling an API made by us.

The file is created fine but when i try to use the LIBNAME JSON Engine, it give me the following error message :

ERROR: JSON incorrect dans input à côté de la ligne 1 colonne 85993: Some code points did not transcode.

I did some research about "some code point did not transcode" and from what i've read, it's because the json file i'm trying to read is encoded in UTF-8; wich is true.

I've also read that i could put ENCONDING="UTF-8" (read example with UTF-8 in lowercase) : Got the same error message but not the same column number :

ERROR: JSON incorrect dans input à côté de la ligne 1 colonne 85959: Some code points did not transcode.

Here a sample from the code i'm using :

This run in a macro wich loop calling the API a few times :

//Setting the filename to write down the JSON file
(not actual code; explaining the variables uses)
&api_call contains the url to the API
&fic_api_call contains the name of the file written
&boucle contains the loop number at that time being

FILENAME users "&api_call.&fic_api_call._&boucle..json";

//Calling the API and creating the users files
(same as above, not actual code)
&list contains a list of argument i'm giving the API

PROC HTTP
	METHOD="GET"
	CT="application/JSON"
	URL="&url.&list."
	OUT=users;
RUN;         

After the macro :
(not the same code as in my program but i'm setting the FILENAME this way)
FILENAME data "x:\api_call\file.json" ENCODING="UTF-8"
LIBNAME data JSON FILEREF=data

I've also read the opening sas 9.4 with unicode support work. Yes, it's working. But we're going to run this code/program on a SAS-SERVER wich is not set with the UFT-8 enconding.

So, need some help reading the JSON file 🙂

Thanks!!

Re: Reading JSON file with JSON LIBNAME engine

japelin — Thu, 11 Mar 2021 01:44:06 GMT

Have you tried to set this encoding?

FILENAME users "&api_call.&fic_api_call._&boucle..json" ENCODING="UTF-8";

Re: Reading JSON file with JSON LIBNAME engine

ChrisNZ — Thu, 11 Mar 2021 02:56:56 GMT

The error comes from the fact that some characters cannot be translated to you session's encoding.

See here http://support.sas.com/kb/30/719.html for example.

The only way according to this (old) support entry seems to be to remove the offending characters from the JSON file.

Longer term, UTF8 is the new standard and you should probably move there.

Re: Reading JSON file with JSON LIBNAME engine

Pierre_L — Thu, 11 Mar 2021 13:16:38 GMT

Yes, i tried it and it didn't work!

Thanks!

Re: Reading JSON file with JSON LIBNAME engine

Tom — Thu, 11 Mar 2021 13:33:15 GMT

You should insist that your local SAS administrator setup a method for you to run your code using UTF-8. It should not be very hard. This is not the only problem you are going to have where you need to use UTF-8 encoded data.

For now see what happens if you tell SAS to ignore the encoding? Or pretend that it is coded as whatever your current SAS session is using. Hopefully that will allow the multiple byte UTF-8 characters to be copied as they are into your SAS datasets where you can then try to convert them to something more useful.

FILENAME data "x:\api_call\file.json" ENCODING="ANY" ;
LIBNAME data JSON FILEREF=data ;

Re: Reading JSON file with JSON LIBNAME engine

Pierre_L — Thu, 11 Mar 2021 13:43:51 GMT

Hello!

I tried it and got the following error:

ERROR: JSON incorrect dans input à côté de la ligne 1 colonne 1: Encountered an illegal character.

Also, we don't want the enconding to malfunction since the data is customer data we have to manipulate 🙂

Thanks!

Re: Reading JSON file with JSON LIBNAME engine

Tom — Thu, 11 Mar 2021 15:07:35 GMT

Another debugging step is to read the JSON file with a data step so you can read the text without transcoding.

data test;
  infile 'myfile.json'  encoding='any' truncover ;
  input line $char2000.;
run;

You can then try transcoding using SAS functions for transcoding strings and see if you can find the offending character.

At which point you might be able to build logic to read the file and write an updated version that your SAS session can read.

Re: Reading JSON file with JSON LIBNAME engine

ChrisNZ — Thu, 11 Mar 2021 22:24:46 GMT

> we don't want the encoding to malfunction

You cannot read UTF-8 characters from a wlatin sessions.

wlatin does know know anything about characters อักษรไทย or ∀∁∂∃∄∅∆∇∈∉

So either you lose "extended" characters or you read in an UTF-8 session.