topic Re: Reading NDJSON (Newline Delimited JSON). in SAS Programming

Reading NDJSON (Newline Delimited JSON).

ndhaubhadel — Mon, 29 Jun 2020 08:37:50 GMT

I am trying to import a NDJSON file from SAS but I am unable to do so using proc json statement. Is there any other way to read in such file?

Link to NDJSON file I am trying to read in SAS is https://bcda.cms.gov/assets/data/Patient.ndjson

Re: Reading NDJSON (Newline Delimited JSON).

AhmedAl_Attar — Mon, 29 Jun 2020 09:58:22 GMT

I answered similar question last week.

Try converting the NDJSON into a JSON, then using SAS to import it

filename jsonl url "https://bcda.cms.gov/assets/data/Patient.ndjson";
filename json temp;
filename map temp;

/* Convert the NDJSON to JSON */
data _null_;
	infile jsonl end=eof;
	file json;
	put '[{"records":['; /* Required for JSON formatting */
	do until (eof);
		input;
		line = STRIP(_infile_);
		if (eof=0) then
			line=cats(line, ',');

		put line;
	end;
	put ']}]'; /* Required for JSON formatting */
	stop;
run;
libname json json automap=create map=map;
/* Check resulted Library and data sets */
proc contents data=json._all_; run;

Hope this helps,

Ahmed

Re: Reading NDJSON (Newline Delimited JSON).

ChrisNZ — Mon, 29 Jun 2020 10:37:33 GMT

Why proc json? Have you tried this?


filename RAW "c:\temp\raw.json";

proc http method = "get"
          url    = "https://.......json"
          out    = RAW;
run;

libname X json fileref=RAW ;

proc copy in=X out=WORK;
run;

Re: Reading NDJSON (Newline Delimited JSON).

ChrisNZ — Mon, 29 Jun 2020 10:47:42 GMT

@AhmedAl_Attar

Nice code!

The strip() function does nothing as used though.

Also, if you want you can have a single line

	do until (EOF);
		input;
		LINE = cats( _infile_, ifc(EOF,,',') );
		put LINE;
	end;

Re: Reading NDJSON (Newline Delimited JSON).

ndhaubhadel — Mon, 29 Jun 2020 10:51:55 GMT

Thank you Ahmed. It works!!!

Re: Reading NDJSON (Newline Delimited JSON).

AhmedAl_Attar — Mon, 29 Jun 2020 10:59:59 GMT

Thanks, Will keep it in mind 🙂