BookmarkSubscribeRSS Feed
sundeep23
Obsidian | Level 7

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

}

]

16 REPLIES 16
BrunoMueller
SAS Super FREQ

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

sundeep23
Obsidian | Level 7

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
Community Manager

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.

 

 

Learn from the Experts! Check out the huge catalog of free sessions in the Ask the Expert webinar series.
NovGetRight
Obsidian | Level 7

How to read JSONL in SAS

BrunoMueller
SAS Super FREQ

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
SAS Employee

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
SAS Employee

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
Community Manager

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;
Learn from the Experts! Check out the huge catalog of free sessions in the Ask the Expert webinar series.
sundeep23
Obsidian | Level 7

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
Community Manager

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;
Learn from the Experts! Check out the huge catalog of free sessions in the Ask the Expert webinar series.
ChrisHemedinger
Community Manager

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.

Learn from the Experts! Check out the huge catalog of free sessions in the Ask the Expert webinar series.
sundeep23
Obsidian | Level 7

Thats great thanks Chris for the blog.

snoopy369
Barite | Level 11

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
Barite | Level 11

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;

 

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 16 replies
  • 6118 views
  • 5 likes
  • 7 in conversation