Loading...
Searching...
No Matches
viewdata.sas
Go to the documentation of this file.
1/**
2 @file viewdata.sas
3 @brief Provide the raw view of the data
4 @details Pass a LIBDS and FILTER_RK to return a dataset for viewing.
5 VIEW datasets include all columns / rows (unlike EDIT, which are filtered
6 for current records and don't include the SCD2 etc cols).
7
8 <h4> Service Inputs </h4>
9
10 <h5> SASCONTROLTABLE </h5>
11 |LIBDS:$41.|FILTER_RK:$5.|SEARCHTYPE:$4|SEARCHVAL:$1000
12 |---|---|---|---
13 |DC258467.MPE_X_TEST|-1|CHAR|Some String|
14
15 <h4> Service Outputs </h4>
16
17 <h5> cols </h5>
18 @li DDTYPE
19 @li FORMAT
20 @li LABEL
21 @li LENGTH
22 @li NAME
23 @li TYPE
24 @li VARNUM
25
26 <h5> sasparams </h5>
27 @li FILTER_TEXT
28 @li NOBS
29 @li PK_FIELDS - string seperated list of primary key fields, if they exist
30 @li TABLENAME
31 @li TABLEURI
32 @li VARS
33
34 <h5> versions </h5>
35 history of DC versions for this particular table
36
37 <h5> viewdata </h5>
38 The raw data from the target table.
39
40 <h4> SAS Macros </h4>
41 @li dc_assignlib.sas
42 @li dc_createdataset.sas
43 @li dc_gettableid.sas
44 @li mf_existds.sas
45 @li mf_getvarcount.sas
46 @li mf_nobs.sas
47 @li mf_verifymacvars.sas
48 @li mp_abort.sas
49 @li mp_cntlout.sas
50 @li mp_getcols.sas
51 @li mp_getpk.sas
52 @li mp_jsonout.sas
53 @li mp_searchdata.sas
54 @li mp_validatecol.sas
55 @li mpe_columnlevelsecurity.sas
56 @li mpe_dsmeta.sas
57 @li mpe_getversions.sas
58 @li mpe_filtermaster.sas
59
60
61 @version 9.2
62 @author 4GL Apps Ltd
63 @copyright 4GL Apps Ltd. This code may only be used within Data Controller
64 and may not be re-distributed or re-sold without the express permission of
65 4GL Apps Ltd.
66**/
67
68%mpeinit()
69
70/* configure macvars */
71%global LIBDS FILTER_RK SEARCHVAL SEARCHTYPE FMT_IND;
72
73%let maxrows=250;
74
75/* avoid code injection */
76%let FMT_IND=0;
77%let SEARCHTYPE=;
78%let SEARCHVAL=;
79%let FILTER_RK=;
80%let LIBDS=;
81
82/**
83 * Validate inputs
84 */
85data work.intest;
86 length libds $41 filter_rk 8. searchval $100 searchtype $4;
87 set work.SASCONTROLTABLE;
88 call symputx('orig_libds',libds);
89 /* validate filter_rk */
90 if filter_rk le 0 then filter_rk=-1;
91
92 /* check if the request is for a format catalog */
93 if substr(cats(reverse(libds)),1,3)=:'CF-' then do;
94 libds=scan(libds,1,'-');
95 putlog "Format Catalog Captured";
96 call symputx('fmt_ind',1);
97 end;
98 putlog (_all_)(=);
99
100 /* validate libds */
101 %mp_validatecol(LIBDS,LIBDS,is_libds)
102
103 if searchtype in ('CHAR','NUM') then do;
104 searchval=tranwrd(searchval,'%','');
105 searchval=tranwrd(searchval,'&','');
106 searchval=tranwrd(searchval,';','');
107 searchval=tranwrd(searchval,'"','');
108 call symputx('searchtype',searchtype);
109 call symputx('searchval',searchval);
110 end;
111 else if searchtype not in ('','NONE') then do;
112 putlog 'ERR' 'OR: Invalid searchtype:' searchtype;
113 stop;
114 end;
115
116 if is_libds=0 then do;
117 putlog 'ERR' 'OR: Invalid libds:' libds;
118 stop;
119 end;
120 else do;
121 call symputx('filter_rk',filter_rk);
122 call symputx('libds',libds);
123 end;
124 output;
125 stop;
126run;
127
128%mp_abort(iftrue= (%mf_verifymacvars(libds filter_rk fmt_ind)=0)
129 ,mac=&_program..sas
130 ,msg=%str(Problem with macro inputs)
131)
132
133%mp_abort(iftrue= (%mf_nobs(work.intest)=0)
134 ,mac=&_program
135 ,msg=%str(Some err with service inputs)
136)
137%mp_abort(iftrue= (&syscc ne 0)
138 ,mac=&_program..sas
139 ,msg=%str(syscc=&syscc)
140)
141
142
143/**
144 * assign the Library
145 */
146%dc_assignlib(READ,%scan(&LIBDS,1,.))
147
148/* abort if looking for a format and the catalog doesn't exist */
149%mp_abort(iftrue= (&fmt_ind=1 and %sysfunc(exist(&libds,CATALOG))=0)
150 ,mac=&_program..sas
151 ,msg=%str(Catalog &libds does not exist!)
152)
153
154/**
155 check if dataset can actually be opened - as library may exist but it may not
156 be possible to assign, and even if it can, the physical table may not exist
157**/
158data _null_;
159 if &fmt_ind=0 then do;
160 dsid=open("&libds");
161 rc=close(dsid);
162 end;
163 else dsid=42;
164 call symputx('existds',dsid,'l');
165 putlog 'dataset exists check:' dsid;
166run;
167
168/**
169 * get the data
170 */
171%global dsobs;
172%let dsobs=0;
173%macro x();
174%if &existds>0 %then %do;
175
176 %if &fmt_ind=1 %then %do;
177 /* export format and point the libds to the output table from here on */
178 %mp_cntlout(
179 libcat=&libds
180 ,fmtlist=0
181 ,cntlout=work.fmtextract
182 )
183 %let libds=WORK.FMTEXTRACT;
184 proc datasets lib=work noprint;
185 modify FMTEXTRACT;
186 index create
187 pk_cntlout=(type fmtname fmtrow)
188 /nomiss unique;
189 quit;
190 %end;
191
192 proc sql noprint;
193 select count(*) into: dsobs from &libds;
194
195 %put preparing query;
196 %mpe_filtermaster(VIEW,&libds,
197 dclib=&mpelib,
198 filter_rk=&filter_rk,
199 outref=filtref,
200 outds=work.query
201 )
202 %put printing generated filterquery:;
203 data _null_;
204 infile filtref;
205 input;
206 putlog _infile_;
207 run;
208
209 %if &searchtype=NONE or "%trim(&searchtype) " = " " %then %do;
210 /* get row count */
211 filename rows temp;
212 data _null_;
213 file rows;
214 infile filtref end=eof;
215 input;
216 if _n_=1 then do;
217 put 'proc sql;';
218 put "select count(*) into: dsobs from &libds where";
219 end;
220 put _infile_;
221 if eof then put ';';
222 run;
223 data _null_;
224 infile rows;
225 input;
226 putlog _infile_;
227 run;
228
229 %inc rows;
230
231 /* send actual data, filtered and row-capped */
232 data work.viewdata;
233 set &libds;
234 where %inc filtref;;
235 if _n_>&maxrows then stop;
236 run;
237 %if %mf_nobs(work.viewdata)=0 %then %do;
238 data work.viewdata;
239 /* send empty row if empty table to help with hot rendering */
240 output;
241 set work.viewdata;
242 run;
243 %end;
244 %end;
245 %else %do;
246 data work.vwsearch/view=work.vwsearch;
247 set &libds;
248 where %inc filtref;;
249 run;
250 %if %upcase(&searchtype)=CHAR %then %do;
251 %mp_searchdata(lib=work
252 ,ds=vwsearch
253 ,string=%superq(searchval)
254 ,outobs=&maxrows
255 )
256 %end;
257 %else %if %upcase(&searchtype)=NUM %then %do;
258 %mp_searchdata(lib=work
259 ,ds=vwsearch
260 ,numval=%superq(searchval)
261 ,outobs=&maxrows
262 )
263 %end;
264 %if %mf_existds(libds=MPSEARCH.vwsearch) %then %do;
265 %let dsobs=%mf_nobs(MPSEARCH.vwsearch);
266 data viewdata;
267 set MPSEARCH.vwsearch;
268 if _n_<&maxrows;
269 run;
270 %end;
271 %else %do;
272 %let dsobs=0;
273 data viewdata;
274 set &libds;
275 stop;
276 run;
277 %end;
278 %end;
279%end;
280%else %do;
281 /* physical table is not accessible so create from metatadata definition */
282 %dc_createdataset(libds=&libds,outds=viewdata)
283 data viewData;
284 output;
285 set viewdata;
286 run;
287
288 /* make filtref / work.query / work.groups to avoid downstream issues */
289 filename filtref temp;
290 data work.query;
291 file filtref;
292 x=0;
293 put x;
294 run;
295 data work.groups;
296 length groupuri groupname $32 groupdesc $128 ;
297 call missing (of _all_);
298 output;
299 stop;
300 run;
301%end;
302%mend x; %x()
303
304/* apply column level security */
305%mpe_columnlevelsecurity(%scan(&libds,1,.),%scan(&libds,2,.),work.viewdata
306 ,mode=VIEW
307 ,clsds=&mpelib..mpe_column_level_security
308 ,groupds=work.groups /* was created in mpe_filtermaster */
309 ,outds=work.viewdata2
310 ,outmeta=work.cls_rules
311)
312
313
314/* get table uri (if sas 9) to enable linking direct to lineage */
315%dc_gettableid(libref=%scan(&libds,1,.)
316 ,ds=%scan(&libds,2,.)
317 ,outds=work.parambase
318)
319
320data _null_;
321 infile filtref end=eof;
322 input;
323 length filter_text $32767;
324 retain filter_text;
325 filter_text=catx(' ',filter_text,_infile_);
326 if eof then do;
327 if cats(filter_text)='1=1' then filter_text='';
328 call symputx('filter_text',filter_text);
329 end;
330run;
331
332%mp_getpk(%scan(&libds,1,.), ds=%scan(&libds,2,.), outds=work.pk_fields)
333
334%let pk_fields=;
335data _null_;
336 set work.pk_fields;
337 call symputx('pk_fields',pk_fields);
338run;
339
340data work.sasparams;
341 set work.parambase;
342 format FILTER_TEXT $32767.;
343 FILTER_TEXT=symget('FILTER_TEXT');
344 length PK_FIELDS $512;
345 PK_FIELDS=symget('PK_FIELDS');
346 nobs=&dsobs;
347 vars=%mf_getvarcount(viewdata);
348 maxrows=&maxrows;
349run;
350
351%mp_abort(iftrue= (&syscc ne 0)
352 ,mac=&_program..sas
353 ,msg=%str(syscc=&syscc)
354)
355
356%mp_getcols(&libds, outds=cols)
357
358%mpe_dsmeta(&orig_libds, outds=dsmeta)
359
360%mpe_getversions(&mpelib,
361 %scan(&orig_libds,1,.),
362 %scan(&orig_libds,2,.),
363 outds=versions
364)
365
366%webout(OPEN)
367%webout(OBJ,cls_rules)
368%webout(OBJ,cols)
369%webout(OBJ,dsmeta)
370%webout(OBJ,query)
371%webout(OBJ,sasparams)
372%webout(OBJ,versions)
373%webout(OBJ,viewData2,fmt=Y,missing=STRING,showmeta=YES,dslabel=viewdata)
374%webout(CLOSE)
375
376%mpeterm()