Your SAS programs, embedded in web apps and elsewhere

Export data to JSON object

Accepted Solution Solved
Reply
Regular Contributor
Posts: 235
Accepted Solution

Export data to JSON object

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?


Accepted Solutions
Solution
‎09-25-2015 06:23 AM
Regular Contributor
Posts: 235

Re: Export data to JSON object

%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 Smiley Happy

View solution in original post


All Replies
Solution
‎09-25-2015 06:23 AM
Regular Contributor
Posts: 235

Re: Export data to JSON object

%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 Smiley Happy

Super User
Super User
Posts: 7,039

Re: Export data to JSON object

The logic of this macro does not make much sense. Why are you converting the data to macro variables?
Super User
Super User
Posts: 7,942

Re: Export data to JSON object

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;

Super User
Super User
Posts: 7,039

Re: Export data to JSON object

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

 

Regular Contributor
Posts: 216

Re: Export data to JSON object

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


SAS Employee
Posts: 35

Re: Export data to JSON object

Posted in reply to AhmedAl_Attar

And at SAS 9.4 you can use:

 

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

 

Super User
Super User
Posts: 7,039

Re: Export data to JSON object

Posted in reply to BillM_SAS

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. Smiley Very Happy

🔒 This topic is solved and locked.

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

Discussion stats
  • 7 replies
  • 3390 views
  • 2 likes
  • 5 in conversation