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!
Have you tried proc json?
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.
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.
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!
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" ] ]
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!
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?
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.
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;
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)), ": {");
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!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: