How to wirte ALLDATA data set back to JSON

[ST9]

Hello,

When using the JSON engine to read a JSON file, an ALLDATA data set was created. How can I convert the ALLDATA data set back to JSON?

Thanks!

[ChrisNZ]

Have you tried proc json?

[ST9]

Yes, I tried proc json with nosastags, nokeys, and pretty option. The sturcture/layout of the output file is completely different than the original JSON.

[ChrisNZ]

So is question is: how to export to JSON using a specific structure. Not how to export JSON.

Show us what you tried, and what the desired format looks like.

[ST9]

Sorry for the confusion.

The JSON I'd like to process:

{
  "group": {
    "prod": {
      "info": {
        "id": 13579,
        "sub": {
          "02468": {
            "description": "Description 1",
            "config": {
              "os": "windows"
            },
            "build": {
              "ver": "1133557799"
            },
            "checksums": {
              "hex": {
                "md5": "a8a64cef262a04de4872b68b63ab7cd8",
                "sha1": "d80a9e5ac1c9f4343d30f70f9f6c2be247cee375"
              }
            }
          }
        },
        "storage": {
          "quota": 22446688,
          "count": 999
        }
      }
    },
    "test": {
      "info": {
        "id": 13579,
        "sub": {
          "02468": {
            "description": "Description 2",
            "config": {
              "os": "unix"
            },
            "build": {
              "ver": "1133557799"
            },
            "checksums": {
              "hex": {
                "md5": "a8a64cef262a04de4872b68b63ab7cd8",
                "sha1": "d80a9e5ac1c9f4343d30f70f9f6c2be247cee375"
              }
            }
          }
        },
        "storage": {
          "quota": 22446688,
          "count": 999
        }
      }
    }
  }
}

Below is what've tried so far:

%let dir = %str(C:\Users\XXXXX\Desktop\Temp);

filename input "&dir\input.json";
libname input json;

data output;
    set input.alldata;
run;

proc json out="&dir\output.json" nosastags nokeys pretty;
   export output;
run;

By running the code above, I got:

