topic Re: Proc Json in SAS Procedures

Proc Json to create newline-delimited JSON (JSONL)

sundeep23 — Wed, 14 Nov 2018 15:38:50 GMT

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

}

]

Re: Proc Json

BrunoMueller — Tue, 13 Nov 2018 20:37:24 GMT

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

Re: Proc Json

sundeep23 — Tue, 13 Nov 2018 20:38:33 GMT

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" }

Re: Proc Json

ChrisHemedinger — Tue, 13 Nov 2018 20:48:19 GMT

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.

Re: Proc Json

BillM_SAS — Tue, 13 Nov 2018 20:57:50 GMT

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 '{'

Re: Proc Json

BillM_SAS — Tue, 13 Nov 2018 21:04:50 GMT

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.

Re: Proc Json

BrunoMueller — Tue, 13 Nov 2018 21:11:28 GMT

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

Re: Proc Json

ChrisHemedinger — Tue, 13 Nov 2018 21:27:31 GMT

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;

Re: Proc Json

sundeep23 — Tue, 13 Nov 2018 22:12:56 GMT

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}]

Re: Proc Json

ChrisHemedinger — Wed, 14 Nov 2018 12:40:38 GMT

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;

Re: Proc Json

ChrisHemedinger — Wed, 14 Nov 2018 16:24:51 GMT

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.

Re: Proc Json

sundeep23 — Wed, 14 Nov 2018 22:06:38 GMT

Thats great thanks Chris for the blog.

Re: Proc Json

snoopy369 — Thu, 15 Nov 2018 19:50:30 GMT

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;

Re: Proc Json

snoopy369 — Thu, 15 Nov 2018 19:58:14 GMT

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;

Re: Proc Json

ChrisHemedinger — Mon, 19 Nov 2018 14:49:11 GMT

Hey @snoopy369 - thanks for the optimized approach! Your method is definitely much faster for the straight one-record-per-line JSON. Writing a single file with PROC JSON is much faster than writing a thousand (or more), and then post-processing each of those to combine them.

But JSONL can have nested data (I guess that's why its enthusiasts like it over CSV), and the use of "},{" as a line delimiter for parsing doesn't work in that case -- and I think there might still be a need to write this style of JSON (with PROC JSON) as one-file-per-complete record.

I've never had a business case for JSONL, so I can only speculate what the data needs might be. But consider this example (adapted from SAS doc):

filename jsonbuf "C:\temp\MultipleDataSets.json";
proc json out=jsonbuf nokeys nosastags;
   write value "data";
   write open object; /* top-level object */
     write value "Roster";
       write open array; /* class list array */
         export sashelp.class ( drop=height weight) /keys;
       write close; /* class list array */
     write value "Measures";
     write open array; /* results array */
       export sashelp.classfit ( drop=name)/ keys;
     write close; /* results array */
   write close; /* top-level object */
run;

The output here defies the simple line parsing, as the associated data values are spread out (structurally) in the file.

To put the nested set of values together in a single JSON record, one per set of values, you might need something like this. It's more complex than my CALL EXECUTE approach, as more data prep is needed to write out small data sets with source data. And the resulting JSON is a little different, so the post-process doesn't need to trim the "[ ]".

/* build the macro list of ID keys */
proc sql;
 select name into: id1- from sashelp.class;
 %let recordcount = &sqlobs;
quit;

/* Create a new subfolder in WORK to hold */
/* temp JSON files, avoiding conflicts    */
options dlcreatedir;
%let workpath = %sysfunc(getoption(WORK))/json;
libname json "&workpath.";
libname json clear;


%macro buildJson;

 %do i = 1 %to &recordcount.;

  data class;
   set sashelp.class (where=(name="&&id&i."));
  run;
  data classfit;
   set sashelp.classfit (where=(name="&&id&i."));
  run;

  filename jsonbuf "&workpath./out&i..json";
  proc json out=jsonbuf nokeys nosastags;
     write open object; /* top-level object */
       write value "Roster";
         write open array; /* class list array */
           export class (  drop=height weight) /keys;
         write close; /* class list array */
       write value "Measures";
       write open array; /* results array */
         export classfit ( drop=name) / keys;
       write close; /* results array */
     write close; /* top-level object */
  run;
 %end;
%mend;
%buildJson;

filename final "c:\temp\final.jsonl";
/* This will concatenate the collection of JSON files */
/* into a single JSONL file                           */
data _null_;
 file final encoding='utf-8' termstr=cr;
 infile "&workpath./out*.json";
 input;
 final = _infile_;
 put final;
run;

Re: Proc Json

mkeintz — Tue, 20 Nov 2018 18:59:10 GMT

Nicely Annotated!

Re: Proc Json

NovGetRight — Tue, 05 Jul 2022 09:45:40 GMT

How to read JSONL in SAS