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.
... View more