Using PROC JSON is it possible to create a hierarchical file such as:
https://gist.github.com/statgeek/4415868d4bf25ee27b4587e3e9bbb608
This particular file/format is used for a lot of the D3 examples, so being to generate a similar file would simplify this process a lot.
I've been working on this manually to try and get it created using PUT statements but haven't gotten very far and was hoping PROC JSON may be another option.
Thanks!
Thanks a lot to Bill's reply, we succeeded in creating the JSON file (and the interactive plot) after making a few changes to his code. On a side note, the code can be easily implemented to hierarchies more than 3 as well, since from hierarchy 2, the open and close macros are in the same form respectively. Please see below:
/********************************************************************
* Proof-of-concept code to demonstrate a method of using the JSON
* procedure available in SAS 9.4 to produce a hierarchical output file
* in JSON format from a SAS data set. This code specifically takes
* the Shoes data set from SASHLEP to create hierarchical output in
* the form:
*
* Region -+
* |
* +- Subsidiary -+
* |
* +- Product -+
* |
* +- <product data>
*
* Note that this code is not the most concise as some pieces are
* hardcoded to allow for easier understanding of the parts and
* resulting files. There are probably better ways to do some
* sections of the code. This code could also be modified to make
* it more easily handles other data sets.
*
* Modifications available in this code:
* * dataFields = The comma-separated names of the data set
* variables that make up the lowest level of
* the hierarchy.
* * h1 = The name of the data set variable that is the highest
* level of the hierarchy.
* * h2 = The name of the data set variable that is the second
* level of the hierarchy.
* * h3 = The name of the data set variable that is the third
* level of the hierarchy.
* * jsonOutputSpec = The location where the formatted JSON output
* will be written after running the custom
* JSON procedure code.
* * jsonProcCodeSpec = The location where the custom JSON
* procedure code is written. It is running
* of this generated file that will produce
* the JSON formatted output.
* * sourceDataSet = The library and member name of the SAS data
* set being written in JSON format.
*
* Author: Bill McNeill
* Company: SAS Institute
* Contact: Bill.McNeill@sas.com
* Date: 14 November 2017
* Modified by: Weiyu Qiu
* Company: Office of Statistics and Information (OSI), Alberta Treasury Board and Finance
* Date: 16 November 2017
********************************************************************/
/**********************************
* Restructure the data if necessary
**********************************/
data shoes;
retain world;
set sashelp.shoes;
world="World";
run;
/*****************************
* Modifiable macro variables
******************************/
%let jsonProcCodeSpec=...\jsonProcCode.sas;
%let jsonOutputSpec=...\jsonOutput_all.txt;
%let sourceDataSet=Shoes;
%let h1=world;
%let h2=region;
%let h3=subsidiary;
%let dataFields=product, sales;
/*****************************
* Constant macro variables
******************************/
%let stmtEnd=%STR(;);
%let byFieldsSQL=&h1, &h2, &h3;
%let byFieldsSDS=&h1 &h2 &h3;
/************************
*************************
* Macro functions *
*************************
************************/
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Macro function: createLowLevelDataSet
* Create the data set specific to each hierarchy.
* Parameters:
* count - The unique identification number for the data set.
* value1 - The first level hierarchy value.
* value2 - The second level hierarchy value.
* value3 - The third level hierarchy value.
* Results:
* Create a data set named WORK.ds_#
* where # is the unique identification number.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%macro createLowLevelDataSet(count, value1, value2, value3);
proc sql;
create table ds_&count as
select &dataFields
from &sourceDataSet
where &h1=&value1 and &h2=&value2 and &h3=&value3
;
quit;
data rnm_ds_&count;
retain product description sales;
set ds_&count;
description=product;
rename product=name sales=size;
run;
%mend createLowLevelDataSet;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Macro function: fileHeader
* Write the one-time JSON procedure statements at the start of
* the JSON procedure.
* Parameters:
* filePath - The file specification to where the JSON
* formatted output is written.
* Results:
* Output the first 2 lines of the JSON procedure to the file
* containing the JSON procedure code.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%macro fileHeader(filePath);
put "proc json pretty out=""" &filePath """ nosastags &stmtEnd";
put "write open object &stmtEnd /* open outermost object */";
%mend fileHeader;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Macro function: hierarchy1Open
* Write the JSON procedure statements to identify and open the
* JSON containers at the level 1 hierarchy.
* Parameters:
* None
* Results:
* Write the needed JSON statements to the file containing the
* JSON procedure code.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%macro hierarchy1Open();
put "write value ""name"" &stmtEnd";
valueStmt=CAT("write value """, TRIM(&h1), """ &stmtEnd");
put valueStmt;
put "write value ""description"" &stmtEnd";
valueStmt=CAT("write value """, TRIM(&h1), """ &stmtEnd");
put valueStmt;
put "write value ""children"" &stmtEnd";
put "write open array &stmtEnd /* open H1 array */";
%mend hierarchy1Open;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Macro function: hierarchy1Close
* Write the JSON procedure statements to close the JSON
* containers to close the level 1 hierarchy.
* Parameters:
* None
* Results:
* Write the needed JSON close statements to the file
* containing the JSON procedure code.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%macro hierarchy1Close();
put "write close &stmtEnd /* close H1 array */";
%mend hierarchy1Close;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Macro function: hierarchy2Open
* Write the JSON procedure statements to identify and open the
* JSON containers at the level 2 hierarchy.
* Parameters:
* None
* Results:
* Write the needed JSON statements to the file containing the
* JSON procedure code.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%macro hierarchy2Open();
put "write open object &stmtEnd /* open H1 object */ ";
put "write value ""name"" &stmtEnd";
valueStmt=CAT("write value """, TRIM(&h2), """ &stmtEnd");
put valueStmt;
put "write value ""description"" &stmtEnd";
valueStmt=CAT("write value """, TRIM(&h2), """ &stmtEnd");
put valueStmt;
put "write value ""children"" &stmtEnd";
put "write open array &stmtEnd /* open H2 array */";
%mend hierarchy2Open;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Macro function: hierarchy2Close
* Write the JSON procedure statements to close the JSON
* containers to close the level 2 hierarchy.
* Parameters:
* None
* Results:
* Write the JSON close statements to the file containing the
* JSON procedure code.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%macro hierarchy2Close();
put "write close &stmtEnd /* close H2 array */";
put "write close &stmtEnd /* close H1 object */";
%mend hierarchy2Close;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Macro function: hierarchy3Stmts
* Write the JSON procedure statements to identify the JSON
* containers that will hold the low-level hierarchy data.
* Parameters:
* None
* Results:
* Write the needed JSON statements to the file containing the
* JSON procedure code.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%macro hierarchy3Open();
put "write open object &stmtEnd /* open H2 object */ ";
put "write value ""name"" &stmtEnd";
valueStmt=CAT("write value """, TRIM(&h3), """ &stmtEnd");
put valueStmt;
put "write value ""description"" &stmtEnd";
valueStmt=CAT("write value """, TRIM(&h3), """ &stmtEnd");
put valueStmt;
put "write value ""children"" &stmtEnd /* open H3 array */";
%mend hierarchy3Open;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Macro function: hierarchy3Close
* Write the JSON procedure statements to close the JSON
* containers to close the level 2 hierarchy.
* Parameters:
* None
* Results:
* Write the JSON close statements to the file containing the
* JSON procedure code.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%macro hierarchy3Close();
put "write close &stmtEnd /* close H3 array */";
%mend hierarchy3Close;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Macro function: fileFooter
* Write the one-time JSON procedure statements at the end of
* the JSON procedure.
* Parameters:
* None
* Results:
* Output the last 2 lines of the JSON procedure to the file
* containing the JSON procedure code.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%macro fileFooter();
put "write close &stmtEnd /* close outermost object */";
put "run &stmtEnd";
%mend fileFooter;
/************************
*************************
* Support Statements *
*************************
************************/
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Create a data set, WORK.combos, containing the unique
* combinations of the 3 variables that constitute the levels
* of hierarchy.
*
* Note that this query produces a reduced data set in order to
* keep the size of the formatted JSON output manageable for
* review. To use the full data set, remove the WHERE statement
* from the query.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
proc sql;
create table work.combos as
select distinct &byFieldsSQL
from &sourceDataSet
/*where (region = "Canada" or region = "Asia")*/
/* just for reducing data */
ORDER BY &byFieldsSQL
;
quit;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Create a sorted data set containing the unique combinations
* of the 3 variables that constitute the levels of hierarchy.
* This makes sure the hierarchies are complete.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
proc sort data=work.combos out=work.sortedShoes;
by &byFieldsSDS;
run;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Create the data sets containing the data in the lowest level
* of the hierarchy. There is a data set for each unique
* combination of hierarchy variables. These data sets are
* needed because the DATA step WHERE statement requires the
* variables in the WHERE also be in the data set results.
* Using the DATA step WHERE statement within the JSON procedure
* would result in duplicate information in the low-level
* hierarchy data.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
data _null_;
set work.sortedShoes;
by &byFieldsSDS;
call execute('%createLowLevelDataSet(' || _N_ || ',"' ||
TRIM(&h1) || '","' ||
TRIM(&h2) || '","' ||
TRIM(&h3) || '")');
run;
/************************
*************************
* Main DATA Step *
*************************
************************/
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* The main DATA step to create the custom JSON procedure code
* that will create the hierarchical JSON formatted output of
* the Shoes data set.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
data _null_;
/* Process each observation in the WORK.sortedShoes data set. The
END option set a flag when the last observation in the data set
is read. The BY statement separates the data by the desired
hierarchy. */
set work.sortedShoes end=lastOne;
by &byFieldsSDS;
/* specifies the output file for PUT statements */
FILE "&jsonProcCodeSpec" DISK;
/* Only on the first observation in the data set, write the required
initial statements to the JSON procedure code file. */
if _N_ eq 1 then
do;
%fileHeader("&jsonOutputSpec");
end;
/* Each time the value of the level 1 hierarchy changes, write the
needed open statements to the JSON procedure code file. */
if first.&h1 then
do;
%hierarchy1Open();
end;
/* Each time the value of the level 2 hierarchy changes, write the
needed open statements to the JSON procedure code file. */
if first.&h2 then
do;
%hierarchy2Open();
end;
/* Each time the value of the level 3 hierarchy changes, write the
needed identifying statements to the JSON procedure code file. */
if first.&h3 then
do;
%hierarchy3Open();
end;
/* The lowest level of the hierarchy has been reached. write the
open statement to the JSON procedure code file. Then, using
the observation count, export the proper low-level data set,
and then close the open JSON container. */
put "write open array &stmtEnd /* open array of data set objects */";
dsName=CAT("rnm_ds_",_N_);
put "export " dsName "&stmtEnd";
put "write close &stmtEnd /* close array of data set objects */";
/* Each time the value of the level 3 hierarchy is about to changes,
write the needed close statements to the JSON procedure code file.*/
if last.&h3 then
do;
%hierarchy3Close();
end;
/* Each time the value of the level 2 hierarchy is about to changes,
write the needed close statements to the JSON procedure code file.*/
if last.&h2 then
do;
%hierarchy2Close();
end;
/* Each time the value of the level 1 hierarchy is about to changes,
write the needed close statements to the JSON procedure code file.*/
if last.&h1 then
do;
%hierarchy1Close();
end;
/* Only on the last observation in the data set, write to the JSON
procedure code file the required statements to end the genearted
JSON procedure. */
if lastOne then
do;
%fileFooter();
end;
run;
/* Now run the generated custom JSON procedure code to produce the
JSON formatted output file of the data set. */
%include "&jsonProcCodeSpec";
I think so. PROC JSON allows you to nest items for WRITE -- ARRAY and VALUE.
Check out the video here: https://www.youtube.com/watch?v=KW6O9R23mtk
And this simple example on the communities for SAS Event Stream Processing.
And of course, the documentation for PROC JSON.
@BillM_SAS might chime in.
Yes, it should be possible to create the output file format you reference. You did not supply or describe the form of the SAS data sets available, so I am unable to send you an brief example to demonstrate the ability specific to your posting. As Chris mentions and the video demonstrates, the JSON procedure has the ability to create hierarchical JSON files using the WRITE statements. The JSON procedure will make the generation of the file much easier than a DATA Step with PUT statements.
I was trying to work with SASHELP.SHOES data set with 3 levels, Region, Subsidiary, Product and then amount as the value.
I looked at the resources Chris sent, but didn't see how I could specify the constant name/description/children labels. If you could provide an example that would be much appreciated. In the end, I'd love to create a macro that worked off a flat CSV file that used BY groups as the hierarchy and a specified value so that I could quickly generate the data sets and automate my full process in SAS.
THANKS 🙂
FYI - this is my crappy (non fully working) approach so far but I'll continue down this road for now 😞
data _null_;
set sashelp.shoes end=eof;
by region subsidiary product;
region=quote(region);
subsidiary=quote(subsidiary);
product=quote(product);
name = '"name":';
description = '"description":';
children = '"children": [';
*header record;
if _n_ = 1 then do;
put '{';
put '"name": "flare",';
put '"description": "flare", ';
put '"children":[';
end;
if first.region then do;
put "{";
put name region ',';
put description region;
if not last.region then do;
put ',';
put children;
end;
end;
if last.region then do;
put '}';
if not eof then put ',';
end;
*trailing close records;
if eof then do;
put ']';
put '}';
end;
run;
Sorry for the delay. Once I got into the code and data, it was a bit more work than I anticipated. Plus I had some higher priority work to complete. I think I have code written using the SASHELP.shoes data set that is close to what you want. It at least should demonstrate a technique for producing hierarchical JSON formatted output with the JSON procedure. I am hoping that you can take the proof-of-concept code I've included and modify it to your specific requirements. As I note in the code comments, this is quick and dirty code to demonstrate the concept. I'm sure there are better ways to do what I did, but it should be a good start on the code you desire.
/********************************************************************
* Proof-of-concept code to demonstrate a method of using the JSON
* procedure available in SAS 9.4 to produce a hierarchical output file
* in JSON format from a SAS data set. This code specifically takes
* the Shoes data set from SASHLEP to create hierarchical output in
* the form:
*
* Region -+
* |
* +- Subsidiary -+
* |
* +- Product -+
* |
* +- <product data>
*
* Note that this code is not the most concise as some pieces are
* hardcoded to allow for easier understanding of the parts and
* resulting files. There are probably better ways to do some
* sections of the code. This code could also be modified to make
* it more easily handles other data sets.
*
* Modifications available in this code:
* * dataFields = The comma-separated names of the data set
* variables that make up the lowest level of
* the hierarchy.
* * h1 = The name of the data set variable that is the highest
* level of the hierarchy.
* * h2 = The name of the data set variable that is the second
* level of the hierarchy.
* * h3 = The name of the data set variable that is the third
* level of the hierarchy.
* * jsonOutputSpec = The location where the formatted JSON output
* will be written after running the custom
* JSON procedure code.
* * jsonProcCodeSpec = The location where the custom JSON
* procedure code is written. It is running
* of this generated file that will produce
* the JSON formatted output.
* * sourceDataSet = The library and member name of the SAS data
* set being written in JSON format.
*
* Author: Bill McNeill
* Company: SAS Institute
* Contact: Bill.McNeill@sas.com
* Date: 14 November 2017
********************************************************************/
/*****************************
* Modifiable macro variables
******************************/
%let jsonProcCodeSpec=./sasuser/jsonProcCode.sas;
%let jsonOutputSpec=./sasuser/jsonOutput.txt;
%let sourceDataSet=SASHELP.Shoes;
%let h1=region;
%let h2=subsidiary;
%let h3=product;
%let dataFields=inventory, returns, stores, sales;
/*****************************
* Constant macro variables
******************************/
%let stmtEnd=%STR(;);
%let byFieldsSQL=&h1, &h2, &h3;
%let byFieldsSDS=&h1 &h2 &h3;
/************************
*************************
* Macro functions *
*************************
************************/
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Macro function: createLowLevelDataSet
* Create the data set specific to each hierarchy.
* Parameters:
* count - The unique identification number for the data set.
* value1 - The first level hierarchy value.
* value2 - The second level hierarchy value.
* value3 - The third level hierarchy value.
* Results:
* Create a data set named WORK.ds_#
* where # is the unique identification number.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%macro createLowLevelDataSet(count, value1, value2, value3);
proc sql;
create table ds_&count as
select &dataFields
from &sourceDataSet
where &h1=&value1 and &h2=&value2 and &h3=&value3
;
quit;
%mend createLowLevelDataSet;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Macro function: fileHeader
* Write the one-time JSON procedure statements at the start of
* the JSON procedure.
* Parameters:
* filePath - The file specification to where the JSON
* formatted output is written.
* Results:
* Output the first 2 lines of the JSON procedure to the file
* containing the JSON procedure code.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%macro fileHeader(filePath);
put "proc json pretty out=""" &filePath """ nosastags &stmtEnd";
put "write open object &stmtEnd /* open outermost object */";
%mend fileHeader;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Macro function: hierarchy1Open
* Write the JSON procedure statements to identify and open the
* JSON containers at the level 1 hierarchy.
* Parameters:
* None
* Results:
* Write the needed JSON statements to the file containing the
* JSON procedure code.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%macro hierarchy1Open();
put "write value ""Name"" &stmtEnd";
valueStmt=CAT("write value """, TRIM(&h1), """ &stmtEnd");
put valueStmt;
put "write value ""Children"" &stmtEnd";
put "write open array &stmtEnd /* open H1 array */";
put "write open object &stmtEnd /* open H1 object */ ";
%mend hierarchy1Open;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Macro function: hierarchy1Close
* Write the JSON procedure statements to close the JSON
* containers to close the level 1 hierarchy.
* Parameters:
* None
* Results:
* Write the needed JSON close statements to the file
* containing the JSON procedure code.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%macro hierarchy1Close();
put "write close &stmtEnd /* close H1 object */";
put "write close &stmtEnd /* close H1 array */";
%mend hierarchy1Close;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Macro function: hierarchy2Open
* Write the JSON procedure statements to identify and open the
* JSON containers at the level 2 hierarchy.
* Parameters:
* None
* Results:
* Write the needed JSON statements to the file containing the
* JSON procedure code.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%macro hierarchy2Open();
put "write value ""Name"" &stmtEnd";
valueStmt=CAT("write value """, TRIM(&h2), """ &stmtEnd");
put valueStmt;
put "write value ""Children"" &stmtEnd";
put "write open array &stmtEnd /* open H2 array */";
put "write open object &stmtEnd /* open H2 object */ ";
%mend hierarchy2Open;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Macro function: hierarchy2Close
* Write the JSON procedure statements to close the JSON
* containers to close the level 2 hierarchy.
* Parameters:
* None
* Results:
* Write the JSON close statements to the file containing the
* JSON procedure code.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%macro hierarchy2Close();
put "write close &stmtEnd /* close H2 object */";
put "write close &stmtEnd /* close H2 array */";
%mend hierarchy2Close;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Macro function: hierarchy3Stmts
* Write the JSON procedure statements to identify the JSON
* containers that will hold the low-level hierarchy data.
* Parameters:
* None
* Results:
* Write the needed JSON statements to the file containing the
* JSON procedure code.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%macro hierarchy3Stmts();
put "write value ""Name"" &stmtEnd";
valueStmt=CAT("write value """, TRIM(&h3), """ &stmtEnd");
put valueStmt;
put "write value ""Children"" &stmtEnd";
%mend hierarchy3Stmts;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Macro function: fileFooter
* Write the one-time JSON procedure statements at the end of
* the JSON procedure.
* Parameters:
* None
* Results:
* Output the last 2 lines of the JSON procedure to the file
* containing the JSON procedure code.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%macro fileFooter();
put "write close &stmtEnd /* close outermost object */";
put "run &stmtEnd";
%mend fileFooter;
/************************
*************************
* Support Statements *
*************************
************************/
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Create a data set, WORK.combos, containing the unique
* combinations of the 3 variables that constitute the levels
* of hierarchy.
*
* Note that this query produces a reduced data set in order to
* keep the size of the formatted JSON output manageable for
* review. To use the full data set, remove the WHERE statement
* from the query.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
proc sql;
create table work.combos as
select distinct &byFieldsSQL
from &sourceDataSet
where (region = "Canada" or region = "Asia") /* just for reducing data */
ORDER BY &byFieldsSQL
;
quit;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Create a sorted data set containing the unique combinations
* of the 3 variables that constitute the levels of hierarchy.
* This makes sure the hierarchies are complete.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
proc sort data=work.combos out=work.sortedShoes;
by &byFieldsSDS;
run;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Create the data sets containing the data in the lowest level
* of the hierarchy. There is a data set for each unique
* combination of hierarchy variables. These data sets are
* needed because the DATA step WHERE statement requires the
* variables in the WHERE also be in the data set results.
* Using the DATA step WHERE statement within the JSON procedure
* would result in duplicate information in the low-level
* hierarchy data.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
data _null_;
set work.sortedShoes;
by &byFieldsSDS;
call execute('%createLowLevelDataSet(' || _N_ || ',"' ||
TRIM(&h1) || '","' ||
TRIM(&h2) || '","' ||
TRIM(&h3) || '")');
run;
/************************
*************************
* Main DATA Step *
*************************
************************/
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* The main DATA step to create the custom JSON procedure code
* that will create the hierarchical JSON formatted output of
* the Shoes data set.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
data _null_;
/* Process each observation in the WORK.sortedShoes data set. The
END option set a flag when the last observation in the data set
is read. The BY statement separates the data by the desired
hierarchy. */
set work.sortedShoes end=lastOne;
by &byFieldsSDS;
/* specifies the output file for PUT statements */
FILE "&jsonProcCodeSpec" DISK;
/* Only on the first observation in the data set, write the required
initial statements to the JSON procedure code file. */
if _N_ eq 1
then do;
%fileHeader("&jsonOutputSpec");
end;
/* Each time the value of the level 1 hierarchy changes, write the
needed open statements to the JSON procedure code file. */
if first.&h1
then do;
%hierarchy1Open();
end;
/* Each time the value of the level 2 hierarchy changes, write the
needed open statements to the JSON procedure code file. */
if first.&h2
then do;
%hierarchy2Open();
end;
/* Each time the value of the level 3 hierarchy changes, write the
needed identifying statements to the JSON procedure code file. */
if first.&h3
then do;
%hierarchy3Stmts();
end;
/* The lowest level of the hierarchy has been reached. write the
open statement to the JSON procedure code file. Then, using
the observation count, export the proper low-level data set,
and then close the open JSON container. */
put "write open array &stmtEnd /* open array of data set objects */";
dsName=CAT("ds_",_N_);
put "export " dsName "&stmtEnd";
put "write close &stmtEnd /* close array of data set objects */";
/* Each time the value of the level 2 hierarchy is about to changes,
write the needed close statements to the JSON procedure code file.*/
if last.&h2 then do;
%hierarchy2Close();
end;
/* Each time the value of the level 1 hierarchy is about to changes,
write the needed close statements to the JSON procedure code file.*/
if last.&h1 then do;
%hierarchy1Close();
end;
/* Only on the last observation in the data set, write to the JSON
procedure code file the required statements to end the genearted
JSON procedure. */
if lastOne
then do;
%fileFooter();
end;
run;
/* Now run the generated custom JSON procedure code to produce the
JSON formatted output file of the data set. */
%include "&jsonProcCodeSpec";
Thanks a lot to Bill's reply, we succeeded in creating the JSON file (and the interactive plot) after making a few changes to his code. On a side note, the code can be easily implemented to hierarchies more than 3 as well, since from hierarchy 2, the open and close macros are in the same form respectively. Please see below:
/********************************************************************
* Proof-of-concept code to demonstrate a method of using the JSON
* procedure available in SAS 9.4 to produce a hierarchical output file
* in JSON format from a SAS data set. This code specifically takes
* the Shoes data set from SASHLEP to create hierarchical output in
* the form:
*
* Region -+
* |
* +- Subsidiary -+
* |
* +- Product -+
* |
* +- <product data>
*
* Note that this code is not the most concise as some pieces are
* hardcoded to allow for easier understanding of the parts and
* resulting files. There are probably better ways to do some
* sections of the code. This code could also be modified to make
* it more easily handles other data sets.
*
* Modifications available in this code:
* * dataFields = The comma-separated names of the data set
* variables that make up the lowest level of
* the hierarchy.
* * h1 = The name of the data set variable that is the highest
* level of the hierarchy.
* * h2 = The name of the data set variable that is the second
* level of the hierarchy.
* * h3 = The name of the data set variable that is the third
* level of the hierarchy.
* * jsonOutputSpec = The location where the formatted JSON output
* will be written after running the custom
* JSON procedure code.
* * jsonProcCodeSpec = The location where the custom JSON
* procedure code is written. It is running
* of this generated file that will produce
* the JSON formatted output.
* * sourceDataSet = The library and member name of the SAS data
* set being written in JSON format.
*
* Author: Bill McNeill
* Company: SAS Institute
* Contact: Bill.McNeill@sas.com
* Date: 14 November 2017
* Modified by: Weiyu Qiu
* Company: Office of Statistics and Information (OSI), Alberta Treasury Board and Finance
* Date: 16 November 2017
********************************************************************/
/**********************************
* Restructure the data if necessary
**********************************/
data shoes;
retain world;
set sashelp.shoes;
world="World";
run;
/*****************************
* Modifiable macro variables
******************************/
%let jsonProcCodeSpec=...\jsonProcCode.sas;
%let jsonOutputSpec=...\jsonOutput_all.txt;
%let sourceDataSet=Shoes;
%let h1=world;
%let h2=region;
%let h3=subsidiary;
%let dataFields=product, sales;
/*****************************
* Constant macro variables
******************************/
%let stmtEnd=%STR(;);
%let byFieldsSQL=&h1, &h2, &h3;
%let byFieldsSDS=&h1 &h2 &h3;
/************************
*************************
* Macro functions *
*************************
************************/
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Macro function: createLowLevelDataSet
* Create the data set specific to each hierarchy.
* Parameters:
* count - The unique identification number for the data set.
* value1 - The first level hierarchy value.
* value2 - The second level hierarchy value.
* value3 - The third level hierarchy value.
* Results:
* Create a data set named WORK.ds_#
* where # is the unique identification number.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%macro createLowLevelDataSet(count, value1, value2, value3);
proc sql;
create table ds_&count as
select &dataFields
from &sourceDataSet
where &h1=&value1 and &h2=&value2 and &h3=&value3
;
quit;
data rnm_ds_&count;
retain product description sales;
set ds_&count;
description=product;
rename product=name sales=size;
run;
%mend createLowLevelDataSet;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Macro function: fileHeader
* Write the one-time JSON procedure statements at the start of
* the JSON procedure.
* Parameters:
* filePath - The file specification to where the JSON
* formatted output is written.
* Results:
* Output the first 2 lines of the JSON procedure to the file
* containing the JSON procedure code.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%macro fileHeader(filePath);
put "proc json pretty out=""" &filePath """ nosastags &stmtEnd";
put "write open object &stmtEnd /* open outermost object */";
%mend fileHeader;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Macro function: hierarchy1Open
* Write the JSON procedure statements to identify and open the
* JSON containers at the level 1 hierarchy.
* Parameters:
* None
* Results:
* Write the needed JSON statements to the file containing the
* JSON procedure code.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%macro hierarchy1Open();
put "write value ""name"" &stmtEnd";
valueStmt=CAT("write value """, TRIM(&h1), """ &stmtEnd");
put valueStmt;
put "write value ""description"" &stmtEnd";
valueStmt=CAT("write value """, TRIM(&h1), """ &stmtEnd");
put valueStmt;
put "write value ""children"" &stmtEnd";
put "write open array &stmtEnd /* open H1 array */";
%mend hierarchy1Open;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Macro function: hierarchy1Close
* Write the JSON procedure statements to close the JSON
* containers to close the level 1 hierarchy.
* Parameters:
* None
* Results:
* Write the needed JSON close statements to the file
* containing the JSON procedure code.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%macro hierarchy1Close();
put "write close &stmtEnd /* close H1 array */";
%mend hierarchy1Close;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Macro function: hierarchy2Open
* Write the JSON procedure statements to identify and open the
* JSON containers at the level 2 hierarchy.
* Parameters:
* None
* Results:
* Write the needed JSON statements to the file containing the
* JSON procedure code.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%macro hierarchy2Open();
put "write open object &stmtEnd /* open H1 object */ ";
put "write value ""name"" &stmtEnd";
valueStmt=CAT("write value """, TRIM(&h2), """ &stmtEnd");
put valueStmt;
put "write value ""description"" &stmtEnd";
valueStmt=CAT("write value """, TRIM(&h2), """ &stmtEnd");
put valueStmt;
put "write value ""children"" &stmtEnd";
put "write open array &stmtEnd /* open H2 array */";
%mend hierarchy2Open;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Macro function: hierarchy2Close
* Write the JSON procedure statements to close the JSON
* containers to close the level 2 hierarchy.
* Parameters:
* None
* Results:
* Write the JSON close statements to the file containing the
* JSON procedure code.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%macro hierarchy2Close();
put "write close &stmtEnd /* close H2 array */";
put "write close &stmtEnd /* close H1 object */";
%mend hierarchy2Close;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Macro function: hierarchy3Stmts
* Write the JSON procedure statements to identify the JSON
* containers that will hold the low-level hierarchy data.
* Parameters:
* None
* Results:
* Write the needed JSON statements to the file containing the
* JSON procedure code.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%macro hierarchy3Open();
put "write open object &stmtEnd /* open H2 object */ ";
put "write value ""name"" &stmtEnd";
valueStmt=CAT("write value """, TRIM(&h3), """ &stmtEnd");
put valueStmt;
put "write value ""description"" &stmtEnd";
valueStmt=CAT("write value """, TRIM(&h3), """ &stmtEnd");
put valueStmt;
put "write value ""children"" &stmtEnd /* open H3 array */";
%mend hierarchy3Open;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Macro function: hierarchy3Close
* Write the JSON procedure statements to close the JSON
* containers to close the level 2 hierarchy.
* Parameters:
* None
* Results:
* Write the JSON close statements to the file containing the
* JSON procedure code.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%macro hierarchy3Close();
put "write close &stmtEnd /* close H3 array */";
%mend hierarchy3Close;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Macro function: fileFooter
* Write the one-time JSON procedure statements at the end of
* the JSON procedure.
* Parameters:
* None
* Results:
* Output the last 2 lines of the JSON procedure to the file
* containing the JSON procedure code.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%macro fileFooter();
put "write close &stmtEnd /* close outermost object */";
put "run &stmtEnd";
%mend fileFooter;
/************************
*************************
* Support Statements *
*************************
************************/
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Create a data set, WORK.combos, containing the unique
* combinations of the 3 variables that constitute the levels
* of hierarchy.
*
* Note that this query produces a reduced data set in order to
* keep the size of the formatted JSON output manageable for
* review. To use the full data set, remove the WHERE statement
* from the query.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
proc sql;
create table work.combos as
select distinct &byFieldsSQL
from &sourceDataSet
/*where (region = "Canada" or region = "Asia")*/
/* just for reducing data */
ORDER BY &byFieldsSQL
;
quit;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Create a sorted data set containing the unique combinations
* of the 3 variables that constitute the levels of hierarchy.
* This makes sure the hierarchies are complete.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
proc sort data=work.combos out=work.sortedShoes;
by &byFieldsSDS;
run;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Create the data sets containing the data in the lowest level
* of the hierarchy. There is a data set for each unique
* combination of hierarchy variables. These data sets are
* needed because the DATA step WHERE statement requires the
* variables in the WHERE also be in the data set results.
* Using the DATA step WHERE statement within the JSON procedure
* would result in duplicate information in the low-level
* hierarchy data.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
data _null_;
set work.sortedShoes;
by &byFieldsSDS;
call execute('%createLowLevelDataSet(' || _N_ || ',"' ||
TRIM(&h1) || '","' ||
TRIM(&h2) || '","' ||
TRIM(&h3) || '")');
run;
/************************
*************************
* Main DATA Step *
*************************
************************/
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* The main DATA step to create the custom JSON procedure code
* that will create the hierarchical JSON formatted output of
* the Shoes data set.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
data _null_;
/* Process each observation in the WORK.sortedShoes data set. The
END option set a flag when the last observation in the data set
is read. The BY statement separates the data by the desired
hierarchy. */
set work.sortedShoes end=lastOne;
by &byFieldsSDS;
/* specifies the output file for PUT statements */
FILE "&jsonProcCodeSpec" DISK;
/* Only on the first observation in the data set, write the required
initial statements to the JSON procedure code file. */
if _N_ eq 1 then
do;
%fileHeader("&jsonOutputSpec");
end;
/* Each time the value of the level 1 hierarchy changes, write the
needed open statements to the JSON procedure code file. */
if first.&h1 then
do;
%hierarchy1Open();
end;
/* Each time the value of the level 2 hierarchy changes, write the
needed open statements to the JSON procedure code file. */
if first.&h2 then
do;
%hierarchy2Open();
end;
/* Each time the value of the level 3 hierarchy changes, write the
needed identifying statements to the JSON procedure code file. */
if first.&h3 then
do;
%hierarchy3Open();
end;
/* The lowest level of the hierarchy has been reached. write the
open statement to the JSON procedure code file. Then, using
the observation count, export the proper low-level data set,
and then close the open JSON container. */
put "write open array &stmtEnd /* open array of data set objects */";
dsName=CAT("rnm_ds_",_N_);
put "export " dsName "&stmtEnd";
put "write close &stmtEnd /* close array of data set objects */";
/* Each time the value of the level 3 hierarchy is about to changes,
write the needed close statements to the JSON procedure code file.*/
if last.&h3 then
do;
%hierarchy3Close();
end;
/* Each time the value of the level 2 hierarchy is about to changes,
write the needed close statements to the JSON procedure code file.*/
if last.&h2 then
do;
%hierarchy2Close();
end;
/* Each time the value of the level 1 hierarchy is about to changes,
write the needed close statements to the JSON procedure code file.*/
if last.&h1 then
do;
%hierarchy1Close();
end;
/* Only on the last observation in the data set, write to the JSON
procedure code file the required statements to end the genearted
JSON procedure. */
if lastOne then
do;
%fileFooter();
end;
run;
/* Now run the generated custom JSON procedure code to produce the
JSON formatted output file of the data set. */
%include "&jsonProcCodeSpec";
It looks like that program is constructed to take the existing dataset and split it out into many small dataset and generate a lot of hardcoded "write" statements around an occasional "export" statement for PROC JSON to run.
How is that an better/easier than just generating the JSON text directly from a data step? What does using PROC JSON add?
As I noted when I posted the code, once I got into the details of the data, producing a solution was a bit more work than I anticipated. My assumption was that SAS programmers are more familiar with manipulating their data than dealing with the syntax of valid JSON output. Also, the original post expressed a desire to use the JSON procedure to solve the problem. The code I presented showed how that could be done.
Thanks @BillM_SAS and @ChrisHemedinger. The solution above is working exactly as needed to support the creation of D3 visualizations.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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: