topic Re: json file, how to eliminate the square brackets in SAS Enterprise Guide

json file, how to eliminate the square brackets

alepage — Wed, 05 Apr 2023 14:01:11 GMT

Hello, I would like to know how to eliminate the square brackets ex: []

%let path=/.../test;
proc json out="&path./json_class.txt" nosastags trimblanks;
export class ;
run;

[{"Name":"Alfred","Sex":"M","Age":14,"Height":69,"Weight":112.5},{"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},{"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}]

Re: json file, how to eliminate the square brackets

Tom — Wed, 05 Apr 2023 14:31:40 GMT

Without the [ ] to indicate that you have an array of values the generated text is not valid JSON.

Why not just write those strings yourself?

filename json temp;
data _null_;
  set sashelp.class(obs=3);
  file json recfm=n;
  if _n_ > 1 then put ',' ;
  put '{"Name":' name :$quote. +(-1) ',"Age":' age +(-1) '}' ;
run;

Result:

{"Name":"Alfred","Age":14},{"Name":"Alice","Age":13},{"Name":"Barbara","Age":13}

Re: json file, how to eliminate the square brackets

alepage — Wed, 05 Apr 2023 15:17:11 GMT

Is there an option can we use with the proc json to get rid of square bracket instead of working on the obtained file

Re: json file, how to eliminate the square brackets

Tom — Wed, 05 Apr 2023 15:18:19 GMT

@alepage wrote:
Is there an option can we use with the proc json to get rid of square bracket instead of working on the obtained file

I could not find one. PROC JSON complains when you try to use it to write something that is not a JSON file.

Re: json file, how to eliminate the square brackets

Quentin — Wed, 05 Apr 2023 15:34:16 GMT

If you want "JSONL" you could post-process the JSON to create it. But IMHO, you're better off sticking with real JSON. For one project I receive JSONL (unfortunately), and I pre-preprocess it to create a real JSON file so that I can then use JSON engine to read it.

Re: json file, how to eliminate the square brackets

Tom — Wed, 05 Apr 2023 17:13:05 GMT

Just post process the file.

filename json1 temp recfm=n;
filename json2 temp;
proc json out=json1 nosastags trimblanks;
export sashelp.class(keep=name age obs=3);
run;

data _null_;
  infile json1 lrecl=1 recfm=f end=eof;
  file json2 recfm=n ;
  input ch $char1. @@;
  if _n_>1 and not eof then put ch $char1. @;
run;

Result:

1884  options generic;
1885  data _null_;
1886    infile json2;
1887    input;
1888    list;
1889  run;

NOTE: The infile JSON2 is:
      (system-specific pathname),
      (system-specific file attributes)

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
1         {"Name":"Alfred","Age":14},{"Name":"Alice","Age":13},{"Name":"Barbara","Age":13} 80
NOTE: 1 record was read from the infile (system-specific pathname).
      The minimum record length was 80.
      The maximum record length was 80.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds