General SAS Programming

[samira]

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" 

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: 

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

[BillM_SAS · Accepted solution]

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.

json_transpose.sas

[Bruno_SAS]

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

[samira]

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.

[Bruno_SAS]

Hi

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

Bruno

[samira]

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

[samira]

here is the proc tranpose output: 

[BillM_SAS · Accepted solution]

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.

json_transpose.sas

[BillM_SAS]

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

samira.txt

[samira]

thank you very much!!! this is really helpful !

[Bruno_SAS]

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

[samira]

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:

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. 

[BillM_SAS]

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;