topic Re: how to export Nested JSON with proc JSON? in SAS Procedures

how to export Nested JSON with proc JSON?

jsut — Fri, 24 Jun 2022 06:00:36 GMT

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}

]

}

]

}

]

Re: how to export Nested JSON with proc JSON?

andreas_lds — Fri, 24 Jun 2022 06:43:10 GMT

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

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

Re: how to export Nested JSON with proc JSON?

V_Altomonte — Fri, 24 Jun 2022 08:56:17 GMT

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

Re: how to export Nested JSON with proc JSON?

jsut — Sun, 26 Jun 2022 03:29:27 GMT

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

Re: how to export Nested JSON with proc JSON?

BillM_SAS — Mon, 27 Jun 2022 17:40:15 GMT

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.

Re: how to export Nested JSON with proc JSON?

jsut — Tue, 28 Jun 2022 00:52:06 GMT

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.