Proc Json

[ChrisHemedinger]

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;

[mkeintz]

Nicely Annotated!