JSON Export with "type" and "value" requirements

[ddavies]

I am working to export a SAS dataset into a specific JSON format due to the system requirements that will be reading the JSON. 

When I use 

PROC JSON out='\\Output Data\File_202207' pretty;
EXPORT CME_final;
run;

it exports as this:

{
"SASJSONExport": "1.0 PRETTY",
"SASTableData+CME_final": [
{
"type":"Agency"
"value": 28,
"Client": "99999",
"Type": "ohr_initial",
"Start Date": "07/29/2022 09:00:00 AM",
"End Date": "07/29/2022 09:00:00 AM",
"Duration (hh:mm)": "0",
"Diagnosis Treated": "XXX",
"NPI": "9999999999",
"Completed By": "99999",
"Place of Service Code": "11",
"Is Telehealth": "1",
"Service Track": "",
"ProgramProvidingService": "ohrs",
"Program Enrollment": "",
"Facility Providing Service": "25",
"Is Initial Contact?": "0",
"Is Crisis": "0",
"Discharge Planning": "0",
"Activity Type": "Phone Call",
"Portal ID": "de839c8c-1b61-4c68-907f-652c3c99b00a"
}
]
}

The system I am working with has indicated the format needs to look like this where "type":"variable Name", "value":"variable value" is there for every entry

{
"method":"startautomation",
"methodParameters":[
{
"type":"IS_API_CALL",
"value":"1"
}
],
"automationKey":"ohr",
"version":"2.0",
"detail":[
{
"id":null,
"foreignKeyID":null,
"action":"ADD",
"keyValue":null,
"data":[
{
"type":"Agency",
"value":"28"
},
{
"type":"Client",
"value":"99999"
},
{
"type":"Type",
"value":"ohr_initial"
},
{
"type":"Start Date",
"value":"07/05/2022 09:00:00 AM"
},
{
"type":"End Date",
"value":"07/05/2022 09:00:00 AM"
},
{
"type":"Duration (hh:mm)",
"value":"0"
},
{
"type":"Diagnosis Treated",
"value":"XXX"
},
{
"type":"Completed By",
"value":"99999"
},
{
"type":"Place of Service Code",
"value":"11"
},
{
"type":"Is Telehealth",
"value":"1"
},
{
"type":"Service Track",
"value":""
},
{
"type":"Program Providing Service",
"value":"ohrs"
},
{
"type":"Program Enrollment",
"value":""
},
{
"type":"Facility Providing Service",
"value":"25"
},
{
"type":"Is Initial Contact?",
"value":"0"
},
{
"type":"Is Crisis",
"value":"0"
},
{
"type":"Discharge Planning",
"value":"0"
},
{
"type":"Activity Type",
"value":"Face to Face"
},
{
"type":"Portal ID",
"value":"2a229325-e425-402c-a760-bfefa354b1cb"
}
],
"subData":[

]
}
]
}

Is there a way in SAS to include the "type" and "value" specifications? I have done a lot of reading and scouring the internet but haven't found an example like this...

Thanks in advance for your help!

[ballardw]

Look at the Example 4: Controlling JSON Containers and Writing Values in the proc json documentation and see if that gets what you want. It will require a bit of work on your part to designate which levels of variables are which containers.

You should paste such things as generated text into a text box opened on the forum with the </> icon that appears above the message window instead of in block quotes. The message windows reformat the text including in quotes.

We might spend some time getting to code to generate exactly what you show in the desired result only to find that required indentation was not used because your example doesn't show it.

[ddavies]

Sorry, here is the JSON exports using the </> feature

Current:

{
  "SASJSONExport": "1.0 PRETTY",
  "SASTableData+CMEBILLING_C": [
    {
      "Agency": "28",
      "Client": "999999",
      "Type": "ohr_initial",
      "Start Date": "07/29/2022 09:00:00 AM",
      "End Date": "07/29/2022 09:00:00 AM",
      "Duration (hh:mm)": "0",
      "Diagnosis Treated": "XXX",
      "NPI": "9999999999",
      "Completed By": "999999",
      "Place of Service Code": 11,
      "Is Telehealth": 1,
      "Service Track": "",
      "ProgramProvidingService": "ohrs",
      "Program Enrollment": "",
      "Facility Providing Service": "25",
      "Is Initial Contact?": 0,
      "Is Crisis": 0,
      "Discharge Planning": 0,
      "Activity Type": "Phone Call",
      "Portal ID": "de839c8c-1b61-4c68-907f-652c3c99b00a"
    }
]
}