[
  [
    1,
    "group",
    "",
    "",
    "",
    "",
    "",
    "",
    "",
    0,
    ""
  ],
  [
    2,
    "group",
    "prod",
    "",
    "",
    "",
    "",
    "",
    "",
    0,
    ""
  ],
  [
    3,
    "group",
    "prod",
    "info",
    "",
    "",
    "",
    "",
    "",
    0,
    ""
  ],
  [
    4,
    "group",
    "prod",
    "info",
    "id",
    "",
    "",
    "",
    "",
    1,
    "13579"
  ],
  [
    4,
    "group",
    "prod",
    "info",
    "sub",
    "",
    "",
    "",
    "",
    0,
    ""
  ],
  [
    5,
    "group",
    "prod",
    "info",
    "sub",
    "02468",
    "",
    "",
    "",
    0,
    ""
  ],
  [
    6,
    "group",
    "prod",
    "info",
    "sub",
    "02468",
    "description",
    "",
    "",
    1,
    "Description 1"
  ],
  [
    6,
    "group",
    "prod",
    "info",
    "sub",
    "02468",
    "config",
    "",
    "",
    0,
    ""
  ],
  [
    7,
    "group",
    "prod",
    "info",
    "sub",
    "02468",
    "config",
    "os",
    "",
    1,
    "windows"
  ],
  [
    6,
    "group",
    "prod",
    "info",
    "sub",
    "02468",
    "build",
    "",
    "",
    0,
    ""
  ],
  [
    7,
    "group",
    "prod",
    "info",
    "sub",
    "02468",
    "build",
    "ver",
    "",
    1,
    "1133557799"
  ],
  [
    6,
    "group",
    "prod",
    "info",
    "sub",
    "02468",
    "checksums",
    "",
    "",
    0,
    ""
  ],
  [
    7,
    "group",
    "prod",
    "info",
    "sub",
    "02468",
    "checksums",
    "hex",
    "",
    0,
    ""
  ],
  [
    8,
    "group",
    "prod",
    "info",
    "sub",
    "02468",
    "checksums",
    "hex",
    "md5",
    1,
    "a8a64cef262a04de4872b68b63ab7cd8"
  ],
  [
    8,
    "group",
    "prod",
    "info",
    "sub",
    "02468",
    "checksums",
    "hex",
    "sha1",
    1,
    "d80a9e5ac1c9f4343d30f70f9f6c2be247cee375"
  ],
  [
    4,
    "group",
    "prod",
    "info",
    "storage",
    "",
    "",
    "",
    "",
    0,
    ""
  ],
  [
    5,
    "group",
    "prod",
    "info",
    "storage",
    "quota",
    "",
    "",
    "",
    1,
    "22446688"
  ],
  [
    5,
    "group",
    "prod",
    "info",
    "storage",
    "count",
    "",
    "",
    "",
    1,
    "999"
  ],
  [
    2,
    "group",
    "test",
    "",
    "",
    "",
    "",
    "",
    "",
    0,
    ""
  ],
  [
    3,
    "group",
    "test",
    "info",
    "",
    "",
    "",
    "",
    "",
    0,
    ""
  ],
  [
    4,
    "group",
    "test",
    "info",
    "id",
    "",
    "",
    "",
    "",
    1,
    "13579"
  ],
  [
    4,
    "group",
    "test",
    "info",
    "sub",
    "",
    "",
    "",
    "",
    0,
    ""
  ],
  [
    5,
    "group",
    "test",
    "info",
    "sub",
    "02468",
    "",
    "",
    "",
    0,
    ""
  ],
  [
    6,
    "group",
    "test",
    "info",
    "sub",
    "02468",
    "description",
    "",
    "",
    1,
    "Description 2"
  ],
  [
    6,
    "group",
    "test",
    "info",
    "sub",
    "02468",
    "config",
    "",
    "",
    0,
    ""
  ],
  [
    7,
    "group",
    "test",
    "info",
    "sub",
    "02468",
    "config",
    "os",
    "",
    1,
    "unix"
  ],
  [
    6,
    "group",
    "test",
    "info",
    "sub",
    "02468",
    "build",
    "",
    "",
    0,
    ""
  ],
  [
    7,
    "group",
    "test",
    "info",
    "sub",
    "02468",
    "build",
    "ver",
    "",
    1,
    "1133557799"
  ],
  [
    6,
    "group",
    "test",
    "info",
    "sub",
    "02468",
    "checksums",
    "",
    "",
    0,
    ""
  ],
  [
    7,
    "group",
    "test",
    "info",
    "sub",
    "02468",
    "checksums",
    "hex",
    "",
    0,
    ""
  ],
  [
    8,
    "group",
    "test",
    "info",
    "sub",
    "02468",
    "checksums",
    "hex",
    "md5",
    1,
    "a8a64cef262a04de4872b68b63ab7cd8"
  ],
  [
    8,
    "group",
    "test",
    "info",
    "sub",
    "02468",
    "checksums",
    "hex",
    "sha1",
    1,
    "d80a9e5ac1c9f4343d30f70f9f6c2be247cee375"
  ],
  [
    4,
    "group",
    "test",
    "info",
    "storage",
    "",
    "",
    "",
    "",
    0,
    ""
  ],
  [
    5,
    "group",
    "test",
    "info",
    "storage",
    "quota",
    "",
    "",
    "",
    1,
    "22446688"
  ],
  [
    5,
    "group",
    "test",
    "info",
    "storage",
    "count",
    "",
    "",
    "",
    1,
    "999"
  ]
]

However, I'd like the output file to have the same structure as the original input file, which is:

{
  "group": {
    "prod": {
      "info": {
        "id": 13579,
        "sub": {
          "02468": {
            "description": "Description 1",
            "config": {
              "os": "windows"
            },
            "build": {
              "ver": "1133557799"
            },
            "checksums": {
              "hex": {
                "md5": "a8a64cef262a04de4872b68b63ab7cd8",
                "sha1": "d80a9e5ac1c9f4343d30f70f9f6c2be247cee375"
              }
            }
          }
        },
        "storage": {
          "quota": 22446688,
          "count": 999
        }
      }
    },
    "test": {
      "info": {
        "id": 13579,
        "sub": {
          "02468": {
            "description": "Description 2",
            "config": {
              "os": "unix"
            },
            "build": {
              "ver": "1133557799"
            },
            "checksums": {
              "hex": {
                "md5": "a8a64cef262a04de4872b68b63ab7cd8",
                "sha1": "d80a9e5ac1c9f4343d30f70f9f6c2be247cee375"
              }
            }
          }
        },
        "storage": {
          "quota": 22446688,
          "count": 999
        }
      }
    }
  }
}

Thanks a lot!

[ST9]

Thank you, and sorry for not describing the question clearly. Below is the JSON file I'd like to process:

