topic ERROR: Invalid JSON in input near line 15837 column 6649: Some code points did not transcode. in SAS Programming

ERROR: Invalid JSON in input near line 15837 column 6649: Some code points did not transcode.

94seanhogan — Thu, 31 Aug 2023 08:52:09 GMT

I have a SAS program which imports these large JSON files which works 99.9% of the time but once a blue moon I get a JSON file which throws up the error message:

39 filename map2 temp;
40 libname json2 JSON map=map2 automap=reuse ;
NOTE: JSON data is only read once. To read the JSON again, reassign the JSON LIBNAME.
ERROR: Invalid JSON in input near line 15837 column 6649: Some code points did not transcode.
ERROR: Error in the LIBNAME statement.

Its not very realistic to open and interrogate the file as its to large. The best I could find was to change the encoding to be UFT-8, which SAS is already set to.

My code is below.

Any suggestions as to how to investigate or solve the issue please do put them as I'm at a loss.

Cheers

filename json "&jdir.\&Jfile.&uat..json" ;

		filename json2 temp lrecl=250000;

		data _null_;
		  infile json end=eof  lrecl=250000;
		  file json2 ;
		  input ;
		  if _n_=1 then put '[' @; else put ',' @;
		  put _infile_;
		  if eof then put ']';
		run;

		filename map2 temp;
		libname json2 JSON map=map2 automap=reuse ;

Re: ERROR: Invalid JSON in input near line 15837 column 6649: Some code points did not transcode.

Amir — Thu, 31 Aug 2023 09:38:57 GMT

Hi,

It looks like it is related to the below SAS note:

Usage Note 64615: The SAS® log displays the error "Invalid JSON in input near line XXX column XXX: Some code points did not transcode

https://support.sas.com/kb/64/615.html

Thanks & kind regards,

Amir.

Re: ERROR: Invalid JSON in input near line 15837 column 6649: Some code points did not transcode.

Quentin — Thu, 31 Aug 2023 13:08:35 GMT

Checking that your SAS session is UTF-8 is a good step. After that, I think you'll have to interrogate the file to identify the problematic characters. Without knowing which characters are causing the problem, it will be difficult to solve.

Since your input file is apparently "JSONL" (i.e. JSON lines rather than a real JSON file) , that should make it easier to process the file in chunks, looking for the problematic line(s). e.g.:

		data _null_;
		  infile json end=eof  lrecl=250000;
		  file json2 ;
		  input ;
		  if _n_=1 then put '[' @; else put ',' @;

		  if 1<=_n_<=1000 then put _infile_;  *check lines 1-1,000 of the JSON file;

		  if eof then put ']';
		run;