Required:

{
   "method":"startautomation",
   "methodParameters":[
      {
         "type":"IS_API_CALL",
         "value":"1"
      }
   ],
   "automationKey":"oh_rise",
   "version":"2.0",
   "detail":[
      {
         "id":null,
         "foreignKeyID":null,
         "action":"ADD",
         "keyValue":null,
         "data":[
            {
               "type":"Agency",
               "value":"28"
            },
            {
               "type":"Client",
               "value":"999999"
            },
            {
               "type":"Type",
               "value":"ohr_initial"
            },
            {
               "type":"Start Date",
               "value":"07/05/2022 09:00:00 AM"
            },
            {
               "type":"End Date",
               "value":"07/05/2022 09:00:00 AM"
            },
            {
               "type":"Duration (hh:mm)",
               "value":"0"
            },
            {
               "type":"Diagnosis Treated",
               "value":"XXX"
            },
            {
               "type":"Completed By",
               "value":"999999"
            },
            {
               "type":"Place of Service Code",
               "value":"11"
            },
            {
               "type":"Is Telehealth",
               "value":"1"
            },
            {
               "type":"Service Track",
               "value":""
            },
            {
               "type":"Program Providing Service",
               "value":"ohrs"
            },
            {
               "type":"Program Enrollment",
               "value":""
            },
            {
               "type":"Facility Providing Service",
               "value":"25"
            },
            {
               "type":"Is Initial Contact?",
               "value":"0"
            },
            {
               "type":"Is Crisis",
               "value":"0"
            },
            {
               "type":"Discharge Planning",
               "value":"0"
            },
            {
               "type":"Activity Type",
               "value":"Face to Face"
            },
            {
               "type":"Portal ID",
               "value":"2a229325-e425-402c-a760-bfefa354b1cb"
            }
         ],
         "subData":[
            
         ]
      }
   ]
}

[Tom]

Your probably need to first transpose the data into TYPE/VALUE varaibles.

So if you data looks something like the XLSX file you posted:

data have ;
  infile datalines dsd dlm='|' truncover;
  input 
    Agency Client Type :$11. Start_Date End_Date Duration
    Diagnosis_Treated :$3. NPI Completed_By Place_of_Service_Code
    Is_Telehealth Service_Track :$1. ProgramProvidingService :$4.
    Program_Enrollment :$1. Facility_Providing_Service
    Is_Initial_Contact Is_Crisis Discharge_Planning
    Activity_Type :$10. Portal_ID :$36.
  ;
  format Start_Date End_Date mdyampm19. ;
  label 
    Start_Date='Start Date' 
    End_Date='End Date'
    Duration='Duration (hh:mm)' 
    Diagnosis_Treated='Diagnosis Treated'
    Completed_By='Completed By'
    Place_of_Service_Code='Place of Service Code'
    Is_Telehealth='Is Telehealth' 
    Service_Track='Service Track'
    Program_Enrollment='Program Enrollment'
    Facility_Providing_Service='Facility Providing Service'
    Is_Initial_Contact='Is Initial Contact?' 
    Is_Crisis='Is Crisis'
    Discharge_Planning='Discharge Planning'
    Activity_Type='Activity Type' 
    Portal_ID='Portal ID'
  ;
