Proc Json to create newline-delimited JSON (JSONL)

[sundeep23]

Hi SAS experts,

I am trying to create a Json file with below format 

{

"col1":abc,

"col2":bca

}

{

"col1":ccc,

"col2":xcv

}

But when I use proc json I am getting the below output I dont want the beginning and end [ ] brackets and the comma separator between first and second  {} brackets .Appreciate any ideas. Thank you 

[

{

"col1":abc,

"col2":bca

},

{

"col1":ccc,

"col2":xcv

}

]

[BrunoMueller]

Are you sure your wanted string is valid JSON syntax? Which software needs this format?

[sundeep23]

Redshift accepted format is this 

{ "one": 1, "two": "Sports", "three": "MLB", "four": "Major League Baseball" }

{ "three": "NHL", "four": "National Hockey League", "one": 2, "two": "Sports" }

[ChrisHemedinger]

That looks like JSONL (JSON Lines), not standard JSON. http://jsonlines.org

PROC JSON doesn't create that, as far as I know.  But you could create a JSON output for each record and append these together in a single file to generate the JSONL.  Depending on how many records you have and what you're trying to automate, this might be worthwhile.

[NovGetRight]

How to read JSONL in SAS

[BrunoMueller]

So essentially each line is a JSON object on its own. Proc JSON always wants to have a valid JSON syntax for the complete file.

Maybe it is easier to write out the text using the DATA Step

[BillM_SAS]

The JSON procedure writes the specified data to a file within a single JSON structure. Therefore you get as your output a JSON array containing 2 JSON objects. What you are asking for is to create a file containing 2 JSON objects. A single PROC JSON statement cannot produce what you desire. But note that the output you desire is not valid JSON. For one thing, the values assigned to “col1” and “col2” are strings and in JSON a string requires surrounding double quotes. Also, a JSON file contains a single JSON structure. When I put the contents of what you want in your JSON file into a JSON validator, like JSONLint, the validator says the file contains errors (even after making the column values proper JSON strings).  

Error: Parse error on line 4:

...",   "col2": "bca"} {       "col1": "ccc", "

---------------------^

Expecting 'EOF', '}', ',', ']', got '{'

[BillM_SAS]

As Chris notes, you could use multiple PROC JSON statements to produce multiple files each containing a single JSON object. This code will produce a single JSON object as you desire:

proc json out='./test.out' nosastags pretty;
  write open object; 
    write values col1 abc; 
    write values col2 cba;
  write close;
run;

Of course, a full solution would probably require some dynamic coding to properly update the file name (most likely with an incremented number appended to the base name) and including the proper data for each object from what I assume is a data set.

[ChrisHemedinger]

Maybe something like this.  This generates a JSON output for each record in a data set, then appends all of those outputs together.

data _null_;
 set sashelp.class;
 call execute(catt('filename out "c:\temp\out',_n_,'.json";'));
 call execute('proc json out=out nosastags ;');
 call execute('export sashelp.class(obs='||_n_||' firstobs='||_n_||');');
 call execute('run;');
run;

data _null_;
 file "c:\temp\final.json" ;
 infile "c:\temp\out*.json";
 input;
 put _infile_;
run;

[sundeep23]

Thanks Chris this is great. Below is the output. The only thing I need to worry about is the array container square brackets . Can those can be removed through proc json or I need to get into file to replace using sed command  ?

[{"Name":"Alfred","Sex":"M","Age":14,"Height":69,"Weight":112.5}]
[{"Name":"John","Sex":"M","Age":12,"Height":59,"Weight":99.5}]
[{"Name":"Joyce","Sex":"F","Age":11,"Height":51.3,"Weight":50.5}]
[{"Name":"Judy","Sex":"F","Age":14,"Height":64.3,"Weight":90}]
[{"Name":"Louise","Sex":"F","Age":12,"Height":56.3,"Weight":77}]
[{"Name":"Mary","Sex":"F","Age":15,"Height":66.5,"Weight":112}]
[{"Name":"Philip","Sex":"M","Age":16,"Height":72,"Weight":150}]
[{"Name":"Robert","Sex":"M","Age":12,"Height":64.8,"Weight":128}]
[{"Name":"Ronald","Sex":"M","Age":15,"Height":67,"Weight":133}]
[{"Name":"Thomas","Sex":"M","Age":11,"Height":57.5,"Weight":85}]
[{"Name":"William","Sex":"M","Age":15,"Height":66.5,"Weight":112}]
[{"Name":"Alice","Sex":"F","Age":13,"Height":56.5,"Weight":84}]
[{"Name":"Barbara","Sex":"F","Age":13,"Height":65.3,"Weight":98}]
[{"Name":"Carol","Sex":"F","Age":14,"Height":62.8,"Weight":102.5}]
[{"Name":"Henry","Sex":"M","Age":14,"Height":63.5,"Weight":102.5}]
[{"Name":"James","Sex":"M","Age":12,"Height":57.3,"Weight":83}]
[{"Name":"Jane","Sex":"F","Age":12,"Height":59.8,"Weight":84.5}]
[{"Name":"Janet","Sex":"F","Age":15,"Height":62.5,"Weight":112.5}]
[{"Name":"Jeffrey","Sex":"M","Age":13,"Height":62.5,"Weight":84}]

