Nested JSON via SAS
We at Butterfly have spent a good amount of time working on a data quality solution for a client. In this project, it was necessary to transfer data files out of our system into a system built by another supplier. Alongside those files it was necessary to supply a ‘meta’ file that described in detail the contents of the data files so the other supplier could correctly interpret the data.
As you can guess from the title of this post, the decision was made to use JSON. The big advantage JSON provides is, of course, the ability to easily represent parent/child relationships in the data. This was critical for us as the meta structure we needed to communicate contained one object with three nested levels inside of it.
Our system is a SAS solution, which encompasses and utilises a combination of a number of great SAS products: stored processes, base and macro SAS code, and SAS Studio tasks. It is also a system that is handling thousands of files that can contain millions of records. Suffice to say it’s a complex system! Therefore, we needed a module in our architecture that would use the datasets in our reference and WORK SAS libraries and, for the specific entity being processed, create a meta JSON file.
SAS comes with a pre-existing JSON generation procedure, PROC JSON, meaning there is no need to write cumbersome code that knows how to handle all the syntactic requirements of JSON. If you have one dataset and you want to output that to a JSON file, the SAS code is as simple as:
%let JSONFile = ./subfolder/example_JSON.json;
proc json out="&JSONFile." pretty keys nosastags;
write open object;
write values "my dataset";
write open array;
export my_data;
write close;
write close;
quit;
The PROC JSON procedure then does all the clever work for you:
Determining which fields are in the dataset, their type, and what the key/value pairs should be called
Including text/string and number data correctly to indicate datatype in the JSON
Adding the needed { } or [ ] brackets
It all runs very quickly and produces you a compliant JSON file.
Where things get more challenging is if, as in the case for our system, you need to have multiple nested levels of arrays of objects. As ever in SAS when needs are more complex, you can apply SAS Macro to it and use all the power it provides.
The simple solution is to send all your datasets that exist above the lowest level of your nested structure into macro variables suffixed with an incremental number using the select…into...from PROC SQL statement. You can then iterate through those macro variables and include them as necessary in your JSON statement, with other variables being control values to enable you to loop through them and decide what to include.
Here is an example of how the key parts of that code would look:
proc json out="&JSONFile." pretty keys nosastags;
write open object;
write values "parent_object" "&object_value.";
write values "parent_number" &parent_number.;
write values "First_level";
write open array;
%do f=1 %to &num_top_lev_objs.;
write open object;
write values "First_level_id" "&&first_lev_id&f..";
write values "First_level_data" "&&first_lev_dat&f..";
/* example of control structure to include level or not */
%if &&amt_first_lev_objs&f.. > 0 %then %do;
write values "Second_level";
write open array;
%do r=1 %to &num_scnd_lev_objs.;
write open object;
write values "Second_level_code" "&&second_level_code&r..";
write values "Second_level_num" &&second_level_num&r..;
/* check the bottom level dataset exists */
%if %sysfunc(exist(WORK.&&second_level_ds&r..)) %then %do;
/* fields array */
write values "Bottom Level";
write open array;
export &&second_level_ds&r..;
write close;
%end;
write close;
%end;
write close;
%end;
write close;
%end;
write close;
write close;
run;
Whilst that may look a bit mad it is actually quite straightforward, presuming you are used to looping through nested levels of macro variables.
With this approach you can create JSON files of any shape and size you like. If you wanted to have multiple nests of objects of differing structures you can easily do that by following one open object block with another one. The possibilities for the kinds of JSON files you can create are endless!
