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