[ChrisHemedinger]

Simple enough to strip out the "[" and "]" in each line:

data _null_;
 set sashelp.class;
 call execute(catt('filename out "c:\temp\out',_n_,'.json";'));
 call execute('proc json out=out nosastags ;');
 call execute('export sashelp.class(obs='||_n_||' firstobs='||_n_||');');
 call execute('run;');
run;

data _null_;
 file "c:\temp\final.json" ;
 infile "c:\temp\out*.json";
 input;
 final = substr(_infile_,2,length(_infile_)-2);
 put final;
run;

[ChrisHemedinger]

Your question here inspired a blog post with what we've learned so far.

Create newline-delimited JSON (or JSONL) in SAS

As a bonus, I also included a tip for how to gzip-compress the output file at the same time, which I've read is a common need.

[sundeep23]

Thats great thanks Chris for the blog.

[snoopy369]

Chris' solution is interesting, but I think it's a bit easier to have PROC JSON create the whole thing, then strip out the crud around it and insert newlines automatically, so long as the total string length is 32767 or less per line (adjust the length str $1024 to the appropriate length).  If it's over that, you will have issues in either solution and may be better off generating a single file from SAS and then using another program (python, for example) to write a simple script to remove the [{ }] and commas.

Edit: I'm making some hopefully minor changes to deal with files > 32767 in length.

filename out "c:\class.json" encoding="utf-8" termstr=cr;    *wherever you put this;
filename jsonbuf temp;           *temporary location for initial json output;
proc json out=jsonbuf nosastags; *output the whole datafile;
  export sashelp.class;
run;

data _null_;
  infile jsonbuf dsd dlmstr='},{' eof=_end;  *each record is delimited by this;
  file out;
  length str $1024;                          *make sure this is long enough for your records;
  
  do _n_ = 1 by 1;                           *just iterating, eof will exit the loop when done;
  	input str  $ @;                      *read that record in;
  	if str  =: '[{' then str   = substr(str  ,3);   *first record, cut off the starting bit;
  	if reverse(trim(str )) =: ']}' then str  = substr(str  ,1,length(str)-2);  *last record, cut off the ending bit;
  	str  = cats('{',str,'}');            *put back in the { } outside braces we lost using DLMSTR;
  	putlog str;                          *just for debugging;
  	put str $;                           *write it out to final file;
  end;
 
_end:                                        *where we go when end of file is reached;
  stop;
  
run;

[snoopy369]

Easier to post this separately I think.

I wanted to test speed, and so generated a moderate sized JSON (1.9 million lines).  This fails with my earlier program as it's over 32767 total line size for the one-line JSON export, so I modify it to use RECFM=N, otherwise it's largely the same.  The EOF=_END bit is no longer really needed as EOF isn't reached.

filename out "h:\class.json"  encoding="utf-8" termstr=cr;
filename jsonbuf temp;
data class;
  set sashelp.class;
  do _n_ = 1 to 1e5;
    output;
  end;
run;


proc json out=jsonbuf nosastags;
  export class;
run;

data _null_;
  infile jsonbuf dsd dlmstr='},{' eof=_end recfm=n;
  file out;
  length str $1024;
  
  do _n_ = 1 by 1 until (str=' ');
  	input str  $ @;
  	if str  =: '[{' then str   = substr(str  ,3);
  	if reverse(trim(str )) =: ']}' then str  = substr(str  ,1,length(str)-2);
  	str  = cats('{',str,'}');
  	*putlog str;
  	put str $;
  end;
 
_end:
  stop;
  
run;