Export data to JSON object

[Filipvdr]

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?

[Filipvdr · Accepted solution]

%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 🙂

[Tom]

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

[RW9]

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;

[Tom]

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"
 }
]

[AhmedAl_Attar]

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
 }
]

[BillM_SAS]

And at SAS 9.4 you can use:

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

[Tom]

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.