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