topic Re: Exporting Nested Tables to JSON in Developers

Exporting Nested Tables to JSON

sc31415 — Fri, 05 Feb 2021 11:23:43 GMT

Hi all,

I'm having a little trouble exporting data to JSON. Here's the data I'm trying to export, in the structure that I'm trying to replicate:

{
    "Owner": [
        {
            "Cars": [
                {
                    "Make":"Manufacturer 1"
                },
                {
                    "Make":"Manufacturer 2"
                }
            ],
            "Name":"SAS Man",
            "Age":"20"
        }
    ]
}

I can read that into SAS with a JSON libname and copied into work no problem. This is the form of the SAS tables that I'm trying to export. The problem is trying to export it back into the same structure. Here's the code I have at the moment:

proc json out=outjson pretty nosastags;
    write open object; write values "Owner";
        write open array; /* I know this is superfluous. I forgot to take it out for the MWE :S */
            write open object; write values "Cars";
                write open array;
                    export work.owner_cars(drop=ordinal_:);
                write close;
                write values "";
                export work.owner(drop=ordinal_:);
            write close;
        write close;
    write close;
run;
quit;

The problem is that this ends up with Owner being an object containing the cars array (good), but with a separate dummy object containing the other data in the owner table (bad):

{
    "Owner": [
        {
            "Cars": [
                {
                    "Make":"Manufacturer 1"
                },
                {
                    "Make":"Manufacturer 2"
                }
            ],
            "": {
                "Name":"SAS Man",
                "Age":"20"
            }
        }
    ]
}

Is there any way I can nest the work.owner_cars data inside the owner table? Perhaps using the map or alldata that was created as part of the JSON libname?

Re: Exporting Nested Tables to JSON

Tom — Fri, 05 Feb 2021 16:39:03 GMT

Just write the JSON yourself. Here is how to do it for your example.

options parmcards=json;
filename json temp;
parmcards4;
{
    "Owner": [
        {
            "Cars": [
                {
                    "Make":"Manufacturer 1"
                },
                {
                    "Make":"Manufacturer 2"
                }
            ],
            "Name":"SAS Man",
            "Age":"20"
        }
    ]
}
;;;;

libname json json ;

data for_print;
  merge json.owner json.owner_cars end=eof;
  by ordinal_owner ;
  file json2 ;
  if _n_=1 then put '{"Owner":' / ' [' ;
  if first.ordinal_owner then put
      '  {"Name": ' name :$quote.
    / '  ,"Age": ' age :$quote.
    / '  ,"Cars":'
    / '   [' @
  ;
  else put '   ,' @ ;
  put '{"Make": ' make $quote. '}' ;
  if last.ordinal_owner then put '   ]' / '  }' ;
  if eof then put ' ]' / '}' ;
run;

libname json2 json;
proc compare data=json.owner compare=json2.owner; run;
proc compare data=json.owner_cars compare=json2.owner_cars; run;

Here is the generated JSON2 file:

{"Owner":
 [
  {"Name": "SAS Man"
  ,"Age": "20"
  ,"Cars":
   [{"Make": "Manufacturer 1"}
   ,{"Make": "Manufacturer 2"}
   ]
  }
 ]
}

Note this will be harder if there is more than one sub list (the CARS list in this example) you want to generate.

Re: Exporting Nested Tables to JSON

BillM_SAS — Fri, 05 Feb 2021 16:33:13 GMT

The PROC JSON EXPORT statement puts each observation of the data set data into a JSON container. Whether the containers are JSON objects or JSON arrays depends on the JSON KEYS argument. Specifying the KEYS argument outputs each observation in a JSON object and NOKEYS outputs to JSON arrays. Your desired JSON shows sending the observations to an already open JSON object. As you have seen, you will not be able to do that with an EXPORT statement.

To use PROC JSON and get the desired output, I would recommend using a DATA Step to create the PROC JSON code and then run the generated program. The advantage of using generated PROC JSON code is that that PROC JSON handles producing correct JSON syntax.

Here is the SAS program that will produce the desired output based on your supplied JSON file having been read in via the JSON LIBNAME.

/*****************************
* Modifiable macro variables
******************************/
%let jsonProcCodeSpec=sasuser\jsonProcCode.sas;
%let jsonOutputSpec=sasuser\jsonOutput.txt;

%put &jsonProcCodeSpec;
/*****************************
* Constant macro variables
******************************/
%let stmtEnd=%STR(;);

/* Write the custom PROC JSON code based on the values in the data set. 
   This will be run after the DATA Step creates it. */
data _null_;
  /* specifies the output file for PUT statements                      */
  FILE "&jsonProcCodeSpec" DISK;
  set x.owner(rename=(name=owner_name)) end=last; 

  if _N_ eq 1 
    then do;
      /* Only on the first observation in the data set, write the required 
         initial statements to the JSON procedure code file.                */
      put "proc json pretty out=""&jsonOutputSpec"" nosastags &stmtEnd";
      put "    write open object &stmtEnd";
	  put "      write values ""Owner"" &stmtEnd";
	  put "        write open array &stmtEnd";
	  put "            write open object &stmtEnd";
	  put "              write values ""Cars"" &stmtEnd";
	  put "                write open array &stmtEnd";
	  put "                    export x.owner_cars(drop=ordinal_:) &stmtEnd";
	  put "                write close &stmtEnd";
	  end;
  /* Write the data set Name column to JSON */
  put "write value ""Name"" """owner_Name""" &stmtEnd";
  /* Write the data set Age column to JSON */
  put "write value ""Age"" " age "&stmtEnd";
  if last
    then do;
      /* Only on the last observation in the data set, write the required 
         final statements to the JSON procedure code file.                */
	  put "            write close &stmtEnd";
	  put "        write close &stmtEnd";
	  put "    write close &stmtEnd";
      put "run &stmtEnd";
    end;
run;

/* Now run the generated custom JSON procedure code to produce the 
   JSON formatted output file of the data set.                         */
%include "&jsonProcCodeSpec";

Re: Exporting Nested Tables to JSON

Tom — Fri, 05 Feb 2021 17:40:13 GMT

How could that work where there is more than one OWNER?

Re: Exporting Nested Tables to JSON

BillM_SAS — Fri, 05 Feb 2021 17:52:23 GMT

I produced what was asked. I hope that the technique I showed gives the customer a starting point from which to modify this most-likely simple case to whatever data the customer has.

Re: Exporting Nested Tables to JSON

sc31415 — Fri, 05 Feb 2021 17:54:42 GMT

Thanks @BillM_SAS for your solution.

+1 to @Tom . I forgot to say that there will be more than one owner.

I'll have a play with your solutions when I get back in to the office on Monday. Have a good weekend!