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

Thanks, Bill, for the code. I'm still reviewing. The holiday has me behind. I noticed the "CommRequest" header repeats for each member. The output we were told to have only had it at the top, but as you say the hierarchy can be changed based on your notes in the proof of concept. I'll just need to play around with it.  ... View more

Yes, basically. There was another section at the top, I wasn't initially coding for, because it wasn't included with the sample code, but it's technically in the requested output. See new requested output format below.   { "commRequest": [{ "source": "ppx", "sourceTrnsReferenceId": "long_guid_id1", "requesterId": "svc-ppx", "templateId": "PPX6", "tenateId": 1, "sourceCompanyCode": 1, "Source_ID": { "contract": "ABC123", "partyId": 56789, "language": "en", "Role": "MEMBER", "data": "GEN" }, "deliveryInfo": { "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": "long_guid_id2", "requesterId": "svc-ppx", "templateId": "PPX6", "tenateId": 1, "sourceCompanyCode": 1, "Source_ID": { "contract": "DEF456", "partyId": 123456, "language": "en", "Role": "MEMBER", "data": "GEN" }, "deliveryInfo": { "firstName": "JANE", "middleInitial": "", "lastName": "DOE", "emailAddress": "", "Override": "N", "Channel": "EMAIL" }, "email": { "product": "H", "plan": "Sample2", "Type": "B", "unqId": "789012", "grpNb": "YYY34", "divisionNb": "DEF" } }     I found code that uses a macro and it gets the order right, but the brackets don't look right and I had to include the key(unqid) on each container. So closer but still not perfect.   %macro TargetstoJSON(UNQID); write open object; write values "commRequest"; /*write open array;*/ export work.commrequest (where=(UNQID=&UNQID) ); write values "Source_ID"; write open array; /* container for member */ export work.member (where=(UNQID=&UNQID) ); write values "delivery_info"; write open array; /* container for delivery */ export work.delivery (where=(UNQID=&UNQID)); write values "email"; write open array; /* container for additional */ export work.additional (where=(UNQID=&UNQID)); write close; /* container for additional */ write close; /* container for the delivery */ write close; /* container for the member */ write close; /* container for commrequest */ %mend TargetstoJSON; /* Loop through all the consumer ids */ %macro TargetRecords; %do i = 1 %to &dim_IDs; %TargetstoJSON(&&&UNQID_&i); %end; %mend TargetRecords; /* Produce a macro variable for each consumer ID and the total count of consumerids in the data set. */ proc sql; select UNQID into :UNQID_1 - :UNQID_&SysMaxLong from work.member; %let dim_IDs = &sqlObs; quit; /* JSON procedure code for getting all the members and their delivery and additional information. The resultant JSON output is stored in the JSON_TEST2.json file in the current location. */ proc json out="C:\Users\d3jq\Desktop\JSON_TEST2.json" pretty nosastags; write open array; /* container for all the data */ %TargetRecords; write close; /* container for all the data */ run; [ { "commRequest": { "source": "ppx", "sourceTrnsReferenceId": "bfe9961i", "requesterId": "svc-ppx", "templateId": "PPX6", "tenateId": 1, "sourceCompanyCode": 1, "UNQID": 123456 }, "Source_ID": [ { "Contract": "ABC123", "PartyID": 567890, "Language": "EN", "Role": "MEMBER", "Data": "GEN", "UNQID": 123456 }, "delivery_info", [ { "FirstName": "JOHN", "MiddleInitial": "", "LastName": "DOE", "EmailAddress": "", "Override": "N", "Channel": "EMAIL", "UNQID": 123456 }, "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, "UNQID": 789012 }, "Source_ID": [ { "Contract": "DEF456", "PartyID": 123456, "Language": "EN", "Role": "MEMBER", "Data": "GEN", "UNQID": 789012 }, "delivery_info", [ { "FirstName": "JANE", "MiddleInitial": "", "LastName": "DOE", "EmailAddress": "", "Override": "N", "Channel": "EMAIL", "UNQID": 789012 }, "email", [ { "Product": "H", "Plan": "Sample2", "Type": "B", "UNQID": 789012, "GRPNB": "YYY34", "DIVISIONNB": "DEF" } ] ] ] } ] ... View more

I'm not sure where/how the source part comes in unless its the same as member, but I did try to generate sample output using that test data set and then broke it up using this code below as it was the only way I could get the sample code given to me to work. The output from the sample json code and the output actually needed was in the attached excel file.   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 unixwork.json_test_input; run; ... View more

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 JSON out="H:\SAS\sasdata\TEST.json" pretty nosastags;   write values "Source_ID";    write open array;     export source1;       write close;    write values "delivery_info";     write open array;       export delivery1;       write close;    write values "email";      write open array;         export email;         write close;         run;   ... View more

Hi again,   Got busy with other projections and finally returning to this one.  Below is the code I ended up with. Fingers crossed I am on the right track?!   Based on the results, it only looks like the target vs control is significant (See attachments). I did a separate test using proc freq to test region significance between target and control and it looks like the West Coast targets were statistically different from the West Coast control group. So I was wondering, how exactly do I interpret the two different results using proc glm and just the chisq from the proc freq? Am I not asking the right question with the model statement in proc glm?    proc glm data = unixwork.col_interim2 outstat=unixwork.stat_sig_testing; class bucket region age_cohort adi_cohort; model Quest_Closed_Gap = Bucket|Region|age_cohort|ADI_cohort / tolerance; lsmeans Bucket|Region|age_cohort|ADI_cohort / pdiff=all adjust=tukey; run; quit; Bucket values are target or control. Region values are West Coast or South Age are 55- 59 or 60 - 64  ADI are in quartiles from 0 -100   proc sort data= unixwork.col_interim2; by region bucket Quest_Closed_Gap; run; proc summary data=unixwork.col_interim2; var COL_TARGET; by region bucket Quest_Closed_Gap; output out=unixwork.region sum=; run; /*ods output PdiffCLs=pdiff;*/ /* Region - does target differ from control group? */ proc freq data=unixwork.region; by region; weight COL_TARGET; table bucket * Quest_Closed_Gap / chisq riskdiff; output out=unixwork.region_stat chisq; run; /* only outputs if statistically differs */ data unixwork.region_stat_sig; set unixwork.region_stat; by region; if P_PCHI < 0.05; run; /* west coast targets performed stat better than control; south was similar to control */ Thanks again! ... View more

Yes, that is the case and what I thinking when I was typing the response to Reeza. I will now go research your suggestion: proc glm with adjust option.   Also, I do have more class variables, I was just limiting it for ease of sharing.   Thank you! ... View more

Thanks for your response. I'm reading up on how exactly to use this procedure, which has given me some follow up questions.   Based on the way my data is layed out, would region, age cohort, adi, etc. actually be groups versus variables? The main measurement I have is gaps closed out of the number targeted (1 = yes, 0=no) then it would be by region, age, etc. within the test and control group. The reason I ask is one article I found states this:   "PROC MULTTEST does not provide closed tests, and therefore, caution is urged, in the following situations: • Multiple comparisons of means involving three or more groups, using permutation resampling • Multiple comparisons of binary variables involving three or more groups. " https://pdfs.semanticscholar.org/93cd/57288bc50ce9bc99aef89f7cab9f61bc3bbb.pdf   And I'm worried my data might fit that? Unless, I am just thinking about my data backwards. Like I said new to this statistical procedures, so need to read more about the test itself.   Thanks again. ... View more