{
  "group": {
    "prod": {
      "info": {
        "id": 13579,
        "sub": {
          "02468": {
            "description": "Description 1",
            "config": {
              "os": "windows"
            },
            "build": {
              "ver": "1133557799"
            },
            "checksums": {
              "hex": {
                "md5": "a8a64cef262a04de4872b68b63ab7cd8",
                "sha1": "d80a9e5ac1c9f4343d30f70f9f6c2be247cee375"
              }
            }
          }
        },
        "storage": {
          "quota": 22446688,
          "count": 999
        }
      }
    }
  }
}

I would like to have the output file to have the same sturcture as the input file above. I've tried the following code:

%let dir = %str(C:\Users\XXXXXX\Temp);

filename input "&dir\input.json";
libname input json;

data output;
    set input.alldata;
run;

proc json out="&dir\output.json" nosastags nokeys pretty;
   export output;
run;

However, the result I was getting is:

[
  [
    1,
    "group",
    "",
    "",
    "",
    "",
    "",
    "",
    "",
    0,
    ""
  ],
  [
    2,
    "group",
    "prod",
    "",
    "",
    "",
    "",
    "",
    "",
    0,
    ""
  ],
  [
    3,
    "group",
    "prod",
    "info",
    "",
    "",
    "",
    "",
    "",
    0,
    ""
  ],
  [
    4,
    "group",
    "prod",
    "info",
    "id",
    "",
    "",
    "",
    "",
    1,
    "13579"
  ],
  [
    4,
    "group",
    "prod",
    "info",
    "sub",
    "",
    "",
    "",
    "",
    0,
    ""
  ],
  [
    5,
    "group",
    "prod",
    "info",
    "sub",
    "02468",
    "",
    "",
    "",
    0,
    ""
  ],
  [
    6,
    "group",
    "prod",
    "info",
    "sub",
    "02468",
    "description",
    "",
    "",
    1,
    "Description 1"
  ],
  [
    6,
    "group",
    "prod",
    "info",
    "sub",
    "02468",
    "config",
    "",
    "",
    0,
    ""
  ],
  [
    7,
    "group",
    "prod",
    "info",
    "sub",
    "02468",
    "config",
    "os",
    "",
    1,
    "windows"
  ],
  [
    6,
    "group",
    "prod",
    "info",
    "sub",
    "02468",
    "build",
    "",
    "",
    0,
    ""
  ],
  [
    7,
    "group",
    "prod",
    "info",
    "sub",
    "02468",
    "build",
    "ver",
    "",
    1,
    "1133557799"
  ],
  [
    6,
    "group",
    "prod",
    "info",
    "sub",
    "02468",
    "checksums",
    "",
    "",
    0,
    ""
  ],
  [
    7,
    "group",
    "prod",
    "info",
    "sub",
    "02468",
    "checksums",
    "hex",
    "",
    0,
    ""
  ],
  [
    8,
    "group",
    "prod",
    "info",
    "sub",
    "02468",
    "checksums",
    "hex",
    "md5",
    1,
    "a8a64cef262a04de4872b68b63ab7cd8"
  ],
  [
    8,
    "group",
    "prod",
    "info",
    "sub",
    "02468",
    "checksums",
    "hex",
    "sha1",
    1,
    "d80a9e5ac1c9f4343d30f70f9f6c2be247cee375"
  ],
  [
    4,
    "group",
    "prod",
    "info",
    "storage",
    "",
    "",
    "",
    "",
    0,
    ""
  ],
  [
    5,
    "group",
    "prod",
    "info",
    "storage",
    "quota",
    "",
    "",
    "",
    1,
    "22446688"
  ],
  [
    5,
    "group",
    "prod",
    "info",
    "storage",
    "count",
    "",
    "",
    "",
    1,
    "999"
  ]
]

[ChrisNZ]