datalines4;
28|111111|ohr_initial|1974704400|1974704400|0|XXX|1234567890|88888|11|1||ohrs||25|0|0|0|Phone Call|de839c8c-1b61-4c68-907f-652c3c99b00a
28|222222|ohr_ftf|1974704400|1974704400|0|YYY|1234567890|88888|11|1||ohrs||25|0|0|0||cf97601c-fa16-409c-9b33-a9af70f2027b
28|333333|ohr_initial|1972630800|1972630800|0|ZZZ|9999999999|99999|11|1||ohrs||25|0|0|0|Phone Call|ae27708f-1965-41dd-a117-bb96bebb3b49
28|444444|ohr_initial|1973840400|1973840400|0|XXX|1112223334|77777|11|1||ohrs||25|0|0|0|Phone Call|822bded5-8c54-474b-ba3a-683ce1af00df
;;;;

Then you can use PROC TRANSPOSE to convert it.  You might want to add some more processing to clean up.

proc transpose data=have out=tall ;
  by client;
  var _all_;
run;

data for_json ;
  set tall;
  _label_ = coalescec(_label_,_name_);
  col1=left(col1);
  rename _label_=type col1=value;
run;

Now you have something that looks like the structure they want.

[Tom]

It is hard to tell what your SAS dataset looks like.  Can you show the data as you have it?

And clarify what values from the dataset need to show up in what parts of the JSON file.

You will probably find it will be easier to write the JSON with a data step than trying to do it into PROC JSON syntax.

[ddavies]

Sample Data attached

[Tom]

---

@ddavies wrote:

Sample Data attached

---

That looks like a REPORT, not a dataset.  First it is an XSLX file, not a SAS program to create a SAS dataset.  And the column headers in the report look like variable labels and not actual variable names.

Not knowing the names and types of the variables makes it very hard to write any code.  For example if you wanted to write code that used an ARRAY the variables in the array would need to all be of the same TYPE (numeric or character).

[ddavies]

Apologies, I was just trying to give a sample. The SAS program is extremely nuanced and joins lots of different data sets together to create the final data set before the JSON export. Since the system that is absorbing the JSON file has very specific naming conventions for the variables, I used options validvarname=any; and wrote the variable names exactly as they need to be exported to the JSON. All the variables are character variables per the requirements of the system bringing in the JSON file. Not sure if it is helpful, but this is the last step before I do the proc json.

proc sql;
create table CME_final as 
select a.Agency,
	   a.Client,
	   b.Event_Code as Type,
	   a.StartDateTime as 'Start Date'n,
	   a.EndDateTime as 'End Date'n,
	   '0' as 'Duration (hh:mm)'n,
	   a.DiagnosisTreated as 'Diagnosis Treated'n,
	   a.NPI,
	   c.id_number as 'Completed By'n,
	   a.PlaceofServiceCode as 'Place of Service Code'n,
	   case when a.TelehealthModifiera = 'GT' then '1' else '0' end as 'Is Telehealth'n,
	   '' as 'Service Track'n,
	   "ohrs" as ProgramProvidingService,
	   '' as 'Program Enrollment'n,
	   '25' as 'Facility Providing Service'n,
	   b.Is_Initial_Contact as 'Is Initial Contact?'n,
	   b.Is_Crisis as 'Is Crisis'n,
	   b.Discharge_Planning as 'Discharge Planning'n,
	   b.Activity_Type as 'Activity Type'n,
	   a.UniqueRecordID as 'Portal ID'n
from CME_b as a left join JSONMapping as b 		on a.ProcedureCode = b.ProcedureCode and a.ProcedureModifier = b.ProcedureModifier
					   left join EvolvNPIMapping as c	on a.NPI = c.ibhis_code;
quit;

The JSON export from SAS gives me for each record:

"Variable1":"record1value",
"Variable2":"record1value",

...

"Variable18":"record1value"

The requirements I am receiving from the system that will be reading the JSON say the format needs to be:

{

"type":"Variable1",

"value":"record1value"

},

{

"type":"Variable2",

"value":"record1value"

},

...

{

"type":"Variable18",

"value":"record1value"

}

I am just having trouble figuring out how to put the word "type" before every variable and "value" before each record's value. 

I played with the containers as suggested by ballardw but with no luck. 

I am curious now about trying writing the JSON with a data step instead of the PROC JSON syntax. I will try and do some reading and research on that approach.  

[BillM_SAS]

