Complex JSON strings

[Stokesy_SAS]

Hi Ive recently found myself involved with a major data migration/re-platforming project which involves extracting data from an ancient mainframe (z/os) based application onto a cloud based azure system

Rather surprisingly the actual translate/transform has not proven much of a problem. Myself and my colleagues are very familiar with the data on the source system and much of the target system requires either one to one mappings or default values to be used(especially with regards to the older, simpler contracts)

The format of the output too was initially not a problem as we were able to use vanilla proc JSON for each of the tables we were creating.

Unfortunately in order to consume our SAS generated JSON files the output needs to be radically re-shaped....

instead of discrete Policy , Component, Roles, Premium history etc files what is required is a series of records based on the primary key of policy number (each of the tables should contain this field as a foreign key) and this single file should contain the JSON containers to enclose the name of each table and containers to establish arrays of tables within each policy string. It is also possible that an overall header will be required too in order to distinguish different dates of extraction and entries on the various control tables that we are using to manage the project.

As a further complication the JSON used here is slightly unusual insofar as it starts with an open array '[' rather than with an open object '{' container. Also objects can be opened within each other without a supervening array. Additionally these back to back objects are to be separated by a comma.....

So.… limiting what is required to 2 tables for illustrative purposes (a minimum of 14 is required for the most basic of contracts) we want something like

[{
"policy": {
"policy" : "1",
"fieldTwo" : "XYZ"
},
"policyComponent" : [{
"benefitType": "A"
},{
"benefitType": "B"
},{
"benefitType": "C"
}
],
{"policy": {
"policy": "2",
}
etc etc
}
]

I have quickly come to realise that I am going to have to place the main part of the JSON output in a macro so that one policy record at a time is output and modded onto my complete JSON file but I will need to open and close the initial array container outside of this macro and I'm struggling to see how I could do this.

My code so far attempt just to output the main policy table and it almost does this trick except that it
fails to close the top level (array) container.
It is also unsatifactory as SAS returns an RC of 8
Not sure it is robust enough to add further tables
code

proc json out=DAT3 pretty nosastags;
write open array;
run;

%macro output_json (table);

proc json; out=DAT3;
write open object;
write values 'policy';
export &table;
write values ',';
write close;
write close;
run;
%mend;
%macro call_json;
%do I = 1 %to 5; <------- will be total number of records eventually
set policy_file (firstobs=&I obs=&I);
call execute (%output_json(policy_single_record)');
run;

%mend;

%call_json;

Proc Json out=dat3;
write close_array;
run;

I don't know if anyone else has attempted any similar to this but I really could do with some pointers

All the best,
Simon.

[Tom]

So is the problem one of generating that JSON like string from data you have in a SAS dataset? If so please show some example records from the SAS dataset. You should not really have any trouble generating strings from SAS datasets, once you can articulate the rules.

[Stokesy_SAS]

Yes, the data is read in from a mainframe qsam file and then transformed into the sas file that is passed to JSON. It is a standard SAS file consisting of character numeric and date fields

[Tom]

---

@Stokesy_SAS wrote:

Yes, the data is read in from a mainframe qsam file and then transformed into the sas file that is passed to JSON. It is a standard SAS file consisting of character numeric and date fields

---

Show the data.  We cannot read your mind or access your computer.

[Stokesy_SAS]

Yes apologies for not doing so.. unfortunately I was on 2 different lap tops and could therefore not cut and paste on to the other.

input file from proc print

output JSON 

which looks ok but as I say I cannot see how to close the final array container and I'm struggling to see how I can incorporate the contents of other files within the body of the first table.... Some sort of macro solution seems the best option but my problem is how to leave part of the proc json inside the macro and part out

[Tom]

Not sure why copy and paste and email don't work for you, but you can just type up an example into the forum editor. Use a DATA step with in-line data to present an example of the data. If you need to use data from multiple datasets then show enough of them that you can describe the problem. For the example data you show please show the string you want to generate.

[BillM_SAS]

I'm still trying to understand the problem. Showing the existing data sets would help. Based on what you showed code wise, the attempt to get PROC JSON to output just the opening and closing array will not work. PROC JSON wants to output valid and complete JSON.

I think that a program I wrote showing how to create hierarchical JSON could be helpful to you. It will show you a working example of dealing with hierarchical output and using SAS Macro with PROC JSON.

[Stokesy_SAS]

Yes I did see your post when I was searching for a solution several days ago unfortunately the link to the %include json spec page which I was hoping would explain the problem that your solution was trying to address was broken

[BillM_SAS]

I'm not sure to which broken link you are referring. The link I included in my reply goes to the original post asking about creating hierarchical JSON output with PROC JSON. Can you be more specific?