Your input file is highly hierarchical. I have no idea how this is imported in SAS as a table (and can't test as my version is tool old to support JSON), but unless traces of the hierarchy are somehow kept in the non-hierarchical SAS table, there is no way to determine what the hierarchy should look like. Maybe someone who has used the JSON engine in SAS can help, otherwise you might have to recreate the hierarchy programmatically.

[Patrick]

@ChrisNZ Below how the alldata table looks like after import of the json via the libname json engine. The pictures shows all rows created (=the closing bit is not in the table).

I'm with @Ksharp that a data step is best suited to recreate the source json structure from such a table.

[ST9]

I was thinking that if SAS has offered an easy way to read and import a certain type of data, then there should also be an easy way to export the imported data to its original state. I've looked over the documentation but no luck, not sure if I missed something... Maybe I should try the data step instead.

Thanks all!

[Tom]

SAS use normal relational tables.  Not the nested stuff the JSON allows.

You should look into seeing if you can just use a DATA step to convert the ALLDATA dataset into a JSON file.

What is the change you are making to the data?  Perhaps you can just make the change in the JSON text itself?

[BillM_SAS]

You can programmatically create the desired output in JSON format using PROC JSON. It will be more involved than what you initially tried. I wrote a proof-of-concept program that shows how to create hierarchical JSON formatted output from a SAS data set. You should be able to use the concepts displayed in the program to produce the output you desire. One advantage of the JSON procedure is that it automatically produces syntactically correct JSON from the supplied data. 

[ST9]

Thanks!

I was able to recreate the JSON with data steps. The code may not be concise enough, but at least it works...

%let dir=%str(C:\Users\XXXXX\Desktop\Temp);
filename input "&dir\input.json";
libname input json;

proc sql noprint;
    create table alldata as
        select *,
            case
                when Value='' then '' 
                when compress(Value, ,'kd')=Value then 'num'
                else 'char'
            end as ValueType,
            monotonic() as OID
        from input.alldata;

    select max(p) into :p_max
        from input.alldata;
quit;

* some code here to update/modify the ALLDATA dataset;

proc sort data=alldata out=temp;
    by desending OID;
run;

data temp;
    set temp;
    InsRow=P-lag(P);

    if InsRow le 0 then
        InsRow=.;

    if _n_=1 then
        InsRow=P;
run;

proc sort data=temp out=temp(drop=OID);
    by OID;
run;

%macro InsRow;

    data temp;
        set temp(obs=1) temp;

        if _n_=1 then
            call missing(of _all_);

        output;

        %do i=1 %to &p_max;
            if InsRow=&i then
                do;
                    call missing(of _all_);

                    %do j=1 %to &i;
                        output;
                    %end;
                end;
        %end;
    run;

%mend InsRow;

%InsRow;

data temp;
    set temp;

    if _n_=1 then
        Indent=0;
    else if P ne . then
        Indent=P;
    else Indent=Indent-1;
    retain Indent;

    OID=_n_;
run;

proc sort data=temp;
    by desending OID;
run;

data temp;
    set temp;
    Lead_Indent=lag(Indent);
run;

proc sort data=temp out=temp(drop=OID);
    by OID;
run;

%macro RebuildJSON;

    data temp;
        length JSON_output $640;
        set temp end=eof;

        Value=tranwrd(Value, '"', '\"');

        if _n_=1 then
            JSON_output=cat("$$", "{");
        else if eof then
            JSON_output=cat("$$", "}");

        %do i=1 %to &p_max;
        else if Indent=&i and V=0 then
            JSON_output=cat("$$", repeat("  ", &i-1), '"', strip(P&i), '"', ": {");
        else if Indent=&i and V=1 and ValueType="num" then
            JSON_output=cat("$$", repeat("  ", &i-1), '"', strip(P&i), '"', ": ", strip(Value));
        else if Indent=&i and V=1 and ValueType="char" then
            JSON_output=cat("$$", repeat("  ", &i-1), '"', strip(P&i), '"', ": ", '"', strip(Value), '"');
        else if Indent=&i and V=1 and ValueType="" then
            JSON_output=cat("$$", repeat("  ", &i-1), '"', strip(P&i), '"', ": ", '""');
        else if Indent=&i then
            JSON_output=cat("$$", repeat("  ", &i-1), "}");
        %end;

        if Indent=Lead_Indent then
            JSON_output=cat(strip(JSON_output), ",");
        else JSON_output=JSON_output;

        JSON_output=tranwrd(JSON_output, "{,", "{},");
    run;

%mend RebuildJSON;

%RebuildJSON;

data _null_;
    options nobomfile;
    file "&dir\output.json" encoding="utf-8";
    set temp(keep=JSON_output);
    JSON_output=tranwrd(JSON_output, "\", "\\");
    JSON_output=tranwrd(JSON_output, '\\"', '\"');
    put JSON_output $;
run;

[ChrisNZ]

Well done!

Notes:

1. You can write 

when compress(Value, ,'kd')=Value then 'num'

as

when compress(Value,'+-.','kd')=Value then 'num'

to capture decimals and negative numbers.

2. You can write 

  JSON_output=cat("$$", repeat("  ", &i-1), '"', strip(P&i), '"', ": {");

as

  JSON_output=cat("$$", repeat("  ", &i-1), quote(strip(P&i)), ": {");

[Ksharp]

Data step is most powerful tool to write json .