how to export Nested JSON with proc JSON?

[jsut]

Hi all,

I'm pretty new with trying to export json file from sas.

Can I please get some help on how to export a nested json with proc json? Thanks in advance.

So, what I'm trying to get is something like:

[

{"person_id": "12345",
"timestamp":"Tue Jun 7 07:21:50 2022",
"message":"sample",
"items":[
         {"category_type":"cat1",
         "category_status":"approved",         

         "details": [

                   {"subcategory_code":"subcat1",

                   "subcategory_total":100}

                   ,{"subcategory_code":"subcat2",

                   "subcategory_total":50 }

                    ]

         },

         {"category_type":"cat2",
         "category_status":"pending",         

         "details": [

                   {"subcategory_code":"subcat1",

                   "subcategory_total":200}

                   ,{"subcategory_code":"subcat2",

                   "subcategory_total":300}

                    ]

          }

       ]

},

{"person_id": "56789",
"timestamp":"Tue Jun 7 07:21:50 2022",
"message":"sample",
"items":[
         {"category_type":"cat3",
         "category_status":"pending",         

         "details": [

                   {"subcategory_code":"subcat1",

                   "subcategory_total":10}

                   ,{"subcategory_code":"subcat2",

                   "subcategory_total":20}

                    ]

         },

         {"category_type":"cat4",
         "category_status":"approved",         

         "details": [

                   {"subcategory_code":"subcat1",

                   "subcategory_total":30}

                   ,{"subcategory_code":"subcat2",

                   "subcategory_total":50}

                    ]

          }

        ]

}

]

[andreas_lds]

Why do you want to create a json file using sas?

Can you post the datasets in usable form (dataset with datalines)?

[jsut]

Hi @andreas_lds ,

we're planning to automate some process using data from sas to be then pushed into another system that's expecting a json file.

some sample datasets as below:

data sampledata;
input person_id $5.
person_name $10.
message $10.
category_type $5.
category_status $8.
subcategory_code $10.
subcategory_total 5.;

datalines;
12345 Person_A Sample_1 cat1 approved subcat1 100
12345 Person_A Sample_1 cat1 approved subcat2 50
12345 Person_A Sample_1 cat2 pending subcat1 200
12345 Person_A Sample_1 cat2 pending subcat2 300
56789 Person_Z Sample_2 cat3 pending subcat1 10
56789 Person_Z Sample_2 cat3 pending subcat2 20
56789 Person_Z Sample_2 cat4 approved subcat1 30
56789 Person_Z Sample_2 cat4 approved subcat2 50
;
run;

Thanks
 

[BillM_SAS · Accepted solution]

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.

[jsut]

Thanks @BillM_SAS , the code seems to work. I just need to tweak them a bit further to get to how we want the json file format to be.

[V_Altomonte]

You can use the PROC JSON, probably with the NOSASTAGS and PRETTY options:
proc JSON documentation

Alternatively, you can compose it manually with a date step,
as shown in this other post:
JSON export post