Need help to create a hierarchical JSON file using PROC JSON

[chatur]

Hi,

I need some help creating a JSON file using PROC JSON. I am new to PROC JSON. I have been working on it and have made some progress. But I have not yet been able to figure out how to create multiple nested hierarchies. Any help would be much appreciated.

Many thanks.

Please note that the data in the JSON file is imported into the JSON file from various SAS datasets using the:

EXPORT SAS_DATASET_NAME statement

-------------------------------------------------------------------------------------------------------------------------

Start of JSON file that I need to create (it is a simplified example)...

{
"Person": {
"Name": {
"Fname": "John",
"Lname": "Smith"
},
"SomeMoreDetails": {
"number": "200",
"Street": "Palm Ave",
"City": "Funky Town",
"State": "Oklahoma",
"Tastes": [
{
"Sweet": "Yes",
"Sour": "No",
"Bitter": "No",
"Movies": [
{
"Thriller": "Yes",
"Comedy": "Yes",
"Horror": "No"
}
],
"Relationships": [
{
"Brother": "Bad",
"Sister": "OK",
"Father": "Good"
},
{
"Friend": "Excellent",
"Acquaintence": "OK",
"Boss": "Formal"
}
]
}]}}}

[BillM_SAS]

I previously posted a proof-of-concept SAS program that produces hierarchical output using PROC JSON. You should be able to use this as a starting point to produce what you want as output.

[chatur]

Bill,

Thank you so much for your response and for pointing me to the sample code.

I have a follow up question. In your code, the lines in BLUE and GREEN are hard coded, with WRITE VALUE statements. The portion in RED is created using an EXPORT Dataset_Name statement.

What if I also needed to create the portions in BLUE and GREEN using an EXPORT SAS_ dataset statement, since that information is dynamic?

Any thoughts would be much appreciated.

Thanks

