mv_webout.sas
Go to the documentation of this file.
1 /**
2  @file
3  @brief Send data to/from the SAS Viya Job Execution Service
4  @details This macro should be added to the start of each Job Execution
5  Service, **immediately** followed by a call to:
6 
7  %mv_webout(FETCH)
8 
9  This will read all the input data and create same-named SAS datasets in the
10  WORK library. You can then insert your code, and send data back using the
11  following syntax:
12 
13  data some datasets; * make some data ;
14  retain some columns;
15  run;
16 
17  %mv_webout(OPEN)
18  %mv_webout(ARR,some) * Array format, fast, suitable for large tables ;
19  %mv_webout(OBJ,datasets) * Object format, easier to work with ;
20  %mv_webout(CLOSE)
21 
22 
23  @param [in] action Either OPEN, ARR, OBJ or CLOSE
24  @param [in] ds The dataset to send back to the frontend
25  @param [in] _webout= fileref for returning the json
26  @param [out] fref=(_mvwtemp) Temp fileref to which to write the output
27  @param [out] dslabel= value to use instead of table name for sending to JSON
28  @param [in] fmt= (N) Setting Y converts all vars to their formatted values
29  @param [in] stream=(Y) Change to N if not streaming to _webout
30  @param [in] missing= (NULL) Special numeric missing values can be sent as NULL
31  (eg `null`) or as STRING values (eg `".a"` or `".b"`)
32  @param [in] showmeta= (N) Set to Y to output metadata alongside each table,
33  such as the column formats and types. The metadata is contained inside an
34  object with the same name as the table but prefixed with a dollar sign - ie,
35  `,"$tablename":{"formats":{"col1":"$CHAR1"},"types":{"COL1":"C"}}`
36  @param [in] maxobs= (MAX) Provide an integer to limit the number of input rows
37  that should be converted to output JSON
38  @param [in] workobs= (0) When set to a positive integer, will create a new
39  output object (WORK) which contains this number of observations from all
40  tables in the WORK library.
41 
42  <h4> SAS Macros </h4>
43  @li mp_jsonout.sas
44  @li mf_getuser.sas
45 
46  <h4> Related Macros </h4>
47  @li ms_webout.sas
48  @li mm_webout.sas
49 
50  @version Viya 3.3
51  @author Allan Bowe, source: https://github.com/sasjs/core
52 
53 **/
54 %macro mv_webout(action,ds,fref=_mvwtemp,dslabel=,fmt=N,stream=Y,missing=NULL
55  ,showmeta=N,maxobs=MAX,workobs=0
56 );
57 %global _webin_file_count _webin_fileuri _debug _omittextlog _webin_name
58  sasjs_tables SYS_JES_JOB_URI;
59 %if %index("&_debug",log) %then %let _debug=131;
60 
61 %local i tempds table;
62 %let action=%upcase(&action);
63 
64 %if &action=FETCH %then %do;
65  %if %upcase(&_omittextlog)=FALSE or %str(&_debug) ge 131 %then %do;
66  options mprint notes mprintnest;
67  %end;
68 
69  %if not %symexist(_webin_fileuri1) %then %do;
70  %let _webin_file_count=%eval(&_webin_file_count+0);
71  %let _webin_fileuri1=&_webin_fileuri;
72  %let _webin_name1=&_webin_name;
73  %end;
74 
75  /* if the sasjs_tables param is passed, we expect param based upload */
76  %if %length(&sasjs_tables.X)>1 %then %do;
77 
78  /* convert data from macro variables to datasets */
79  %do i=1 %to %sysfunc(countw(&sasjs_tables));
80  %let table=%scan(&sasjs_tables,&i,%str( ));
81  %if %symexist(sasjs&i.data0)=0 %then %let sasjs&i.data0=1;
82  data _null_;
83  file "%sysfunc(pathname(work))/&table..csv" recfm=n;
84  retain nrflg 0;
85  length line $32767;
86  do i=1 to &&sasjs&i.data0;
87  if &&sasjs&i.data0=1 then line=symget("sasjs&i.data");
88  else line=symget(cats("sasjs&i.data",i));
89  if i=1 and substr(line,1,7)='%nrstr(' then do;
90  nrflg=1;
91  line=substr(line,8);
92  end;
93  if i=&&sasjs&i.data0 and nrflg=1 then do;
94  line=substr(line,1,length(line)-1);
95  end;
96  put line +(-1) @;
97  end;
98  run;
99  data _null_;
100  infile "%sysfunc(pathname(work))/&table..csv" termstr=crlf ;
101  input;
102  if _n_=1 then call symputx('input_statement',_infile_);
103  list;
104  data work.&table;
105  infile "%sysfunc(pathname(work))/&table..csv" firstobs=2 dsd
106  termstr=crlf;
107  input &input_statement;
108  run;
109  %end;
110  %end;
111  %else %do i=1 %to &_webin_file_count;
112  /* read in any files that are sent */
113  /* this part needs refactoring for wide files */
114  filename indata filesrvc "&&_webin_fileuri&i" lrecl=999999;
115  data _null_;
116  infile indata termstr=crlf lrecl=32767;
117  input;
118  if _n_=1 then call symputx('input_statement',_infile_);
119  %if %str(&_debug) ge 131 %then %do;
120  if _n_<20 then putlog _infile_;
121  else stop;
122  %end;
123  %else %do;
124  stop;
125  %end;
126  run;
127  data &&_webin_name&i;
128  infile indata firstobs=2 dsd termstr=crlf ;
129  input &input_statement;
130  run;
131  %let sasjs_tables=&sasjs_tables &&_webin_name&i;
132  %end;
133 %end;
134 %else %if &action=OPEN %then %do;
135  /* setup webout */
136  OPTIONS NOBOMFILE;
137  %if "X&SYS_JES_JOB_URI.X"="XX" %then %do;
138  filename _webout temp lrecl=999999 mod;
139  %end;
140  %else %do;
141  filename _webout filesrvc parenturi="&SYS_JES_JOB_URI"
142  name="_webout.json" lrecl=999999 mod;
143  %end;
144 
145  /* setup temp ref */
146  %if %upcase(&fref) ne _WEBOUT %then %do;
147  filename &fref temp lrecl=999999 permission='A::u::rwx,A::g::rw-,A::o::---';
148  %end;
149 
150  /* setup json */
151  data _null_;file &fref;
152  put '{"SYSDATE" : "' "&SYSDATE" '"';
153  put ',"SYSTIME" : "' "&SYSTIME" '"';
154  run;
155 %end;
156 %else %if &action=ARR or &action=OBJ %then %do;
157  %mp_jsonout(&action,&ds,dslabel=&dslabel,fmt=&fmt,jref=&fref
158  ,engine=DATASTEP,missing=&missing,showmeta=&showmeta,maxobs=&maxobs
159  )
160 %end;
161 %else %if &action=CLOSE %then %do;
162  %if %str(&workobs) > 0 %then %do;
163  /* send back first XX records of each work table for debugging */
164  data;run;%let tempds=%scan(&syslast,2,.);
165  ods output Members=&tempds;
166  proc datasets library=WORK memtype=data;
167  %local wtcnt;%let wtcnt=0;
168  data _null_;
169  set &tempds;
170  if not (upcase(name) =:"DATA"); /* ignore temp datasets */
171  i+1;
172  call symputx(cats('wt',i),name,'l');
173  call symputx('wtcnt',i,'l');
174  data _null_; file &fref mod; put ",""WORK"":{";
175  %do i=1 %to &wtcnt;
176  %let wt=&&wt&i;
177  data _null_; file &fref mod;
178  dsid=open("WORK.&wt",'is');
179  nlobs=attrn(dsid,'NLOBS');
180  nvars=attrn(dsid,'NVARS');
181  rc=close(dsid);
182  if &i>1 then put ','@;
183  put " ""&wt"" : {";
184  put '"nlobs":' nlobs;
185  put ',"nvars":' nvars;
186  %mp_jsonout(OBJ,&wt,jref=&fref,dslabel=first10rows,showmeta=Y
187  ,maxobs=&workobs
188  )
189  data _null_; file &fref mod;put "}";
190  %end;
191  data _null_; file &fref mod;put "}";run;
192  %end;
193 
194  /* close off json */
195  data _null_;file &fref mod;
196  length SYSPROCESSNAME syserrortext syswarningtext autoexec $512;
197  put ",""_DEBUG"" : ""&_debug"" ";
198  _PROGRAM=quote(trim(resolve(symget('_PROGRAM'))));
199  put ',"_PROGRAM" : ' _PROGRAM ;
200  autoexec=quote(urlencode(trim(getoption('autoexec'))));
201  put ',"AUTOEXEC" : ' autoexec;
202  put ",""MF_GETUSER"" : ""%mf_getuser()"" ";
203  SYS_JES_JOB_URI=quote(trim(resolve(symget('SYS_JES_JOB_URI'))));
204  put ',"SYS_JES_JOB_URI" : ' SYS_JES_JOB_URI ;
205  put ",""SYSJOBID"" : ""&sysjobid"" ";
206  put ",""SYSCC"" : ""&syscc"" ";
207  syserrortext=cats(symget('syserrortext'));
208  if findc(syserrortext,'"\'!!'0A0D09000E0F010210111A'x) then do;
209  syserrortext='"'!!trim(
210  prxchange('s/"/\\"/',-1, /* double quote */
211  prxchange('s/\x0A/\n/',-1, /* new line */
212  prxchange('s/\x0D/\r/',-1, /* carriage return */
213  prxchange('s/\x09/\\t/',-1, /* tab */
214  prxchange('s/\x00/\\u0000/',-1, /* NUL */
215  prxchange('s/\x0E/\\u000E/',-1, /* SS */
216  prxchange('s/\x0F/\\u000F/',-1, /* SF */
217  prxchange('s/\x01/\\u0001/',-1, /* SOH */
218  prxchange('s/\x02/\\u0002/',-1, /* STX */
219  prxchange('s/\x10/\\u0010/',-1, /* DLE */
220  prxchange('s/\x11/\\u0011/',-1, /* DC1 */
221  prxchange('s/\x1A/\\u001A/',-1, /* SUB */
222  prxchange('s/\\/\\\\/',-1,syserrortext)
223  )))))))))))))!!'"';
224  end;
225  else syserrortext=cats('"',syserrortext,'"');
226  put ',"SYSERRORTEXT" : ' syserrortext;
227  put ",""SYSHOSTNAME"" : ""&syshostname"" ";
228  put ",""SYSPROCESSID"" : ""&SYSPROCESSID"" ";
229  put ",""SYSPROCESSMODE"" : ""&SYSPROCESSMODE"" ";
230  SYSPROCESSNAME=quote(urlencode(cats(SYSPROCESSNAME)));
231  put ",""SYSPROCESSNAME"" : " SYSPROCESSNAME;
232  put ",""SYSJOBID"" : ""&sysjobid"" ";
233  put ",""SYSSCPL"" : ""&sysscpl"" ";
234  put ",""SYSSITE"" : ""&syssite"" ";
235  put ",""SYSUSERID"" : ""&sysuserid"" ";
236  sysvlong=quote(trim(symget('sysvlong')));
237  put ',"SYSVLONG" : ' sysvlong;
238  syswarningtext=cats(symget('syswarningtext'));
239  if findc(syswarningtext,'"\'!!'0A0D09000E0F010210111A'x) then do;
240  syswarningtext='"'!!trim(
241  prxchange('s/"/\\"/',-1, /* double quote */
242  prxchange('s/\x0A/\n/',-1, /* new line */
243  prxchange('s/\x0D/\r/',-1, /* carriage return */
244  prxchange('s/\x09/\\t/',-1, /* tab */
245  prxchange('s/\x00/\\u0000/',-1, /* NUL */
246  prxchange('s/\x0E/\\u000E/',-1, /* SS */
247  prxchange('s/\x0F/\\u000F/',-1, /* SF */
248  prxchange('s/\x01/\\u0001/',-1, /* SOH */
249  prxchange('s/\x02/\\u0002/',-1, /* STX */
250  prxchange('s/\x10/\\u0010/',-1, /* DLE */
251  prxchange('s/\x11/\\u0011/',-1, /* DC1 */
252  prxchange('s/\x1A/\\u001A/',-1, /* SUB */
253  prxchange('s/\\/\\\\/',-1,syswarningtext)
254  )))))))))))))!!'"';
255  end;
256  else syswarningtext=cats('"',syswarningtext,'"');
257  put ',"SYSWARNINGTEXT" : ' syswarningtext;
258  put ',"END_DTTM" : "' "%sysfunc(datetime(),E8601DT26.6)" '" ';
259  length memsize $32;
260  memsize="%sysfunc(INPUTN(%sysfunc(getoption(memsize)), best.),sizekmg.)";
261  memsize=quote(cats(memsize));
262  put ',"MEMSIZE" : ' memsize;
263  put "}";
264 
265  %if %upcase(&fref) ne _WEBOUT and &stream=Y %then %do;
266  data _null_; rc=fcopy("&fref","_webout");run;
267  %end;
268 
269 %end;
270 
271 %mend mv_webout;