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";
... View more
