topic Re: Export data to JSON object in Developers

Export data to JSON object

Filipvdr — Fri, 18 Sep 2015 07:54:45 GMT

I got a question:

I want to stream a dataset as a JSON object in my browser.

As we are not yet on SAS 9.4 i'm using this macro:

%stpbegin;
%macro json(indata=,outfile=);
proc contents data=&indata noprint out=cont;
proc sort data=cont; by varnum;
data _null_;
set cont end=eof;
call symput(compress("var"||put(_n_,best10.)),trim(name));
if eof then do; call symput("nv",compress(put(_N_,best10.))); call symput("mv",compress(put(_N_-1,best10.))); end;
run;
data _null_;
file "&outfile" PS=32767;
set &indata end=lastrec;
if _N_ eq 1 then do;
put '[';
end;
put '{ "' @; put "%trim(&var1)"@; put '":"' &var1 '",';
%do i = 2 %to &mv;
put '"' @; put "%trim(&&var&i)"@; put '":"' &&var&i '",';
%end;
put '"' @; put "%trim(&&var&nv)"@; put '":"' &&var&nv '"}';
if lastrec eq 1 then do;
put ']';
end;
else do;
put ',';
end;
RUN;
%mend json;
%json(indata=output_json,outfile=temp);
%stpend;

But my result does not show anything in my browser, any idea?

Re: Export data to JSON object

Filipvdr — Fri, 18 Sep 2015 08:22:00 GMT

%macro json(indata=);
proc contents data=&indata noprint out=cont;
proc sort data=cont; by varnum;
data _null_;
set cont end=eof;
call symput(compress("var"||put(_n_,best10.)),trim(name));
if eof then do; call symput("nv",compress(put(_N_,best10.))); call symput("mv",compress(put(_N_-1,best10.))); end;
run;
data _null_;
file _webout PS=32767;
set &indata end=lastrec;
if _N_ eq 1 then do;
put '[';
end;
put '{ "' @; put "%trim(&var1)"@; put '":"' &var1 '",';
%do i = 2 %to &mv;
put '"' @; put "%trim(&&var&i)"@; put '":"' &&var&i '",';
%end;
put '"' @; put "%trim(&&var&nv)"@; put '":"' &&var&nv '"}';
if lastrec eq 1 then do;
put ']';
end;
else do;
put ',';
end;
RUN;
%mend json;
%json(indata=output_json);

this solved it 🙂

Re: Export data to JSON object

Tom — Fri, 18 Sep 2015 08:43:48 GMT

The logic of this macro does not make much sense. Why are you converting the data to macro variables?

Re: Export data to JSON object

RW9 — Fri, 18 Sep 2015 08:58:41 GMT

I would totally agree with Tom here. Why not just write a datastep - no macro needed - which outputs the data to a text file?

data _null_;
set sashelp.cars end=last;
file "s:\temp\rob\cars.txt";

array numeric_variables{*} _NUMERIC_ ;
array character_variables{*} _CHARACTER_ ;

if _n_=1 then put '[';
if last then put ']';

do i = 1 to dim(numeric_variables) ;
output_line=cats("{",vname(numeric_variables{i}),'=',numeric_variables{i},"}");
put output_line;
end ;
do i = 1 to dim(character_variables) ;
output_line=cats("{",vname(character_variables{i}),'=',character_variables{i},"}");
put output_line;
end ;
run;

Re: Export data to JSON object

Tom — Fri, 18 Sep 2015 09:30:10 GMT

Here is a version that uses CALL VNEXT and VVALUEX() functions to output the name/value pairs in order without having to generate code. It will also preserve the variable order (unlike array based solutions).

%macro json(in,out=_webout);
data _null_;
  file &out ;
  if _n_=1 then _prefix='[';
  else _prefix=',';
  if _EOF  then put ']';
  set &in end=_EOF ;
  length _name $32 _value $300 ;
  _sep = _prefix ||'{';
  do until (upcase(_name)='_NAME');
    call vnext(_name);
    if not (upcase(_name) in ('_PREFIX','_EOF','_NAME')) then do;
       _value = left(vvaluex(_name));
       put _sep $char2. _name :$quote. +(-1) ':' _value :$quote. ;
       _sep=' ,';
    end;
  end;
  put ' }';
run;
%mend json;

So if call it with:

%json(sashelp.class(obs=3),out=print);

It will print:

[{"Name":"Alfred"
 ,"Sex":"M"
 ,"Age":"14"
 ,"Height":"69"
 ,"Weight":"112.5"
 }
,{"Name":"Alice"
 ,"Sex":"F"
 ,"Age":"13"
 ,"Height":"56.5"
 ,"Weight":"84"
 }
,{"Name":"Barbara"
 ,"Sex":"F"
 ,"Age":"13"
 ,"Height":"65.3"
 ,"Weight":"98"
 }
]

Re: Export data to JSON object

AhmedAl_Attar — Fri, 18 Sep 2015 16:06:10 GMT

I loved the sophistication yet the simplicity of Tom's solution. Here is one simple modification to Tom's original solution.

JSON is Type sensitive, therefore if you needed to mimic SAS's original data types (Num -> JSON Num, Char -> JSON Char) here is what you need to use

%macro json(in,out=_webout);
data _null_;
file &out ;
if _n_=1 then _prefix='[';
else _prefix=',';
if _EOF then put ']';
set &in end=_EOF ;
length _name $32 _value $300 _type $1; /* Added _type var def */
_sep = _prefix ||'{';
do until (upcase(_name)='_NAME');
    call vnext(_name,_type); /* using _type to get the variable's data type */
    if not (upcase(_name) in ('_PREFIX','_EOF','_NAME')) then do;
       _value = left(vvaluex(_name));
       if (_type = 'C') then /* Quote Character values only */
          put _sep $char2. _name :$quote. +(-1) ':' _value :$quote. ;
       else
          put _sep $char2. _name :$quote. +(-1) ':' _value ;
       _sep=' ,';
    end;
end;
put ' }';
run;
%mend json;
%json(sashelp.class(obs=3),out=print);

[{"Name":"Alfred"
,"Sex":"M"
,"Age":14
,"Height":69
,"Weight":112.5
}
,{"Name":"Alice"
,"Sex":"F"
,"Age":13
,"Height":56.5
,"Weight":84
}
,{"Name":"Barbara"
,"Sex":"F"
,"Age":13
,"Height":65.3
,"Weight":98
}
]

Re: Export data to JSON object

BillM_SAS — Fri, 02 Oct 2015 15:34:16 GMT

And at SAS 9.4 you can use:

proc json out=_webout pretty nosastags;
   export sashelp.class(obs=3);
run;

Re: Export data to JSON object

Tom — Fri, 02 Oct 2015 15:46:10 GMT

The original question was what to do if you don't have access to PROC JSON.

Also proc json's PRETTY option is placing the commas on the wrong end of the line.