{

  "name": "World",

  "description": "World",

  "children": [

    {

      "name": "Africa",

      "description": "Africa",

      "children": [

        {

          "name": "Addis Ababa",

          "description": "Addis Ababa",

          "children": [

            {

              "name": "Boot",

              "description": "Boot",

              "size": 29761

            },

            {

              "name": "Men's Casual",

              "description": "Men's Casual",

              "size": 67242

            },

            {

              "name": "Men's Dress",

              "description": "Men's Dress",

              "size": 76793

            },

            {

              "name": "Sandal",

              "description": "Sandal",

              "size": 62819

            },

[Tom]

Show the PROC JSON code that generated that JSON file.

You can probably use a simple data step with BY group processing to write the PROC JSON code.  This is generate the CONSTANT text that the PROC JSON code needs dynamically be using the values in the dataset.

Or just skip the PROC JSON completely and use BY group processing to write the JSON text directly from the data .

So for the first observation in a BY group write the block opening logic of the JSON text.  And on the last observation in the BY group write the JSON text that closes that block.

Looking at the GREEN text for example:

 "name": "Africa",

      "description": "Africa",

      "children": [

So you might have a by variable named CONTINENT that has values like "Africa".  So on the first observation for the continent Africa you write that green text.  For the non-first observation you need to write the COMMA that sperates the items in the list opened by the square bracket.  And on the last observation you write the closing square backet.

Nesting these gets the nested structure you want.

[chatur]

Thank you for your response. Here is part of the code that generated that output...

proc json pretty out="C:\Users\jsonOutput_all.txt" nosastags ;

write open object ; /* open outermost object */
write value "name" ;
write value "World" ;
write value "description" ;
write value "World" ;
write value "children" ;
write open array ; /* open H1 array */
write open object ; /* open H1 object */
write value "name" ;
write value "Africa" ;
write value "description" ;
write value "Africa" ;
write value "children" ;
write open array ; /* open H2 array */
write open object ; /* open H2 object */
write value "name" ;
write value "Addis Ababa" ;
write value "description" ;
write value "Addis Ababa" ;
write value "children" ; /* open H3 array */
write open array ; /* open array of data set objects */
export rnm_ds_1 ;
write close ; /* close array of data set objects */
write close ; /* close H3 array */

[BillM_SAS]

I am confused by your question. All the data in the JSON output file is generated dynamically. The colored JSON you are referencing looks a little like what should be in the generated JSON procedure code that the proof-of-concept (POC) SAS program produces. The generated JSON procedure code is generated dynamically by the POC SAS program from whatever is in the SAS data set (in the POC case, SASHELP.shoes). All that the POC SAS program needs are the names of the variables in the data set and order for the variables to appear in the hierarchy to produce the hierarchical output. I tried my best to show that in a graphical representation of the hierarchy in the comments at the top of the POC SAS program:

 Region -+
         |
         +- Subsidiary -+
                        |
                        +- Product -+
                                    |
                                    +- <product data>

The "hard coded WRITE VALUE statements" you mention are generated dynamically by the POC SAS program from the data in the data set. The POC SAS program is what you will need to modify for your specific data set. When modified correctly, the POC SAS program will generate from the data set all the JSON procedure code needed to produce the desired JSON output.

[Tom]

Are you saying your SAS datasets looks like the result of reading in that JSON text?

NOTE: Copying JSON.ALLDATA to WORK.ALLDATA (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: There were 25 observations read from the data set JSON.ALLDATA.
NOTE: The data set WORK.ALLDATA has 25 observations and 8 variables.
NOTE: Copying JSON.PERSON_NAME to WORK.PERSON_NAME (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: There were 1 observations read from the data set JSON.PERSON_NAME.
NOTE: The data set WORK.PERSON_NAME has 1 observations and 4 variables.
NOTE: Copying JSON.PERSON_SOMEMOREDETAILS to WORK.PERSON_SOMEMOREDETAILS (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: There were 1 observations read from the data set JSON.PERSON_SOMEMOREDETAILS.
NOTE: The data set WORK.PERSON_SOMEMOREDETAILS has 1 observations and 6 variables.
NOTE: Copying JSON.SOMEMOREDETAILS_TASTES to WORK.SOMEMOREDETAILS_TASTES (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: There were 1 observations read from the data set JSON.SOMEMOREDETAILS_TASTES.
NOTE: The data set WORK.SOMEMOREDETAILS_TASTES has 1 observations and 5 variables.
NOTE: Copying JSON.TASTES_MOVIES to WORK.TASTES_MOVIES (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: There were 1 observations read from the data set JSON.TASTES_MOVIES.
NOTE: The data set WORK.TASTES_MOVIES has 1 observations and 5 variables.
NOTE: Copying JSON.TASTES_RELATIONSHIPS to WORK.TASTES_RELATIONSHIPS (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: There were 2 observations read from the data set JSON.TASTES_RELATIONSHIPS.
NOTE: The data set WORK.TASTES_RELATIONSHIPS has 2 observations and 8 variables.

[chatur]

Yes. That is correct. My data has similar hierarchy to the datasets you created.

Thanks

[Tom]

So what is the actual SAS dataset structure you are using?  Are you really using a half dozen independent datasets?

Or did you combine them into something that is more usable?

[chatur]

I am using about a dozen separate datasets.

Is there a better way to do it?

[BillM_SAS]

To use the POC SAS program, the easiest thing to do would be to create a new SAS data set from the dozen data sets you are currently using. In this new data set, put the data you want to output in JSON. Build this data set so that it has the same type of layout as the SASHELP.shoes data set (used to drive the POC SAS program). By that, I mean that each observation should contain all the data representing the levels of the hierarchy. So basically, you would take the dozen current data set and unnormalize them into a single data set. Once in a single data set, you should be able to modify the POC SAS program to accommodate your data to produce the JSON output desired.

The other alternative is to use the technique demonstrated in the POC SAS program - identifying the hierarchy keys and using the keys to subset the data into hierarchy specific data sets. If your data is already normalized, I think it should be possible to modify the POC SAS program to produce the desired JSON output with your current data sets.

[chatur]

Thank you for your response. I will try what you suggested.