I may have to claim defeat. Every time I try to change something in the code I get the error " A string is required at this point in the test to that an object key may be emitted". Below is the code with the minor edits I made (mentioned in comments), trying to see it it would get me the expected output, which I have also recopied below. /* alternative method provided by SAS forum */
%macro createCommReqDataSet(count, value);
proc sql;
create table dsCR_&count as
select source, sourceTrnsReferenceId, requesterId,
templateId, tenateId, sourceCompanyCode
from work.commrequest
where CIPID=&value
;
quit;
%mend createCommReqDataSet;
%macro createSIDDataSet(count, value);
proc sql;
create table dsSID_&count as
select ContractID,PartyID,Language,principalRoleCode,dataEntitlement
from work.json_test
where CIPID=&value
;
quit;
%mend createSIDDataSet;
%macro createDIDataSet(count, value);
proc sql;
create table dsDI_&count as
select FirstName, MiddleInitial, LastName, EmailAddress, preferenceOverride, deliveryChnnl
from work.json_test
where CIPID=&value
;
quit;
%mend createDIDataSet;
%macro createEDataSet(count, value);
proc sql;
create table dsE_&count as
select productType, planName, planType, CIPID, GRPNB, DIVISIONNB
from work.json_test
where CIPID=&value
;
quit;
%mend createEDataSet;
proc sort data=work.json_test;
by cipid;
run;
data _null_;
set work.json_test;
by CIPID;
call execute('%createCommReqDataSet(' || _N_ || ',' ||
TRIM(CIPID) || ')');
call execute('%createSIDDataSet(' || _N_ || ',' ||
TRIM(CIPID) || ')');
call execute('%createDIDataSet(' || _N_ || ',' ||
TRIM(CIPID) || ')');
call execute('%createEDataSet(' || _N_ || ',' ||
TRIM(CIPID) || ')');
run;
proc datasets lib=work; quit;
%let stmtEnd=%STR(;);
%macro fileHeader(filePath);
put "proc json pretty out=""" &filePath """ nosastags &stmtEnd";
put "write open object &stmtEnd /* open outermost array */";
put "write values CommRequest &stmtEnd"; /* ADDED - TRYING TO ONLY GET COMMREQUEST TO SHOW UP ONCE, SHOULD NOT REPEAT FOR EACH CIPID */
%mend fileHeader;
%macro fileFooter();
put "write close &stmtEnd /* close outermost object */";
put "run &stmtEnd";
%mend fileFooter;
%let jsonProcCodeSpec=C:\Users\d3jq\Desktop\jsonProcCode.sas;
%let jsonOutputSpec=C:\Users\d3jq\Desktop\JSON_TEST4.json;
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 CIPID;
/* 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 array &stmtEnd"; /* changed from object to array */
put "write values SOURCE &stmtEnd"; /* I REALLY DO NOT WANT A VALUE HERE AT ALL BUT IT ALWAYS THROWS AN ERROR WHEN I REMOVE THIS LINE */
dsName=CAT("dsCR_",_N_);
put "export " dsName "&stmtEnd";
put "write values MEMBERINFO &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"; REQUIRED OUTPUT: {
"commRequest": [ {
"source": "ppx",
"sourceTrnsReferenceId": "bfe9961i",
"requesterId": "svc-ppx",
"templateId": "PPX6",
"tenateId": 1,
"sourceCompanyCode": 1,
"MEMBERINFO": {
"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"
}
}, {
"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"
}
}
] }
... View more