Using the code supplied by @Tom to create a data set with the data in type/value format, I’ve created a JSON procedure program to produce the example results you specified.

data c111111;
set for_json;
where client=111111;
run;

proc json out=".\client.json" pretty nosastags;
  write open object;
    write values method startautomation;
	write value methodParameters;
	write open array;
	  write open object;
	    write values type IS_API_CALL;
		write values value "1";
        write close; /* methodParameters object */
      write close; /* methodParameters array */
	  write values automationKey oh_rise;
      write values version "2.0";
      write value detail;
	  write open array;
	    write open object;
		  write values id null;
          write values foreignKeyID null;
          write values action ADD;
          write values keyValue null;
          write values data;
		  write open array;
            export c111111(drop=client _name_);
		    write close; /* data array */
	      write close; /* detail object */
	  write close; /* detail array */
    write close; /* outermost object */
run;

Of course, this program only produces the JSON output for the client #111111 as shown in your example. To make the JSON procedure program more dynamic, you will need to modify it to use data sets specific to the client number. I have previously written a proof-of-concept program showing how to use the JSON procedure to produce hierarchical JSON output from data sets. You can use the above program as a template for the dynamic version of the program producing output for all the clients.

[ddavies]

Thank you so much Bill!! This worked PERFECT to get all the text formatted properly at the top. 

I think I am doing a poor job articulating the main issue I was having with the "type" and "value" requirements so hoping this picture of the 2 JSONs side-by-side will help. 

The one on the left is what the system admin is telling me they need to be able to read the JSON and the one on the right is what I am able to export from SAS (and other online JSON converters if I use a csv to JSON converter). I have never seen a JSON formatted the way they are saying it has to be formatted. Open to any advice or thoughts on how to accomplish that formatting.

[Tom]

Not sure why you are having trouble seeing the difference.  The "data" object is totally different in the two photographs.

The one on the left has a multiple observations of just two variables each.  The one on the right has just one observation with multiple variables.

[ddavies]

I can see the difference, I just cant replicate the difference. Both exports are the same single record. The one on the left labels the variables as "type" and the record values as "value" but the one on the right does not include those labels. There are 20 unique variables on the left just as there are on the right, the one on the left just labels the individual variables and corresponding values.

[Tom]

JSON files are just text.  The SAS data step can write text files very easily.

It is not clear how your JSON structure gets modified when there are multiple "clients" or where the values for that plethora of other fields comes from, but writing the name/value pairs is trivial.  Just transpose your original data and use that as the source.

proc transpose data=have out=tall;
  var _all_;
run;

data _null_;
  set tall end=eof;
  by client;
  file json ;
  if _n_=1 then put '/* all the stuff before the list of clients */';
  if first.client then put '/* all the stuff before the "data" tag*/'
   / ',"data":'
   / ' [' @
  ;
  else put ' ,' @ ;
  _label_=coalescec(_label_,_name_);
  col1=left(col1);
  put '{"type":' _label_ :$quote. ',"value":' col1 :$quote. '}';
  if last.client then put ' ]'
   / '/* all the stuff after the "data" tag*/'
  ;
  if eof then put '/*all the stuff after the list of clients*/';
run;

Result:

/* all the stuff before the list of clients */
/* all the stuff before the "data" tag*/
,"data":
 [{"type":"Agency" ,"value":"28" }
 ,{"type":"Client" ,"value":"111111" }
 ,{"type":"Type" ,"value":"ohr_initial" }
 ,{"type":"Start Date" ,"value":"7/29/2022  9:00 AM" }
 ,{"type":"End Date" ,"value":"7/29/2022  9:00 AM" }
 ,{"type":"Duration (hh:mm)" ,"value":"0" }
 ,{"type":"Diagnosis Treated" ,"value":"XXX" }
 ,{"type":"NPI" ,"value":"1234567890" }
 ,{"type":"Completed By" ,"value":"88888" }
 ,{"type":"Place of Service Code" ,"value":"11" }
 ,{"type":"Is Telehealth" ,"value":"1" }
 ,{"type":"Service Track" ,"value":"" }
 ,{"type":"ProgramProvidingService" ,"value":"ohrs" }
 ,{"type":"Program Enrollment" ,"value":"" }
 ,{"type":"Facility Providing Service" ,"value":"25" }
 ,{"type":"Is Initial Contact?" ,"value":"0" }
 ,{"type":"Is Crisis" ,"value":"0" }
 ,{"type":"Discharge Planning" ,"value":"0" }
 ,{"type":"Activity Type" ,"value":"Phone Call" }
 ,{"type":"Portal ID" ,"value":"de839c8c-1b61-4c68-907f-652c3c99b00a" }
 ]
