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
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 | . |
as mentioned , the json attributes are dynamic like in this example friends data set is dynamic
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.