Loading...
Searching...
No Matches
mpe_makedata.sas
Go to the documentation of this file.
1/**
2 @file
3 @brief Populates the Data Controller tables with sample data
4 @details
5
6 Usage:
7
8 %mpe_makedata(lib=DC869651
9 ,mpeadmins=SASAdministrators
10 ,path=/opt/data/dc/VIYA8698
11 )
12
13 <h4> SAS Macros </h4>
14 @li mf_increment.sas
15
16 @version 9.2
17 @author 4GL Apps Ltd
18 @copyright 4GL Apps Ltd - this is a licensed product and NOT FOR RESALE
19 OR DISTRIBUTION.
20**/
21
22%macro mpe_makedata(lib=,mpeadmins=,path=);
23%if &syscc ne 0 %then %do;
24 %put syscc=&syscc exiting &sysmacroname;
25 %return;
26%end;
27
28proc sql;
29insert into &lib..mpe_column_level_security set
30 tx_from=0
31 ,tx_to='31DEC9999:23:59:59'dt
32 ,CLS_SCOPE='EDIT'
33 ,CLS_GROUP='AllUsers'
34 ,CLS_LIBREF="&lib"
35 ,CLS_TABLE='MPE_LOCKANYTABLE'
36 ,CLS_VARIABLE_NM='LOCK_STATUS_CD'
37 ,CLS_ACTIVE=1
38 ,CLS_HIDE=0;
39
40insert into &lib..mpe_config set
41 tx_from=0
42 ,tx_to='31DEC9999:23:59:59'dt
43 ,var_scope="DC"
44 ,var_name="DC_EMAIL_ALERTS"
45 ,var_value='NO'
46 ,var_active=1
47 ,var_desc='YES or NO to enable email alerts. Note - this requires email '
48 !!'options to be preconfigured! They can be configured in the '
49 !!'settings stp if needed.';
50insert into &lib..mpe_config set
51 tx_from=0
52 ,tx_to='31DEC9999:23:59:59'dt
53 ,var_scope="DC"
54 ,var_name="DC_VIEWLIB_CHECK"
55 ,var_value='NO'
56 ,var_active=1
57 ,var_desc=
58 'Set to YES to enable library validity checking in viewLibs service.'
59 !!' Note: this can make the service very slow if there are lots of '
60 !!'external libraries. If enabled, this removes empty libraries from '
61 !!'the viewer library dropdown. To switch off, set to NO.';
62insert into &lib..mpe_config set
63 tx_from=0
64 ,tx_to='31DEC9999:23:59:59'dt
65 ,var_scope="DC"
66 ,var_name="DC_MACROS"
67 ,var_value=cats(symget('path'),"/dc_macros")
68 ,var_active=1
69 ,var_desc='Location of underlying macros - EUC feature.';
70insert into &lib..mpe_config set
71 tx_from=0
72 ,tx_to='31DEC9999:23:59:59'dt
73 ,var_scope="DC"
74 ,var_name="DC_MAXOBS_WEBEDIT"
75 ,var_value="100"
76 ,var_active=1
77 ,var_desc='This sets the maximum number of observations that can be loaded'
78 !!' into the browser for editing in the EDIT screen. A higher number'
79 !!' will require a decent browser (ie, not IE) and more memory on the'
80 !!' client side.';
81insert into &lib..mpe_config set
82 tx_from=0
83 ,tx_to='31DEC9999:23:59:59'dt
84 ,var_scope="DC"
85 ,var_name="DC_REQUEST_LOGS"
86 ,var_value="YES"
87 ,var_active=1
88 ,var_desc='Setting to NO will prevent each request being logged to the'
89 !!' MPE_REQUESTS table Default=YES.';
90insert into &lib..mpe_config set
91 tx_from=0
92 ,tx_to='31DEC9999:23:59:59'dt
93 ,var_scope="DC"
94 ,var_name="DC_RESTRICT_VIEWER"
95 ,var_value="NO"
96 ,var_active=1
97 ,var_desc='YES will restrict the list of libraries and tables in VIEWER to'
98 !!' those explicitly set to VIEW in the MPE_SECURITY table. Default=NO.';
99insert into &lib..mpe_config set
100 tx_from=0
101 ,tx_to='31DEC9999:23:59:59'dt
102 ,var_scope="DC"
103 ,var_name="DC_RESTRICT_EDITRECORD"
104 ,var_value="NO"
105 ,var_active=1
106 ,var_desc='Setting YES will prevent the EDIT RECORD dialog appearing in the'
107 !!' EDIT screen by removing the "Edit Row" option in the right click menu'
108 !!', and the "ADD RECORD" button in the bottom left. Default=NO.';
109insert into &lib..mpe_config set
110 tx_from=0
111 ,tx_to='31DEC9999:23:59:59'dt
112 ,var_scope="DC_CATALOG"
113 ,var_name="DC_IGNORELIBS"
114 ,var_value="|MAPSSAS|MAPS|"
115 ,var_active=1
116 ,var_desc='Pipe seperated list of librefs (uppercase) to be ignored when'
117 !!' running the Data Catalog refresh process. This can enable a clean'
118 !!' run when invalid librefs are returned by the mpe_refreshlibs macro.';
119insert into &lib..mpe_config set
120 tx_from=0
121 ,tx_to='31DEC9999:23:59:59'dt
122 ,var_scope="DC"
123 ,var_name="DC_LOCALE"
124 ,var_value="SYSTEM"
125 ,var_active=1
126 ,var_desc='Set to a locale (such as en_gb or en_be) to override the system'
127 !!' value (which can be driven from the browser settings). This is '
128 !!'useful when importing ambiguous dates from CSV or Excel (eg 1/2/20 vs '
129 !!'2/1/20) as DC uses the anydtdtm informats for import. Default=SYSTEM.';
130insert into &lib..mpe_config set
131 tx_from=0
132 ,tx_to='31DEC9999:23:59:59'dt
133 ,var_scope="DCBL_REDSH"
134 ,var_name="BULKLOAD"
135 ,var_value="YES"
136 ,var_active=0
137 ,var_desc='Set to YES to enable BULKLOAD=YES in redshift';
138insert into &lib..mpe_config set
139 tx_from=0
140 ,tx_to='31DEC9999:23:59:59'dt
141 ,var_scope="DCBL_REDSH"
142 ,var_name="BL_BUCKET"
143 ,var_value="'your-aws-bucket/Exchange'"
144 ,var_active=0
145 ,var_desc='Set to the (quoted) value of the AWS bucket to'
146 !!' use for s3 uploads in redshift';
147insert into &lib..mpe_config set
148 tx_from=0
149 ,tx_to='31DEC9999:23:59:59'dt
150 ,var_scope="DCBL_REDSH"
151 ,var_name="BL_AWS_CREDENTIALS_FILE"
152 ,var_value="'/path/to/your/aws/s3/.credentials'"
153 ,var_active=0
154 ,var_desc='Set to the (quoted) value of the AWS creds file';
155insert into &lib..mpe_config set
156 tx_from=0
157 ,tx_to='31DEC9999:23:59:59'dt
158 ,var_scope="DCBL_REDSH"
159 ,var_name="BL_REGION"
160 ,var_value="'eu-west-1'"
161 ,var_active=0
162 ,var_desc='Set to the (quoted) AWS region in use';
163insert into &lib..mpe_config set
164 tx_from=0
165 ,tx_to='31DEC9999:23:59:59'dt
166 ,var_scope="DCBL_REDSH"
167 ,var_name="BL_COMPRESS"
168 ,var_value="YES"
169 ,var_active=0
170 ,var_desc='Set to YES to perform compression ahead of the COPY command';
171insert into &lib..mpe_config set
172 tx_from=0
173 ,tx_to='31DEC9999:23:59:59'dt
174 ,var_scope="DCBL_REDSH"
175 ,var_name="BL_USE_SSL"
176 ,var_value="YES"
177 ,var_active=0
178 ,var_desc='Set to YES to use SSL encryption';
179insert into &lib..mpe_config set
180 tx_from=0
181 ,tx_to='31DEC9999:23:59:59'dt
182 ,var_scope="DC_REVIEW"
183 ,var_name="HISTORY_ROWS"
184 ,var_value='100'
185 ,var_active=1
186 ,var_desc='Number of rows (or additional rows) to return in the HISTORY '
187 !!'page';
188insert into &lib..mpe_config set
189 tx_from=0
190 ,tx_to='31DEC9999:23:59:59'dt
191 ,var_scope="DC"
192 ,var_name="DC_LICENCE_KEY"
193 ,var_value=' '
194 ,var_active=1
195 ,var_desc='Licence Key';
196insert into &lib..mpe_config set
197 tx_from=0
198 ,tx_to='31DEC9999:23:59:59'dt
199 ,var_scope="DC"
200 ,var_name="DC_ACTIVATION_KEY"
201 ,var_value=' '
202 ,var_active=1
203 ,var_desc='Activation Key';
204insert into &lib..mpe_config set
205 tx_from=0
206 ,tx_to='31DEC9999:23:59:59'dt
207 ,var_scope="DC_EMAIL"
208 ,var_name="SUBMITTED_TEMPLATE"
209 ,var_value='Dear user,'!!'0A'x!!'Please be advised that a change to table'
210 !!' &alert_lib..&alert_ds has been proposed by &from_user on the '
211 !!'&syshostname SAS server.'!!'0A'x!!'Reason provided: '
212 !!'%superq(SUBMITTED_TXT)'
213 !!'0A'x!!'This is an automated email by Data Controller for SAS. For '
214 !!'documentation, please visit https://docs.datacontroller.io'
215 ,var_active=1
216 ,var_desc='Template email, sent after submitting a change';
217insert into &lib..mpe_config set
218 tx_from=0
219 ,tx_to='31DEC9999:23:59:59'dt
220 ,var_scope="DC_EMAIL"
221 ,var_name="APPROVED_TEMPLATE"
222 ,var_value='Dear user,'!!'0A'x!!'Please be advised that a change to table'
223 !!' &alert_lib..&alert_ds has been approved by &from_user on the '
224 !!'&syshostname SAS server.'!!'0A'x!!'This is an automated email by Data'
225 !!' Controller for SAS. For documentation, please visit '
226 !!'https://docs.datacontroller.io'
227 ,var_active=1
228 ,var_desc='Template email, sent after approving a change';
229insert into &lib..mpe_config set
230 tx_from=0
231 ,tx_to='31DEC9999:23:59:59'dt
232 ,var_scope="DC_EMAIL"
233 ,var_name="REJECTED_TEMPLATE"
234 ,var_value='Dear user,'!!'0A'x!!'Please be advised that a change to table'
235 !!' &alert_lib..&alert_ds has been rejected by &from_user on the '
236 !!'&syshostname SAS server.'!!'0A'x!!'Reason provided: '
237 !!'%superq(REVIEW_REASON_TXT)'
238 !!'0A'x!!'This is an automated email by Data Controller for SAS. For '
239 !!'documentation, please visit https://docs.datacontroller.io'
240 ,var_active=1
241 ,var_desc='Template email, sent after rejecting a change';
242
243
244insert into &lib..mpe_datadictionary set
245 tx_from=0
246 ,DD_TYPE='LIBRARY'
247 ,DD_SOURCE="&lib"
248 ,DD_SHORTDESC="Data Controller Control Tables"
249 ,DD_LONGDESC="# The Data Controller Library"
250 ,DD_OWNER="&sysuserid"
251 ,DD_RESPONSIBLE="&sysuserid"
252 ,DD_SENSITIVITY="Low"
253 ,tx_to='31DEC5999:23:59:59'dt;
254insert into &lib..mpe_datadictionary set
255 tx_from=0
256 ,DD_TYPE='TABLE'
257 ,DD_SOURCE="&lib..MPE_TABLES"
258 ,DD_SHORTDESC="Configuration of new tables for Data Controller"
259 ,DD_LONGDESC="# MPE_TABLES - adding new tabels to Data Controller"
260 ,DD_OWNER="&sysuserid"
261 ,DD_RESPONSIBLE="&sysuserid"
262 ,DD_SENSITIVITY="Low"
263 ,tx_to='31DEC5999:23:59:59'dt;
264insert into &lib..mpe_datadictionary set
265 tx_from=0
266 ,DD_TYPE='COLUMN'
267 ,DD_SOURCE="&lib..MPE_TABLES.DSN"
268 ,DD_SHORTDESC="Dataset Name to be edited"
269 ,DD_LONGDESC="_DSN_ - must be UPCASE"
270 ,DD_OWNER="&sysuserid"
271 ,DD_RESPONSIBLE="&sysuserid"
272 ,DD_SENSITIVITY="Low"
273 ,tx_to='31DEC5999:23:59:59'dt;
274insert into &lib..mpe_datadictionary set
275 tx_from=0
276 ,DD_TYPE='DIRECTORY'
277 ,DD_SOURCE="/some/directory"
278 ,DD_SHORTDESC="Directory for some purpose"
279 ,DD_LONGDESC="This directory is great. It's great directory.
280 It trumps all other directories."
281 ,DD_OWNER="&sysuserid"
282 ,DD_RESPONSIBLE="&sysuserid"
283 ,DD_SENSITIVITY="Low"
284 ,tx_to='31DEC5999:23:59:59'dt;
285insert into &lib..mpe_datadictionary set
286 tx_from=0
287 ,DD_TYPE='TABLE'
288 ,DD_SOURCE="&lib"
289 ,DD_SHORTDESC="Transaction table for capturing Data Controller users"
290 ,DD_LONGDESC="After a user accepts the Data Controller EULA they are "
291 !!"registered as a user in this table."
292 ,DD_OWNER="&sysuserid"
293 ,DD_RESPONSIBLE="&sysuserid"
294 ,DD_SENSITIVITY="Low"
295 ,tx_to='31DEC5999:23:59:59'dt;
296insert into &lib..mpe_datadictionary set
297 tx_from=0
298 ,DD_TYPE='COLUMN'
299 ,DD_SOURCE="&lib..MPE_CONFIG.VAR_ACTIVE"
300 ,DD_SHORTDESC="Set to 1 to make an option active"
301 ,DD_LONGDESC="This value is used as a filter by data controller whenever "
302 !!"querying for option settings."
303 ,DD_OWNER="&sysuserid"
304 ,DD_RESPONSIBLE="&sysuserid"
305 ,DD_SENSITIVITY="Low"
306 ,tx_to='31DEC5999:23:59:59'dt;
307
308/**
309 * mpe_xlmap_info
310 */
311insert into &lib..mpe_xlmap_info set
312 tx_from=0
313 ,tx_to='31DEC5999:23:59:59'dt
314 ,xlmap_id='BASEL-KM1'
315 ,xlmap_description='Basel 3 Key Metrics report'
316 ,XLMAP_TARGETLIBDS="&lib..MPE_XLMAP_DATA";
317
318/**
319 * mpe_xlmap_rules
320 */
321insert into &lib..mpe_xlmap_rules set
322 tx_from=0
323 ,tx_to='31DEC5999:23:59:59'dt
324 ,xlmap_id='BASEL-KM1'
325 ,xlmap_range_id='KM1:a'
326 ,xlmap_sheet='KM1'
327 ,xlmap_start='MATCH 4 R[2]C[0]:a';
328insert into &lib..mpe_xlmap_rules set
329 tx_from=0
330 ,tx_to='31DEC5999:23:59:59'dt
331 ,xlmap_id='BASEL-KM1'
332 ,xlmap_range_id='KM1:b'
333 ,xlmap_sheet='KM1'
334 ,xlmap_start='MATCH 4 R[2]C[0]:b';
335insert into &lib..mpe_xlmap_rules set
336 tx_from=0
337 ,tx_to='31DEC5999:23:59:59'dt
338 ,xlmap_id='BASEL-KM1'
339 ,xlmap_range_id='KM1:c'
340 ,xlmap_sheet='KM1'
341 ,xlmap_start='MATCH 4 R[2]C[0]:c';
342insert into &lib..mpe_xlmap_rules set
343 tx_from=0
344 ,tx_to='31DEC5999:23:59:59'dt
345 ,xlmap_id='BASEL-KM1'
346 ,xlmap_range_id='KM1:d'
347 ,xlmap_sheet='KM1'
348 ,xlmap_start='MATCH 4 R[2]C[0]:d';
349insert into &lib..mpe_xlmap_rules set
350 tx_from=0
351 ,tx_to='31DEC5999:23:59:59'dt
352 ,xlmap_id='BASEL-KM1'
353 ,xlmap_range_id='KM1:e'
354 ,xlmap_sheet='KM1'
355 ,xlmap_start='MATCH 4 R[2]C[0]:e';
356insert into &lib..mpe_xlmap_rules set
357 tx_from=0
358 ,tx_to='31DEC5999:23:59:59'dt
359 ,xlmap_id='BASEL-KM1'
360 ,xlmap_range_id='KM1:f'
361 ,xlmap_sheet='KM1'
362 ,xlmap_start='MATCH 4 R[2]C[0]:f';
363insert into &lib..mpe_xlmap_rules set
364 tx_from=0
365 ,tx_to='31DEC5999:23:59:59'dt
366 ,xlmap_id='BASEL-KM1'
367 ,xlmap_range_id='KM1:1/a'
368 ,xlmap_sheet='KM1'
369 ,xlmap_start='MATCH C R[0]C[1]:Common Equity Tier 1 (CET1)';
370insert into &lib..mpe_xlmap_rules set
371 tx_from=0
372 ,tx_to='31DEC5999:23:59:59'dt
373 ,xlmap_id='BASEL-KM1'
374 ,xlmap_range_id='KM1:1/b'
375 ,xlmap_sheet='KM1'
376 ,xlmap_start='MATCH C R[0]C[2]:Common Equity Tier 1 (CET1)';
377 insert into &lib..mpe_xlmap_rules set
378 tx_from=0
379 ,tx_to='31DEC5999:23:59:59'dt
380 ,xlmap_id='BASEL-KM1'
381 ,xlmap_range_id='KM1:1/c'
382 ,xlmap_sheet='KM1'
383 ,xlmap_start='MATCH C R[0]C[3]:Common Equity Tier 1 (CET1)';
384 insert into &lib..mpe_xlmap_rules set
385 tx_from=0
386 ,tx_to='31DEC5999:23:59:59'dt
387 ,xlmap_id='BASEL-KM1'
388 ,xlmap_range_id='KM1:1/d'
389 ,xlmap_sheet='KM1'
390 ,xlmap_start='MATCH C R[0]C[4]:Common Equity Tier 1 (CET1)';
391 insert into &lib..mpe_xlmap_rules set
392 tx_from=0
393 ,tx_to='31DEC5999:23:59:59'dt
394 ,xlmap_id='BASEL-KM1'
395 ,xlmap_range_id='KM1:1/e'
396 ,xlmap_sheet='KM1'
397 ,xlmap_start='MATCH C R[0]C[5]:Common Equity Tier 1 (CET1)';
398insert into &lib..mpe_xlmap_rules set
399 tx_from=0
400 ,tx_to='31DEC5999:23:59:59'dt
401 ,xlmap_id='BASEL-KM1'
402 ,xlmap_range_id='KM1:1/f'
403 ,xlmap_sheet='KM1'
404 ,xlmap_start='MATCH C R[0]C[6]:Common Equity Tier 1 (CET1)';
405insert into &lib..mpe_xlmap_rules set
406 tx_from=0
407 ,tx_to='31DEC5999:23:59:59'dt
408 ,xlmap_id='BASEL-KM1'
409 ,xlmap_range_id='KM1:1a/e'
410 ,xlmap_sheet='KM1'
411 ,xlmap_start='MATCH C R[1]C[5]:Common Equity Tier 1 (CET1)';
412insert into &lib..mpe_xlmap_rules set
413 tx_from=0
414 ,tx_to='31DEC5999:23:59:59'dt
415 ,xlmap_id='BASEL-KM1'
416 ,xlmap_range_id='KM1:1a/f'
417 ,xlmap_sheet='KM1'
418 ,xlmap_start='MATCH C R[1]C[6]:Common Equity Tier 1 (CET1)';
419insert into &lib..mpe_xlmap_rules set
420 tx_from=0
421 ,tx_to='31DEC5999:23:59:59'dt
422 ,xlmap_id='BASEL-KM1'
423 ,xlmap_range_id='KM1:2/a'
424 ,xlmap_sheet='KM1'
425 ,xlmap_start='ABSOLUTE D10';
426insert into &lib..mpe_xlmap_rules set
427 tx_from=0
428 ,tx_to='31DEC5999:23:59:59'dt
429 ,xlmap_id='BASEL-KM1'
430 ,xlmap_range_id='KM1:2/b'
431 ,xlmap_sheet='/3'
432 ,xlmap_start='ABSOLUTE E10';
433insert into &lib..mpe_xlmap_rules set
434 tx_from=0
435 ,tx_to='31DEC5999:23:59:59'dt
436 ,xlmap_id='BASEL-KM1'
437 ,xlmap_range_id='KM1:2/c'
438 ,xlmap_sheet='/3'
439 ,xlmap_start='RELATIVE R[10]C[6]';
440insert into &lib..mpe_xlmap_rules set
441 tx_from=0
442 ,tx_to='31DEC5999:23:59:59'dt
443 ,xlmap_id='BASEL-KM1'
444 ,xlmap_range_id='KM1:2/d'
445 ,xlmap_sheet='/3'
446 ,xlmap_start='RELATIVE R[10]C[8]';
447insert into &lib..mpe_xlmap_rules set
448 tx_from=0
449 ,tx_to='31DEC5999:23:59:59'dt
450 ,xlmap_id='BASEL-KM1'
451 ,xlmap_range_id='KM1:2/e'
452 ,xlmap_sheet='/3'
453 ,xlmap_start='RELATIVE R[10]C[9]';
454insert into &lib..mpe_xlmap_rules set
455 tx_from=0
456 ,tx_to='31DEC5999:23:59:59'dt
457 ,xlmap_id='BASEL-KM1'
458 ,xlmap_range_id='KM1:2/f'
459 ,xlmap_sheet='/3'
460 ,xlmap_start='RELATIVE R[10]C[10]';
461insert into &lib..mpe_xlmap_rules set
462 tx_from=0
463 ,tx_to='31DEC5999:23:59:59'dt
464 ,xlmap_id='BASEL-KM1'
465 ,xlmap_range_id='KM1:2a'
466 ,xlmap_sheet='KM1'
467 ,xlmap_start='ABSOLUTE H11'
468 ,xlmap_finish='RELATIVE R[0]C[1]';
469insert into &lib..mpe_xlmap_rules set
470 tx_from=0
471 ,tx_to='31DEC5999:23:59:59'dt
472 ,xlmap_id='BASEL-KM1'
473 ,xlmap_range_id='KM1:3'
474 ,xlmap_sheet='KM1'
475 ,xlmap_start='RELATIVE R[12]C[4]'
476 ,xlmap_finish='ABSOLUTE I13';
477insert into &lib..mpe_xlmap_rules set
478 tx_from=0
479 ,tx_to='31DEC5999:23:59:59'dt
480 ,xlmap_id='BASEL-CR2'
481 ,xlmap_range_id='CR2-sec1'
482 ,xlmap_sheet='CR2'
483 ,xlmap_start='ABSOLUTE D8'
484 ,xlmap_finish='BLANKROW';
485insert into &lib..mpe_xlmap_rules set
486 tx_from=0
487 ,tx_to='31DEC5999:23:59:59'dt
488 ,xlmap_id='BASEL-CR2'
489 ,xlmap_range_id='CR2-sec2'
490 ,xlmap_sheet='CR2'
491 ,xlmap_start='ABSOLUTE D18'
492 ,xlmap_finish='LASTDOWN';
493insert into &lib..mpe_xlmap_rules set
494 tx_from=0
495 ,tx_to='31DEC5999:23:59:59'dt
496 ,xlmap_id='SAMPLE'
497 ,xlmap_range_id='header'
498 ,xlmap_sheet='/1'
499 ,xlmap_start='ABSOLUTE B3'
500 ,xlmap_finish='ABSOLUTE B8';
501insert into &lib..mpe_xlmap_rules set
502 tx_from=0
503 ,tx_to='31DEC5999:23:59:59'dt
504 ,xlmap_id='SAMPLE'
505 ,xlmap_range_id='data'
506 ,xlmap_sheet='/1'
507 ,xlmap_start='ABSOLUTE B13'
508 ,xlmap_finish='ABSOLUTE E16';
509
510
511
512/**
513 * MPE_GROUPS
514 */
515insert into &lib..mpe_groups set
516 tx_from=0
517 ,group_name="dc-admin"
518 ,group_desc="Custom Group for Data Controller Purposes"
519 ,user_name="allbow"
520 ,tx_to='31DEC5999:23:59:59'dt;
521insert into &lib..mpe_groups set
522 tx_from=0
523 ,group_name="dc-admin"
524 ,group_desc="Custom Group for Data Controller Purposes"
525 ,user_name="dctestuser1"
526 ,tx_to='31DEC5999:23:59:59'dt;
527insert into &lib..mpe_groups set
528 tx_from=0
529 ,group_name="dc-admin"
530 ,group_desc="Custom Group for Data Controller Purposes"
531 ,user_name="mihmed"
532 ,tx_to='31DEC5999:23:59:59'dt;
533insert into &lib..mpe_groups set
534 tx_from=0
535 ,group_name="sec-sas9-prd-ext-sasplatform-300115datacontroller"
536 ,group_desc="Custom Group for Data Controller Purposes"
537 ,user_name="DCTest"
538 ,tx_to='31DEC5999:23:59:59'dt;
539
540/**
541 * MPE_ROW_LEVEL_SECURITY
542 */
543insert into &lib..mpe_row_level_security set
544 tx_from=0
545 ,tx_to='31DEC5999:23:59:59'dt
546 ,RLS_RK=1
547 ,RLS_SCOPE='ALL'
548 ,RLS_GROUP='sec-sas9-prd-int-sasplatform-300114sasjs'
549 ,RLS_LIBREF="&lib."
550 ,RLS_TABLE="MPE_GROUPS"
551 ,RLS_GROUP_LOGIC='AND'
552 ,RLS_SUBGROUP_LOGIC='OR'
553 ,RLS_SUBGROUP_ID=0
554 ,RLS_VARIABLE_NM='GROUP_NAME'
555 ,RLS_OPERATOR_NM='NE'
556 ,RLS_RAW_VALUE="'-1'"
557 ,RLS_ACTIVE=1;
558insert into &lib..mpe_row_level_security set
559 tx_from=0
560 ,tx_to='31DEC5999:23:59:59'dt
561 ,RLS_RK=2
562 ,RLS_SCOPE='ALL'
563 ,RLS_GROUP='sec-sas9-prd-int-sasplatform-300114sasjs'
564 ,RLS_LIBREF="&lib"
565 ,RLS_TABLE="MPE_ROW_LEVEL_SECURITY"
566 ,RLS_GROUP_LOGIC='AND'
567 ,RLS_SUBGROUP_LOGIC='OR'
568 ,RLS_SUBGROUP_ID=0
569 ,RLS_VARIABLE_NM='RLS_RK'
570 ,RLS_OPERATOR_NM='>'
571 ,RLS_RAW_VALUE='0'
572 ,RLS_ACTIVE=1;
573insert into &lib..mpe_row_level_security set
574 tx_from=0
575 ,tx_to='31DEC5999:23:59:59'dt
576 ,RLS_RK=3
577 ,RLS_SCOPE='ALL'
578 ,RLS_GROUP='DC Demo Group'
579 ,RLS_LIBREF="&lib"
580 ,RLS_TABLE="MPE_SECURITY"
581 ,RLS_GROUP_LOGIC='AND'
582 ,RLS_SUBGROUP_LOGIC='OR'
583 ,RLS_SUBGROUP_ID=0
584 ,RLS_VARIABLE_NM='ACCESS_LEVEL'
585 ,RLS_OPERATOR_NM='NE'
586 ,RLS_RAW_VALUE="'N/A'"
587 ,RLS_ACTIVE=1;
588
589
590/**
591 * MPE_SECURITY
592 */
593insert into &lib..mpe_security set
594 tx_from=0
595 ,libref="*ALL*"
596 ,dsn="*ALL*"
597 ,access_level="APPROVE"
598 ,sas_group="sec-sas9-prd-int-sasplatform-300114sasjs"
599 ,tx_to='31DEC5999:23:59:59'dt;
600insert into &lib..mpe_security set
601 tx_from=0
602 ,libref="*ALL*"
603 ,dsn="*ALL*"
604 ,access_level="EDIT"
605 ,sas_group="sec-sas9-prd-int-sasplatform-300114sasjs"
606 ,tx_to='31DEC5999:23:59:59'dt;
607insert into &lib..mpe_security set
608 tx_from=0
609 ,libref="*ALL*"
610 ,dsn="*ALL*"
611 ,access_level="APPROVE"
612 ,sas_group="sec-sas9-prd-ext-sasplatform-300114sasjs"
613 ,tx_to='31DEC5999:23:59:59'dt;
614insert into &lib..mpe_security set
615 tx_from=0
616 ,libref="*ALL*"
617 ,dsn="*ALL*"
618 ,access_level="EDIT"
619 ,sas_group="sec-sas9-prd-ext-sasplatform-300114sasjs"
620 ,tx_to='31DEC5999:23:59:59'dt;
621insert into &lib..mpe_security set
622 tx_from=0
623 ,libref="*ALL*"
624 ,dsn="*ALL*"
625 ,access_level="EDIT"
626 ,sas_group="dc-admin"
627 ,tx_to='31DEC5999:23:59:59'dt;
628insert into &lib..mpe_security set
629 tx_from=0
630 ,libref="*ALL*"
631 ,dsn="*ALL*"
632 ,access_level="APPROVE"
633 ,sas_group="dc-admin"
634 ,tx_to='31DEC5999:23:59:59'dt;
635
636
637 /* mpe_selectbox */
638 %let rk=1;
639 insert into &lib..mpe_selectbox set
640 selectbox_rk=&rk
641 ,ver_from_dttm=0
642 ,select_lib="&lib"
643 ,select_ds="MPE_LOCKANYTABLE"
644 ,base_column="LOCK_STATUS_CD"
645 ,selectbox_value='LOCKED'
646 ,selectbox_order=1
647 ,ver_to_dttm='31DEC5999:23:59:59'dt;
648 insert into &lib..mpe_selectbox set
649 selectbox_rk=%mf_increment(rk)
650 ,ver_from_dttm=0
651 ,select_lib="&lib"
652 ,select_ds="MPE_LOCKANYTABLE"
653 ,base_column="LOCK_STATUS_CD"
654 ,selectbox_value='UNLOCKED'
655 ,selectbox_order=2
656 ,ver_to_dttm='31DEC5999:23:59:59'dt;
657 insert into &lib..mpe_selectbox set
658 selectbox_rk=%mf_increment(rk)
659 ,ver_from_dttm=0
660 ,select_lib="&lib"
661 ,select_ds="MPE_SECURITY"
662 ,base_column="ACCESS_LEVEL"
663 ,selectbox_value='EDIT'
664 ,selectbox_order=0
665 ,ver_to_dttm='31DEC5999:23:59:59'dt;
666 insert into &lib..mpe_selectbox set
667 selectbox_rk=%mf_increment(rk)
668 ,ver_from_dttm=0
669 ,select_lib="&lib"
670 ,select_ds="MPE_SECURITY"
671 ,base_column="ACCESS_LEVEL"
672 ,selectbox_value='APPROVE'
673 ,selectbox_order=1
674 ,ver_to_dttm='31DEC5999:23:59:59'dt;
675 insert into &lib..mpe_selectbox set
676 selectbox_rk=%mf_increment(rk)
677 ,ver_from_dttm=0
678 ,select_lib="&lib"
679 ,select_ds="MPE_SECURITY"
680 ,base_column="ACCESS_LEVEL"
681 ,selectbox_value='VIEW'
682 ,selectbox_order=2
683 ,ver_to_dttm='31DEC5999:23:59:59'dt;
684 insert into &lib..mpe_selectbox set
685 selectbox_rk=%mf_increment(rk)
686 ,ver_from_dttm=0
687 ,select_lib="&lib"
688 ,select_ds="MPE_SECURITY"
689 ,base_column="ACCESS_LEVEL"
690 ,selectbox_value='SIGNOFF'
691 ,selectbox_order=3
692 ,ver_to_dttm='31DEC5999:23:59:59'dt;
693 insert into &lib..mpe_selectbox set
694 selectbox_rk=%mf_increment(rk)
695 ,ver_from_dttm=0
696 ,select_lib="&lib"
697 ,select_ds="MPE_TABLES"
698 ,base_column="LOADTYPE"
699 ,selectbox_value='UPDATE'
700 ,selectbox_order=1
701 ,ver_to_dttm='31DEC5999:23:59:59'dt;
702 insert into &lib..mpe_selectbox set
703 selectbox_rk=%mf_increment(rk)
704 ,ver_from_dttm=0
705 ,select_lib="&lib"
706 ,select_ds="MPE_TABLES"
707 ,base_column="LOADTYPE"
708 ,selectbox_value='REPLACE'
709 ,selectbox_order=2
710 ,ver_to_dttm='31DEC5999:23:59:59'dt;
711 insert into &lib..mpe_selectbox set
712 selectbox_rk=%mf_increment(rk)
713 ,ver_from_dttm=0
714 ,select_lib="&lib"
715 ,select_ds="MPE_TABLES"
716 ,base_column="LOADTYPE"
717 ,selectbox_value='TXTEMPORAL'
718 ,selectbox_order=3
719 ,ver_to_dttm='31DEC5999:23:59:59'dt;
720 insert into &lib..mpe_selectbox set
721 selectbox_rk=%mf_increment(rk)
722 ,ver_from_dttm=0
723 ,select_lib="&lib"
724 ,select_ds="MPE_TABLES"
725 ,base_column="LOADTYPE"
726 ,selectbox_value='BITEMPORAL'
727 ,selectbox_order=4
728 ,ver_to_dttm='31DEC5999:23:59:59'dt;
729insert into &lib..mpe_selectbox set
730 selectbox_rk=%mf_increment(rk)
731 ,ver_from_dttm=0
732 ,select_lib="&lib"
733 ,select_ds="MPE_TABLES"
734 ,base_column="LOADTYPE"
735 ,selectbox_value='FORMAT_CAT'
736 ,selectbox_order=5
737 ,ver_to_dttm='31DEC5999:23:59:59'dt;
738 insert into &lib..mpe_selectbox set
739 selectbox_rk=%mf_increment(rk)
740 ,ver_from_dttm=0
741 ,select_lib="&lib"
742 ,select_ds="MPE_ALERTS"
743 ,base_column="ALERT_EVENT"
744 ,selectbox_value='*ALL*'
745 ,selectbox_order=1
746 ,ver_to_dttm='31DEC5999:23:59:59'dt;
747 insert into &lib..mpe_selectbox set
748 selectbox_rk=%mf_increment(rk)
749 ,ver_from_dttm=0
750 ,select_lib="&lib"
751 ,select_ds="MPE_ALERTS"
752 ,base_column="ALERT_EVENT"
753 ,selectbox_value='SUBMITTED'
754 ,selectbox_order=2
755 ,ver_to_dttm='31DEC5999:23:59:59'dt;
756 insert into &lib..mpe_selectbox set
757 selectbox_rk=%mf_increment(rk)
758 ,ver_from_dttm=0
759 ,select_lib="&lib"
760 ,select_ds="MPE_ALERTS"
761 ,base_column="ALERT_EVENT"
762 ,selectbox_value='APPROVED'
763 ,selectbox_order=3
764 ,ver_to_dttm='31DEC5999:23:59:59'dt;
765 insert into &lib..mpe_selectbox set
766 selectbox_rk=%mf_increment(rk)
767 ,ver_from_dttm=0
768 ,select_lib="&lib"
769 ,select_ds="MPE_ALERTS"
770 ,base_column="ALERT_EVENT"
771 ,selectbox_value='REJECTED'
772 ,selectbox_order=4
773 ,ver_to_dttm='31DEC5999:23:59:59'dt;
774 insert into &lib..mpe_selectbox set
775 selectbox_rk=%mf_increment(rk)
776 ,ver_from_dttm=0
777 ,select_lib="&lib"
778 ,select_ds="MPE_X_TEST"
779 ,base_column="SOME_DROPDOWN"
780 ,selectbox_value='Option 1'
781 ,selectbox_order=1
782 ,ver_to_dttm='31DEC5999:23:59:59'dt;
783 insert into &lib..mpe_selectbox set
784 selectbox_rk=%mf_increment(rk)
785 ,ver_from_dttm=0
786 ,select_lib="&lib"
787 ,select_ds="MPE_X_TEST"
788 ,base_column="SOME_DROPDOWN"
789 ,selectbox_value='Option 2'
790 ,selectbox_order=2
791 ,ver_to_dttm='31DEC5999:23:59:59'dt;
792 insert into &lib..mpe_selectbox set
793 selectbox_rk=%mf_increment(rk)
794 ,ver_from_dttm=0
795 ,select_lib="&lib"
796 ,select_ds="MPE_X_TEST"
797 ,base_column="SOME_DROPDOWN"
798 ,selectbox_value='Option 3'
799 ,selectbox_order=2
800 ,ver_to_dttm='31DEC5999:23:59:59'dt;
801 insert into &lib..mpe_selectbox set
802 selectbox_rk=%mf_increment(rk)
803 ,ver_from_dttm=0
804 ,select_lib="&lib"
805 ,select_ds="MPE_X_TEST"
806 ,base_column="SOME_DROPDOWN"
807 ,selectbox_value="This is a long option. This option is very long. "
808 !!"It is optional, though."
809 ,selectbox_order=3
810 ,ver_to_dttm='31DEC5999:23:59:59'dt;
811 insert into &lib..mpe_selectbox set
812 selectbox_rk=%mf_increment(rk)
813 ,ver_from_dttm=0
814 ,select_lib="&lib"
815 ,select_ds="MPE_VALIDATIONS"
816 ,base_column="RULE_TYPE"
817 ,selectbox_value="CASE"
818 ,selectbox_order=1
819 ,ver_to_dttm='31DEC5999:23:59:59'dt;
820 insert into &lib..mpe_selectbox set
821 selectbox_rk=%mf_increment(rk)
822 ,ver_from_dttm=0
823 ,select_lib="&lib"
824 ,select_ds="MPE_VALIDATIONS"
825 ,base_column="RULE_TYPE"
826 ,selectbox_value="MINVAL"
827 ,selectbox_order=2
828 ,ver_to_dttm='31DEC5999:23:59:59'dt;
829 insert into &lib..mpe_selectbox set
830 selectbox_rk=%mf_increment(rk)
831 ,ver_from_dttm=0
832 ,select_lib="&lib"
833 ,select_ds="MPE_VALIDATIONS"
834 ,base_column="RULE_TYPE"
835 ,selectbox_value="MAXVAL"
836 ,selectbox_order=3
837 ,ver_to_dttm='31DEC5999:23:59:59'dt;
838 insert into &lib..mpe_selectbox set
839 selectbox_rk=%mf_increment(rk)
840 ,ver_from_dttm=0
841 ,select_lib="&lib"
842 ,select_ds="MPE_VALIDATIONS"
843 ,base_column="RULE_TYPE"
844 ,selectbox_value="HARDSELECT"
845 ,selectbox_order=4
846 ,ver_to_dttm='31DEC5999:23:59:59'dt;
847 insert into &lib..mpe_selectbox set
848 selectbox_rk=%mf_increment(rk)
849 ,ver_from_dttm=0
850 ,select_lib="&lib"
851 ,select_ds="MPE_VALIDATIONS"
852 ,base_column="RULE_TYPE"
853 ,selectbox_value="SOFTSELECT"
854 ,selectbox_order=5
855 ,ver_to_dttm='31DEC5999:23:59:59'dt;
856 insert into &lib..mpe_selectbox set
857 selectbox_rk=%mf_increment(rk)
858 ,ver_from_dttm=0
859 ,select_lib="&lib"
860 ,select_ds="MPE_VALIDATIONS"
861 ,base_column="RULE_TYPE"
862 ,selectbox_value="NOTNULL"
863 ,selectbox_order=6
864 ,ver_to_dttm='31DEC5999:23:59:59'dt;
865 insert into &lib..mpe_selectbox set
866 selectbox_rk=%mf_increment(rk)
867 ,ver_from_dttm=0
868 ,select_lib="&lib"
869 ,select_ds="MPE_SECURITY"
870 ,base_column="DSN"
871 ,selectbox_value="SOME_DATASET"
872 ,selectbox_order=1
873 ,ver_to_dttm='31DEC5999:23:59:59'dt;
874 insert into &lib..mpe_selectbox set
875 selectbox_rk=%mf_increment(rk)
876 ,ver_from_dttm=0
877 ,select_lib="&lib"
878 ,select_ds="MPE_SECURITY"
879 ,base_column="DSN"
880 ,selectbox_value="EXAMPLE"
881 ,selectbox_order=2
882 ,ver_to_dttm='31DEC5999:23:59:59'dt;
883 insert into &lib..mpe_selectbox set
884 selectbox_rk=%mf_increment(rk)
885 ,ver_from_dttm=0
886 ,select_lib="&lib"
887 ,select_ds="MPE_DATADICTIONARY"
888 ,base_column="DD_TYPE"
889 ,selectbox_value="COLUMN"
890 ,selectbox_order=1
891 ,ver_to_dttm='31DEC5999:23:59:59'dt;
892 insert into &lib..mpe_selectbox set
893 selectbox_rk=%mf_increment(rk)
894 ,ver_from_dttm=0
895 ,select_lib="&lib"
896 ,select_ds="MPE_DATADICTIONARY"
897 ,base_column="DD_TYPE"
898 ,selectbox_value="TABLE"
899 ,selectbox_order=2
900 ,ver_to_dttm='31DEC5999:23:59:59'dt;
901 insert into &lib..mpe_selectbox set
902 selectbox_rk=%mf_increment(rk)
903 ,ver_from_dttm=0
904 ,select_lib="&lib"
905 ,select_ds="MPE_DATADICTIONARY"
906 ,base_column="DD_TYPE"
907 ,selectbox_value="LIBRARY"
908 ,selectbox_order=3
909 ,ver_to_dttm='31DEC5999:23:59:59'dt;
910 insert into &lib..mpe_selectbox set
911 selectbox_rk=%mf_increment(rk)
912 ,ver_from_dttm=0
913 ,select_lib="&lib"
914 ,select_ds="MPE_DATADICTIONARY"
915 ,base_column="DD_TYPE"
916 ,selectbox_value="CATALOG"
917 ,selectbox_order=3
918 ,ver_to_dttm='31DEC5999:23:59:59'dt;
919 insert into &lib..mpe_selectbox set
920 selectbox_rk=%mf_increment(rk)
921 ,ver_from_dttm=0
922 ,select_lib="&lib"
923 ,select_ds="MPE_DATADICTIONARY"
924 ,base_column="DD_TYPE"
925 ,selectbox_value="FORMAT"
926 ,selectbox_order=3
927 ,ver_to_dttm='31DEC5999:23:59:59'dt;
928 insert into &lib..mpe_selectbox set
929 selectbox_rk=%mf_increment(rk)
930 ,ver_from_dttm=0
931 ,select_lib="&lib"
932 ,select_ds="MPE_SECURITY"
933 ,base_column="LIBREF"
934 ,selectbox_value='*ALL*'
935 ,selectbox_order=1
936 ,ver_to_dttm='31DEC5999:23:59:59'dt;
937 insert into &lib..mpe_selectbox set
938 selectbox_rk=%mf_increment(rk)
939 ,ver_from_dttm=0
940 ,select_lib="&lib"
941 ,select_ds="MPE_SECURITY"
942 ,base_column="ACCESS_LEVEL"
943 ,selectbox_value='AUDIT'
944 ,selectbox_order=4
945 ,ver_to_dttm='31DEC5999:23:59:59'dt;
946 insert into &lib..mpe_selectbox set
947 selectbox_rk=%mf_increment(rk)
948 ,ver_from_dttm=0
949 ,select_lib="&lib"
950 ,select_ds="MPE_VALIDATIONS"
951 ,base_column="RULE_TYPE"
952 ,selectbox_value="HARDSELECT_HOOK"
953 ,selectbox_order=7
954 ,ver_to_dttm='31DEC5999:23:59:59'dt;
955 insert into &lib..mpe_selectbox set
956 selectbox_rk=%mf_increment(rk)
957 ,ver_from_dttm=0
958 ,select_lib="&lib"
959 ,select_ds="MPE_VALIDATIONS"
960 ,base_column="RULE_TYPE"
961 ,selectbox_value="SOFTSELECT_HOOK"
962 ,selectbox_order=7
963 ,ver_to_dttm='31DEC5999:23:59:59'dt;
964 insert into &lib..mpe_selectbox set
965 selectbox_rk=%mf_increment(rk)
966 ,ver_from_dttm=0
967 ,select_lib="&lib"
968 ,select_ds="MPE_ROW_LEVEL_SECURITY"
969 ,base_column="RLS_SCOPE"
970 ,selectbox_value="ALL"
971 ,selectbox_order=1
972 ,ver_to_dttm='31DEC5999:23:59:59'dt;
973 insert into &lib..mpe_selectbox set
974 selectbox_rk=%mf_increment(rk)
975 ,ver_from_dttm=0
976 ,select_lib="&lib"
977 ,select_ds="MPE_ROW_LEVEL_SECURITY"
978 ,base_column="RLS_SCOPE"
979 ,selectbox_value="EDIT"
980 ,selectbox_order=1
981 ,ver_to_dttm='31DEC5999:23:59:59'dt;
982 insert into &lib..mpe_selectbox set
983 selectbox_rk=%mf_increment(rk)
984 ,ver_from_dttm=0
985 ,select_lib="&lib"
986 ,select_ds="MPE_ROW_LEVEL_SECURITY"
987 ,base_column="RLS_SCOPE"
988 ,selectbox_value="VIEW"
989 ,selectbox_order=1
990 ,ver_to_dttm='31DEC5999:23:59:59'dt;
991 insert into &lib..mpe_selectbox set
992 selectbox_rk=%mf_increment(rk)
993 ,ver_from_dttm=0
994 ,select_lib="&lib"
995 ,select_ds="MPE_ROW_LEVEL_SECURITY"
996 ,base_column="RLS_GROUP_LOGIC"
997 ,selectbox_value="AND"
998 ,selectbox_order=1
999 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1000 insert into &lib..mpe_selectbox set
1001 selectbox_rk=%mf_increment(rk)
1002 ,ver_from_dttm=0
1003 ,select_lib="&lib"
1004 ,select_ds="MPE_ROW_LEVEL_SECURITY"
1005 ,base_column="RLS_GROUP_LOGIC"
1006 ,selectbox_value="OR"
1007 ,selectbox_order=2
1008 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1009 insert into &lib..mpe_selectbox set
1010 selectbox_rk=%mf_increment(rk)
1011 ,ver_from_dttm=0
1012 ,select_lib="&lib"
1013 ,select_ds="MPE_ROW_LEVEL_SECURITY"
1014 ,base_column="RLS_SUBGROUP_LOGIC"
1015 ,selectbox_value="AND"
1016 ,selectbox_order=1
1017 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1018 insert into &lib..mpe_selectbox set
1019 selectbox_rk=%mf_increment(rk)
1020 ,ver_from_dttm=0
1021 ,select_lib="&lib"
1022 ,select_ds="MPE_ROW_LEVEL_SECURITY"
1023 ,base_column="RLS_SUBGROUP_LOGIC"
1024 ,selectbox_value="OR"
1025 ,selectbox_order=2
1026 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1027 insert into &lib..mpe_selectbox set
1028 selectbox_rk=%mf_increment(rk)
1029 ,ver_from_dttm=0
1030 ,select_lib="&lib"
1031 ,select_ds="MPE_ROW_LEVEL_SECURITY"
1032 ,base_column="RLS_OPERATOR_NM"
1033 ,selectbox_value="="
1034 ,selectbox_order=0
1035 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1036 insert into &lib..mpe_selectbox set
1037 selectbox_rk=%mf_increment(rk)
1038 ,ver_from_dttm=0
1039 ,select_lib="&lib"
1040 ,select_ds="MPE_ROW_LEVEL_SECURITY"
1041 ,base_column="RLS_OPERATOR_NM"
1042 ,selectbox_value=">"
1043 ,selectbox_order=1
1044 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1045 insert into &lib..mpe_selectbox set
1046 selectbox_rk=%mf_increment(rk)
1047 ,ver_from_dttm=0
1048 ,select_lib="&lib"
1049 ,select_ds="MPE_ROW_LEVEL_SECURITY"
1050 ,base_column="RLS_OPERATOR_NM"
1051 ,selectbox_value="<"
1052 ,selectbox_order=1
1053 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1054 insert into &lib..mpe_selectbox set
1055 selectbox_rk=%mf_increment(rk)
1056 ,ver_from_dttm=0
1057 ,select_lib="&lib"
1058 ,select_ds="MPE_ROW_LEVEL_SECURITY"
1059 ,base_column="RLS_OPERATOR_NM"
1060 ,selectbox_value="<="
1061 ,selectbox_order=1
1062 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1063 insert into &lib..mpe_selectbox set
1064 selectbox_rk=%mf_increment(rk)
1065 ,ver_from_dttm=0
1066 ,select_lib="&lib"
1067 ,select_ds="MPE_ROW_LEVEL_SECURITY"
1068 ,base_column="RLS_OPERATOR_NM"
1069 ,selectbox_value=">="
1070 ,selectbox_order=1
1071 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1072 insert into &lib..mpe_selectbox set
1073 selectbox_rk=%mf_increment(rk)
1074 ,ver_from_dttm=0
1075 ,select_lib="&lib"
1076 ,select_ds="MPE_ROW_LEVEL_SECURITY"
1077 ,base_column="RLS_OPERATOR_NM"
1078 ,selectbox_value="BETWEEN"
1079 ,selectbox_order=1
1080 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1081 insert into &lib..mpe_selectbox set
1082 selectbox_rk=%mf_increment(rk)
1083 ,ver_from_dttm=0
1084 ,select_lib="&lib"
1085 ,select_ds="MPE_ROW_LEVEL_SECURITY"
1086 ,base_column="RLS_OPERATOR_NM"
1087 ,selectbox_value="IN"
1088 ,selectbox_order=1
1089 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1090 insert into &lib..mpe_selectbox set
1091 selectbox_rk=%mf_increment(rk)
1092 ,ver_from_dttm=0
1093 ,select_lib="&lib"
1094 ,select_ds="MPE_ROW_LEVEL_SECURITY"
1095 ,base_column="RLS_OPERATOR_NM"
1096 ,selectbox_value="NOT IN"
1097 ,selectbox_order=1
1098 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1099 insert into &lib..mpe_selectbox set
1100 selectbox_rk=%mf_increment(rk)
1101 ,ver_from_dttm=0
1102 ,select_lib="&lib"
1103 ,select_ds="MPE_ROW_LEVEL_SECURITY"
1104 ,base_column="RLS_OPERATOR_NM"
1105 ,selectbox_value="NE"
1106 ,selectbox_order=1
1107 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1108 insert into &lib..mpe_selectbox set
1109 selectbox_rk=%mf_increment(rk)
1110 ,ver_from_dttm=0
1111 ,select_lib="&lib"
1112 ,select_ds="MPE_ROW_LEVEL_SECURITY"
1113 ,base_column="RLS_OPERATOR_NM"
1114 ,selectbox_value="CONTAINS"
1115 ,selectbox_order=1
1116 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1117 insert into &lib..mpe_selectbox set
1118 selectbox_rk=%mf_increment(rk)
1119 ,ver_from_dttm=0
1120 ,select_lib="&lib"
1121 ,select_ds="MPE_EXCEL_CONFIG"
1122 ,base_column="XL_RULE"
1123 ,selectbox_value="FORMULA"
1124 ,selectbox_order=1
1125 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1126 insert into &lib..mpe_selectbox set
1127 selectbox_rk=%mf_increment(rk)
1128 ,ver_from_dttm=0
1129 ,select_lib="&lib"
1130 ,select_ds="MPE_ROW_LEVEL_SECURITY"
1131 ,base_column="RLS_ACTIVE"
1132 ,selectbox_value="1"
1133 ,selectbox_order=1
1134 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1135 insert into &lib..mpe_selectbox set
1136 selectbox_rk=%mf_increment(rk)
1137 ,ver_from_dttm=0
1138 ,select_lib="&lib"
1139 ,select_ds="MPE_ROW_LEVEL_SECURITY"
1140 ,base_column="RLS_ACTIVE"
1141 ,selectbox_value="0"
1142 ,selectbox_order=2
1143 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1144 insert into &lib..mpe_selectbox set
1145 selectbox_rk=%mf_increment(rk)
1146 ,ver_from_dttm=0
1147 ,select_lib="&lib"
1148 ,select_ds="MPE_COLUMN_LEVEL_SECURITY"
1149 ,base_column="CLS_ACTIVE"
1150 ,selectbox_value="1"
1151 ,selectbox_order=1
1152 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1153 insert into &lib..mpe_selectbox set
1154 selectbox_rk=%mf_increment(rk)
1155 ,ver_from_dttm=0
1156 ,select_lib="&lib"
1157 ,select_ds="MPE_COLUMN_LEVEL_SECURITY"
1158 ,base_column="CLS_ACTIVE"
1159 ,selectbox_value="0"
1160 ,selectbox_order=2
1161 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1162 insert into &lib..mpe_selectbox set
1163 selectbox_rk=%mf_increment(rk)
1164 ,ver_from_dttm=0
1165 ,select_lib="&lib"
1166 ,select_ds="MPE_COLUMN_LEVEL_SECURITY"
1167 ,base_column="CLS_SCOPE"
1168 ,selectbox_value="EDIT"
1169 ,selectbox_order=1
1170 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1171 insert into &lib..mpe_selectbox set
1172 selectbox_rk=%mf_increment(rk)
1173 ,ver_from_dttm=0
1174 ,select_lib="&lib"
1175 ,select_ds="MPE_COLUMN_LEVEL_SECURITY"
1176 ,base_column="CLS_SCOPE"
1177 ,selectbox_value="VIEW"
1178 ,selectbox_order=2
1179 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1180 insert into &lib..mpe_selectbox set
1181 selectbox_rk=%mf_increment(rk)
1182 ,ver_from_dttm=0
1183 ,select_lib="&lib"
1184 ,select_ds="MPE_COLUMN_LEVEL_SECURITY"
1185 ,base_column="CLS_SCOPE"
1186 ,selectbox_value="ALL"
1187 ,selectbox_order=3
1188 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1189 insert into &lib..mpe_selectbox set
1190 selectbox_rk=%mf_increment(rk)
1191 ,ver_from_dttm=0
1192 ,select_lib="&lib"
1193 ,select_ds="MPE_COLUMN_LEVEL_SECURITY"
1194 ,base_column="CLS_HIDE"
1195 ,selectbox_value="0"
1196 ,selectbox_order=1
1197 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1198 insert into &lib..mpe_selectbox set
1199 selectbox_rk=%mf_increment(rk)
1200 ,ver_from_dttm=0
1201 ,select_lib="&lib"
1202 ,select_ds="MPE_COLUMN_LEVEL_SECURITY"
1203 ,base_column="CLS_HIDE"
1204 ,selectbox_value="1"
1205 ,selectbox_order=2
1206 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1207
1208/**
1209 * MPE_TABLES
1210 */
1211 insert into &lib..mpe_tables
1212 set tx_from=0
1213 ,tx_to='31DEC5999:23:59:59'dt
1214 ,libref="&lib"
1215 ,dsn='MPE_COLUMN_LEVEL_SECURITY'
1216 ,num_of_approvals_required=1
1217 ,loadtype='TXTEMPORAL'
1218 ,var_txfrom='TX_FROM'
1219 ,var_txto='TX_TO'
1220 ,buskey='CLS_SCOPE CLS_GROUP CLS_LIBREF CLS_TABLE CLS_VARIABLE_NM'
1221 ,notes='Docs: https://docs.datacontroller.io/column-level-security'
1222 ,post_edit_hook='services/hooks/mpe_column_level_security_postedit'
1223 ;
1224 insert into &lib..mpe_tables
1225 set tx_from=0
1226 ,tx_to='31DEC5999:23:59:59'dt
1227 ,libref="&lib"
1228 ,dsn='MPE_XLMAP_INFO'
1229 ,num_of_approvals_required=1
1230 ,loadtype='TXTEMPORAL'
1231 ,var_txfrom='TX_FROM'
1232 ,var_txto='TX_TO'
1233 ,buskey='XLMAP_ID'
1234 ,notes='Docs: https://docs.datacontroller.io/complex-excel-uploads'
1235 ,post_edit_hook='services/hooks/mpe_xlmap_info_postedit'
1236 ;
1237 insert into &lib..mpe_tables
1238 set tx_from=0
1239 ,tx_to='31DEC5999:23:59:59'dt
1240 ,libref="&lib"
1241 ,dsn='MPE_XLMAP_RULES'
1242 ,num_of_approvals_required=1
1243 ,loadtype='TXTEMPORAL'
1244 ,var_txfrom='TX_FROM'
1245 ,var_txto='TX_TO'
1246 ,buskey='XLMAP_ID XLMAP_RANGE_ID'
1247 ,notes='Docs: https://docs.datacontroller.io/complex-excel-uploads'
1248 ,post_edit_hook='services/hooks/mpe_xlmap_rules_postedit'
1249 ;
1250 insert into &lib..mpe_tables
1251 set tx_from=0
1252 ,tx_to='31DEC5999:23:59:59'dt
1253 ,libref="&lib"
1254 ,dsn='MPE_XLMAP_DATA'
1255 ,num_of_approvals_required=1
1256 ,loadtype='UPDATE'
1257 ,buskey='LOAD_REF XLMAP_ID XLMAP_RANGE_ID ROW_NO COL_NO'
1258 ,notes='Docs: https://docs.datacontroller.io/complex-excel-uploads'
1259 ;
1260 insert into &lib..mpe_tables
1261 set tx_from=0
1262 ,tx_to='31DEC5999:23:59:59'dt
1263 ,libref="&lib"
1264 ,dsn='MPE_LOCKANYTABLE'
1265 ,num_of_approvals_required=1
1266 ,loadtype='UPDATE'
1267 ,buskey='LOCK_LIB LOCK_DS'
1268 ,notes='This table may be edited when a process failed and left a lock'
1269 ;
1270 insert into &lib..mpe_tables
1271 set tx_from=0
1272 ,tx_to='31DEC5999:23:59:59'dt
1273 ,libref="&lib"
1274 ,dsn='MPE_TABLES'
1275 ,num_of_approvals_required=1
1276 ,loadtype='TXTEMPORAL'
1277 ,buskey='LIBREF DSN'
1278 ,var_txfrom='TX_FROM'
1279 ,var_txto='TX_TO'
1280 ,notes='This entry allows the MP Editor to edit itself!'
1281 ,post_edit_hook='services/hooks/mpe_tables_postedit'
1282 ;
1283 insert into &lib..mpe_tables
1284 set tx_from=0
1285 ,tx_to='31DEC5999:23:59:59'dt
1286 ,libref="&lib"
1287 ,dsn='MPE_SECURITY'
1288 ,num_of_approvals_required=1
1289 ,loadtype='TXTEMPORAL'
1290 ,buskey='LIBREF DSN ACCESS_LEVEL SAS_GROUP'
1291 ,var_txfrom='TX_FROM'
1292 ,var_txto='TX_TO'
1293 ,notes='Determines which groups can view/edit/approve which tables'
1294 ,post_edit_hook='services/hooks/mpe_security_postedit'
1295 ;
1296 insert into &lib..mpe_tables
1297 set tx_from=0
1298 ,tx_to='31DEC5999:23:59:59'dt
1299 ,libref="&lib"
1300 ,dsn='MPE_SELECTBOX'
1301 ,num_of_approvals_required=1
1302 ,loadtype='TXTEMPORAL'
1303 ,buskey='SELECTBOX_RK'
1304 ,var_txfrom='VER_FROM_DTTM'
1305 ,var_txto='VER_TO_DTTM'
1306 ,notes='Can configure dropdowns for the front end'
1307 ,rk_underlying='SELECT_LIB SELECT_DS BASE_COLUMN SELECTBOX_VALUE'
1308 ;
1309 insert into &lib..mpe_tables
1310 set tx_from=0
1311 ,tx_to='31DEC5999:23:59:59'dt
1312 ,libref="&lib"
1313 ,dsn='MPE_X_TEST'
1314 ,num_of_approvals_required=1
1315 ,loadtype='UPDATE'
1316 ,buskey='PRIMARY_KEY_FIELD'
1317 ,notes='Test table for controller'
1318 ;
1319 insert into &lib..mpe_tables
1320 set tx_from=0
1321 ,tx_to='31DEC5999:23:59:59'dt
1322 ,libref="&lib"
1323 ,dsn='MPE_EMAILS'
1324 ,num_of_approvals_required=1
1325 ,loadtype='TXTEMPORAL'
1326 ,buskey='USER_NAME'
1327 ,notes='Primary Emails Table (backup is metadata)'
1328 ,var_txfrom='TX_FROM'
1329 ,var_txto='TX_TO'
1330 ;
1331 insert into &lib..mpe_tables
1332 set tx_from=0
1333 ,tx_to='31DEC5999:23:59:59'dt
1334 ,libref="&lib"
1335 ,dsn='MPE_CONFIG'
1336 ,num_of_approvals_required=1
1337 ,loadtype='TXTEMPORAL'
1338 ,buskey='VAR_SCOPE VAR_NAME'
1339 ,notes='Configuration variables for Data Controller'
1340 ,var_txfrom='TX_FROM'
1341 ,var_txto='TX_TO'
1342 ;
1343 insert into &lib..mpe_tables
1344 set tx_from=0
1345 ,tx_to='31DEC5999:23:59:59'dt
1346 ,libref="&lib"
1347 ,dsn='MPE_ALERTS'
1348 ,num_of_approvals_required=1
1349 ,loadtype='TXTEMPORAL'
1350 ,buskey='ALERT_EVENT ALERT_LIB ALERT_DS ALERT_USER'
1351 ,notes='Configuration for alert email events'
1352 ,var_txfrom='TX_FROM'
1353 ,var_txto='TX_TO'
1354 ;
1355 insert into &lib..mpe_tables
1356 set tx_from=0
1357 ,tx_to='31DEC5999:23:59:59'dt
1358 ,libref="&lib"
1359 ,dsn='MPE_GROUPS'
1360 ,num_of_approvals_required=1
1361 ,loadtype='TXTEMPORAL'
1362 ,buskey='GROUP_NAME USER_NAME'
1363 ,notes='Configuration for additional groups within Data Controller'
1364 ,var_txfrom='TX_FROM'
1365 ,var_txto='TX_TO'
1366 ;
1367 insert into &lib..mpe_tables
1368 set tx_from=0
1369 ,tx_to='31DEC5999:23:59:59'dt
1370 ,libref="&lib"
1371 ,dsn='MPE_VALIDATIONS'
1372 ,num_of_approvals_required=1
1373 ,loadtype='TXTEMPORAL'
1374 ,buskey='BASE_LIB BASE_DS BASE_COL RULE_TYPE'
1375 ,notes='Configuration of data quality rules in Editor component'
1376 ,var_txfrom='TX_FROM'
1377 ,var_txto='TX_TO'
1378 ,post_edit_hook='services/hooks/mpe_validations_postedit'
1379 ;
1380 insert into &lib..mpe_tables
1381 set tx_from=0
1382 ,tx_to='31DEC5999:23:59:59'dt
1383 ,libref="&lib"
1384 ,dsn='MPE_DATADICTIONARY'
1385 ,num_of_approvals_required=1
1386 ,loadtype='TXTEMPORAL'
1387 ,buskey='DD_TYPE DD_SOURCE'
1388 ,notes='Configuration of data dictionary'
1389 ,var_txfrom='TX_FROM'
1390 ,var_txto='TX_TO'
1391 ;
1392 insert into &lib..mpe_tables
1393 set tx_from=0
1394 ,tx_to='31DEC5999:23:59:59'dt
1395 ,libref="&lib"
1396 ,dsn='MPE_EXCEL_CONFIG'
1397 ,num_of_approvals_required=1
1398 ,loadtype='TXTEMPORAL'
1399 ,buskey='XL_LIBREF XL_TABLE XL_COLUMN'
1400 ,notes='Configuration of the excel import rules'
1401 ,var_txfrom='TX_FROM'
1402 ,var_txto='TX_TO'
1403 ;
1404 insert into &lib..mpe_tables
1405 set tx_from=0
1406 ,tx_to='31DEC5999:23:59:59'dt
1407 ,libref="&lib"
1408 ,dsn='MPE_ROW_LEVEL_SECURITY'
1409 ,num_of_approvals_required=1
1410 ,loadtype='TXTEMPORAL'
1411 ,buskey='RLS_RK'
1412 ,notes='Configuration of Row Level Security'
1413 ,var_txfrom='TX_FROM'
1414 ,var_txto='TX_TO'
1415 ,rk_underlying='RLS_SCOPE RLS_GROUP RLS_LIBREF RLS_TABLE RLS_GROUP_LOGIC '
1416 !!'RLS_SUBGROUP_LOGIC RLS_SUBGROUP_ID RLS_VARIABLE_NM RLS_OPERATOR_NM '
1417 !!'RLS_RAW_VALUE '
1418 ,post_edit_hook='services/hooks/mpe_row_level_security_postedit'
1419 ;
1420 insert into &lib..mpe_tables
1421 set tx_from=0
1422 ,tx_to='31DEC5999:23:59:59'dt
1423 ,libref="&lib"
1424 ,dsn='MPE_X_CATALOG-FC'
1425 ,num_of_approvals_required=1
1426 ,loadtype='FORMAT_CAT'
1427 ,buskey='TYPE FMTNAME FMTROW'
1428 ,notes='Sample Format Catalog'
1429 ;
1430
1431/* mpe_validations */
1432insert into &lib..MPE_VALIDATIONS set
1433 tx_from=0
1434 ,base_lib="&lib"
1435 ,base_ds="MPE_COLUMN_LEVEL_SECURITY"
1436 ,base_col="CLS_SCOPE"
1437 ,rule_type='CASE'
1438 ,rule_value='UPCASE'
1439 ,rule_active=1
1440 ,tx_to='31DEC5999:23:59:59'dt;
1441insert into &lib..MPE_VALIDATIONS set
1442 tx_from=0
1443 ,base_lib="&lib"
1444 ,base_ds="MPE_COLUMN_LEVEL_SECURITY"
1445 ,base_col="CLS_LIBREF"
1446 ,rule_type='CASE'
1447 ,rule_value='UPCASE'
1448 ,rule_active=1
1449 ,tx_to='31DEC5999:23:59:59'dt;
1450insert into &lib..MPE_VALIDATIONS set
1451 tx_from=0
1452 ,base_lib="&lib"
1453 ,base_ds="MPE_COLUMN_LEVEL_SECURITY"
1454 ,base_col="CLS_LIBREF"
1455 ,rule_type='SOFTSELECT_HOOK'
1456 ,rule_value="services/validations/libraries_all"
1457 ,rule_active=1
1458 ,tx_to='31DEC5999:23:59:59'dt;
1459insert into &lib..MPE_VALIDATIONS set
1460 tx_from=0
1461 ,base_lib="&lib"
1462 ,base_ds="MPE_COLUMN_LEVEL_SECURITY"
1463 ,base_col="CLS_TABLE"
1464 ,rule_type='CASE'
1465 ,rule_value='UPCASE'
1466 ,rule_active=1
1467 ,tx_to='31DEC5999:23:59:59'dt;
1468insert into &lib..MPE_VALIDATIONS set
1469 tx_from=0
1470 ,base_lib="&lib"
1471 ,base_ds="MPE_COLUMN_LEVEL_SECURITY"
1472 ,base_col="CLS_TABLE"
1473 ,rule_type='SOFTSELECT_HOOK'
1474 ,rule_value="services/validations/tables_all"
1475 ,rule_active=1
1476 ,tx_to='31DEC5999:23:59:59'dt;
1477insert into &lib..MPE_VALIDATIONS set
1478 tx_from=0
1479 ,base_lib="&lib"
1480 ,base_ds="MPE_COLUMN_LEVEL_SECURITY"
1481 ,base_col="CLS_VARIABLE_NM"
1482 ,rule_type='CASE'
1483 ,rule_value='UPCASE'
1484 ,rule_active=1
1485 ,tx_to='31DEC5999:23:59:59'dt;
1486insert into &lib..MPE_VALIDATIONS set
1487 tx_from=0
1488 ,base_lib="&lib"
1489 ,base_ds="MPE_COLUMN_LEVEL_SECURITY"
1490 ,base_col="CLS_VARIABLE_NM"
1491 ,rule_type='SOFTSELECT_HOOK'
1492 ,rule_value="services/validations/columns_in_libds"
1493 ,rule_active=1
1494 ,tx_to='31DEC5999:23:59:59'dt;
1495insert into &lib..MPE_VALIDATIONS set
1496 tx_from=0
1497 ,base_lib="&lib"
1498 ,base_ds="MPE_COLUMN_LEVEL_SECURITY"
1499 ,base_col="CLS_ACTIVE"
1500 ,rule_type='MAXVAL'
1501 ,rule_value='1'
1502 ,rule_active=1
1503 ,tx_to='31DEC5999:23:59:59'dt;
1504insert into &lib..MPE_VALIDATIONS set
1505 tx_from=0
1506 ,base_lib="&lib"
1507 ,base_ds="MPE_COLUMN_LEVEL_SECURITY"
1508 ,base_col="CLS_HIDE"
1509 ,rule_type='MAXVAL'
1510 ,rule_value='1'
1511 ,rule_active=1
1512 ,tx_to='31DEC5999:23:59:59'dt;
1513insert into &lib..MPE_VALIDATIONS set
1514 tx_from=0
1515 ,base_lib="&lib"
1516 ,base_ds="MPE_COLUMN_LEVEL_SECURITY"
1517 ,base_col="CLS_GROUP"
1518 ,rule_type='SOFTSELECT_HOOK'
1519 ,rule_value="services/validations/sas_groups"
1520 ,rule_active=1
1521 ,tx_to='31DEC5999:23:59:59'dt;
1522
1523insert into &lib..MPE_VALIDATIONS set
1524 tx_from=0
1525 ,base_lib="&lib"
1526 ,base_ds="MPE_ALERTS"
1527 ,base_col="ALERT_LIB"
1528 ,rule_type='HARDSELECT_HOOK'
1529 ,rule_value="services/validations/mpe_alerts.alert_lib"
1530 ,rule_active=1
1531 ,tx_to='31DEC5999:23:59:59'dt;
1532
1533insert into &lib..MPE_VALIDATIONS set
1534 tx_from=0
1535 ,base_lib="&lib"
1536 ,base_ds="MPE_XLMAP_INFO"
1537 ,base_col="XLMAP_ID"
1538 ,rule_type='CASE'
1539 ,rule_value='UPCASE'
1540 ,rule_active=1
1541 ,tx_to='31DEC5999:23:59:59'dt;
1542
1543insert into &lib..MPE_VALIDATIONS set
1544 tx_from=0
1545 ,base_lib="&lib"
1546 ,base_ds="MPE_XLMAP_RULES"
1547 ,base_col="XLMAP_ID"
1548 ,rule_type='CASE'
1549 ,rule_value='UPCASE'
1550 ,rule_active=1
1551 ,tx_to='31DEC5999:23:59:59'dt;
1552
1553insert into &lib..MPE_VALIDATIONS set
1554 tx_from=0
1555 ,base_lib="&lib"
1556 ,base_ds="MPE_TABLES"
1557 ,base_col="LIBREF"
1558 ,rule_type='CASE'
1559 ,rule_value='UPCASE'
1560 ,rule_active=1
1561 ,tx_to='31DEC5999:23:59:59'dt;
1562insert into &lib..MPE_VALIDATIONS set
1563 tx_from=0
1564 ,base_lib="&lib"
1565 ,base_ds="MPE_TABLES"
1566 ,base_col="DSN"
1567 ,rule_type='CASE'
1568 ,rule_value='UPCASE'
1569 ,rule_active=1
1570 ,tx_to='31DEC5999:23:59:59'dt;
1571insert into &lib..MPE_VALIDATIONS set
1572 tx_from=0
1573 ,base_lib="&lib"
1574 ,base_ds="MPE_TABLES"
1575 ,base_col="LIBREF"
1576 ,rule_type='NOTNULL'
1577 ,rule_value=' '
1578 ,rule_active=1
1579 ,tx_to='31DEC5999:23:59:59'dt;
1580insert into &lib..MPE_VALIDATIONS set
1581 tx_from=0
1582 ,base_lib="&lib"
1583 ,base_ds="MPE_TABLES"
1584 ,base_col="DSN"
1585 ,rule_type='NOTNULL'
1586 ,rule_value=' '
1587 ,rule_active=1
1588 ,tx_to='31DEC5999:23:59:59'dt;
1589insert into &lib..MPE_VALIDATIONS set
1590 tx_from=0
1591 ,base_lib="&lib"
1592 ,base_ds="MPE_TABLES"
1593 ,base_col="NUM_OF_APPROVALS_REQUIRED"
1594 ,rule_type='MINVAL'
1595 ,rule_value='1'
1596 ,rule_active=1
1597 ,tx_to='31DEC5999:23:59:59'dt;
1598insert into &lib..MPE_VALIDATIONS set
1599 tx_from=0
1600 ,base_lib="&lib"
1601 ,base_ds="MPE_TABLES"
1602 ,base_col="BUSKEY"
1603 ,rule_type='CASE'
1604 ,rule_value='UPCASE'
1605 ,rule_active=1
1606 ,tx_to='31DEC5999:23:59:59'dt;
1607insert into &lib..MPE_VALIDATIONS set
1608 tx_from=0
1609 ,base_lib="&lib"
1610 ,base_ds="MPE_TABLES"
1611 ,base_col="BUSKEY"
1612 ,rule_type='NOTNULL'
1613 ,rule_value=" "
1614 ,rule_active=1
1615 ,tx_to='31DEC5999:23:59:59'dt;
1616insert into &lib..MPE_VALIDATIONS set
1617 tx_from=0
1618 ,base_lib="&lib"
1619 ,base_ds="MPE_TABLES"
1620 ,base_col="VAR_TXFROM"
1621 ,rule_type='CASE'
1622 ,rule_value='UPCASE'
1623 ,rule_active=1
1624 ,tx_to='31DEC5999:23:59:59'dt;
1625insert into &lib..MPE_VALIDATIONS set
1626 tx_from=0
1627 ,base_lib="&lib"
1628 ,base_ds="MPE_TABLES"
1629 ,base_col="VAR_TXTO"
1630 ,rule_type='CASE'
1631 ,rule_value='UPCASE'
1632 ,rule_active=1
1633 ,tx_to='31DEC5999:23:59:59'dt;
1634insert into &lib..MPE_VALIDATIONS set
1635 tx_from=0
1636 ,base_lib="&lib"
1637 ,base_ds="MPE_TABLES"
1638 ,base_col="VAR_BUSFROM"
1639 ,rule_type='CASE'
1640 ,rule_value='UPCASE'
1641 ,rule_active=1
1642 ,tx_to='31DEC5999:23:59:59'dt;
1643insert into &lib..MPE_VALIDATIONS set
1644 tx_from=0
1645 ,base_lib="&lib"
1646 ,base_ds="MPE_TABLES"
1647 ,base_col="VAR_BUSTO"
1648 ,rule_type='CASE'
1649 ,rule_value='UPCASE'
1650 ,rule_active=1
1651 ,tx_to='31DEC5999:23:59:59'dt;
1652insert into &lib..MPE_VALIDATIONS set
1653 tx_from=0
1654 ,base_lib="&lib"
1655 ,base_ds="MPE_SECURITY"
1656 ,base_col="LIBREF"
1657 ,rule_type='CASE'
1658 ,rule_value="UPCASE"
1659 ,rule_active=1
1660 ,tx_to='31DEC5999:23:59:59'dt;
1661insert into &lib..MPE_VALIDATIONS set
1662 tx_from=0
1663 ,base_lib="&lib"
1664 ,base_ds="MPE_TABLES"
1665 ,base_col="VAR_PROCESSED"
1666 ,rule_type='CASE'
1667 ,rule_value='UPCASE'
1668 ,rule_active=1
1669 ,tx_to='31DEC5999:23:59:59'dt;
1670insert into &lib..MPE_VALIDATIONS set
1671 tx_from=0
1672 ,base_lib="&lib"
1673 ,base_ds="MPE_SECURITY"
1674 ,base_col="LIBREF"
1675 ,rule_type='HARDSELECT'
1676 ,rule_value="&lib..MPE_TABLES.LIBREF"
1677 ,rule_active=1
1678 ,tx_to='31DEC5999:23:59:59'dt;
1679insert into &lib..MPE_VALIDATIONS set
1680 tx_from=0
1681 ,base_lib="&lib"
1682 ,base_ds="MPE_SECURITY"
1683 ,base_col="DSN"
1684 ,rule_type='CASE'
1685 ,rule_value="UPCASE"
1686 ,rule_active=1
1687 ,tx_to='31DEC5999:23:59:59'dt;
1688insert into &lib..MPE_VALIDATIONS set
1689 tx_from=0
1690 ,base_lib="&lib"
1691 ,base_ds="MPE_SECURITY"
1692 ,base_col="DSN"
1693 ,rule_type='SOFTSELECT'
1694 ,rule_value="&lib..MPE_TABLES.DSN"
1695 ,rule_active=1
1696 ,tx_to='31DEC5999:23:59:59'dt;
1697insert into &lib..MPE_VALIDATIONS set
1698 tx_from=0
1699 ,base_lib="&lib"
1700 ,base_ds="MPE_SECURITY"
1701 ,base_col="SAS_GROUP"
1702 ,rule_type='SOFTSELECT_HOOK'
1703 ,rule_value="services/validations/sas_groups"
1704 ,rule_active=1
1705 ,tx_to='31DEC5999:23:59:59'dt;
1706insert into &lib..MPE_VALIDATIONS set
1707 tx_from=0
1708 ,base_lib="&lib"
1709 ,base_ds="MPE_VALIDATIONS"
1710 ,base_col="BASE_LIB"
1711 ,rule_type='SOFTSELECT_HOOK'
1712 ,rule_value="services/validations/libraries_editable"
1713 ,rule_active=1
1714 ,tx_to='31DEC5999:23:59:59'dt;
1715insert into &lib..MPE_VALIDATIONS set
1716 tx_from=0
1717 ,base_lib="&lib"
1718 ,base_ds="MPE_VALIDATIONS"
1719 ,base_col="BASE_DS"
1720 ,rule_type='SOFTSELECT_HOOK'
1721 ,rule_value="services/validations/tables_editable"
1722 ,rule_active=1
1723 ,tx_to='31DEC5999:23:59:59'dt;
1724insert into &lib..MPE_VALIDATIONS set
1725 tx_from=0
1726 ,base_lib="&lib"
1727 ,base_ds="MPE_VALIDATIONS"
1728 ,base_col="BASE_COL"
1729 ,rule_type='SOFTSELECT_HOOK'
1730 ,rule_value="services/validations/columns_in_libds"
1731 ,rule_active=1
1732 ,tx_to='31DEC5999:23:59:59'dt;
1733insert into &lib..MPE_VALIDATIONS set
1734 tx_from=0
1735 ,base_lib="&lib"
1736 ,base_ds="MPE_VALIDATIONS"
1737 ,base_col="RULE_ACTIVE"
1738 ,rule_type='MINVAL'
1739 ,rule_value="0"
1740 ,rule_active=1
1741 ,tx_to='31DEC5999:23:59:59'dt;
1742insert into &lib..MPE_VALIDATIONS set
1743 tx_from=0
1744 ,base_lib="&lib"
1745 ,base_ds="MPE_VALIDATIONS"
1746 ,base_col="RULE_ACTIVE"
1747 ,rule_type='MAXVAL'
1748 ,rule_value="1"
1749 ,rule_active=1
1750 ,tx_to='31DEC5999:23:59:59'dt;
1751insert into &lib..MPE_VALIDATIONS set
1752 tx_from=0
1753 ,base_lib="&lib"
1754 ,base_ds="MPE_EXCEL_CONFIG"
1755 ,base_col="XL_LIBREF"
1756 ,rule_type='SOFTSELECT_HOOK'
1757 ,rule_value="services/validations/libraries_editable"
1758 ,rule_active=1
1759 ,tx_to='31DEC5999:23:59:59'dt;
1760insert into &lib..MPE_VALIDATIONS set
1761 tx_from=0
1762 ,base_lib="&lib"
1763 ,base_ds="MPE_EXCEL_CONFIG"
1764 ,base_col="XL_TABLE"
1765 ,rule_type='SOFTSELECT_HOOK'
1766 ,rule_value="services/validations/tables_editable"
1767 ,rule_active=1
1768 ,tx_to='31DEC5999:23:59:59'dt;
1769insert into &lib..MPE_VALIDATIONS set
1770 tx_from=0
1771 ,base_lib="&lib"
1772 ,base_ds="MPE_EXCEL_CONFIG"
1773 ,base_col="XL_COLUMN"
1774 ,rule_type='SOFTSELECT_HOOK'
1775 ,rule_value="services/validations/columns_in_libds"
1776 ,rule_active=1
1777 ,tx_to='31DEC5999:23:59:59'dt;
1778insert into &lib..MPE_VALIDATIONS set
1779 tx_from=0
1780 ,base_lib="&lib"
1781 ,base_ds="MPE_TABLES"
1782 ,base_col="LIBREF"
1783 ,rule_type='SOFTSELECT_HOOK'
1784 ,rule_value="services/validations/libraries_all"
1785 ,rule_active=1
1786 ,tx_to='31DEC5999:23:59:59'dt;
1787insert into &lib..MPE_VALIDATIONS set
1788 tx_from=0
1789 ,base_lib="&lib"
1790 ,base_ds="MPE_TABLES"
1791 ,base_col="DSN"
1792 ,rule_type='SOFTSELECT_HOOK'
1793 ,rule_value="services/validations/mpe_tables.dsn"
1794 ,rule_active=1
1795 ,tx_to='31DEC5999:23:59:59'dt;
1796insert into &lib..MPE_VALIDATIONS set
1797 tx_from=0
1798 ,base_lib="&lib"
1799 ,base_ds="MPE_TABLES"
1800 ,base_col="VAR_TXFROM"
1801 ,rule_type='SOFTSELECT_HOOK'
1802 ,rule_value="services/validations/columns_in_libds"
1803 ,rule_active=1
1804 ,tx_to='31DEC5999:23:59:59'dt;
1805insert into &lib..MPE_VALIDATIONS set
1806 tx_from=0
1807 ,base_lib="&lib"
1808 ,base_ds="MPE_TABLES"
1809 ,base_col="VAR_TXTO"
1810 ,rule_type='SOFTSELECT_HOOK'
1811 ,rule_value="services/validations/columns_in_libds"
1812 ,rule_active=1
1813 ,tx_to='31DEC5999:23:59:59'dt;
1814insert into &lib..MPE_VALIDATIONS set
1815 tx_from=0
1816 ,base_lib="&lib"
1817 ,base_ds="MPE_TABLES"
1818 ,base_col="VAR_BUSFROM"
1819 ,rule_type='SOFTSELECT_HOOK'
1820 ,rule_value="services/validations/columns_in_libds"
1821 ,rule_active=1
1822 ,tx_to='31DEC5999:23:59:59'dt;
1823insert into &lib..MPE_VALIDATIONS set
1824 tx_from=0
1825 ,base_lib="&lib"
1826 ,base_ds="MPE_TABLES"
1827 ,base_col="VAR_BUSTO"
1828 ,rule_type='SOFTSELECT_HOOK'
1829 ,rule_value="services/validations/columns_in_libds"
1830 ,rule_active=1
1831 ,tx_to='31DEC5999:23:59:59'dt;
1832insert into &lib..MPE_VALIDATIONS set
1833 tx_from=0
1834 ,base_lib="&lib"
1835 ,base_ds="MPE_TABLES"
1836 ,base_col="VAR_PROCESSED"
1837 ,rule_type='SOFTSELECT_HOOK'
1838 ,rule_value="services/validations/columns_in_libds"
1839 ,rule_active=1
1840 ,tx_to='31DEC5999:23:59:59'dt;
1841insert into &lib..MPE_VALIDATIONS set
1842 tx_from=0
1843 ,base_lib="&lib"
1844 ,base_ds="MPE_SELECTBOX"
1845 ,base_col="SELECT_LIB"
1846 ,rule_type='SOFTSELECT_HOOK'
1847 ,rule_value="services/validations/libraries_editable"
1848 ,rule_active=1
1849 ,tx_to='31DEC5999:23:59:59'dt;
1850insert into &lib..MPE_VALIDATIONS set
1851 tx_from=0
1852 ,base_lib="&lib"
1853 ,base_ds="MPE_SELECTBOX"
1854 ,base_col="SELECT_DS"
1855 ,rule_type='SOFTSELECT_HOOK'
1856 ,rule_value="services/validations/tables_editable"
1857 ,rule_active=1
1858 ,tx_to='31DEC5999:23:59:59'dt;
1859insert into &lib..MPE_VALIDATIONS set
1860 tx_from=0
1861 ,base_lib="&lib"
1862 ,base_ds="MPE_SELECTBOX"
1863 ,base_col="BASE_COLUMN"
1864 ,rule_type='SOFTSELECT_HOOK'
1865 ,rule_value="services/validations/columns_in_libds"
1866 ,rule_active=1
1867 ,tx_to='31DEC5999:23:59:59'dt;
1868insert into &lib..MPE_VALIDATIONS set
1869 tx_from=0
1870 ,base_lib="&lib"
1871 ,base_ds="MPE_ROW_LEVEL_SECURITY"
1872 ,base_col="RLS_GROUP"
1873 ,rule_type='SOFTSELECT_HOOK'
1874 ,rule_value="services/validations/sas_groups"
1875 ,rule_active=1
1876 ,tx_to='31DEC5999:23:59:59'dt;
1877insert into &lib..MPE_VALIDATIONS set
1878 tx_from=0
1879 ,base_lib="&lib"
1880 ,base_ds="MPE_ROW_LEVEL_SECURITY"
1881 ,base_col="RLS_LIBREF"
1882 ,rule_type='SOFTSELECT_HOOK'
1883 ,rule_value="services/validations/libraries_all"
1884 ,rule_active=1
1885 ,tx_to='31DEC5999:23:59:59'dt;
1886insert into &lib..MPE_VALIDATIONS set
1887 tx_from=0
1888 ,base_lib="&lib"
1889 ,base_ds="MPE_ROW_LEVEL_SECURITY"
1890 ,base_col="RLS_TABLE"
1891 ,rule_type='SOFTSELECT_HOOK'
1892 ,rule_value="services/validations/tables_all"
1893 ,rule_active=1
1894 ,tx_to='31DEC5999:23:59:59'dt;
1895insert into &lib..MPE_VALIDATIONS set
1896 tx_from=0
1897 ,base_lib="&lib"
1898 ,base_ds="MPE_ROW_LEVEL_SECURITY"
1899 ,base_col="RLS_SUBGROUP_ID"
1900 ,rule_type='MINVAL'
1901 ,rule_value='0'
1902 ,rule_active=1
1903 ,tx_to='31DEC5999:23:59:59'dt;
1904insert into &lib..MPE_VALIDATIONS set
1905 tx_from=0
1906 ,base_lib="&lib"
1907 ,base_ds="MPE_ROW_LEVEL_SECURITY"
1908 ,base_col="RLS_SUBGROUP_ID"
1909 ,rule_type='NOTNULL'
1910 ,rule_value='0'
1911 ,rule_active=1
1912 ,tx_to='31DEC5999:23:59:59'dt;
1913insert into &lib..MPE_VALIDATIONS set
1914 tx_from=0
1915 ,base_lib="&lib"
1916 ,base_ds="MPE_ROW_LEVEL_SECURITY"
1917 ,base_col="RLS_VARIABLE_NM"
1918 ,rule_type='SOFTSELECT_HOOK'
1919 ,rule_value="services/validations/columns_in_libds"
1920 ,rule_active=1
1921 ,tx_to='31DEC5999:23:59:59'dt;
1922/* test softselect on numeric var (should be ordered numerically) */
1923insert into &lib..MPE_VALIDATIONS set
1924 tx_from=0
1925 ,base_lib="&lib"
1926 ,base_ds="MPE_X_TEST"
1927 ,base_col="SOME_BESTNUM"
1928 ,rule_type='SOFTSELECT'
1929 ,rule_value="&lib..MPE_X_TEST.SOME_BESTNUM"
1930 ,rule_active=1
1931 ,tx_to='31DEC5999:23:59:59'dt;
1932insert into &lib..MPE_VALIDATIONS set
1933 tx_from=0
1934 ,base_lib="&lib"
1935 ,base_ds="MPE_X_TEST"
1936 ,base_col="SOME_NUM"
1937 ,rule_type='HARDSELECT_HOOK'
1938 ,rule_value="services/validations/mpe_x_test.some_num"
1939 ,rule_active=1
1940 ,tx_to='31DEC5999:23:59:59'dt;
1941insert into &lib..MPE_VALIDATIONS set
1942 tx_from=0
1943 ,base_lib="&lib"
1944 ,base_ds="MPE_EXCEL_CONFIG"
1945 ,base_col="XL_ACTIVE"
1946 ,rule_type='MINVAL'
1947 ,rule_value='0'
1948 ,rule_active=1
1949 ,tx_to='31DEC5999:23:59:59'dt;
1950insert into &lib..MPE_VALIDATIONS set
1951 tx_from=0
1952 ,base_lib="&lib"
1953 ,base_ds="MPE_EXCEL_CONFIG"
1954 ,base_col="XL_ACTIVE"
1955 ,rule_type='MAXVAL'
1956 ,rule_value='1'
1957 ,rule_active=1
1958 ,tx_to='31DEC5999:23:59:59'dt;
1959insert into &lib..MPE_VALIDATIONS set
1960 tx_from=0
1961 ,base_lib="&lib"
1962 ,base_ds="MPE_XLMAP_INFO"
1963 ,base_col="XLMAP_ID"
1964 ,rule_type='SOFTSELECT'
1965 ,rule_value="&lib..MPE_XLMAP_RULES.XLMAP_ID"
1966 ,rule_active=1
1967 ,tx_to='31DEC5999:23:59:59'dt;
1968
1969
1970/**
1971 * MPE_X_TEST
1972 */
1973 insert into &lib..mpe_x_test
1974 set primary_key_field=0
1975 ,some_char='this is dummy data'
1976 ,some_dropdown='Option 1'
1977 ,some_num=42
1978 ,some_date=42
1979 ,some_datetime=42
1980 ,some_time=42
1981 ,some_shortnum=3
1982 ,some_bestnum=44;
1983 insert into &lib..mpe_x_test
1984 set primary_key_field=1
1985 ,some_char='more dummy data'
1986 ,some_dropdown='Option 2'
1987 ,some_num=42
1988 ,some_date=42
1989 ,some_datetime=42
1990 ,some_time=422
1991 ,some_shortnum=3
1992 ,some_bestnum=44;
1993 insert into &lib..mpe_x_test
1994 set primary_key_field=2
1995 ,some_char='even more dummy data'
1996 ,some_dropdown='Option 3'
1997 ,some_num=42
1998 ,some_date=42
1999 ,some_datetime=42
2000 ,some_time=142
2001 ,some_shortnum=3
2002 ,some_bestnum=44;
2003 insert into &lib..mpe_x_test
2004 set primary_key_field=3
2005 ,some_char=repeat('It was a dark and stormy night. The wind was blowing'
2006 !!' a gale! The captain said to his mate - mate, tell us a tale. And'
2007 !!' this, is the tale he told: ',3)
2008 ,some_dropdown='Option 2'
2009 ,some_num=1613.001
2010 ,some_date=423
2011 ,some_datetime=423
2012 ,some_time=44
2013 ,some_shortnum=3
2014 ,some_bestnum=44;
2015 insert into &lib..mpe_x_test
2016 set primary_key_field=4
2017 ,some_char='if you can fill the unforgiving minute'
2018 ,some_dropdown='Option 1'
2019 ,some_num=1613.001123456
2020 ,some_date=4231
2021 ,some_datetime=423123123
2022 ,some_time=412
2023 ,some_shortnum=3
2024 ,some_bestnum=44;
2025%do x=10 %to 500;
2026 insert into &lib..mpe_x_test
2027 set primary_key_field=10&x
2028 ,some_char="&x bottles of beer on the wall"
2029 ,some_dropdown='Option 1'
2030 ,some_num=ranuni(0)
2031 ,some_date=round(ranuni(0)*1000,1)
2032 ,some_datetime=round(ranuni(0)*50000,1)
2033 ,some_time=round(ranuni(0)*100,1)
2034 ,some_shortnum=round(ranuni(0)*100,1)
2035 ,some_bestnum=round(ranuni(0)*100,1);
2036%end;
2037
2038
2039/* https://support.sas.com/resources/papers/proceedings/proceedings/sugi27/p056-27.pdf */
2040proc format library=&lib..mpe_x_catalog;
2041 value otdate
2042 .Z = 'Some Zs'
2043 .N = 'Some 9s'
2044 other = [date9.]
2045 ;
2046 invalue disc
2047 'ABC' = 0.20
2048 'DEF' = 0.25
2049 'XYZ' = 0.00
2050 other = 0.00
2051 ;
2052 invalue indate
2053 '00000000' = .Z
2054 '99999999' = .N
2055 other = [yymmdd8.]
2056 ;
2057 value age(multilabel)
2058 20 - 29 = '20 - 29'
2059 30 - 39 = '30 - 39'
2060 40 - 49 = '40 - 49'
2061 50 - 59 = '50 - 59'
2062 60 - high = '60 +++'
2063 20 - 35 = '20 - 35'
2064 36 - 55 = '36 - 55'
2065 55 - high = '55 +++'
2066 ;
2067/* https://libguides.library.kent.edu/SAS/UserDefinedFormats */
2068 VALUE $GENDERLABEL
2069 "M" = "Male"
2070 "F" = "Female"
2071 ;
2072 VALUE LIKERT_SEVEN
2073 1 = "Strongly Disagree"
2074 2 = "Disagree"
2075 3 = "Slightly Disagree"
2076 4 = "Neither Agree nor Disagree"
2077 5 = "Slightly Agree"
2078 6 = "Agree"
2079 7 = "Strongly Agree"
2080 ;
2081 VALUE LIKERT7_ELEVEN
2082 1,2,3 = "Disagree"
2083 4 = "Neither Agree nor Disagree"
2084 5,6,7 = "Agree"
2085 ;
2086 VALUE LIKERT7_SISTERS
2087 1-3 = "Disagree"
2088 4 = "Neither Agree nor Disagree"
2089 5-7 = "Agree"
2090 ;
2091 VALUE INCOME
2092 LOW -< 20000 = "Low"
2093 20000 -< 60000 = "Middle"
2094 60000 - HIGH = "High"
2095 ;
2096 VALUE RACE
2097 1 = "White"
2098 2 = "Black"
2099 OTHER = "Other"
2100 ;
2101 VALUE GENDERCODE
2102 0 = 'Male'
2103 1 = 'Female';
2104 VALUE ATHLETECODE
2105 0 = 'Non-athlete'
2106 1 = 'Athlete';
2107 VALUE SMOKINGCODE
2108 0 = 'Nonsmoker'
2109 1 = 'Past smoker'
2110 2 = 'Current smoker';
2111/* https://documentation.sas.com/doc/en/pgmsascdc/v_017/proc/p1upn25lbfo6mkn1wncu4dyh9q91.htm */
2112 value $state
2113 'Delaware'='DE'
2114 'Florida'='FL'
2115 'Ohio'='OH';
2116 value MYfmt
2117 /* Format dates prior to 31DEC2011 using only a year. */
2118 low-'31DEC2011'd=[year4.]
2119
2120 /* Format 2012 dates using the month and year. */
2121 '01jan2012'd-'31DEC12'd=[monyy7.]
2122
2123 /* Format dates 01JAN2013 and beyond using the day, month, and year. */
2124 '01JAN2013'd-high=[date9.]
2125
2126 /* Catch missing values. */
2127 other='n/a';
2128 value newfmt .='N/A' other=[12.1];
2129/* https://www.lexjansen.com/nesug/nesug08/cc/cc14.pdf */
2130 value $genderml (multilabel)
2131 '1'='Male'
2132 '2'='Female'
2133 '1','2',' '='Total people';
2134 value agemla (multilabel)
2135 1-4='Preschool'
2136 1-18='Children'
2137 19-120='Adults';
2138 value agemlb (multilabel)
2139 19-120='Adults'
2140 1-18='Children'
2141 1-4='Preschool';
2142 value agemlc (multilabel notsorted)
2143 19-120='Adults'
2144 1-18='Children'
2145 1-4='Preschool';
2146%mend mpe_makedata;