Production Ready Macros for SAS Application Developers
https://github.com/sasjs/core
mp_jsonout.sas
Go to the documentation of this file.
1/**
2 @file mp_jsonout.sas
3 @brief Writes JSON in SASjs format to a fileref
4 @details PROC JSON is faster but will produce errs like the ones below if
5 special chars are encountered.
6
7 > (ERR)OR: Some code points did not transcode.
8
9 > An object or array close is not valid at this point in the JSON text.
10
11 > Date value out of range
12
13 If this happens, try running with ENGINE=DATASTEP.
14
15 Usage:
16
17 filename tmp temp;
18 data class; set sashelp.class;run;
19
20 %mp_jsonout(OPEN,jref=tmp)
21 %mp_jsonout(OBJ,class,jref=tmp)
22 %mp_jsonout(CLOSE,jref=tmp)
23
24 data _null_;
25 infile tmp;
26 input;list;
27 run;
28
29 If you are building web apps with SAS then you are strongly encouraged to use
30 the mX_createwebservice macros in combination with the
31 [sasjs adapter](https://github.com/sasjs/adapter).
32 For more information see https://sasjs.io
33
34 @param action Valid values:
35 @li OPEN - opens the JSON
36 @li OBJ - sends a table with each row as an object
37 @li ARR - sends a table with each row in an array
38 @li CLOSE - closes the JSON
39
40 @param ds the dataset to send. Must be a work table.
41 @param jref= the fileref to which to send the JSON
42 @param dslabel= the name to give the table in the exported JSON
43 @param fmt= Whether to keep or strip formats from the table
44 @param engine= Which engine to use to send the JSON, valid options are:
45 @li PROCJSON (default)
46 @li DATASTEP (more reliable when data has non standard characters)
47
48 @param dbg= DEPRECATED - was used to conditionally add PRETTY to
49 proc json but this can cause line truncation in large files.
50
51 <h4> Related Macros <h4>
52 @li mp_ds2fmtds.sas
53
54 @version 9.2
55 @author Allan Bowe
56 @source https://github.com/sasjs/core
57
58**/
59
60%macro mp_jsonout(action,ds,jref=_webout,dslabel=,fmt=Y,engine=DATASTEP,dbg=0
61)/*/STORE SOURCE*/;
62%put output location=&jref;
63%if &action=OPEN %then %do;
64 options nobomfile;
65 data _null_;file &jref encoding='utf-8';
66 put '{"START_DTTM" : "' "%sysfunc(datetime(),datetime20.3)" '"';
67 run;
68%end;
69%else %if (&action=ARR or &action=OBJ) %then %do;
70 options validvarname=upcase;
71 data _null_;file &jref mod encoding='utf-8';
72 put ", ""%lowcase(%sysfunc(coalescec(&dslabel,&ds)))"":";
73
74 %if &engine=PROCJSON %then %do;
75 data;run;%let tempds=&syslast;
76 proc sql;drop table &tempds;
77 data &tempds /view=&tempds;set &ds;
78 %if &fmt=N %then format _numeric_ best32.;;
79 proc json out=&jref pretty
80 %if &action=ARR %then nokeys ;
81 ;export &tempds / nosastags fmtnumeric;
82 run;
83 proc sql;drop view &tempds;
84 %end;
85 %else %if &engine=DATASTEP %then %do;
86 %local cols i tempds;
87 %let cols=0;
88 %if %sysfunc(exist(&ds)) ne 1 & %sysfunc(exist(&ds,VIEW)) ne 1 %then %do;
89 %put &sysmacroname: &ds NOT FOUND!!!;
90 %return;
91 %end;
92 %if &fmt=Y %then %do;
93 %put converting every variable to a formatted variable;
94 /* see mp_ds2fmtds.sas for source */
95 proc contents noprint data=&ds
96 out=_data_(keep=name type length format formatl formatd varnum);
97 run;
98 proc sort;
99 by varnum;
100 run;
101 %local fmtds;
102 %let fmtds=%scan(&syslast,2,.);
103 /* prepare formats and varnames */
104 data _null_;
105 if _n_=1 then call symputx('nobs',nobs,'l');
106 set &fmtds end=last nobs=nobs;
107 name=upcase(name);
108 /* fix formats */
109 if type=2 or type=6 then do;
110 length fmt $49.;
111 if format='' then fmt=cats('$',length,'.');
112 else if formatl=0 then fmt=cats(format,'.');
113 else fmt=cats(format,formatl,'.');
114 newlen=max(formatl,length);
115 end;
116 else do;
117 if format='' then fmt='best.';
118 else if formatl=0 then fmt=cats(format,'.');
119 else if formatd=0 then fmt=cats(format,formatl,'.');
120 else fmt=cats(format,formatl,'.',formatd);
121 /* needs to be wide, for datetimes etc */
122 newlen=max(length,formatl,24);
123 end;
124 /* 32 char unique name */
125 newname='sasjs'!!substr(cats(put(md5(name),$hex32.)),1,27);
126
127 call symputx(cats('name',_n_),name,'l');
128 call symputx(cats('newname',_n_),newname,'l');
129 call symputx(cats('len',_n_),newlen,'l');
130 call symputx(cats('fmt',_n_),fmt,'l');
131 call symputx(cats('type',_n_),type,'l');
132 run;
133 data &fmtds;
134 /* rename on entry */
135 set &ds(rename=(
136 %local i;
137 %do i=1 %to &nobs;
138 &&name&i=&&newname&i
139 %end;
140 ));
141 %do i=1 %to &nobs;
142 length &&name&i $&&len&i;
143 &&name&i=left(put(&&newname&i,&&fmt&i));
144 drop &&newname&i;
145 %end;
146 if _error_ then call symputx('syscc',1012);
147 run;
148 %let ds=&fmtds;
149 %end; /* &fmt=Y */
150 data _null_;file &jref mod encoding='utf-8';
151 put "["; call symputx('cols',0,'l');
152 proc sort
153 data=sashelp.vcolumn(where=(libname='WORK' & memname="%upcase(&ds)"))
154 out=_data_;
155 by varnum;
156
157 data _null_;
158 set _last_ end=last;
159 call symputx(cats('name',_n_),name,'l');
160 call symputx(cats('type',_n_),type,'l');
161 call symputx(cats('len',_n_),length,'l');
162 if last then call symputx('cols',_n_,'l');
163 run;
164
165 proc format; /* credit yabwon for special null removal */
166 value bart ._ - .z = null
167 other = [best.];
168
169 data;run; %let tempds=&syslast; /* temp table for spesh char management */
170 proc sql; drop table &tempds;
171 data &tempds/view=&tempds;
172 attrib _all_ label='';
173 %do i=1 %to &cols;
174 %if &&type&i=char %then %do;
175 length &&name&i $32767;
176 format &&name&i $32767.;
177 %end;
178 %end;
179 set &ds;
180 format _numeric_ bart.;
181 %do i=1 %to &cols;
182 %if &&type&i=char %then %do;
183 &&name&i='"'!!trim(prxchange('s/"/\"/',-1,
184 prxchange('s/'!!'0A'x!!'/\n/',-1,
185 prxchange('s/'!!'0D'x!!'/\r/',-1,
186 prxchange('s/'!!'09'x!!'/\t/',-1,
187 prxchange('s/\\/\\\\/',-1,&&name&i)
188 )))))!!'"';
189 %end;
190 %end;
191 run;
192 /* write to temp loc to avoid _webout truncation
193 - https://support.sas.com/kb/49/325.html */
194 filename _sjs temp lrecl=131068 encoding='utf-8';
195 data _null_; file _sjs lrecl=131068 encoding='utf-8' mod;
196 set &tempds;
197 if _n_>1 then put "," @; put
198 %if &action=ARR %then "[" ; %else "{" ;
199 %do i=1 %to &cols;
200 %if &i>1 %then "," ;
201 %if &action=OBJ %then """&&name&i"":" ;
202 &&name&i
203 %end;
204 %if &action=ARR %then "]" ; %else "}" ; ;
205 proc sql;
206 drop view &tempds;
207 /* now write the long strings to _webout 1 byte at a time */
208 data _null_;
209 length filein 8 fileid 8;
210 filein = fopen("_sjs",'I',1,'B');
211 fileid = fopen("&jref",'A',1,'B');
212 rec = '20'x;
213 do while(fread(filein)=0);
214 rc = fget(filein,rec,1);
215 rc = fput(fileid, rec);
216 rc =fwrite(fileid);
217 end;
218 rc = fclose(filein);
219 rc = fclose(fileid);
220 run;
221 filename _sjs clear;
222 data _null_; file &jref mod encoding='utf-8';
223 put "]";
224 run;
225 %end;
226%end;
227
228%else %if &action=CLOSE %then %do;
229 data _null_;file &jref encoding='utf-8' mod;
230 put "}";
231 run;
232%end;
233%mend mp_jsonout;