BookmarkSubscribeRSS Feed
slottemc
Obsidian | Level 7

Hello, we received a request from IT to start delivering our files in JSON format. Problem is they said they don't know how to do it 100% correctly from SAS (we have 9.4) and we've never worked with JSON before. They gave us this starter code (Attached on second tab), but said it outputs the containers in the wrong sort order. Meaning, if I have one record with John Doe and another with Jane Doe plus their respective information. The output would be container 1: John, Jane, container 2: John, Jane, container 3: John Jane. However, IT said they need it to be all John's information first then Jane's information.

 

Attached is sample data and the sample code provided by IT. I had to break the sample data up into three tables:member, deliver, and additional to make their code work. Also code provided below. 

 

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;

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;
run;



PROC JSON out="C:\Desktop\JSON_TEST.json" pretty nosastags;
  write values "Source_ID";
   write open array;
    export member;
      write close;
   write values "delivery_info";
    write open array;
      export delivery;
      write close;
   write values "email";
     write open array;
        export additional;
        write close;
        run;

 

Is there a way to do this?

10 REPLIES 10
Tom
Super User Tom
Super User

Please post code as text, not via attachments.  Use the Insert Code or Insert SAS Code button on the forum editor.

 

Post sample data as a simple data step that recreates the data from text lines.

slottemc
Obsidian | Level 7
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;

 

BillM_SAS
SAS Employee

Additional information is needed. The PROC JSON code supplied does not work with the DATA step supplied as the referenced data sets (source1, delivery1, & email) are not defined. Please supply the JSON output you desire from the json_test data set.

slottemc
Obsidian | Level 7

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;
Tom
Super User Tom
Super User

Is this the output you want?

{
  "Source_ID": [
    {
      "Contract": "ABC123",
      "PartyID": 56789,
      "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_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"
    }
  ]
}
slottemc
Obsidian | Level 7

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"
}
]
]
]
}
]

BillM_SAS
SAS Employee

Take a look at the code I wrote for a proof-of-concept for creating hierarchical JSON from SAS data sets. This code should give you a way to avoiding having the unique ID in each of the low-level JSON containers.

BillM_SAS
SAS Employee

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"
    }
  }
]
slottemc
Obsidian | Level 7

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. 

slottemc
Obsidian | Level 7

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" } } ]
}

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1088 views
  • 2 likes
  • 3 in conversation