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="NOTNULL"
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="HARDSELECT"
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="HARDSELECT_HOOK"
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_VALIDATIONS"
870 ,base_column="RULE_TYPE"
871 ,selectbox_value="SOFTSELECT"
872 ,selectbox_order=7
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_VALIDATIONS"
879 ,base_column="RULE_TYPE"
880 ,selectbox_value="SOFTSELECT_HOOK"
881 ,selectbox_order=8
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_VALIDATIONS"
888 ,base_column="RULE_ACTIVE"
889 ,selectbox_value="1"
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_VALIDATIONS"
897 ,base_column="RULE_ACTIVE"
898 ,selectbox_value="0"
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_SECURITY"
906 ,base_column="DSN"
907 ,selectbox_value="*ALL*"
908 ,selectbox_order=1
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="COLUMN"
917 ,selectbox_order=1
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="TABLE"
926 ,selectbox_order=2
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_DATADICTIONARY"
933 ,base_column="DD_TYPE"
934 ,selectbox_value="LIBRARY"
935 ,selectbox_order=3
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_DATADICTIONARY"
942 ,base_column="DD_TYPE"
943 ,selectbox_value="CATALOG"
944 ,selectbox_order=3
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_DATADICTIONARY"
951 ,base_column="DD_TYPE"
952 ,selectbox_value="FORMAT"
953 ,selectbox_order=3
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_SECURITY"
960 ,base_column="LIBREF"
961 ,selectbox_value='*ALL*'
962 ,selectbox_order=1
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_SECURITY"
969 ,base_column="ACCESS_LEVEL"
970 ,selectbox_value='AUDIT'
971 ,selectbox_order=4
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="ALL"
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="EDIT"
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_SCOPE"
997 ,selectbox_value="VIEW"
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="AND"
1007 ,selectbox_order=1
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_GROUP_LOGIC"
1015 ,selectbox_value="OR"
1016 ,selectbox_order=2
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="AND"
1025 ,selectbox_order=1
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_SUBGROUP_LOGIC"
1033 ,selectbox_value="OR"
1034 ,selectbox_order=2
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=0
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=">="
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="BETWEEN"
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="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="NOT IN"
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="NE"
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_ROW_LEVEL_SECURITY"
1122 ,base_column="RLS_OPERATOR_NM"
1123 ,selectbox_value="CONTAINS"
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_EXCEL_CONFIG"
1131 ,base_column="XL_RULE"
1132 ,selectbox_value="FORMULA"
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="1"
1142 ,selectbox_order=1
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_ROW_LEVEL_SECURITY"
1149 ,base_column="RLS_ACTIVE"
1150 ,selectbox_value="0"
1151 ,selectbox_order=2
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="1"
1160 ,selectbox_order=1
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_ACTIVE"
1168 ,selectbox_value="0"
1169 ,selectbox_order=2
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="EDIT"
1178 ,selectbox_order=1
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="VIEW"
1187 ,selectbox_order=2
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_SCOPE"
1195 ,selectbox_value="ALL"
1196 ,selectbox_order=3
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="0"
1205 ,selectbox_order=1
1206 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1207 insert into &lib..mpe_selectbox set
1208 selectbox_rk=%mf_increment(rk)
1209 ,ver_from_dttm=0
1210 ,select_lib="&lib"
1211 ,select_ds="MPE_COLUMN_LEVEL_SECURITY"
1212 ,base_column="CLS_HIDE"
1213 ,selectbox_value="1"
1214 ,selectbox_order=2
1215 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1216
1217/**
1218 * MPE_TABLES
1219 */
1220 insert into &lib..mpe_tables
1221 set tx_from=0
1222 ,tx_to='31DEC5999:23:59:59'dt
1223 ,libref="&lib"
1224 ,dsn='MPE_COLUMN_LEVEL_SECURITY'
1225 ,num_of_approvals_required=1
1226 ,loadtype='TXTEMPORAL'
1227 ,var_txfrom='TX_FROM'
1228 ,var_txto='TX_TO'
1229 ,buskey='CLS_SCOPE CLS_GROUP CLS_LIBREF CLS_TABLE CLS_VARIABLE_NM'
1230 ,notes='Docs: https://docs.datacontroller.io/column-level-security'
1231 ,post_edit_hook='services/hooks/mpe_column_level_security_postedit'
1232 ;
1233 insert into &lib..mpe_tables
1234 set tx_from=0
1235 ,tx_to='31DEC5999:23:59:59'dt
1236 ,libref="&lib"
1237 ,dsn='MPE_XLMAP_INFO'
1238 ,num_of_approvals_required=1
1239 ,loadtype='TXTEMPORAL'
1240 ,var_txfrom='TX_FROM'
1241 ,var_txto='TX_TO'
1242 ,buskey='XLMAP_ID'
1243 ,notes='Docs: https://docs.datacontroller.io/complex-excel-uploads'
1244 ,post_edit_hook='services/hooks/mpe_xlmap_info_postedit'
1245 ;
1246 insert into &lib..mpe_tables
1247 set tx_from=0
1248 ,tx_to='31DEC5999:23:59:59'dt
1249 ,libref="&lib"
1250 ,dsn='MPE_XLMAP_RULES'
1251 ,num_of_approvals_required=1
1252 ,loadtype='TXTEMPORAL'
1253 ,var_txfrom='TX_FROM'
1254 ,var_txto='TX_TO'
1255 ,buskey='XLMAP_ID XLMAP_RANGE_ID'
1256 ,notes='Docs: https://docs.datacontroller.io/complex-excel-uploads'
1257 ,post_edit_hook='services/hooks/mpe_xlmap_rules_postedit'
1258 ;
1259 insert into &lib..mpe_tables
1260 set tx_from=0
1261 ,tx_to='31DEC5999:23:59:59'dt
1262 ,libref="&lib"
1263 ,dsn='MPE_XLMAP_DATA'
1264 ,num_of_approvals_required=1
1265 ,loadtype='UPDATE'
1266 ,buskey='LOAD_REF XLMAP_ID XLMAP_RANGE_ID ROW_NO COL_NO'
1267 ,notes='Docs: https://docs.datacontroller.io/complex-excel-uploads'
1268 ;
1269 insert into &lib..mpe_tables
1270 set tx_from=0
1271 ,tx_to='31DEC5999:23:59:59'dt
1272 ,libref="&lib"
1273 ,dsn='MPE_LOCKANYTABLE'
1274 ,num_of_approvals_required=1
1275 ,loadtype='UPDATE'
1276 ,buskey='LOCK_LIB LOCK_DS'
1277 ,notes='This table may be edited when a process failed and left a lock'
1278 ;
1279 insert into &lib..mpe_tables
1280 set tx_from=0
1281 ,tx_to='31DEC5999:23:59:59'dt
1282 ,libref="&lib"
1283 ,dsn='MPE_TABLES'
1284 ,num_of_approvals_required=1
1285 ,loadtype='TXTEMPORAL'
1286 ,buskey='LIBREF DSN'
1287 ,var_txfrom='TX_FROM'
1288 ,var_txto='TX_TO'
1289 ,notes='This entry allows the MP Editor to edit itself!'
1290 ,post_edit_hook='services/hooks/mpe_tables_postedit'
1291 ;
1292 insert into &lib..mpe_tables
1293 set tx_from=0
1294 ,tx_to='31DEC5999:23:59:59'dt
1295 ,libref="&lib"
1296 ,dsn='MPE_SECURITY'
1297 ,num_of_approvals_required=1
1298 ,loadtype='TXTEMPORAL'
1299 ,buskey='LIBREF DSN ACCESS_LEVEL SAS_GROUP'
1300 ,var_txfrom='TX_FROM'
1301 ,var_txto='TX_TO'
1302 ,notes='Determines which groups can view/edit/approve which tables'
1303 ,post_edit_hook='services/hooks/mpe_security_postedit'
1304 ;
1305 insert into &lib..mpe_tables
1306 set tx_from=0
1307 ,tx_to='31DEC5999:23:59:59'dt
1308 ,libref="&lib"
1309 ,dsn='MPE_SELECTBOX'
1310 ,num_of_approvals_required=1
1311 ,loadtype='TXTEMPORAL'
1312 ,buskey='SELECTBOX_RK'
1313 ,var_txfrom='VER_FROM_DTTM'
1314 ,var_txto='VER_TO_DTTM'
1315 ,notes='Can configure dropdowns for the front end'
1316 ,rk_underlying='SELECT_LIB SELECT_DS BASE_COLUMN SELECTBOX_VALUE'
1317 ;
1318 insert into &lib..mpe_tables
1319 set tx_from=0
1320 ,tx_to='31DEC5999:23:59:59'dt
1321 ,libref="&lib"
1322 ,dsn='MPE_X_TEST'
1323 ,num_of_approvals_required=1
1324 ,loadtype='UPDATE'
1325 ,buskey='PRIMARY_KEY_FIELD'
1326 ,notes='Test table for controller'
1327 ;
1328 insert into &lib..mpe_tables
1329 set tx_from=0
1330 ,tx_to='31DEC5999:23:59:59'dt
1331 ,libref="&lib"
1332 ,dsn='MPE_EMAILS'
1333 ,num_of_approvals_required=1
1334 ,loadtype='TXTEMPORAL'
1335 ,buskey='USER_NAME'
1336 ,notes='Primary Emails Table (backup is metadata)'
1337 ,var_txfrom='TX_FROM'
1338 ,var_txto='TX_TO'
1339 ;
1340 insert into &lib..mpe_tables
1341 set tx_from=0
1342 ,tx_to='31DEC5999:23:59:59'dt
1343 ,libref="&lib"
1344 ,dsn='MPE_CONFIG'
1345 ,num_of_approvals_required=1
1346 ,loadtype='TXTEMPORAL'
1347 ,buskey='VAR_SCOPE VAR_NAME'
1348 ,notes='Configuration variables for Data Controller'
1349 ,var_txfrom='TX_FROM'
1350 ,var_txto='TX_TO'
1351 ;
1352 insert into &lib..mpe_tables
1353 set tx_from=0
1354 ,tx_to='31DEC5999:23:59:59'dt
1355 ,libref="&lib"
1356 ,dsn='MPE_ALERTS'
1357 ,num_of_approvals_required=1
1358 ,loadtype='TXTEMPORAL'
1359 ,buskey='ALERT_EVENT ALERT_LIB ALERT_DS ALERT_USER'
1360 ,notes='Configuration for alert email events'
1361 ,var_txfrom='TX_FROM'
1362 ,var_txto='TX_TO'
1363 ;
1364 insert into &lib..mpe_tables
1365 set tx_from=0
1366 ,tx_to='31DEC5999:23:59:59'dt
1367 ,libref="&lib"
1368 ,dsn='MPE_GROUPS'
1369 ,num_of_approvals_required=1
1370 ,loadtype='TXTEMPORAL'
1371 ,buskey='GROUP_NAME USER_NAME'
1372 ,notes='Configuration for additional groups within Data Controller'
1373 ,var_txfrom='TX_FROM'
1374 ,var_txto='TX_TO'
1375 ;
1376 insert into &lib..mpe_tables
1377 set tx_from=0
1378 ,tx_to='31DEC5999:23:59:59'dt
1379 ,libref="&lib"
1380 ,dsn='MPE_VALIDATIONS'
1381 ,num_of_approvals_required=1
1382 ,loadtype='TXTEMPORAL'
1383 ,buskey='BASE_LIB BASE_DS BASE_COL RULE_TYPE'
1384 ,notes='Configuration of data quality rules in Editor component'
1385 ,var_txfrom='TX_FROM'
1386 ,var_txto='TX_TO'
1387 ,post_edit_hook='services/hooks/mpe_validations_postedit'
1388 ;
1389 insert into &lib..mpe_tables
1390 set tx_from=0
1391 ,tx_to='31DEC5999:23:59:59'dt
1392 ,libref="&lib"
1393 ,dsn='MPE_DATADICTIONARY'
1394 ,num_of_approvals_required=1
1395 ,loadtype='TXTEMPORAL'
1396 ,buskey='DD_TYPE DD_SOURCE'
1397 ,notes='Configuration of data dictionary'
1398 ,var_txfrom='TX_FROM'
1399 ,var_txto='TX_TO'
1400 ;
1401 insert into &lib..mpe_tables
1402 set tx_from=0
1403 ,tx_to='31DEC5999:23:59:59'dt
1404 ,libref="&lib"
1405 ,dsn='MPE_EXCEL_CONFIG'
1406 ,num_of_approvals_required=1
1407 ,loadtype='TXTEMPORAL'
1408 ,buskey='XL_LIBREF XL_TABLE XL_COLUMN'
1409 ,notes='Configuration of the excel import rules'
1410 ,var_txfrom='TX_FROM'
1411 ,var_txto='TX_TO'
1412 ;
1413 insert into &lib..mpe_tables
1414 set tx_from=0
1415 ,tx_to='31DEC5999:23:59:59'dt
1416 ,libref="&lib"
1417 ,dsn='MPE_ROW_LEVEL_SECURITY'
1418 ,num_of_approvals_required=1
1419 ,loadtype='TXTEMPORAL'
1420 ,buskey='RLS_RK'
1421 ,notes='Configuration of Row Level Security'
1422 ,var_txfrom='TX_FROM'
1423 ,var_txto='TX_TO'
1424 ,rk_underlying='RLS_SCOPE RLS_GROUP RLS_LIBREF RLS_TABLE RLS_GROUP_LOGIC '
1425 !!'RLS_SUBGROUP_LOGIC RLS_SUBGROUP_ID RLS_VARIABLE_NM RLS_OPERATOR_NM '
1426 !!'RLS_RAW_VALUE '
1427 ,post_edit_hook='services/hooks/mpe_row_level_security_postedit'
1428 ;
1429 insert into &lib..mpe_tables
1430 set tx_from=0
1431 ,tx_to='31DEC5999:23:59:59'dt
1432 ,libref="&lib"
1433 ,dsn='MPE_X_CATALOG-FC'
1434 ,num_of_approvals_required=1
1435 ,loadtype='FORMAT_CAT'
1436 ,buskey='TYPE FMTNAME FMTROW'
1437 ,notes='Sample Format Catalog'
1438 ;
1439
1440/* mpe_validations */
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_SCOPE"
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='CASE'
1456 ,rule_value='UPCASE'
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_LIBREF"
1464 ,rule_type='SOFTSELECT_HOOK'
1465 ,rule_value="services/validations/libraries_all"
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='CASE'
1474 ,rule_value='UPCASE'
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_TABLE"
1482 ,rule_type='SOFTSELECT_HOOK'
1483 ,rule_value="services/validations/tables_all"
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='CASE'
1492 ,rule_value='UPCASE'
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_VARIABLE_NM"
1500 ,rule_type='SOFTSELECT_HOOK'
1501 ,rule_value="services/validations/columns_in_libds"
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_ACTIVE"
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_HIDE"
1518 ,rule_type='MAXVAL'
1519 ,rule_value='1'
1520 ,rule_active=1
1521 ,tx_to='31DEC5999:23:59:59'dt;
1522insert into &lib..MPE_VALIDATIONS set
1523 tx_from=0
1524 ,base_lib="&lib"
1525 ,base_ds="MPE_COLUMN_LEVEL_SECURITY"
1526 ,base_col="CLS_GROUP"
1527 ,rule_type='SOFTSELECT_HOOK'
1528 ,rule_value="services/validations/sas_groups"
1529 ,rule_active=1
1530 ,tx_to='31DEC5999:23:59:59'dt;
1531
1532insert into &lib..MPE_VALIDATIONS set
1533 tx_from=0
1534 ,base_lib="&lib"
1535 ,base_ds="MPE_ALERTS"
1536 ,base_col="ALERT_LIB"
1537 ,rule_type='HARDSELECT_HOOK'
1538 ,rule_value="services/validations/mpe_alerts.alert_lib"
1539 ,rule_active=1
1540 ,tx_to='31DEC5999:23:59:59'dt;
1541
1542insert into &lib..MPE_VALIDATIONS set
1543 tx_from=0
1544 ,base_lib="&lib"
1545 ,base_ds="MPE_XLMAP_INFO"
1546 ,base_col="XLMAP_ID"
1547 ,rule_type='CASE'
1548 ,rule_value='UPCASE'
1549 ,rule_active=1
1550 ,tx_to='31DEC5999:23:59:59'dt;
1551
1552insert into &lib..MPE_VALIDATIONS set
1553 tx_from=0
1554 ,base_lib="&lib"
1555 ,base_ds="MPE_XLMAP_RULES"
1556 ,base_col="XLMAP_ID"
1557 ,rule_type='CASE'
1558 ,rule_value='UPCASE'
1559 ,rule_active=1
1560 ,tx_to='31DEC5999:23:59:59'dt;
1561
1562insert into &lib..MPE_VALIDATIONS set
1563 tx_from=0
1564 ,base_lib="&lib"
1565 ,base_ds="MPE_TABLES"
1566 ,base_col="LIBREF"
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="DSN"
1576 ,rule_type='CASE'
1577 ,rule_value='UPCASE'
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="LIBREF"
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="DSN"
1594 ,rule_type='NOTNULL'
1595 ,rule_value=' '
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="NUM_OF_APPROVALS_REQUIRED"
1603 ,rule_type='MINVAL'
1604 ,rule_value='1'
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='CASE'
1613 ,rule_value='UPCASE'
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="BUSKEY"
1621 ,rule_type='NOTNULL'
1622 ,rule_value=" "
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_TXFROM"
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_TXTO"
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_BUSFROM"
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_TABLES"
1656 ,base_col="VAR_BUSTO"
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_SECURITY"
1665 ,base_col="LIBREF"
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_TABLES"
1674 ,base_col="VAR_PROCESSED"
1675 ,rule_type='CASE'
1676 ,rule_value='UPCASE'
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="LIBREF"
1684 ,rule_type='HARDSELECT'
1685 ,rule_value="&lib..MPE_TABLES.LIBREF"
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='CASE'
1694 ,rule_value="UPCASE"
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="DSN"
1702 ,rule_type='SOFTSELECT'
1703 ,rule_value="&lib..MPE_TABLES.DSN"
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_SECURITY"
1710 ,base_col="SAS_GROUP"
1711 ,rule_type='SOFTSELECT_HOOK'
1712 ,rule_value="services/validations/sas_groups"
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_LIB"
1720 ,rule_type='SOFTSELECT_HOOK'
1721 ,rule_value="services/validations/libraries_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_DS"
1729 ,rule_type='SOFTSELECT_HOOK'
1730 ,rule_value="services/validations/tables_editable"
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="BASE_COL"
1738 ,rule_type='SOFTSELECT_HOOK'
1739 ,rule_value="services/validations/columns_in_libds"
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='MINVAL'
1748 ,rule_value="0"
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_VALIDATIONS"
1755 ,base_col="RULE_ACTIVE"
1756 ,rule_type='MAXVAL'
1757 ,rule_value="1"
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_LIBREF"
1765 ,rule_type='SOFTSELECT_HOOK'
1766 ,rule_value="services/validations/libraries_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_TABLE"
1774 ,rule_type='SOFTSELECT_HOOK'
1775 ,rule_value="services/validations/tables_editable"
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_EXCEL_CONFIG"
1782 ,base_col="XL_COLUMN"
1783 ,rule_type='SOFTSELECT_HOOK'
1784 ,rule_value="services/validations/columns_in_libds"
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="LIBREF"
1792 ,rule_type='SOFTSELECT_HOOK'
1793 ,rule_value="services/validations/libraries_all"
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="DSN"
1801 ,rule_type='SOFTSELECT_HOOK'
1802 ,rule_value="services/validations/mpe_tables.dsn"
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_TXFROM"
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_TXTO"
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_BUSFROM"
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_BUSTO"
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_TABLES"
1845 ,base_col="VAR_PROCESSED"
1846 ,rule_type='SOFTSELECT_HOOK'
1847 ,rule_value="services/validations/columns_in_libds"
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_LIB"
1855 ,rule_type='SOFTSELECT_HOOK'
1856 ,rule_value="services/validations/libraries_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="SELECT_DS"
1864 ,rule_type='SOFTSELECT_HOOK'
1865 ,rule_value="services/validations/tables_editable"
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_SELECTBOX"
1872 ,base_col="BASE_COLUMN"
1873 ,rule_type='SOFTSELECT_HOOK'
1874 ,rule_value="services/validations/columns_in_libds"
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_GROUP"
1882 ,rule_type='SOFTSELECT_HOOK'
1883 ,rule_value="services/validations/sas_groups"
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_LIBREF"
1891 ,rule_type='SOFTSELECT_HOOK'
1892 ,rule_value="services/validations/libraries_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_TABLE"
1900 ,rule_type='SOFTSELECT_HOOK'
1901 ,rule_value="services/validations/tables_all"
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='MINVAL'
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_SUBGROUP_ID"
1918 ,rule_type='NOTNULL'
1919 ,rule_value='0'
1920 ,rule_active=1
1921 ,tx_to='31DEC5999:23:59:59'dt;
1922insert into &lib..MPE_VALIDATIONS set
1923 tx_from=0
1924 ,base_lib="&lib"
1925 ,base_ds="MPE_ROW_LEVEL_SECURITY"
1926 ,base_col="RLS_VARIABLE_NM"
1927 ,rule_type='SOFTSELECT_HOOK'
1928 ,rule_value="services/validations/columns_in_libds"
1929 ,rule_active=1
1930 ,tx_to='31DEC5999:23:59:59'dt;
1931/* test softselect on numeric var (should be ordered numerically) */
1932insert into &lib..MPE_VALIDATIONS set
1933 tx_from=0
1934 ,base_lib="&lib"
1935 ,base_ds="MPE_X_TEST"
1936 ,base_col="SOME_BESTNUM"
1937 ,rule_type='SOFTSELECT'
1938 ,rule_value="&lib..MPE_X_TEST.SOME_BESTNUM"
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_X_TEST"
1945 ,base_col="SOME_NUM"
1946 ,rule_type='HARDSELECT_HOOK'
1947 ,rule_value="services/validations/mpe_x_test.some_num"
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='MINVAL'
1956 ,rule_value='0'
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_EXCEL_CONFIG"
1963 ,base_col="XL_ACTIVE"
1964 ,rule_type='MAXVAL'
1965 ,rule_value='1'
1966 ,rule_active=1
1967 ,tx_to='31DEC5999:23:59:59'dt;
1968insert into &lib..MPE_VALIDATIONS set
1969 tx_from=0
1970 ,base_lib="&lib"
1971 ,base_ds="MPE_XLMAP_INFO"
1972 ,base_col="XLMAP_ID"
1973 ,rule_type='SOFTSELECT'
1974 ,rule_value="&lib..MPE_XLMAP_RULES.XLMAP_ID"
1975 ,rule_active=1
1976 ,tx_to='31DEC5999:23:59:59'dt;
1977
1978
1979/**
1980 * MPE_X_TEST
1981 */
1982 insert into &lib..mpe_x_test
1983 set primary_key_field=0
1984 ,some_char='this is dummy data'
1985 ,some_dropdown='Option 1'
1986 ,some_num=42
1987 ,some_date=42
1988 ,some_datetime=42
1989 ,some_time=42
1990 ,some_shortnum=3
1991 ,some_bestnum=44;
1992 insert into &lib..mpe_x_test
1993 set primary_key_field=1
1994 ,some_char='more dummy data'
1995 ,some_dropdown='Option 2'
1996 ,some_num=42
1997 ,some_date=42
1998 ,some_datetime=42
1999 ,some_time=422
2000 ,some_shortnum=3
2001 ,some_bestnum=44;
2002 insert into &lib..mpe_x_test
2003 set primary_key_field=2
2004 ,some_char='even more dummy data'
2005 ,some_dropdown='Option 3'
2006 ,some_num=42
2007 ,some_date=42
2008 ,some_datetime=42
2009 ,some_time=142
2010 ,some_shortnum=3
2011 ,some_bestnum=44;
2012 insert into &lib..mpe_x_test
2013 set primary_key_field=3
2014 ,some_char=repeat('It was a dark and stormy night. The wind was blowing'
2015 !!' a gale! The captain said to his mate - mate, tell us a tale. And'
2016 !!' this, is the tale he told: ',3)
2017 ,some_dropdown='Option 2'
2018 ,some_num=1613.001
2019 ,some_date=423
2020 ,some_datetime=423
2021 ,some_time=44
2022 ,some_shortnum=3
2023 ,some_bestnum=44;
2024 insert into &lib..mpe_x_test
2025 set primary_key_field=4
2026 ,some_char='if you can fill the unforgiving minute'
2027 ,some_dropdown='Option 1'
2028 ,some_num=1613.001123456
2029 ,some_date=4231
2030 ,some_datetime=423123123
2031 ,some_time=412
2032 ,some_shortnum=3
2033 ,some_bestnum=44;
2034%do x=10 %to 500;
2035 insert into &lib..mpe_x_test
2036 set primary_key_field=10&x
2037 ,some_char="&x bottles of beer on the wall"
2038 ,some_dropdown='Option 1'
2039 ,some_num=ranuni(0)
2040 ,some_date=round(ranuni(0)*1000,1)
2041 ,some_datetime=round(ranuni(0)*50000,1)
2042 ,some_time=round(ranuni(0)*100,1)
2043 ,some_shortnum=round(ranuni(0)*100,1)
2044 ,some_bestnum=round(ranuni(0)*100,1);
2045%end;
2046
2047
2048/* https://support.sas.com/resources/papers/proceedings/proceedings/sugi27/p056-27.pdf */
2049proc format library=&lib..mpe_x_catalog;
2050 value otdate
2051 .Z = 'Some Zs'
2052 .N = 'Some 9s'
2053 other = [date9.]
2054 ;
2055 invalue disc
2056 'ABC' = 0.20
2057 'DEF' = 0.25
2058 'XYZ' = 0.00
2059 other = 0.00
2060 ;
2061 invalue indate
2062 '00000000' = .Z
2063 '99999999' = .N
2064 other = [yymmdd8.]
2065 ;
2066 value age(multilabel)
2067 20 - 29 = '20 - 29'
2068 30 - 39 = '30 - 39'
2069 40 - 49 = '40 - 49'
2070 50 - 59 = '50 - 59'
2071 60 - high = '60 +++'
2072 20 - 35 = '20 - 35'
2073 36 - 55 = '36 - 55'
2074 55 - high = '55 +++'
2075 ;
2076/* https://libguides.library.kent.edu/SAS/UserDefinedFormats */
2077 VALUE $GENDERLABEL
2078 "M" = "Male"
2079 "F" = "Female"
2080 ;
2081 VALUE LIKERT_SEVEN
2082 1 = "Strongly Disagree"
2083 2 = "Disagree"
2084 3 = "Slightly Disagree"
2085 4 = "Neither Agree nor Disagree"
2086 5 = "Slightly Agree"
2087 6 = "Agree"
2088 7 = "Strongly Agree"
2089 ;
2090 VALUE LIKERT7_ELEVEN
2091 1,2,3 = "Disagree"
2092 4 = "Neither Agree nor Disagree"
2093 5,6,7 = "Agree"
2094 ;
2095 VALUE LIKERT7_SISTERS
2096 1-3 = "Disagree"
2097 4 = "Neither Agree nor Disagree"
2098 5-7 = "Agree"
2099 ;
2100 VALUE INCOME
2101 LOW -< 20000 = "Low"
2102 20000 -< 60000 = "Middle"
2103 60000 - HIGH = "High"
2104 ;
2105 VALUE RACE
2106 1 = "White"
2107 2 = "Black"
2108 OTHER = "Other"
2109 ;
2110 VALUE GENDERCODE
2111 0 = 'Male'
2112 1 = 'Female';
2113 VALUE ATHLETECODE
2114 0 = 'Non-athlete'
2115 1 = 'Athlete';
2116 VALUE SMOKINGCODE
2117 0 = 'Nonsmoker'
2118 1 = 'Past smoker'
2119 2 = 'Current smoker';
2120/* https://documentation.sas.com/doc/en/pgmsascdc/v_017/proc/p1upn25lbfo6mkn1wncu4dyh9q91.htm */
2121 value $state
2122 'Delaware'='DE'
2123 'Florida'='FL'
2124 'Ohio'='OH';
2125 value MYfmt
2126 /* Format dates prior to 31DEC2011 using only a year. */
2127 low-'31DEC2011'd=[year4.]
2128
2129 /* Format 2012 dates using the month and year. */
2130 '01jan2012'd-'31DEC12'd=[monyy7.]
2131
2132 /* Format dates 01JAN2013 and beyond using the day, month, and year. */
2133 '01JAN2013'd-high=[date9.]
2134
2135 /* Catch missing values. */
2136 other='n/a';
2137 value newfmt .='N/A' other=[12.1];
2138/* https://www.lexjansen.com/nesug/nesug08/cc/cc14.pdf */
2139 value $genderml (multilabel)
2140 '1'='Male'
2141 '2'='Female'
2142 '1','2',' '='Total people';
2143 value agemla (multilabel)
2144 1-4='Preschool'
2145 1-18='Children'
2146 19-120='Adults';
2147 value agemlb (multilabel)
2148 19-120='Adults'
2149 1-18='Children'
2150 1-4='Preschool';
2151 value agemlc (multilabel notsorted)
2152 19-120='Adults'
2153 1-18='Children'
2154 1-4='Preschool';
2155%mend mpe_makedata;