working with json files

[alepage]

filename mydata2 "/finsys/bicoe/users/rpnna322/Original.json";
data _null_;
file mydata2;
input;
put _infile_;
datalines;
{"contacts":[
{"firstName": "Gustave","lastName" : "Someone","extRef":"AAAAAAAAAAA","email": "Gustave.Someone@hotmail2.com","embeddedData": {"Brand": "Beneva"}, "ConsentDate":"2023-03-28T00:00:00", "unsubscribed": true},
{"firstName": "Gustave","lastName" : "Someone","extRef":"AAAAASSSSSS","email": "Gustave.Someone@hotmail2.com","embeddedData": {"Brand": "Beneva"}, "ConsentDate":"2023-03-28T00:00:00", "unsubscribed": false},
{"firstName": "Gustave","lastName" : "Someone","extRef":"AAAAAAADDDD","email": "Gustave.Someone@hotmail2.com","embeddedData": {"Brand": "Beneva"}, "ConsentDate":"2023-03-28T00:00:00", "unsubscribed": true},
{"firstName": "Gustave","lastName" : "Someone","extRef":"EEEEEEEEEEE","email": "Gustave.Someone@hotmail2.com","embeddedData": {"Brand":"Beneva"}, "ConsentDate":"2023-03-28T00:00:00", "unsubscribed": false}
]}
;
run;

libname myjson json fileref=mydata2;
proc datasets lib=myjson; quit;

data alldata;
set myjson.alldata;
run;

data contacts;
set myjson.contacts;
run;

data contacts_embeddeddata;
set myjson.contacts_embeddeddata;
run;

/**** From that point, how to we get the good sas dataset that we can use to obtain the original json file at the top ? */

proc transpose data=myjson.alldata out=have1 (drop=_name_ p1 rename=(p3=Brand));
where v>0;
by p1 p3 notsorted;
id p2;
var value;
run;

Data have1;
retain firstName lastName extRef email embeddedData Brand ConsentDate;
set have1;
run;

/* Using Proc SQL approach */

proc sql;
create table have2 as
select a.*,b.brand
from myjson.contacts as a
inner join contacts_embeddeddata as b
on a.ordinal_contacts = b.ordinal_contacts ;
quit;

/**** The final Step ? ***/

proc json out="/finsys/bicoe/users/rpnna322/Original1.json" pretty;
   export have1 / nosastags;
run;

proc json out="/finsys/bicoe/users/rpnna322/Original2.json" pretty;
   export have2 / nosastags;
run;

Hello,   

I would like to import the original json file, change few records then regenerate that file from the updated sas dataset but up to now, I notice differences from the original json file in term of structure.

Could someone help me with that.

[Quentin]

I think that is going to be hard to do.  Since a SAS dataset is a table, it's not obvious how you could generate a json file with "embedded data", i.e. a json file that essentially stores two tables, with one embedded in the other.  This example from the SAS docs looks close to what you want: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/p1n0o64iiv77win10abhx582as2s.htm  , and this example is also interesting https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/n1aq4o1nbg1b72n1rfleh8gxz1zk.htm .

But neither is exporting data from one dataset that is "embedded" within the records from another dataset.  Interesting question.  Look forward to hearing ideas from others.

[AlanC]

There is no good way to move back and forth from tables to JSON. Whenever you work with hierarchical data (JSON, XML, YAML, HTML. etc.) moving to tables is a bit of interpretation. The engine needs to understand intent which is hard. 

When working with these structures you can try and work within the SAS libname JSON engine. If you only have a few changes, and the structure remains intact, consider just treating it as a text file and doing a regex replace. If not, roll your own using data step and filenames. Much easier to control the layout. My view is not to allow SAS to intelligently guess what you want: just roll your own.

Try the JSON libname first, however, and see if it does what you want.

BTW, we face the same issue in .NET which is the 'intent' when switching between tables and object-oriented structures like JSON.