BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Filipvdr
Pyrite | Level 9

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Filipvdr
Pyrite | Level 9

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

View solution in original post

7 REPLIES 7
Filipvdr
Pyrite | Level 9

%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
Super User Tom
Super User
The logic of this macro does not make much sense. Why are you converting the data to macro variables?
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User Tom
Super User

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
Rhodochrosite | Level 12

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
SAS Employee

And at SAS 9.4 you can use:

 

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

 

Tom
Super User Tom
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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