topic Re: How to concatenate all datasets in json library as per Json model schema in SAS Programming

How to concatenate all datasets in json library as per Json model schema

learn_SAS_23 — Wed, 13 Mar 2024 10:37:25 GMT

Hello Team,

am reading data from Json file , where its contents are dynamic as shown in below format

the first record has all attributes , where as second record has only few attributes.

[
  {
    "id": 0,
    "name": "Nora",
    "city": "New Orleans",
    "age": 55,
    "friends": [
      {
        "name": "Emma",
        "hobbies": [ "Calligraphy", "Television", "Music" ]
      },
      {
        "name": "Lucas",
        "hobbies": [ "Yoga", "Playing Cards" ]
      }
    ]
  },
  {
    "id": 1,
    "name": "Mia",
  },
  
]

	libname APIJSON JSON fileref=jsonout;

	proc datasets lib=APIJSON; quit;

i would like to concatenate all datasets in json library as per Json model schema and load null values for optional attributes or data sets

In this example , friends data set is optional and city , age attributes are optional

Re: How to concatenate all datasets in json library as per Json model schema

SASJedi — Wed, 13 Mar 2024 12:00:03 GMT

Do you really mean concatenate, or do you want the result of a join? I'd expect something like this would fit the bill:

proc sql;
select id, r.name, city, age, f.name, hobbies1, hobbies2, hobbies3  
	from apijson.root as r
	left join 
		  apijson.friends as f
		on r.ordinal_root=f.ordinal_root
	left join 
	  apijson.Friends_hobbies as h
		on h.ordinal_friends=f.ordinal_friends
	order by ID
;
quit;

Result:

id	name	city	age	name	hobbies1	hobbies2	hobbies3
0	Nora	New Orleans	55	Lucas	Yoga	Playing Cards
0	Nora	New Orleans	55	Emma	Calligraphy	Television	Music
1	Mia		.

Re: How to concatenate all datasets in json library as per Json model schema

learn_SAS_23 — Wed, 13 Mar 2024 13:31:06 GMT

as mentioned , the json attributes are dynamic like in this example friends data set is dynamic

Re: How to concatenate all datasets in json library as per Json model schema

learn_SAS_23 — Wed, 13 Mar 2024 13:31:59 GMT

which means for few records we will get friends data and for other records we wont't get as it is a optional field