importing a Json file into sas

[Nandeep]

Hi,

We receive Json files on a daily basis via ftp. I am required to import these into SAS. I have been looking online and on this forum for solutions but they seem to all use the proc http method, which doesn't quite seem to be working, I followed the following example:

filename rept "C:\UNComtrade\classificationHS.JSON";

proc http

method = "get"

url =

"https://comtrade.un.org/data/cache/classificationHS.json"

out = rept;

run;

enddata;

run;

quit;

thanks in advance for your help

Regards

Nandeep

[Ksharp]

@ChrisHemedinger  wrote many blogs about it .

filename rept "C:\UNComtrade\classificationHS.JSON";

proc http

method = "get"

url =

"https://comtrade.un.org/data/cache/classificationHS.json"

out = rept;

run;

LIBNAME x JSON FILEREF=rept ;

proc copy in=x out=work;

run;

[ghosh]

* This works for me.  DAT is a folder ref off the root ;
filename rept "~/dat/classificationHS.JSON";

proc http
method = "get"
url = "https://comtrade.un.org/data/cache/classificationHS.json"
out = rept;
run;

libname mydata JSON fileref=rept;
proc contents data=mydata._all_;
run;

[Nandeep]

I think my issues seems to be that as i'm not getting the Json file from a website it doesn't seem to like the proc http. My json file is stored on the server and the address is "\\cagv22\QuoteData\Archive" - will the proc http work in this instance?

[ghosh]

filename rept "\\cagv22\QuoteData\Archive\classificationHS.JSON";

proc http
method = "get"
url = "https://comtrade.un.org/data/cache/classificationHS.json"
out = rept;
run;

libname mydata JSON fileref=rept;

well then change your filename ref to point to this location.  If have SAS running from a server, it would not be able to read the c drive

[Tom]

---

@Nandeep wrote:

I think my issues seems to be that as i'm not getting the Json file from a website it doesn't seem to like the proc http. My json file is stored on the server and the address is "\\cagv22\QuoteData\Archive" - will the proc http work in this instance?

---

Then skip the HTTP step and just point the FILENAME statement at the existing file.  Or modify the LIBNAME statement to reference the physical file and skip the FILENAME statement also.

Does the file really not have any extension?  Assuming that the path you gave is for the actual file you want something like this to copy all of the dataset that the JSON is parsed into to the WORK library.

libname myjson json "\\cagv22\QuoteData\Archive";
proc copy inlib=myjson outlib=work;
run;

[Nandeep]

Hi thanks for your response.

I amended the code as shown below:

libname myjson json "\\cagv22\QuoteData\Archive\integra_qhv5_extract-YG-2019.09.22.json";

proc copy inlib=myjson outlib=work;

run;

the following errors were noted:

ERROR: Invalid JSON in input near line 2 column 2: Unexpected characters found after valid JSON

text.

ERROR: Error in the LIBNAME statement.

1462 proc copy inlib=myjson outlib=work;

1463 run;

ERROR: Libref MYJSON is not assigned.

Regards

Nandeep

[Tom]

So it looks like your file is NOT a JSON file.  SInce it complains on the second line perhaps you have a JSON LINES file instead?  The JSON engine cannot read that type of file, but it is pretty trivial to convert it to a valid JSON format by just adding a container around the whole file and commas between the lines.

Try something like this and see.

filename fixed temp;
data _null_;
  infile "\\cagv22\QuoteData\Archive\integra_qhv5_extract-YG-2019.09.22.json" end=eof;
  input;
  file fixed;
  if _n_=1 then put '[' @; 
  else put ',' @;
  put _infile_;
  if eof then put ']';
run;

libname myjson json fileref=fixed;

proc copy inlib=myjson outlib=work;
run;

[Nandeep]

Hi Tom,

This seems to work to a certain point, but then errors due to invalid JSON in input near line 5 column 1:
NOTE: The infile "\\cagv22\QuoteData\Archive\integra_qhv5_extract-YG-2019.09.22.json" is:
Filename=\\cagv22\QuoteData\Archive\integra_qhv5_extract-YG-2019.09.22.json,
RECFM=V,LRECL=32767,
File Size (bytes)=1956860659,
Last Modified=23 September 2019 01:23:59 o'clock,
Create Time=23 September 2019 11:42:47 o'clock

NOTE: The file FIXED is:

Filename=C:\Users\NANDEE~1.SIN\AppData\Local\Temp\SAS Temporary
Files\_TD3344_INTSAS02_\#LN00061,
RECFM=V,LRECL=32767,File Size (bytes)=0,
Last Modified=23 September 2019 16:27:07 o'clock,
Create Time=23 September 2019 16:27:07 o'clock

NOTE: 97655 records were read from the infile
"\\cagv22\QuoteData\Archive\integra_qhv5_extract-YG-2019.09.22.json".
The minimum record length was 1781.
The maximum record length was 32767.
One or more lines were truncated.
NOTE: 123154 records were written to the file FIXED.
The minimum record length was 1.
The maximum record length was 32767.
NOTE: DATA statement used (Total process time):
real time 58.65 seconds
cpu time 5.13 seconds


1474 libname myjson json fileref=fixed;
NOTE: JSON data is only read once. To read the JSON again, reassign the JSON LIBNAME.
ERROR: Invalid JSON in input near line 5 column 1: Illegal JSON string character detected.
ERROR: Error in the LIBNAME statement.
1475
1476 proc copy inlib=myjson outlib=work;
1477 run;

ERROR: Libref MYJSON is not assigned.
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE COPY used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

NOTE: The SAS System stopped processing this step because of errors.

Regards

Nandeep

[Tom]

We cannot help debug without seeing the actual text of the file. Also make sure to use the Insert Code or Insert SAS code buttons on the editor menu bar to get a pop-up window for pasting your code/text lines/log lines so that the forum editor doesn't try to reflow them into paragraphs.

Also check the encoding on the file as misaligned character encodings can make parsing text files difficult.

[Tom]

One or more lines were truncated.

That is your real problem.  Perhaps you can try adding the LRECL= option to your INFILE and FILE step to use a longer record length?  You should be able to use up to around 10 million characters per line depending on your operating system and version of SAS. 

Perhaps the original file will actually work with proper record length?  Although I am not sure the JSON librname engine cares about splitting data into lines since JSON files that care about line breaks.  But if the data really is JSON LINES format then line breaks are critical.