How to produce a JSON with a 3-way classification using SAS proc JSON?

[nsabhyankar]

I am creating a Json file from a SAS data set using SAS proc Json .

The Json is used in creating a two way table, say with by variables x and y.

There's a third variable z. I want to create a Json which will produce a table of x vs y for each value of z.

How can I do that preferably in proc Json or otherwise if not possible directly in proc json?

[Cynthia_sas]

What code have you tried and what does your data look like? It will be hard for anyone to offer help if they have to make some test data, AND write a program. If you provide them with the code you're using and a sample of the data, that will be more incentive to try to offer help.

Here's a posting that describes in more detail the most helpful way to post a question https://communities.sas.com/t5/Getting-Started/How-to-get-fast-helpful-answers/ta-p/226133

cynthia

[nsabhyankar]

Thanks Cynthia for the reply.

Unfortunately I don't have the data right now.The data set is a list of bugs reported. It has the three variables 'status', 'priority' and 'track' along with other variables.

Attached is part of a json that creates a two way table for the variables, bug status (Closed or Open) vs priority (H: High or M: Medium).It shows the number of bugs for each combination of the status and priority.

My objective is to create such a table of status vs priority for each value of the variable 'Track' available in the data (Automation, Performance)

[Tom]

You will need to explain how that structure has anything to do with a 2-way classification.

To me it sounds like you just want to do a frequency on three variables.  Not sure how JSON enters into it.

proc freq data=have ;
  tables  track*status*priority ;
run;

[ChrisHemedinger]

If it's a matter of adding extra levels into the JSON, check out this article.

There's also a handy tip sheet for PROC JSON that might help you.  You can add levels to the JSON with additional WRITE statements.

proc json out="c:\temp\ex.json" 
	pretty;
	write values "Girls";
	write open object;
	export  sashelp.class
		(where=(sex='F'));
	write values "Boys";
	write open object;
	export sashelp.class
		(where=(sex='M'));
	write close;
	write close;
run;

[nsabhyankar]

I want to add extra levels in json but for each value of a certain variable. Currently, all my variables are at the same level with a block for each row.

I want to make sub blocks for each value of a certain classifier variable.

[nsabhyankar]

I already have the frequency table. It has columns for track, status,priority and the corresponding frequency of each combination of these variables.

There is some further process which needs this data to be in json. 

The attached file has the example of what I could produce and what I need.

Pasting it here too:

I was able to produce this:

Here, the variable track is at the same level as status and priority.

{
"info": [
[
"SASJSONExport",
"1.0 PRETTY",
"SASTableData+SUMMARY1",
[
{

"TRACK": "track_nm1",
"STATUS": "Closed",
"N": 21,
"PRIORITY": "M"
},
{

"TRACK": "track_nm2",
"STATUS": "Closed",
"N": 15,
"PRIORITY": "H"
}
]
]
]
}

However, I want this:

Here, status and priority are inside a block for each track.

"summary": {
"groupedData": [
{
"TRACK": "track_nm1",,
"data": [
{
"STATUS": "Closed",
"N": 21,
"PRIORITY": "M"
}
]
},
{
"TRACK": "track_nm2",
"data": [
{
"STATUS": "Closed",
"N": 15,
"PRIORITY": "H"
}
]
}
]
}

[Tom]

Can you show what format you currently have the data that your posted JSON represenets in a SAS table?  

What variables are in the SAS dataset? What values do the variables have?

Preferable post it as a data step with inline data that we can run to recreate your SAS dataset.

[nikhil_sas]

Here is datalines code to generate data similar to what I have:

