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  >An object or array close is not valid at this point in the JSON text.
8  >Date value out of range
9 
10  If this happens, try running with ENGINE=DATASTEP.
11 
12  Usage:
13 
14  filename tmp temp;
15  data class; set sashelp.class;run;
16 
17  %mp_jsonout(OBJ,class,jref=tmp)
18 
19  data _null_;
20  infile tmp;
21  input;list;
22  run;
23 
24  If you are building web apps with SAS then you are strongly encouraged to use
25  the mX_createwebservice macros in combination with the
26  [sasjs adapter](https://github.com/sasjs/adapter).
27  For more information see https://sasjs.io
28 
29  @param action Valid values:
30  * OPEN - opens the JSON
31  * OBJ - sends a table with each row as an object
32  * ARR - sends a table with each row in an array
33  * CLOSE - closes the JSON
34 
35  @param ds the dataset to send. Must be a work table.
36  @param jref= the fileref to which to send the JSON
37  @param dslabel= the name to give the table in the exported JSON
38  @param fmt= Whether to keep or strip formats from the table
39  @param engine= Which engine to use to send the JSON, options are:
40  * PROCJSON (default)
41  * DATASTEP
42 
43  @param dbg= DEPRECATED - was used to conditionally add PRETTY to
44  proc json but this can cause line truncation in large files.
45 
46 
47  @version 9.2
48  @author Allan Bowe
49  @source https://github.com/sasjs/core
50 
51 **/
52 
53 %macro mp_jsonout(action,ds,jref=_webout,dslabel=,fmt=Y,engine=PROCJSON,dbg=0
54 )/*/STORE SOURCE*/;
55 %put output location=&jref;
56 %if &action=OPEN %then %do;
57  data _null_;file &jref encoding='utf-8';
58  put '{"START_DTTM" : "' "%sysfunc(datetime(),datetime20.3)" '"';
59  run;
60 %end;
61 %else %if (&action=ARR or &action=OBJ) %then %do;
62  options validvarname=upcase;
63  data _null_;file &jref mod encoding='utf-8';
64  put ", ""%lowcase(%sysfunc(coalescec(&dslabel,&ds)))"":";
65 
66  %if &engine=PROCJSON %then %do;
67  data;run;%let tempds=&syslast;
68  proc sql;drop table &tempds;
69  data &tempds /view=&tempds;set &ds;
70  %if &fmt=N %then format _numeric_ best32.;;
71  proc json out=&jref pretty
72  %if &action=ARR %then nokeys ;
73  ;export &tempds / nosastags fmtnumeric;
74  run;
75  proc sql;drop view &tempds;
76  %end;
77  %else %if &engine=DATASTEP %then %do;
78  %local cols i tempds;
79  %let cols=0;
80  %if %sysfunc(exist(&ds)) ne 1 & %sysfunc(exist(&ds,VIEW)) ne 1 %then %do;
81  %put &sysmacroname: &ds NOT FOUND!!!;
82  %return;
83  %end;
84  data _null_;file &jref mod ;
85  put "["; call symputx('cols',0,'l');
86  proc sort data=sashelp.vcolumn(where=(libname='WORK' & memname="%upcase(&ds)"))
87  out=_data_;
88  by varnum;
89 
90  data _null_;
91  set _last_ end=last;
92  call symputx(cats('name',_n_),name,'l');
93  call symputx(cats('type',_n_),type,'l');
94  call symputx(cats('len',_n_),length,'l');
95  if last then call symputx('cols',_n_,'l');
96  run;
97 
98  proc format; /* credit yabwon for special null removal */
99  value bart ._ - .z = null
100  other = [best.];
101 
102  data;run; %let tempds=&syslast; /* temp table for spesh char management */
103  proc sql; drop table &tempds;
104  data &tempds/view=&tempds;
105  attrib _all_ label='';
106  %do i=1 %to &cols;
107  %if &&type&i=char %then %do;
108  length &&name&i $32767;
109  format &&name&i $32767.;
110  %end;
111  %end;
112  set &ds;
113  format _numeric_ bart.;
114  %do i=1 %to &cols;
115  %if &&type&i=char %then %do;
116  &&name&i='"'!!trim(prxchange('s/"/\"/',-1,
117  prxchange('s/'!!'0A'x!!'/\n/',-1,
118  prxchange('s/'!!'0D'x!!'/\r/',-1,
119  prxchange('s/'!!'09'x!!'/\t/',-1,
120  prxchange('s/\\/\\\\/',-1,&&name&i)
121  )))))!!'"';
122  %end;
123  %end;
124  run;
125  /* write to temp loc to avoid _webout truncation - https://support.sas.com/kb/49/325.html */
126  filename _sjs temp lrecl=131068 encoding='utf-8';
127  data _null_; file _sjs lrecl=131068 encoding='utf-8' mod;
128  set &tempds;
129  if _n_>1 then put "," @; put
130  %if &action=ARR %then "[" ; %else "{" ;
131  %do i=1 %to &cols;
132  %if &i>1 %then "," ;
133  %if &action=OBJ %then """&&name&i"":" ;
134  &&name&i
135  %end;
136  %if &action=ARR %then "]" ; %else "}" ; ;
137  proc sql;
138  drop view &tempds;
139  /* now write the long strings to _webout 1 byte at a time */
140  data _null_;
141  length filein 8 fileid 8;
142  filein = fopen("_sjs",'I',1,'B');
143  fileid = fopen("&jref",'A',1,'B');
144  rec = '20'x;
145  do while(fread(filein)=0);
146  rc = fget(filein,rec,1);
147  rc = fput(fileid, rec);
148  rc =fwrite(fileid);
149  end;
150  rc = fclose(filein);
151  rc = fclose(fileid);
152  run;
153  filename _sjs clear;
154  data _null_; file &jref mod encoding='utf-8';
155  put "]";
156  run;
157  %end;
158 %end;
159 
160 %else %if &action=CLOSE %then %do;
161  data _null_;file &jref encoding='utf-8';
162  put "}";
163  run;
164 %end;
165 %mend;