topic Re: How to wirte ALLDATA data set back to JSON in SAS Programming

How to wirte ALLDATA data set back to JSON

ST9 — Fri, 16 Jul 2021 07:07:52 GMT

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!

Re: How to wirte ALLDATA data set back to JSON

ChrisNZ — Fri, 16 Jul 2021 08:23:29 GMT

Have you tried proc json?

Re: How to wirte ALLDATA data set back to JSON

ST9 — Fri, 16 Jul 2021 08:27:10 GMT

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.

Re: How to wirte ALLDATA data set back to JSON

ChrisNZ — Fri, 16 Jul 2021 08:41:37 GMT

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.

Re: How to wirte ALLDATA data set back to JSON

ST9 — Fri, 16 Jul 2021 09:27:55 GMT

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!

Re: How to wirte ALLDATA data set back to JSON

Ksharp — Fri, 16 Jul 2021 12:18:53 GMT

Data step is most powerful tool to write json .

Re: How to wirte ALLDATA data set back to JSON

ST9 — Sat, 17 Jul 2021 05:11:32 GMT

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

Re: How to wirte ALLDATA data set back to JSON

ChrisNZ — Sat, 17 Jul 2021 07:47:32 GMT

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.

Re: How to wirte ALLDATA data set back to JSON

Patrick — Sat, 17 Jul 2021 08:23:21 GMT

@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.

Re: How to wirte ALLDATA data set back to JSON

ST9 — Sat, 17 Jul 2021 08:40:30 GMT

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!

Re: How to wirte ALLDATA data set back to JSON

Tom — Sat, 17 Jul 2021 17:00:08 GMT

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?

Re: How to wirte ALLDATA data set back to JSON

BillM_SAS — Mon, 19 Jul 2021 14:00:00 GMT

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.

Re: How to wirte ALLDATA data set back to JSON

ST9 — Fri, 30 Jul 2021 06:20:34 GMT

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;

Re: How to wirte ALLDATA data set back to JSON

ChrisNZ — Fri, 30 Jul 2021 07:56:19 GMT

Well done!

Notes:

1. You can write

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

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), '"', ": {");

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