Parsing a JSON file in SAS

[Sean_OConnor]

Folks,

I'm new to json formats and I'm having trouble attempting to import data into SAS.

I was orginally given a json schema which I could load perfectly into SAS with the following code

filename in "\\location\BigSimA1000C100D10.txt";
filename map 'my.map';
libname in_json json map=map automap=reuse;

This data was in a nested format and SAS seemed to parse it perfectly.

However, the data received yesterday is different.

When opening in notepad++ it looks like one record per line. I'm told this is a valid json format.

A record always begins as such 

{"time_stamp"

 and ends as follows

false}

There are other curly between the two above but is there an easy way to either parse this json file as done above or can anyway provide me with some information of how I might go about it?

[TomKari]

Very interesting!

This is something I wanted to know more about anyway, so I played around with it a bit.

You didn't specify the exact format of your JSON text, so I "invented" the following, from a SAS paper that I reviewed:

{"firstName": "John", "lastName": "Smith", "age": 25}

When I run the following code, which creates a one-line file, it works:

%let DirRef = /home/me;

data _null_;
length OutLine $32767;
file "&DirRef./JSExample.json" lrecl=32767;
OutLine = '{"firstName": "John", "lastName": "Smith", "age": 25}'; put OutLine;
/* OutLine = '{"firstName": "Joho", "lastName": "Smiti", "age": 26}'; put OutLine; */
run;

filename jsonxmp "&DirRef./JSExample.json";

libname jstest JSON fileref=jsonxmp;

proc print data=jstest.root;
run;

However, when I run the following, which creates two lines:

%let DirRef = /home/cstkari/my_content;

data _null_;
length OutLine $32767;
file "&DirRef./JSExample.json" lrecl=32767;
OutLine = '{"firstName": "John", "lastName": "Smith", "age": 25}'; put OutLine;
OutLine = '{"firstName": "Joho", "lastName": "Smiti", "age": 26}'; put OutLine;
run;

filename jsonxmp "&DirRef./JSExample.json";

libname jstest JSON fileref=jsonxmp;

proc print data=jstest.root;
run;

it fails with the following error:

 ERROR: Invalid JSON in input near line 2 column 2: Unexpected characters found after valid JSON text.
 ERROR: Error in the LIBNAME statement.

Either the multi-line format isn't actually valid JSON, or SAS can't interpret a valid JSON format correctly.

Hope this helps!

    Tom

[Tom]

That is NOT a JSON file.  That is something called JSON LInes.  http://jsonlines.org/ 

The SAS engine does NOT support that format.

You will need to modify the file to wrap the lines up into a valid JSON list.  Or parse the lines one by one.

Let's make a file in that format so we have something to test with.

filename json temp;
data _null_;
  file json ;
  put  '{"firstName": "John", "lastName": "Smith", "age": 25}';
  put  '{"firstName": "Joho", "lastName": "Smiti", "age": 26}';
run;

If we try to read it we will get an error.

But if we convert it to a valid JSON string:

filename json2 temp;

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

then we can read it.

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

proc print data=json2.root; 
run;

Result:

       ordinal_    first    last
Obs      root      Name     Name     age

 1         1       John     Smith     25
 2         2       Joho     Smiti     26

[TomKari]

Fascinating! Thank you for the reference, other Tom!

However, I was only imagining a file format, as there wasn't enough information in the original post to replicate the poster's situation.

I was thinking the same thing...it would be pretty easy to turn the JSON Lines into JSON!

Thanks for filling in the gaps in my knowledge,

   Tom

[Sean_OConnor]

Hi Tom,

Thanks very much for this. Your code successfully loaded it into json. However, I'm running into some issues which perhaps I could ask you about.

When I run the libname json command 

libname json3 JSON map=map2 automap=reuse ;

The json file is parsed into a number of separate datasets.

I'm aware that SAS creates an ordinal variable which should allow you to link the tables back together again. However, given the structure of my json file I'm running into some issues.

Take "address" for example. The json engine is separating this into one dataset. However, a number of records contain no information of this kind.  So if the first reference of address doesn't appear until record 26 SAS is assigning an ordinal root key of 1 as it's the first time it encounters it.

Therefore, if I start linking back up, I'll be linking to the wrong record. This type of issues will be the same for other cases where a variable isn't on a record.

So I need ensure an accurate unique ID is on each table.

I have two possible workarounds, but need some guidance around them.

Your code supplied below allowed me to alter the json file so I could read it into SAS.

However, is it possible to extend this to create a enumeration variable called record_id which would be =1 for the first record and =n for the nth record?

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

Secondly, when I'm using the libname json command is it possible to tell SAS to ensure that the variable record_id appears on every table which is parsed?

So say for table which relates to addresses, when this is created the ordinal root key would be 1,2,3 as it only encounters this variable 3 types but the record_id would be 26,500,23,000 as these are the records it relates to?

I would appreciate any help on this.

[Tom]

The MAP file is the instructions to the JSON engine about what values from the JSON object to put into which variables.

The simple code you used is just letting SAS make a guess at to how you want to map that file and automatically generate a map file.

You can create you own map file.  Take a look at the help pages and the map that SAS automatically generated.

SAS even has a GUI tool to allow you to edit the map file.

[Sean_OConnor]

Hi Tom,

How would I see the map which SAS generates?

[BillM_SAS]

An example from the on-line documentation:

libname in json 'example.json' map='user.map' automap=create;

[BillM_SAS]

Since you are new to the JSON format, you might (if you have not already found it) want to read Michael's and Eric's paper on the JSON LIBNAME engine from SAS Global Forum 2017.