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?
%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 🙂
%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 🙂
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;
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" } ]
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
}
]
And at SAS 9.4 you can use:
proc json out=_webout pretty nosastags;
export sashelp.class(obs=3);
run;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: