Proc JSON modification

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

Proc JSON modification

[ Edited ]

Hello,

 

I have question regarding PROC JSON. I have used this statement but I aam not satisfy with the output of the PROC JSON. 

 

I have below table.named "trig" 

 

Untitled.png

 

and when I ran the following code for the trig table: 

 

proc json out=_webout pretty;
export trig / nokeys nosastags;
run;

 

here is part of the result: 

 

Untitled.png

 

but what I need is : 

 

{

"week": [1,2,3,.....53]

''year2012": [...entire column....]

"avg": [...entire column....]

"yhat": [...entire column....]

"sd": [...entire column....]

"ucl": [...entire column....]

"lcl": [...entire column....]

 

}

 

I would really appriciated if somebody help me with this.

 

Samira


Accepted Solutions
Solution
‎10-06-2016 02:35 PM
SAS Employee
Posts: 28

Re: Proc JSON modification

Working off Bruno's suggestion of using the TRANSPOSE procedure, I was able to produce what you desired. Note that I wrote the SAS code in a simple and straighforward manner. The code could have been written with fewer lines, but I did not want to confuse you since you are new to SAS coding. If you would like to see a more compact version of the code, let me know and I will post it.

 

1) With the data transposed, I saved each observation to a data set. To do this I used the WHERE statement. This requires that the value used in the WHERE statement are part of the output data set. This means that the original column name is in this temporary data set.

 

2) I created for each observation a new and final data set by dropping the column name that was saved as data. This was needed to produce the JSON output you desired.

 

3) I used WRITE VALUE statements in the JSON procedure to put the column name in the desired place.

 

4) I used the EXPORT statement for each data set after the column name.

 

Attached is the SAS code and JSON output.

View solution in original post

Attachment

All Replies
SAS Super FREQ
Posts: 676

Re: Proc JSON modification

Hi

 

Some Questions:

How many observation to you expect to have in dataset trig?

Are they all numeric?

 

I do not think that Proc JSON will create the structure you are after with a simple export. Maybe you need to write your own code to get the structure you want.

 

Here is a sample that uses Proc TRANSPOSE and a DATA Step to create what you are looking for, but depending on the answer to questions above one needs to think of a different approach.

 

proc transpose 
  data=sashelp.class
  out=class_trsp
;
run;

filename jout "c:\temp\sample.json";


data _null_;
  file jout;
  set class_trsp end=_last;
  array xcol{*} col:;

  if _n_ = 1 then do;
    put "{";
  end;

  put _name_ $quote. ":[" @;

  do i = 1 to dim(xcol);
    if i < dim(xcol) then do;
      put xcol{i} "," @;
    end;
    else do;
      put xcol{i}  @;
    end;
  end;

  if _last = 0 then do;
    put "],";
  end;
  else do;
    put "]";
  end;

  if _last = 1 then do;
    put "}";
  end;
run;

Bruno

Contributor
Posts: 35

Re: Proc JSON modification

Thank you Bruno! I have 53 observation and all of them are numerical. 

 

I have ran your code and the following is the result: 

 

{
"week" :[],
"year2012":[],
"avg" :[],
"yhat" :[],
"sd" :[],
"ucl" :[],
"lcl" :[]
}

 

I am not sure why it does not have value. 

 

Thank you for your help.

SAS Super FREQ
Posts: 676

Re: Proc JSON modification

Hi

 

did you run the Proc TRANSPOSE on your original data? Does my sample code create the proper structure?

 

Bruno

Contributor
Posts: 35

Re: Proc JSON modification

yes it did run, this structure is what I needed. but there is no any data between brackets.

Contributor
Posts: 35

Re: Proc JSON modification

here is the proc tranpose output: 

 

Untitled.png

Solution
‎10-06-2016 02:35 PM
SAS Employee
Posts: 28

Re: Proc JSON modification

Working off Bruno's suggestion of using the TRANSPOSE procedure, I was able to produce what you desired. Note that I wrote the SAS code in a simple and straighforward manner. The code could have been written with fewer lines, but I did not want to confuse you since you are new to SAS coding. If you would like to see a more compact version of the code, let me know and I will post it.

 

1) With the data transposed, I saved each observation to a data set. To do this I used the WHERE statement. This requires that the value used in the WHERE statement are part of the output data set. This means that the original column name is in this temporary data set.

 

2) I created for each observation a new and final data set by dropping the column name that was saved as data. This was needed to produce the JSON output you desired.

 

3) I used WRITE VALUE statements in the JSON procedure to put the column name in the desired place.

 

4) I used the EXPORT statement for each data set after the column name.

 

Attached is the SAS code and JSON output.

Attachment
SAS Employee
Posts: 28

Re: Proc JSON modification

I am working from my home computer and it did not post both the SAS code and JSON output. Here is the JSON output.

Contributor
Posts: 35

Re: Proc JSON modification

thank you very much!!! this is really helpful !
SAS Super FREQ
Posts: 676

Re: Proc JSON modification

Hi

 

To get the right structure for the code you need a plain Proc TRANSPOSE, like

proc transpose 
  data=<yourDSN>
  out=class_trsp
;
run;

no ID, no BY statement.

 

The transposed datasets must have a variable _NAME_, the other varaibles are called COL1 - COLn

 

Bruno

 

 

Contributor
Posts: 35

Re: Proc JSON modification

Bruno, 

 

Thank you for your reply. 

 

That is exactly what I did. the following is my tranpose code: 

 

 

 proc transpose data=trig2 out=trig3 (DROP=_LABEL_)  prefix=week;
   run;

 

 

the sult of running above code is the following table named trig:

 

Untitled.png

 

finally after running proc json for the "trig" table, the structure of json would be: 

 

[
  [ "week",1,2,3,4,...52],["year2012",null,null,null,null,1,2,2,....],["avg", 0.5,0.6,......].....[]
]  

 

I need to somehow remove the first string element from my json, in order to do that I need to drop the _NAME_ column, which is easy to do. but on the other hand I need _NAME_ column in export where condition: for example : export trig (where (_NAME_="week"))

because using this condition and wrting additional value in my json I can get what I want. 

 

 

 

SAS Employee
Posts: 28

Re: Proc JSON modification

Here is the more dynamic version using DATA step and macro:

 

/* Some of your original data */
%let originalDS=original;
data &originalDS;
input week year2012 avg yhat sd ucl lcl;
datalines;
1 0 0.5 0.80727 0.19705 1.39841 0.21612
2 0 0.6 0.89024 0.18538 1.44638 0.33410
3 0 0.6 0.95489 0.17511 1.49023 0.43956
4 0 1.0 1.02744 0.16661 1.52727 0.52760
5 1 0.7 1.07480 0.16002 1.55486 0.59473
;;;
run;
/*
   title "Data set &originalDS;
   proc print data=&originalDS; run;
*/

%let transposedDS=transposed;
proc transpose data=&originalDS
               out=&transposedDS;
     run;
/*
   title "Data set &transposedDS;
   proc print data=&transposedDS; run;
*/

%macro makeVarDataSet(variable);
   /* Remove the _name_ column from the data set
      for use with the JSON procedure. */
   data &variable(drop=_name_);
     set &transposedDS;
     where "&variable"=_name_;
     run;
   /*
   title "Data set &variable";
   proc print data=&variable; run;
   */
%mend makeVarDataSet;

%macro generateVariableData(variable);
   write value "&variable";
   export &variable;
%mend generateVariableData;

%macro generateJsonStatements;
  %do i = 1 %to &total;
      %generateVariableData(&&val&i);
  %end;
%mend generateJsonStatements;

%macro cleanupEnv;
  %local dataSets;
  %let   dataSets=&originalDS &transposedDS;
  %do i = 1 %to &total;
      %let dataSets=&dataSets &&val&i;
	  %symdel val&i; /* clean up macro variables */
  %end;
  %symdel originalDS transposedDS total;
  proc delete data=&dataSets;
    run;
%mend cleanupEnv;



/* Create data sets and macro variables of the data set names and total count. */
data _null_;
   set &transposedDS;
   call execute('%makeVarDataSet(' || _name_ || ');' );
   /* create macro variable "val<index>" with value of _name_ data set variable */
   call symputx('val'||strip(put(_n_,3.)),_name_); 
   call symputx('total',_n_);
   RUN;

/* Create the JSON file */
proc json out="samira.json" pretty nosastags nokeys;
   %generateJsonStatements;
   run;

%cleanupEnv;
☑ This topic is SOLVED.

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

Discussion stats
  • 11 replies
  • 861 views
  • 2 likes
  • 3 in conversation