DATA Step, Macro, Functions and more

PROC JSON - create a hierarchical file

Accepted Solution Solved
Reply
Super User
Posts: 23,311
Accepted Solution

PROC JSON - create a hierarchical file

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!


Accepted Solutions
Solution
‎11-20-2017 11:24 AM
New Contributor
Posts: 4

Re: PROC JSON - create a hierarchical file

[ Edited ]
Posted in reply to BillM_SAS

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 solution in original post


All Replies
Community Manager
Posts: 3,380

Re: PROC JSON - create a hierarchical file

think so.  PROC JSON allows you to nest items for WRITE -- ARRAY and VALUE.

 

json.png

 

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.

 

 

The video demos how to use the SAS 9.4 JSON procedure to write data in JSON format. Whether it's a SAS data set that you want to export, or your own free-form JSON output, PROC JSON enables you to create and control the content of a JSON output file.
SAS Employee
Posts: 41

Re: PROC JSON - create a hierarchical file

Posted in reply to ChrisHemedinger

 

 

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.

Super User
Posts: 23,311

Re: PROC JSON - create a hierarchical file

Posted in reply to BillM_SAS

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 Smiley Happy

 

FYI - this is my crappy (non fully working) approach so far but I'll continue down this road for now Smiley Sad

 

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;

 

SAS Employee
Posts: 41

Re: PROC JSON - create a hierarchical file

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";
Solution
‎11-20-2017 11:24 AM
New Contributor
Posts: 4

Re: PROC JSON - create a hierarchical file

[ Edited ]
Posted in reply to BillM_SAS

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




Super User
Super User
Posts: 7,935

Re: PROC JSON - create a hierarchical file

Posted in reply to levender8622

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?

SAS Employee
Posts: 41

Re: PROC JSON - create a hierarchical file

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.

Super User
Posts: 23,311

Re: PROC JSON - create a hierarchical file

Posted in reply to BillM_SAS

Thanks @BillM_SAS and @ChrisHemedinger. The solution above is working exactly as needed to support the creation of D3 visualizations. 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 501 views
  • 8 likes
  • 5 in conversation