Loading...
Searching...
No Matches
getdata.sas
Go to the documentation of this file.
1/**
2 @file getdata.sas
3 @brief Returns a dataset to the editor front end
4 @details
5
6 <h4> Service Inputs </h4>
7
8 <h5> SASCONTROLTABLE </h5>
9 |LIBDS:$41.|FILTER_RK:$5.|
10 |---|---|
11 |DC258467.MPE_X_TEST|-1|
12
13 <h4> Service Outputs </h4>
14 <h5> sasdata </h5>
15 <h5> sasparams </h5>
16 Contains info on the request. One row is returned.
17 @li CLS_FLG - set to 0 if there are no CLS rules (everything editable)
18 else set to 1 (CLS rules exist)
19 @li ISMAP - set to 1 if the target DS is an excel map target, else 0
20
21 <h5> approvers </h5>
22 <h5> dqrules </h5>
23 <h5> dqdata </h5>
24 <h5> cols </h5>
25 Contains column level attributes.
26 @li NAME - column name
27 @li VARNUM - variable position. Source: https://core.sasjs.io/mp__getcols_8sas.html
28 @li LABEL - variable label. Source: https://core.sasjs.io/mp__getcols_8sas.html
29 @li FMTNAME - derived format name. Source: https://core.sasjs.io/mp__getcols_8sas.html
30 @li DDTYPE - derived dropdown type. Source: https://core.sasjs.io/mp__getcols_8sas.html
31 @li CLS_RULE - values include:
32 - EDIT - the column is editable
33 - READ - the column should be readonly
34 - HIDE - the column should be hidden
35 @li memlabel
36 @li desc- augmented with MPE_DATADICTIONARY if exists, else label
37 @li longdesc - from MPE_DATADICTIONARY
38
39
40 <h5> maxvarlengths </h5>
41 <h5> xl_rules </h5>
42 <h5> query </h5>
43
44 <h5> versions </h5>
45 history of DC versions for this particular table
46
47
48 <h4> SAS Macros </h4>
49 @li dc_assignlib.sas
50 @li dc_getgroupmembers.sas
51 @li mf_existvar.sas
52 @li mf_getattrn.sas
53 @li mf_getvarlist.sas
54 @li mf_existds.sas
55 @li mf_getquotedstr.sas
56 @li mf_getuser.sas
57 @li mf_nobs.sas
58 @li mf_verifymacvars.sas
59 @li mf_wordsinstr1butnotstr2.sas
60 @li mp_abort.sas
61 @li mp_cntlout.sas
62 @li mp_getcols.sas
63 @li mp_getmaxvarlengths.sas
64 @li mp_validatecol.sas
65 @li mpe_accesscheck.sas
66 @li mpe_columnlevelsecurity.sas
67 @li mpe_dsmeta.sas
68 @li mpe_getlabels.sas
69 @li mpe_getversions.sas
70 @li mpe_filtermaster.sas
71 @li mpe_runhook.sas
72
73 @version 9.2
74 @author 4GL Apps Ltd
75 @copyright 4GL Apps Ltd. This code may only be used within Data Controller
76 and may not be re-distributed or re-sold without the express permission of
77 4GL Apps Ltd.
78
79**/
80
81%mpeinit()
82
83/**
84 * Validate inputs
85 */
86data work.intest;
87 length filter_rk 8;
88 set work.SASCONTROLTABLE;
89
90 /* validate filter_rk */
91 if filter_rk le 0 then filter_rk=-1;
92
93 call symputx('orig_libds',upcase(libds));
94
95 is_fmt=0;
96 if substr(cats(reverse(libds)),1,3)=:'CF-' then do;
97 libds=scan(libds,1,'-');
98 putlog "Format Catalog Captured";
99 is_fmt=1;
100 libds='work.fmtextract';
101 call symputx('libds',libds);
102 end;
103 call symputx('is_fmt',is_fmt);
104 putlog (_all_)(=);
105
106 /* validate libds */
107 %mp_validatecol(LIBDS,LIBDS,is_libds)
108
109 if is_libds=0 then do;
110 putlog 'ERR' 'OR: Invalid libds:' libds;
111 stop;
112 end;
113 else do;
114 call symputx('filter_rk',filter_rk);
115 call symputx('libds',libds);
116 end;
117 output;
118 stop;
119run;
120
121%mp_abort(iftrue= (%mf_nobs(work.intest)=0)
122 ,mac=&_program
123 ,msg=%str(Some err with service inputs)
124)
125
126%mp_abort(
127 iftrue=(%mf_verifymacvars(libds filter_rk)=0)
128 ,mac=&_program
129 ,msg=%str(Missing: libds filter_rk)
130)
131
132/* export format catalog */
133%mp_cntlout(
134 iftrue=(&is_fmt=1)
135 ,libcat=&orig_libds
136 ,fmtlist=0
137 ,cntlout=work.fmtextract
138)
139
140/* stream back meta info, further calls will return col metadata and actual data
141*/
142%let libref=%upcase(%scan(&libds,1,.));
143%let dsn=%upcase(%scan(&libds,2,.));
144%dc_assignlib(WRITE,&libref)
145
146/**
147 * First check user has access permission to edit the table
148 */
149%put checking access;
150%let user=%mf_getuser();
151%mpe_accesscheck(&orig_libds,outds=mw_auth,user=&user,access_level=EDIT)
152
153
154%mp_abort(iftrue= (%mf_getattrn(work.mw_auth,NLOBS)=0)
155 ,mac=mpestp_getdata.sas
156 ,msg=&user is not authorised to edit &orig_libds %trim(
157 )in the &mpelib..MPE_SECURITY table
158)
159
160%mp_abort(iftrue= ( %mf_existds(libds=&libds) ne 1)
161 ,mac=mpestp_getdata.sas
162 ,msg=dataset &libds does not exist!!
163)
164
165%mp_abort(iftrue= (&syscc ne 0)
166 ,mac=&_program..sas
167 ,msg=%str(syscc=&syscc at line 60 )
168)
169
170
171%global loadtype var_txfrom var_txto var_processed filter_text pk coltype
172 sortpk;
173
174%put getting table attributes;
175proc sql noprint;
176select upcase(loadtype)
177 ,var_txfrom,var_txto
178 ,var_busfrom,var_busto
179 ,var_processed,rk_underlying,buskey
180 ,coalesce(rk_underlying,buskey)
181 ,pre_edit_hook
182 ,case when missing(rk_underlying) then buskey else rk_underlying end
183 into: loadtype,:var_txfrom,:var_txto
184 ,:var_busfrom ,:var_busto
185 ,:var_processed,:rk_underlying,:buskey, :sortPK, :pre_edit_hook,:pk
186 from &mpelib..mpe_tables
187 where &dc_dttmtfmt. lt TX_TO
188 and upcase(dsn)="%scan(&orig_libds,2,.)"
189 and upcase(libref)="%scan(&orig_libds,1,.)";
190
191%put preparing filter query:;
192%mpe_filtermaster(EDIT,&orig_libds,
193 dclib=&mpelib,
194 filter_rk=&filter_rk,
195 outref=filtref,
196 outds=work.query
197)
198
199%macro mpestp_getdata();
200 %if not %symexist(DC_MAXOBS_WEBEDIT) %then %do;
201 %put NOTE:;%put NOTE- DC_MAXOBS_WEBEDIT not found!;
202 %put NOTE- Please add to &mpelib..MPE_CONFIG table;
203 %put NOTE-;%put NOTE-;
204 %global DC_MAXOBS_WEBEDIT;
205 %let DC_MAXOBS_WEBEDIT=500;
206 %end;
207 /* for tables which use RKs/SKs then we just expose the business key to
208 users - this lets uploads be sent to multiple environments (with
209 potentially different RK/SK values for the same business key).
210 Note that the config table has the RK column in the buskey field in
211 this scenario. */
212 %if %length(&rk_underlying)>0 %then %let drop_rk=&buskey;
213 %else %let drop_rk=;
214
215 /* always remove the PROCESSED_DTTM column, if it exists */
216 %if %length(&var_processed)=0 %then %do;
217 %if %mf_existvar(&libds,PROCESSED_DTTM)>0 %then
218 %let var_processed=PROCESSED_DTTM;
219 %end;
220
221 /**
222 * Now get the slice of the actual table
223 */
224 options obs=10000;
225
226 %if &loadtype=BITEMPORAL %then %do;
227 data out (drop=&var_txfrom &var_txto &var_processed &drop_rk );
228 _____DELETE__THIS__RECORD_____="No";
229 set &libds;
230 where %inc filtref;;
231 run;
232 proc sort data=out;
233 by &pk &var_busfrom;
234 run;
235 data out;
236 set out;
237 by &pk &var_busfrom;
238 if last.%scan(&pk,-1);
239 run;
240 %end;
241 %else %do;
242 data out (drop=&var_txfrom &var_txto &var_processed &drop_rk);
243 _____DELETE__THIS__RECORD_____="No";
244 set &libds;
245 where %inc filtref;;
246 run;
247 %end;
248 options obs=max;
249 %mp_abort(iftrue= (&syscc ne 0)
250 ,mac=&_program
251 ,msg=%str(Issue with filtering (line 165) )
252 )
253
254 options obs=&DC_MAXOBS_WEBEDIT;
255 %let sortpk=%sysfunc(coalescec(&sortpk &var_busfrom,_ALL_));
256 proc sort data=work.out; by &sortPK; run;
257 options obs=max;
258
259 %mpe_runhook(PRE_EDIT_HOOK)
260
261 %let obscnt=%mf_getattrn(work.out,NLOBS);
262 %mp_abort(iftrue=(&obscnt>&DC_MAXOBS_WEBEDIT)
263 ,mac=&_program
264 ,msg=Table is too big (&obscnt rows) - please filter and try again!
265 )
266
267
268 /* order delete var and pk fields at start of table */
269 %let sourcevars=%mf_wordsInStr1ButNotStr2(
270 Str1=%mf_getvarlist(work.out)
271 ,Str2= _____DELETE__THIS__RECORD_____ &pk
272 );
273 %put sourcevars=&sourcevars;
274 data outdata;
275 /* delete & pk fields come first */
276 attrib _____DELETE__THIS__RECORD_____ &pk label='';
277 /* keep remaining variable order */
278 %if %length(&sourcevars)>0 %then %do;
279 attrib &sourcevars label='';
280 %end;
281 _____DELETE__THIS__RECORD_____="No ";
282 %if %mf_nobs(work.out)=0 %then %do;
283 /* send empty row if empty table to help with hot rendering */
284 output;
285 %end;
286 set work.out ;
287 run;
288
289
290
291 /* get list of variables and their formats */
292 proc contents noprint data=outdata
293 out=vars(keep=name type length varnum format: label);
294 run;
295
296 proc sort;
297 by varnum;
298 run;
299
300 data vars3(keep=name type length format label pk varnum ctrloptions formatd);
301 set vars(rename=(format=format2 type=type2));
302 name=upcase(name);
303 /* not interested in transaction or processing dates
304 (append table must be supplied without them) */
305 if name not in ("&VAR_TXFROM","&VAR_TXTO","&VAR_PROCESSED");
306 if type2=2 or type2=6 then do;
307 length format $49.;
308 if format2='' then format=cats('$',length,'.');
309 else format=cats(format2,formatl,'.');
310 type='char';
311 end;
312 else do;
313 if format2='' then format=cats(length,'.');
314 else if upcase(format2)='DATETIME' and formatl=0 then format='DATETIME.';
315 else format=cats(format2,formatl,'.',formatd);
316 type='num';
317 end;
318
319 if name in ('',%upcase(%mf_getQuotedStr(&pk,dlm=%str(,),quote=S)))
320 then PK='YES';
321
322 length ctrlOptions $500;
323 if name="_____DELETE__THIS__RECORD_____" then ctrlOptions='["No","Yes"]';
324 else ctrlOptions='';
325 run;
326 %mp_abort(iftrue= (&syscc ne 0)
327 ,mac=&_program..sas
328 ,msg=%str(syscc=&syscc at 242 (vars3 step) in &_program \n
329 %superq(syserrortext)
330 )
331 )
332
333 %global jsdttmvars jsdtvars jstmvars;
334 data _null_;
335 set vars3 end=last;
336 if _n_>1 then comma=',';
337 length coltype $500.;
338 format=upcase(format);
339 coltype=cats(comma,'{"data":"',name,'"');
340 if ctrlOptions ne '' then
341 colType=cats(coltype,',"type":"dropdown","source":',ctrlOptions,"}");
342 else if type='num' then do;
343 if format=:'DATETIME' or format=:'E8601DT' or format=:'NLDATM'
344 then do;
345 colType=cats(coltype
346 ,',"type":"date","dateFormat":"YYYY-MM-DD HH:mm:ss"'
347 ,',"correctFormat":"true"}');
348 /* build var list to reformat datetimes in javascript format */
349 call symput('jsdttmvars',symget('jsdttmvars')!!' '!!name);
350 end;
351 else if format=:'DATE' or format=:'DDMMYY' or format=:'MMDDYY'
352 or format=:'YYMMDD' or format=:'E8601DA' or format=:'B8601DA'
353 or format=:'MONYY' or format=:'NLDATE'
354 then do;
355 /* see bottom of file for more date formats!! */
356 /* also when updating, update stagedata.sas and mp_getcols.sas
357 and mpe_loader.sas */
358 colType=cats(coltype,',"type":"date","dateFormat":"YYYY-MM-DD"'
359 /*colType=cats(coltype,',"type":"date","dateFormat":"MM/DD/YYYY"'*/
360 ,',"correctFormat":"true"}');
361 /* build var list to reformat as javascript dates */
362 call symput('jsdtvars',symget('jsdtvars')!!' '!!name);
363 end;
364 else if format=:'TIME' or format=:'HHMM' then do;
365 colType=cats(coltype,',"type":"time","timeFormat":"HH:mm:ss"'
366 ,',"correctFormat":"true"}');
367 /* build var list to reformat as javascript times */
368 call symput('jstmvars',symget('jstmvars')!!' '!!name);
369 end;
370 else do;
371 /* is standard numeric but need to ascertain precision */
372 retain base '000000000000000000';
373 if formatd>0 then numFormat=cats('.',substr(base,1,formatd));
374 colType=cats(coltype,',"type":"numeric","format":"0',numFormat,'"}');
375 end;
376 end;
377 else colType=cats(coltype,'}');
378 length concatcoltype $32767;
379 retain concatcoltype;
380 concatcoltype=cats(concatcoltype,coltype);
381 if last then call symputx('colType',strip(concatcoltype),'g');
382 putlog (_all_)(=);
383 run;
384
385 %mp_abort(iftrue= (&syscc ne 0)
386 ,mac=&_program..sas
387 ,msg=%str(syscc=&syscc at 283 (null step) in &_program)
388 )
389
390 PROC FORMAT;
391 picture yymmddThhmmss (default=28) other='%0Y-%0m-%0d %0H:%0M:%0s'
392 (datatype=datetime);
393 picture JSyymmdd other='%0Y-%0m-%0d' (datatype=date);
394 picture JShhmmss (default=16) other='%0H:%0M:%0s' (datatype=time);
395 RUN;
396 /* before we send the data, need to rebuild all date & datetime vars as char*/
397 %let finalvars=%mf_getvarlist(work.outdata);
398 data sasdata;
399 /* set formats & col order ahead of rename+import */
400 informat &finalvars ;
401 /* read dataset and rename date / datetime vars as necessary */
402 set outdata
403 %if %length(&jsdttmvars&jsdtvars&jstmvars)>0 %then %do;
404 (rename=(
405 %local dtvarnum dtvar tmvar;
406 /* temp datetime vars end in _____ */
407 %do dtvarnum=1 %to %sysfunc(countw(&jsdttmvars,%str( )));
408 %let dtvar=%scan(&jsdttmvars ,&dtvarnum);
409 &dtvar=_____&dtvarnum._____
410 %end;
411 /* temp date vars do not end in _____ */
412 %do dtvarnum=1 %to %sysfunc(countw(&jsdtvars,%str( )));
413 %let dtvar=%scan( &jsdtvars,&dtvarnum);
414 &dtvar=_____&dtvarnum
415 %end;
416 /* temp time vars end in ___tm */
417 %do tmvarnum=1 %to %sysfunc(countw(&jstmvars,%str( )));
418 %let tmvar=%scan( &jstmvars,&tmvarnum);
419 &tmvar=_____&tmvarnum.___tm
420 %end;
421 ))
422 %end;
423 ;
424 %if %length(&jsdttmvars)>0 %then %do ;
425 %do dtvarnum=1 %to %sysfunc(countw(&jsdttmvars,%str( )));
426 %let dtvar=%scan(&jsdttmvars,&dtvarnum);
427 &dtvar=cats(put(_____&dtvarnum._____,yymmddThhmmss28.));
428 if &dtvar="ERROR" then call missing(&dtvar);
429 drop _____&dtvarnum._____;
430 %end;
431 %end;
432 %if %length(&jsdtvars)>0 %then %do;
433 %do dtvarnum=1 %to %sysfunc(countw(&jsdtvars,%str( )));
434 %let dtvar=%scan(&jsdtvars,&dtvarnum);
435 &dtvar=cats(put(_____&dtvarnum,JSyymmdd.));
436 if &dtvar="ERROR" then call missing(&dtvar);
437 drop _____&dtvarnum;
438 %end;
439 %end;
440 %if %length(&jstmvars)>0 %then %do;
441 %do tmvarnum=1 %to %sysfunc(countw(&jstmvars,%str( )));
442 %let tmvar=%scan(&jstmvars,&tmvarnum);
443 &tmvar=cats(put(_____&tmvarnum.___tm,JShhmmss14.));
444 if &tmvar="ERROR" then call missing(&tmvar);
445 drop _____&tmvarnum.___tm;
446 %end;
447 %end;
448 output;
449 run;
450
451 /* get the relevant approvers for the drop down */
452 %put getting approvers;
453 %local sas_groups sas_i sas_group;
454 proc sql noprint;
455 select distinct sas_Group into: sas_groups separated by "|"
456 from &mpelib..mpe_security
457 where libref="%scan(&orig_libds,1,.)"
458 and dsn="%scan(&orig_libds,2,.)"
459 and access_level='APPROVE'
460 and &dc_dttmtfmt. lt TX_TO;
461
462 %if %length(&sas_groups)=0 %then %do;
463 %dc_getgroupmembers(&dc_admin_group,outds=work.access1)
464 %end;
465 %else %do sas_i=1 %to %sysfunc(countw(&sas_groups,%str(|)));
466 %let sas_group=%scan(&sas_Groups,&sas_i,%str(|));
467 %dc_getgroupmembers(&sas_group,outds=work.temp&sas_i)
468 proc append base=work.access1 data=work.temp&sas_i;run;
469 %end;
470
471%mend mpestp_getdata;
472
473%mpestp_getdata()
474
475%mp_abort(mode=INCLUDE)
476
477/* extract column level security rules */
478%mpe_columnlevelsecurity(%scan(&libds,1,.),%scan(&libds,2,.),work.sasdata
479 ,mode=EDIT
480 ,clsds=&mpelib..mpe_column_level_security
481 ,groupds=work.groups /* was created in mpe_filtermaster */
482 ,outds=work.sasdata1
483 ,outmeta=work.cls_rules
484)
485
486/* get labels */
487%mpe_getlabels(COLUMNS,sasdata1,outds=spec)
488%mp_abort(iftrue= (&syscc ne 0)
489 ,mac=&_program
490 ,msg=%str(syscc=&syscc extracting spec info)
491)
492
493/* extract col info */
494%mp_getcols(&libds, outds=cols1)
495
496/* join with cls rules */
497proc sql;
498create table work.cols as
499 select a.NAME
500 ,a.VARNUM
501 ,a.LABEL
502 ,a.FMTNAME
503 ,a.DDTYPE
504 ,case b.cls_hide
505 when 1 then 'HIDE'
506 when 0 then 'EDIT'
507 else 'READ' end as CLS_RULE
508 ,c.memlabel
509 ,c.desc
510 ,c.longdesc
511 from work.cols1 a
512 left join work.cls_rules b
513 on a.NAME=b.CLS_VARIABLE_NM
514 left join work.spec c
515 on a.NAME=c.NAME;
516
517proc sql;
518create table approvers as select distinct membername as personname
519 ,membername as email, membername as userid
520 from work.access1;
521/*
522create table access3 as select b.userid,b.email
523 from access2 a
524 ,support.users b
525 where a.personname=b.userid
526 and a.personname ne "%mf_getuser()"
527 and %sysfunc(datetime()) lt b.tx_to_dttm
528 order by 1;
529*/
530data _null_;
531 infile filtref end=eof;
532 input;
533 length filter_text $32767;
534 retain filter_text;
535 filter_text=catx(' ',filter_text,_infile_);
536 if eof then do;
537 if cats(filter_text)='1=1' then filter_text='';
538 call symputx('filter_text',filter_text);
539 end;
540run;
541
542%put params;
543%let ismap=0;
544proc sql noprint;
545select count(*) into: ismap from &mpelib..mpe_xlmap_info
546 where XLMAP_TARGETLIBDS="&orig_libds" and &dc_dttmtfmt. le TX_TO;
547
548data sasparams;
549 length colHeaders $20000 filter_text $32767;
550 colHeaders=cats(upcase("%mf_getvarlist(sasdata1,dlm=%str(,))"));
551 pkCnt=countw("&pk");
552 pk="&pk";
553 dtvars=compbl("&jsdtvars");
554 dttmvars=compbl("&jsdttmvars");
555 tmvars=compbl("&jstmvars");
556 length coltype $32000;
557 coltype=symget('coltype');
558 loadtype=symget('loadtype');
559 if trim(symget('rk_underlying')) ne '' then rk_flag=1;
560 else rk_flag=0;
561 filter_text=symget('filter_text');
562 if %mf_nobs(work.cls_rules)=0 then cls_flag=0;
563 else cls_flag=1;
564 put (_all_)(=);
565 if "&orig_libds"="&mpelib..MPE_XLMAP_DATA" or &ismap ne 0 then ismap=1;
566 else ismap=0;
567run;
568
569
570/* Extract validation DQ Rules */
571proc sort data=&mpelib..mpe_validations
572 (where=(&dc_dttmtfmt. le TX_TO
573 and BASE_LIB="%scan(&orig_libds,1,.)" and BASE_DS="%scan(&orig_libds,2,.)"
574 and rule_active=1))
575 out=dqrules (keep=base_col rule_type rule_value);
576 by base_col rule_type rule_value;
577run;
578
579/* merge with NOTNULL constraints in the physical table */
580proc sql;
581create table _data_ as
582 select * from dqrules
583union
584 select upcase(name) as base_col
585 ,'NOTNULL' as rule_type
586 ,'' as rule_value
587 from dictionary.columns
588 where upcase(libname)="%scan(&orig_libds,1,.)"
589 and upcase(memname)="%scan(&orig_libds,2,.)"
590 and upcase(name) in (select name from vars3)
591 and notnull='yes'
592 order by 1,2,3;
593data dqrules;
594 set &syslast;
595 by base_col rule_type rule_value;
596 if last.rule_type;
597 if rule_type in ('HARDSELECT','SOFTSELECT') and countw(rule_value)=3 then
598 do;
599 retain x 0; x+1;
600 call symputx(cats('source',x),rule_value);
601 %let sourcecnt=0;
602 call symputx('sourcecnt',x);
603 call symputx(cats('base_col',x),base_col);
604 end;
605run;
606
607proc sql;
608create table dqdata as
609 select distinct base_column as base_col length=32
610 ,upcase(base_column) as rule_value length=74 /* deprecated */
611 ,selectbox_value as rule_data length=1000
612 ,selectbox_order
613 from &mpelib..mpe_selectbox
614 where &dc_dttmtfmt. lt ver_to_dttm
615 and select_lib="%scan(&orig_libds,1,.)"
616 and select_ds="%scan(&orig_libds,2,.)";
617
618%mp_abort(iftrue= (&syscc ne 0)
619 ,mac=&_program
620 ,msg=%str(syscc=&syscc during DQ rule validation)
621)
622
623/* extract selectbox data */
624%macro dq_selects();
625 %local x source lib ds col;
626 %do x=1 %to &sourcecnt;
627 %let source=&&source&x;
628 %let lib=%scan(&source,1,.);
629 %let ds=%scan(&source,2,.);
630 %let col=%scan(&source,3,.);
631 %put &=source;
632 %put &=lib;
633 %dc_assignlib(READ,&lib)
634 proc sql;
635 create table dqdata&x as
636 select distinct "&&base_col&x" as base_col length=32
637 ,"&source" as rule_value length=74
638 ,cats(&col) as rule_data length=1000
639 ,&col as tmp_order
640 from &lib..&ds
641 order by tmp_order;
642 /* ensure both numerics and char vals are ordered correctly */
643 data work.dqdata&x (drop=tmp_order);
644 set work.dqdata&x;
645 selectbox_order=_n_;
646 run;
647 %mp_abort(iftrue= (&syscc ne 0)
648 ,mac=&_program
649 ,msg=%str(syscc=&syscc when selecting &&base_col&x from &orig_libds)
650 )
651 proc append base=dqdata data=dqdata&x;run;
652 proc sql; drop table dqdata&x;
653 %end;
654%mend dq_selects;
655%dq_selects()
656
657proc sort data=dqdata;
658 /* order by selectbox_order then the value */
659 by base_col selectbox_order rule_data;
660run;
661
662%mp_getmaxvarlengths(work.sasdata1,outds=maxvarlengths)
663
664data maxvarlengths;
665 set maxvarlengths;
666 if name='_____DELETE__THIS__RECORD_____' then mAXLEN=3;
667run;
668
669data xl_rules;
670 set &mpelib..mpe_excel_config;
671 where &dc_dttmtfmt. lt tx_to;
672 where also upcase(xl_libref)="%scan(&orig_libds,1,.)";
673 where also upcase(xl_table)="%scan(&orig_libds,2,.)";
674 where also xl_active=1;
675 keep xl_column xl_rule;
676run;
677
678%mpe_dsmeta(&orig_libds, outds=dsmeta)
679
680%mpe_getversions(&mpelib,
681 %scan(&orig_libds,1,.),
682 %scan(&orig_libds,2,.),
683 outds=versions
684)
685
686
687/* send to the client */
688%webout(OPEN)
689%webout(OBJ,approvers)
690%webout(OBJ,cols)
691%webout(OBJ,dqdata,missing=STRING)
692%webout(OBJ,dqrules)
693%webout(OBJ,dsmeta)
694%webout(OBJ,maxvarlengths)
695%webout(OBJ,query)
696%webout(OBJ,sasdata1,fmt=N,missing=STRING,showmeta=YES,dslabel=sasdata)
697%webout(OBJ,sasparams)
698%webout(OBJ,versions)
699%webout(OBJ,xl_rules)
700%webout(CLOSE)
701
702/*
703$N8601Bw
704$N8601BAw
705$N8601Ew
706$N8601EAw
707$N8601EHw
708$N8601EXw
709$N8601Hw
710$N8601Xw
711B8601DAw
712B8601DNw
713B8601DTw
714B8601DZw
715B8601LZw
716B8601TMw
717B8601TZw
718DATEw
719DATEAMPMw
720DATETIMEw
721DAYw
722DDMMYYw
723DDMMYYxw
724DOWNAMEw
725DTDATEw
726DTMONYYw
727DTWKDATXw
728DTYEARw
729DTYYQCw
730E8601DAw
731E8601DNw
732E8601DTw
733E8601DZw
734E8601LZw
735E8601TMw
736E8601TZw
737HHMMw
738HOURw
739JULDAYw
740JULIANw
741MMDDYYw
742MMDDYYxw
743MMSSw
744MMYYw
745MMYYxw
746MONNAMEw
747MONTHw
748MONYYw
749PDJULGw
750PDJULIw
751QTRw
752QTRRw
753TIMEw
754TIMEAMPMw
755TODw
756WEEKDATEw
757WEEKDATXw
758WEEKDAYw
759WEEKUw
760WEEKVw
761WEEKWw
762WORDDATEw
763WORDDATXw
764YEARw
765YYMMw
766YYMMxw
767YYMMDDw
768YYMMDDxw
769YYMONw
770YYQw
771YYQxw
772YYQRw
773YYQRxw
774$N8601BAw
775$N8601Ew
776$N8601EAw
777$N8601EHw
778$N8601EXw
779$N8601Hw
780$N8601Xw
781B8601DAw
782B8601DNw
783B8601DTw
784B8601DZw
785B8601LZw
786B8601TMw
787B8601TZw
788E8601DAw
789E8601DNw
790E8601DTw
791E8601DZw
792E8601LZw
793E8601TMw
794E8601TZw
795*/
796%mpeterm()