data bugs;
    input issue_type $ product $ component $ assignee $14. status $ priority $ N;
    datalines;
    BUG product1 UI assignee_name1 open high 5
    BUG product2 UI assignee_name3 open low 6
    BUG product1 UI assignee_name1 closed high 9
    BUG product1 docs assignee_name2 closed low 3    
    BUG product1 docs assignee_name2 open high 5
    BUG product2 server assignee_name1 open low 6
    BUG product2 server assignee_name3 closed high 9
    BUG product1 server assignee_name1 closed low 13    
    FEATURE product2 UI assignee_name2 open high 6
    FEATURE product1 UI assignee_name2 open low 7
    FEATURE product2 docs assignee_name3 closed high 11
    FEATURE product1 docs assignee_name1 closed low 5    
    FEATURE product1 server assignee_name1 open high 7
    FEATURE product2 docs assignee_name2 open low 4
    FEATURE product2 docs assignee_name3 closed high 9
    FEATURE product1 server assignee_name3 closed low 16    
    OTHER product2 UI assignee_name2 open high 8
    OTHER product1 UI assignee_name2 open low 6
    OTHER product2 docs assignee_name3 closed high 9
    OTHER product1 docs assignee_name1 closed low 13    
    OTHER product1 server assignee_name1 open high 5
    OTHER product2 docs assignee_name2 open low 8
    OTHER product2 docs assignee_name3 closed high 14
    OTHER product1 server assignee_name3 closed low 7
    ;
run;  

We currently have the JSON to create a 2D report for the number of bugs, say assigne by status. Now, we need a JSON to generate a 3D report, say product by assigne by status. This report should have a table of assigne by status for each distinct value of product. How do I generate a JSON file which can have such a hierarchy?

[Tom]

You keep posting data and JSON text that are totally unrelated to each another. If you cannot explain the meaning of the JSON that you want then we really cannot help you create it.  

So given the raw data you posted we can use PROC FREQ with a WEIGHT statement to make a three variable cross tab.

proc freq data=bugs ;
  tables product*assignee*status / noprint out=have;
  weight N;
run;

So for your posted data you end up with this HAVE dataset.

data have ;
  length product $8 assignee $14 status $8 count 8;
  input product -- count;
cards;
product1 assignee_name1 closed 40
product1 assignee_name1 open 17
product1 assignee_name2 closed 3
product1 assignee_name2 open 18
product1 assignee_name3 closed 23
product2 assignee_name1 open 6
product2 assignee_name2 open 26
product2 assignee_name3 closed 52
product2 assignee_name3 open 6
;

Now if you can show what you want the JSON file for those 9 records to look like then perhaps someone can help you with a SAS program to produce the JSON file.

Perhaps you can use example 4 in the documentation on PROC JSON as the model and a little data driven code generation.

filename json temp;
filename code temp;
data _null_;
  file code;
  set have end=eof;;
  by product ;
  if _n_=1 then put 
 'proc json out=json pretty nosastags;'
/ 'write values "summary" ;'
/ 'write open object;'
/ 'write value "groupedData";'
/ 'write open array;'
  ;
  if first.product then put
 'write values ' product :$quote. ';'
/'write open array;'
/'export have(where=(product=' product :$quote. ')) ;'
/'write close;'
  ;
  if eof then put
 'write close;'
/'write close;'
/'write close;'
  ;
run;
%include code / source2; 

This generates this file.

{
  "summary": {
    "groupedData": [
      "product1",
      [
        {
          "product": "product1",
          "assignee": "assignee_name1",
          "status": "closed",
          "count": 40
        },
        {
          "product": "product1",
          "assignee": "assignee_name1",
          "status": "open",
          "count": 17
        },
        {
          "product": "product1",
          "assignee": "assignee_name2",
          "status": "closed",
          "count": 3
        },
        {
          "product": "product1",
          "assignee": "assignee_name2",
          "status": "open",
          "count": 18
        },
        {
          "product": "product1",
          "assignee": "assignee_name3",
          "status": "closed",
          "count": 23
        }
      ],
      "product2",
      [
        {
          "product": "product2",
          "assignee": "assignee_name1",
          "status": "open",
          "count": 6
        },
        {
          "product": "product2",
          "assignee": "assignee_name2",
          "status": "open",
          "count": 26
        },
        {
          "product": "product2",
          "assignee": "assignee_name3",
          "status": "closed",
          "count": 52
        },
        {
          "product": "product2",
          "assignee": "assignee_name3",
          "status": "open",
          "count": 6
        }
      ]
    ]
  }
}

If that is not what you wanted then it is probably going to be easier to just generate the JSON file directly from the data step.