I took some pieces from my proof-of-concept code, your original data set, a recreation of your other data set, and produced this SAS program. It puts each record into a JSON container in a format that is probably close to what you want. I am still not completely clear on your desired JSON output. But you should be able to modify my code to get exactly what you want. Note that my code could be more concise. I wanted to make it understandable to someone not familiar with the techniques used. If you are uncertain, the proof-of-concept code I borrowed from contains many comments explaining the code.
data json_test;
infile datalines dlm=',' dsd;
input Contract $ PartyID Language $ Role $ Data $ FirstName $ MiddleInitial $ LastName $ EmailAddress $ Override $ Channel $ Product $ Plan $ Type $ UNQID GRPNB $ DIVISIONNB $;
datalines;
ABC123,567890,EN,MEMBER,GEN,JOHN,,DOE,,N,EMAIL,P,Sample,A,123456,XXX12,ABC
DEF456,123456,EN,MEMBER,GEN,JANE,,DOE,,N,EMAIL,H,Sample2,B,789012,YYY34,DEF
;
run;
proc print data=work.json_test; run;
data commReq;
infile datalines dlm=',' dsd;
input source $ sourceTrnsReferenceId $ requesterId $ templateId $ tenateId sourceCompanyCode UNQID;
datalines;
ppx, bfe9961i, svc-ppx, PPX6, 1, 1, 123456
ppx, zgtewwyr, svc-ppx, PPX6, 1, 1, 789012
;
run;
proc print data=work.commReq; run;
data member (keep= Contract PartyID Language Role Data)
delivery (keep= FirstName MiddleInitial LastName EmailAddress Override Channel)
additional (keep= Product Plan Type UNQID GRPNB DIVISIONNB);
set work.json_test;
run;
proc print data=work.member; run;
proc print data=work.delivery; run;
proc print data=work.additional; run;
%macro createCommReqDataSet(count, value);
proc sql;
create table dsCR_&count as
select source, sourceTrnsReferenceId, requesterId,
templateId, tenateId, sourceCompanyCode
from work.commReq
where UNQID=&value
;
quit;
%mend createCommReqDataSet;
%macro createSIDDataSet(count, value);
proc sql;
create table dsSID_&count as
select Contract,PartyID,Language,Role,Data
from work.json_test
where UNQID=&value
;
quit;
%mend createSIDDataSet;
%macro createDIDataSet(count, value);
proc sql;
create table dsDI_&count as
select FirstName, MiddleInitial, LastName, EmailAddress, Override, Channel
from work.json_test
where UNQID=&value
;
quit;
%mend createDIDataSet;
%macro createEDataSet(count, value);
proc sql;
create table dsE_&count as
select Product, Plan, Type, UNQID, GRPNB, DIVISIONNB
from work.json_test
where UNQID=&value
;
quit;
%mend createEDataSet;
data _null_;
set work.json_test;
by UNQID;
call execute('%createCommReqDataSet(' || _N_ || ',' ||
TRIM(UNQID) || ')');
call execute('%createSIDDataSet(' || _N_ || ',' ||
TRIM(UNQID) || ')');
call execute('%createDIDataSet(' || _N_ || ',' ||
TRIM(UNQID) || ')');
call execute('%createEDataSet(' || _N_ || ',' ||
TRIM(UNQID) || ')');
run;
proc datasets lib=work; quit;
%let stmtEnd=%STR(;);
%macro fileHeader(filePath);
put "proc json pretty out=""" &filePath """ nosastags &stmtEnd";
put "write open array &stmtEnd /* open outermost array */";
%mend fileHeader;
%macro fileFooter();
put "write close &stmtEnd /* close outermost object */";
put "run &stmtEnd";
%mend fileFooter;
%let jsonProcCodeSpec=./sasuser/jsonProcCode.sas;
%let jsonOutputSpec=./sasuser/jsonOutput.txt;
data _null_;
/* Process each observation in the WORK.json_test data set. The
END option set a flag when the last observation in the data set
is read. The BY statement groups the data by the desired
ID. */
set work.json_test end=lastOne;
by UNQID;
/* specifies the output file for PUT statements */
FILE "&jsonProcCodeSpec" DISK;
/* Only on the first observation in the data set, write the required
initial statements to the JSON procedure code file. */
if _N_ eq 1
then do;
%fileHeader("&jsonOutputSpec");
end;
put "write open object &stmtEnd";
put "write values commRequest &stmtEnd";
dsName=CAT("dsCR_",_N_);
put "export " dsName "&stmtEnd";
put "write values Source_ID &stmtEnd";
dsName=CAT("dsSID_",_N_);
put "export " dsName "&stmtEnd";
put "write values delivery_info &stmtEnd";
dsName=CAT("dsDI_",_N_);
put "export " dsName "&stmtEnd";
put "write values email &stmtEnd";
dsName=CAT("dsE_",_N_);
put "export " dsName "&stmtEnd";
put "write close &stmtEnd";
/* Only on the last observation in the data set, write to the JSON
procedure code file the required statements to end the generated
JSON procedure. */
if lastOne
then do;
%fileFooter();
end;
run;
/* Now run the generated custom JSON procedure code to produce the
JSON formatted output file of the data set. */
%include "&jsonProcCodeSpec";
Here is the output produced:
[
{
"commRequest": {
"source": "ppx",
"sourceTrnsReferenceId": "bfe9961i",
"requesterId": "svc-ppx",
"templateId": "PPX6",
"tenateId": 1,
"sourceCompanyCode": 1
},
"Source_ID": {
"Contract": "ABC123",
"PartyID": 567890,
"Language": "EN",
"Role": "MEMBER",
"Data": "GEN"
},
"delivery_info": {
"FirstName": "JOHN",
"MiddleInitial": "",
"LastName": "DOE",
"EmailAddress": "",
"Override": "N",
"Channel": "EMAIL"
},
"email": {
"Product": "P",
"Plan": "Sample",
"Type": "A",
"UNQID": 123456,
"GRPNB": "XXX12",
"DIVISIONNB": "ABC"
}
},
{
"commRequest": {
"source": "ppx",
"sourceTrnsReferenceId": "zgtewwyr",
"requesterId": "svc-ppx",
"templateId": "PPX6",
"tenateId": 1,
"sourceCompanyCode": 1
},
"Source_ID": {
"Contract": "DEF456",
"PartyID": 123456,
"Language": "EN",
"Role": "MEMBER",
"Data": "GEN"
},
"delivery_info": {
"FirstName": "JANE",
"MiddleInitial": "",
"LastName": "DOE",
"EmailAddress": "",
"Override": "N",
"Channel": "EMAIL"
},
"email": {
"Product": "H",
"Plan": "Sample2",
"Type": "B",
"UNQID": 789012,
"GRPNB": "YYY34",
"DIVISIONNB": "DEF"
}
}
]
