Macros for SAS Application Developers
https://github.com/sasjs/core
Loading...
Searching...
No Matches
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 This macro can be used to OPEN a JSON stream and send one or more
5 tables as arrays of rows, where each row can be an object or a nested array.
6
7 There are two engines available - DATASTEP or PROCJSON.
8
9 PROC JSON is fast but will produce errs like the ones below if
10 special chars are encountered.
11
12 > (ERR)OR: Some code points did not transcode.
13
14 > An object or array close is not valid at this point in the JSON text.
15
16 > Date value out of range
17
18 If this happens, try running with ENGINE=DATASTEP.
19
20 The DATASTEP engine is used to handle special SAS missing numerics, and
21 can also convert entire datasets to formatted values. Output JSON is always
22 in UTF-8.
23
24 Usage:
25
26 filename tmp temp;
27 data class; set sashelp.class;run;
28
29 %mp_jsonout(OPEN,jref=tmp)
30 %mp_jsonout(OBJ,class,jref=tmp)
31 %mp_jsonout(OBJ,class,dslabel=class2,jref=tmp,showmeta=Y)
32 %mp_jsonout(CLOSE,jref=tmp)
33
34 data _null_;
35 infile tmp;
36 input;putlog _infile_;
37 run;
38
39 If you are building web apps with SAS then you are strongly encouraged to use
40 the mX_createwebservice macros in combination with the
41 [sasjs adapter](https://github.com/sasjs/adapter).
42 For more information see https://sasjs.io
43
44 @param [in] action Valid values:
45 @li OPEN - opens the JSON
46 @li OBJ - sends a table with each row as an object
47 @li ARR - sends a table with each row in an array
48 @li CLOSE - closes the JSON
49 @param [in] ds The dataset to send. Must be a work table.
50 @param [out] jref= (_webout) The fileref to which to send the JSON
51 @param [out] dslabel= The name to give the table in the exported JSON
52 @param [in] fmt= (Y) Whether to keep (Y) or strip (N) formats from the table
53 @param [in] engine= (DATASTEP) Which engine to use to send the JSON. Options:
54 @li PROCJSON (default)
55 @li DATASTEP (more reliable when data has non standard characters)
56 @param [in] missing= (NULL) Special numeric missing values can be sent as NULL
57 (eg `null`) or as STRING values (eg `".a"` or `".b"`)
58 @param [in] showmeta= (N) Set to Y to output metadata alongside each table,
59 such as the column formats and types. The metadata is contained inside an
60 object with the same name as the table but prefixed with a dollar sign - ie,
61 `,"$tablename":{"formats":{"col1":"$CHAR1"},"types":{"COL1":"C"}}`
62 @param [in] maxobs= (MAX) Provide an integer to limit the number of input rows
63 that should be converted to JSON
64
65 <h4> Related Files </h4>
66 @li mp_ds2fmtds.sas
67
68 @version 9.2
69 @author Allan Bowe
70 @source https://github.com/sasjs/core
71
72**/
73%macro mp_jsonout(action,ds,jref=_webout,dslabel=,fmt=Y
74 ,engine=DATASTEP
75 ,missing=NULL
76 ,showmeta=N
77 ,maxobs=MAX
78)/*/STORE SOURCE*/;
79%local tempds colinfo fmtds i numcols numobs stmt_obs lastobs optval
80 tmpds1 tmpds2 tmpds3 tmpds4;
81%let numcols=0;
82%if &maxobs ne MAX %then %let stmt_obs=%str(if _n_>&maxobs then stop;);
83
84%if &action=OPEN %then %do;
85 options nobomfile;
86 data _null_;file &jref encoding='utf-8' lrecl=200;
87 put '{"PROCESSED_DTTM" : "' "%sysfunc(datetime(),E8601DT26.6)" '"';
88 run;
89%end;
90%else %if (&action=ARR or &action=OBJ) %then %do;
91 /* force variable names to always be uppercase in the JSON */
92 options validvarname=upcase;
93 /* To avoid issues with _webout on EBI - such as encoding diffs and truncation
94 (https://support.sas.com/kb/49/325.html) we use temporary files */
95 filename _sjs1 temp lrecl=200 ;
96 data _null_; file _sjs1 encoding='utf-8';
97 put ", ""%lowcase(%sysfunc(coalescec(&dslabel,&ds)))"":";
98 run;
99 /* now write to _webout 1 char at a time */
100 data _null_;
101 infile _sjs1 lrecl=1 recfm=n;
102 file &jref mod lrecl=1 recfm=n;
103 input sourcechar $char1. @@;
104 format sourcechar hex2.;
105 put sourcechar char1. @@;
106 run;
107 filename _sjs1 clear;
108
109 /* grab col defs */
110 proc contents noprint data=&ds
111 out=_data_(keep=name type length format formatl formatd varnum label);
112 run;
113 %let colinfo=%scan(&syslast,2,.);
114 proc sort data=&colinfo;
115 by varnum;
116 run;
117 /* move meta to mac vars */
118 data &colinfo;
119 if _n_=1 then call symputx('numcols',nobs,'l');
120 set &colinfo end=last nobs=nobs;
121 name=upcase(name);
122 /* fix formats */
123 if type=2 or type=6 then do;
124 typelong='char';
125 length fmt $49.;
126 if format='' then fmt=cats('$',length,'.');
127 else if formatl=0 then fmt=cats(format,'.');
128 else fmt=cats(format,formatl,'.');
129 end;
130 else do;
131 typelong='num';
132 if format='' then fmt='best.';
133 else if formatl=0 then fmt=cats(format,'.');
134 else if formatd=0 then fmt=cats(format,formatl,'.');
135 else fmt=cats(format,formatl,'.',formatd);
136 end;
137 /* 32 char unique name */
138 newname='sasjs'!!substr(cats(put(md5(name),$hex32.)),1,27);
139
140 call symputx(cats('name',_n_),name,'l');
141 call symputx(cats('newname',_n_),newname,'l');
142 call symputx(cats('length',_n_),length,'l');
143 call symputx(cats('fmt',_n_),fmt,'l');
144 call symputx(cats('type',_n_),type,'l');
145 call symputx(cats('typelong',_n_),typelong,'l');
146 call symputx(cats('label',_n_),coalescec(label,name),'l');
147 /* overwritten when fmt=Y and a custom format exists in catalog */
148 if typelong='num' then call symputx(cats('fmtlen',_n_),200,'l');
149 else call symputx(cats('fmtlen',_n_),min(32767,ceil((length+10)*1.5)),'l');
150 run;
151
152 %let tempds=%substr(_%sysfunc(compress(%sysfunc(uuidgen()),-)),1,32);
153 proc sql;
154 select count(*) into: lastobs from &ds;
155 %if &maxobs ne MAX %then %let lastobs=%sysfunc(min(&lastobs,&maxobs));
156
157 %if &engine=PROCJSON %then %do;
158 %if &missing=STRING %then %do;
159 %put &sysmacroname: Special Missings not supported in proc json.;
160 %put &sysmacroname: Switching to DATASTEP engine;
161 %goto datastep;
162 %end;
163 data &tempds;
164 set &ds;
165 &stmt_obs;
166 %if &fmt=N %then format _numeric_ best32.;;
167 /* PRETTY is necessary to avoid line truncation in large files */
168 filename _sjs2 temp lrecl=131068 encoding='utf-8';
169 proc json out=_sjs2 pretty
170 %if &action=ARR %then nokeys ;
171 ;export &tempds / nosastags fmtnumeric;
172 run;
173 /* send back to webout */
174 data _null_;
175 infile _sjs2 lrecl=1 recfm=n;
176 file &jref mod lrecl=1 recfm=n;
177 input sourcechar $char1. @@;
178 format sourcechar hex2.;
179 put sourcechar char1. @@;
180 run;
181 filename _sjs2 clear;
182 %end;
183 %else %if &engine=DATASTEP %then %do;
184 %datastep:
185 %if %sysfunc(exist(&ds)) ne 1 & %sysfunc(exist(&ds,VIEW)) ne 1
186 %then %do;
187 %put &sysmacroname: &ds NOT FOUND!!!;
188 %return;
189 %end;
190
191 %if &fmt=Y %then %do;
192 /**
193 * Extract format definitions
194 * First, by getting library locations from dictionary.formats
195 * Then, by exporting the width using proc format
196 * Cannot use maxw from sashelp.vformat as not always populated
197 * Cannot use fmtinfo() as not supported in all flavours
198 */
199 %let tmpds1=%substr(fmtsum%sysfunc(compress(%sysfunc(uuidgen()),-)),1,32);
200 %let tmpds2=%substr(cntl%sysfunc(compress(%sysfunc(uuidgen()),-)),1,32);
201 %let tmpds3=%substr(cntl%sysfunc(compress(%sysfunc(uuidgen()),-)),1,32);
202 %let tmpds4=%substr(col%sysfunc(compress(%sysfunc(uuidgen()),-)),1,32);
203 proc sql noprint;
204 create table &tmpds1 as
205 select cats(libname,'.',memname) as FMTCAT,
206 FMTNAME
207 from dictionary.formats
208 where fmttype='F' and libname is not null
209 and fmtname in (select format from &colinfo where format is not null)
210 order by 1;
211 create table &tmpds2(
212 FMTNAME char(32),
213 LENGTH num
214 );
215 %local catlist cat fmtlist i;
216 select distinct fmtcat into: catlist separated by ' ' from &tmpds1;
217 %do i=1 %to %sysfunc(countw(&catlist,%str( )));
218 %let cat=%scan(&catlist,&i,%str( ));
219 proc sql;
220 select distinct fmtname into: fmtlist separated by ' '
221 from &tmpds1 where fmtcat="&cat";
222 proc format lib=&cat cntlout=&tmpds3(keep=fmtname length);
223 select &fmtlist;
224 run;
225 proc sql;
226 insert into &tmpds2 select distinct fmtname,length from &tmpds3;
227 %end;
228
229 proc sql;
230 create table &tmpds4 as
231 select a.*, b.length as MAXW
232 from &colinfo a
233 left join &tmpds2 b
234 on cats(a.format)=cats(upcase(b.fmtname))
235 order by a.varnum;
236 data _null_;
237 set &tmpds4;
238 if not missing(maxw);
239 call symputx(
240 cats('fmtlen',_n_),
241 /* vars need extra padding due to JSON escaping of special chars */
242 min(32767,ceil((max(length,maxw)+10)*1.5))
243 ,'l'
244 );
245 run;
246
247 /* configure varlenchk - as we are explicitly shortening the variables */
248 %let optval=%sysfunc(getoption(varlenchk));
249 options varlenchk=NOWARN;
250 data _data_(compress=char);
251 /* shorten the new vars */
252 length
253 %do i=1 %to &numcols;
254 &&name&i $&&fmtlen&i
255 %end;
256 ;
257 /* rename on entry */
258 set &ds(rename=(
259 %do i=1 %to &numcols;
260 &&name&i=&&newname&i
261 %end;
262 ));
263 &stmt_obs;
264
265 drop
266 %do i=1 %to &numcols;
267 &&newname&i
268 %end;
269 ;
270 %do i=1 %to &numcols;
271 %if &&typelong&i=num %then %do;
272 &&name&i=cats(put(&&newname&i,&&fmt&i));
273 %end;
274 %else %do;
275 &&name&i=put(&&newname&i,&&fmt&i);
276 %end;
277 %end;
278 if _error_ then do;
279 call symputx('syscc',1012);
280 stop;
281 end;
282 run;
283 %let fmtds=&syslast;
284 options varlenchk=&optval;
285 %end;
286
287 proc format; /* credit yabwon for special null removal */
288 value bart (default=40)
289 %if &missing=NULL %then %do;
290 ._ - .z = null
291 %end;
292 %else %do;
293 ._ = [quote()]
294 . = null
295 .a - .z = [quote()]
296 %end;
297 other = [best.];
298
299 data &tempds;
300 attrib _all_ label='';
301 %do i=1 %to &numcols;
302 %if &&typelong&i=char or &fmt=Y %then %do;
303 length &&name&i $&&fmtlen&i...;
304 format &&name&i $&&fmtlen&i...;
305 %end;
306 %end;
307 %if &fmt=Y %then %do;
308 set &fmtds;
309 %end;
310 %else %do;
311 set &ds;
312 %end;
313 &stmt_obs;
314 format _numeric_ bart.;
315 %do i=1 %to &numcols;
316 %if &&typelong&i=char or &fmt=Y %then %do;
317 if findc(&&name&i,'"\'!!'0A0D09000E0F010210111A'x) then do;
318 &&name&i='"'!!trim(
319 prxchange('s/"/\\"/',-1, /* double quote */
320 prxchange('s/\x0A/\n/',-1, /* new line */
321 prxchange('s/\x0D/\r/',-1, /* carriage return */
322 prxchange('s/\x09/\\t/',-1, /* tab */
323 prxchange('s/\x00/\\u0000/',-1, /* NUL */
324 prxchange('s/\x0E/\\u000E/',-1, /* SS */
325 prxchange('s/\x0F/\\u000F/',-1, /* SF */
326 prxchange('s/\x01/\\u0001/',-1, /* SOH */
327 prxchange('s/\x02/\\u0002/',-1, /* STX */
328 prxchange('s/\x10/\\u0010/',-1, /* DLE */
329 prxchange('s/\x11/\\u0011/',-1, /* DC1 */
330 prxchange('s/\x1A/\\u001A/',-1, /* SUB */
331 prxchange('s/\\/\\\\/',-1,&&name&i)
332 )))))))))))))!!'"';
333 end;
334 else &&name&i=quote(cats(&&name&i));
335 %end;
336 %end;
337 run;
338
339 filename _sjs3 temp lrecl=131068 ;
340 data _null_;
341 file _sjs3 encoding='utf-8';
342 if _n_=1 then put "[";
343 set &tempds;
344 if _n_>1 then put "," @; put
345 %if &action=ARR %then "[" ; %else "{" ;
346 %do i=1 %to &numcols;
347 %if &i>1 %then "," ;
348 %if &action=OBJ %then """&&name&i"":" ;
349 "&&name&i"n /* name literal for reserved variable names */
350 %end;
351 %if &action=ARR %then "]" ; %else "}" ; ;
352
353 /* close out the table */
354 data _null_;
355 file _sjs3 mod encoding='utf-8';
356 put ']';
357 run;
358 data _null_;
359 infile _sjs3 lrecl=1 recfm=n;
360 file &jref mod lrecl=1 recfm=n;
361 input sourcechar $char1. @@;
362 format sourcechar hex2.;
363 put sourcechar char1. @@;
364 run;
365 filename _sjs3 clear;
366 %end;
367
368 proc sql;
369 drop table &colinfo, &tempds;
370
371 %if %substr(&showmeta,1,1)=Y %then %do;
372 filename _sjs4 temp lrecl=131068 encoding='utf-8';
373 data _null_;
374 file _sjs4;
375 length label $350;
376 put ", ""$%lowcase(%sysfunc(coalescec(&dslabel,&ds)))"":{""vars"":{";
377 do i=1 to &numcols;
378 name=quote(trim(symget(cats('name',i))));
379 format=quote(trim(symget(cats('fmt',i))));
380 label=quote(prxchange('s/\\/\\\\/',-1,trim(symget(cats('label',i)))));
381 length=quote(trim(symget(cats('length',i))));
382 type=quote(trim(symget(cats('typelong',i))));
383 if i>1 then put "," @@;
384 put name ':{"format":' format ',"label":' label
385 ',"length":' length ',"type":' type '}';
386 end;
387 put '}}';
388 run;
389 /* send back to webout */
390 data _null_;
391 infile _sjs4 lrecl=1 recfm=n;
392 file &jref mod lrecl=1 recfm=n;
393 input sourcechar $char1. @@;
394 format sourcechar hex2.;
395 put sourcechar char1. @@;
396 run;
397 filename _sjs4 clear;
398 %end;
399%end;
400
401%else %if &action=CLOSE %then %do;
402 data _null_; file &jref encoding='utf-8' mod ;
403 put "}";
404 run;
405%end;
406%mend mp_jsonout;