/* all the stuff after the "data" tag*/
/* all the stuff before the "data" tag*/
,"data":
 [{"type":"Agency" ,"value":"28" }
 ,{"type":"Client" ,"value":"222222" }
 ,{"type":"Type" ,"value":"ohr_ftf" }
 ,{"type":"Start Date" ,"value":"7/29/2022  9:00 AM" }
 ,{"type":"End Date" ,"value":"7/29/2022  9:00 AM" }
 ,{"type":"Duration (hh:mm)" ,"value":"0" }
 ,{"type":"Diagnosis Treated" ,"value":"YYY" }
 ,{"type":"NPI" ,"value":"1234567890" }
 ,{"type":"Completed By" ,"value":"88888" }
 ,{"type":"Place of Service Code" ,"value":"11" }
 ,{"type":"Is Telehealth" ,"value":"1" }
 ,{"type":"Service Track" ,"value":"" }
 ,{"type":"ProgramProvidingService" ,"value":"ohrs" }
 ,{"type":"Program Enrollment" ,"value":"" }
 ,{"type":"Facility Providing Service" ,"value":"25" }
 ,{"type":"Is Initial Contact?" ,"value":"0" }
 ,{"type":"Is Crisis" ,"value":"0" }
 ,{"type":"Discharge Planning" ,"value":"0" }
 ,{"type":"Activity Type" ,"value":"" }
 ,{"type":"Portal ID" ,"value":"cf97601c-fa16-409c-9b33-a9af70f2027b" }
 ]
/* all the stuff after the "data" tag*/
/* all the stuff before the "data" tag*/
,"data":
 [{"type":"Agency" ,"value":"28" }
 ,{"type":"Client" ,"value":"333333" }
 ,{"type":"Type" ,"value":"ohr_initial" }
 ,{"type":"Start Date" ,"value":"7/5/2022  9:00 AM" }
 ,{"type":"End Date" ,"value":"7/5/2022  9:00 AM" }
 ,{"type":"Duration (hh:mm)" ,"value":"0" }
 ,{"type":"Diagnosis Treated" ,"value":"ZZZ" }
 ,{"type":"NPI" ,"value":"9999999999" }
 ,{"type":"Completed By" ,"value":"99999" }
 ,{"type":"Place of Service Code" ,"value":"11" }
 ,{"type":"Is Telehealth" ,"value":"1" }
 ,{"type":"Service Track" ,"value":"" }
 ,{"type":"ProgramProvidingService" ,"value":"ohrs" }
 ,{"type":"Program Enrollment" ,"value":"" }
 ,{"type":"Facility Providing Service" ,"value":"25" }
 ,{"type":"Is Initial Contact?" ,"value":"0" }
 ,{"type":"Is Crisis" ,"value":"0" }
 ,{"type":"Discharge Planning" ,"value":"0" }
 ,{"type":"Activity Type" ,"value":"Phone Call" }
 ,{"type":"Portal ID" ,"value":"ae27708f-1965-41dd-a117-bb96bebb3b49" }
 ]
/* all the stuff after the "data" tag*/
/* all the stuff before the "data" tag*/
,"data":
 [{"type":"Agency" ,"value":"28" }
 ,{"type":"Client" ,"value":"444444" }
 ,{"type":"Type" ,"value":"ohr_initial" }
 ,{"type":"Start Date" ,"value":"7/19/2022  9:00 AM" }
 ,{"type":"End Date" ,"value":"7/19/2022  9:00 AM" }
 ,{"type":"Duration (hh:mm)" ,"value":"0" }
 ,{"type":"Diagnosis Treated" ,"value":"XXX" }
 ,{"type":"NPI" ,"value":"1112223334" }
 ,{"type":"Completed By" ,"value":"77777" }
 ,{"type":"Place of Service Code" ,"value":"11" }
 ,{"type":"Is Telehealth" ,"value":"1" }
 ,{"type":"Service Track" ,"value":"" }
 ,{"type":"ProgramProvidingService" ,"value":"ohrs" }
 ,{"type":"Program Enrollment" ,"value":"" }
 ,{"type":"Facility Providing Service" ,"value":"25" }
 ,{"type":"Is Initial Contact?" ,"value":"0" }
 ,{"type":"Is Crisis" ,"value":"0" }
 ,{"type":"Discharge Planning" ,"value":"0" }
 ,{"type":"Activity Type" ,"value":"Phone Call" }
 ,{"type":"Portal ID" ,"value":"822bded5-8c54-474b-ba3a-683ce1af00df" }
 ]
/* all the stuff after the "data" tag*/
/*all the stuff after the list of clients*/

Note: I hate the ugly style that most JSON formatters use where the continuation characters (commas usually) are hidden at the end of the line where it is hard for humans to see them.  But putting the continuation characters at the start of the next line works just fine since the white space like line breaks are ignored.

It is also not hard to replace the empty strings with the JSON keyword null. Just split the PUT statement into multiple statements.

  put '{"type":' _label_ :$quote. ',"value":' @;
  if missing(col1) then put 'null' @;
  else put col1 :$quote. '@';
  put '}';

[BillM_SAS]

I used the data set that @Tom created so that your original data is in the desired type/value format. Once in this state, the program I posted started by isolating in a data set the data for the client matching "111111". This data set was then used in the JSON procedure EXPORT statement to include the client "111111" data in the JSON output you specified. The program output pretty much matches the left side JSON output you posted (except for the empty "subData" array at the bottom - which is easy to add). These programs demonstrate how to take the original data to produce what I understand to be what you desire. 

PROGRAM OUTPUT:

{
  "method": "startautomation",
  "methodParameters": [
    {
      "type": "IS_API_CALL",
      "value": "1"
    }
  ],
  "automationKey": "oh_rise",
  "version": "2.0",
  "detail": [
    {
      "id": null,
      "foreignKeyID": null,
      "action": "ADD",
      "keyValue": null,
      "data": [
        {
          "type": "Agency",
          "value": "28"
        },
        {
          "type": "Client",
          "value": "111111"
        },
        {
          "type": "Type",
          "value": "ohr_initial"
        },
        {
          "type": "Start Date",
          "value": "7/29/2022  9:00 AM"
        },
        {
          "type": "End Date",
          "value": "7/29/2022  9:00 AM"
        },
        {
          "type": "Duration (hh:mm)",
          "value": "0"
        },
        {
          "type": "Diagnosis Treated",
          "value": "XXX"
        },
        {
          "type": "NPI",
          "value": "1234567890"
        },
        {
          "type": "Completed By",
          "value": "88888"
        },
        {
          "type": "Place of Service Code",
          "value": "11"
        },
        {
          "type": "Is Telehealth",
          "value": "1"
        },
        {
          "type": "Service Track",
          "value": ""
        },
        {
          "type": "ProgramProvidingService",
          "value": "ohrs"
        },
        {
          "type": "Program Enrollment",
          "value": ""
        },
        {
          "type": "Facility Providing Service",
          "value": "25"
        },
        {
          "type": "Is Initial Contact?",
          "value": "0"
        },
        {
          "type": "Is Crisis",
          "value": "0"
        },
        {
          "type": "Discharge Planning",
          "value": "0"
        },
        {
          "type": "Activity Type",
          "value": "Phone Call"
        },
        {
          "type": "Portal ID",
          "value": "de839c8c-1b61-4c68-907f-652c3c99b00a"
        }
      ]
    }
  ]
}