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="HARDSELECT"
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="HARDSELECT_HOOK"
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="HIDDEN"
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="MAXVAL"
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="MINVAL"
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="NOTNULL"
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="NUMBER_FORMAT"
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_TYPE"
889 ,selectbox_value="READONLY"
890 ,selectbox_order=9
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_TYPE"
898 ,selectbox_value="ROUND"
899 ,selectbox_order=10
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_VALIDATIONS"
906 ,base_column="RULE_TYPE"
907 ,selectbox_value="SOFTSELECT"
908 ,selectbox_order=11
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_VALIDATIONS"
915 ,base_column="RULE_TYPE"
916 ,selectbox_value="SOFTSELECT_HOOK"
917 ,selectbox_order=12
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_VALIDATIONS"
924 ,base_column="RULE_TYPE"
925 ,selectbox_value="HARDREGEX"
926 ,selectbox_order=13
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_VALIDATIONS"
933 ,base_column="RULE_TYPE"
934 ,selectbox_value="SOFTREGEX"
935 ,selectbox_order=14
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_VALIDATIONS"
942 ,base_column="RULE_ACTIVE"
943 ,selectbox_value="1"
944 ,selectbox_order=1
945 ,ver_to_dttm='31DEC5999:23:59:59'dt;
946 insert into &lib..mpe_selectbox set
947 selectbox_rk=%mf_increment(rk)
948 ,ver_from_dttm=0
949 ,select_lib="&lib"
950 ,select_ds="MPE_VALIDATIONS"
951 ,base_column="RULE_ACTIVE"
952 ,selectbox_value="0"
953 ,selectbox_order=2
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="DSN"
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_DATADICTIONARY"
969 ,base_column="DD_TYPE"
970 ,selectbox_value="COLUMN"
971 ,selectbox_order=1
972 ,ver_to_dttm='31DEC5999:23:59:59'dt;
973 insert into &lib..mpe_selectbox set
974 selectbox_rk=%mf_increment(rk)
975 ,ver_from_dttm=0
976 ,select_lib="&lib"
977 ,select_ds="MPE_DATADICTIONARY"
978 ,base_column="DD_TYPE"
979 ,selectbox_value="TABLE"
980 ,selectbox_order=2
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_DATADICTIONARY"
987 ,base_column="DD_TYPE"
988 ,selectbox_value="LIBRARY"
989 ,selectbox_order=3
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_DATADICTIONARY"
996 ,base_column="DD_TYPE"
997 ,selectbox_value="CATALOG"
998 ,selectbox_order=3
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_DATADICTIONARY"
1005 ,base_column="DD_TYPE"
1006 ,selectbox_value="FORMAT"
1007 ,selectbox_order=3
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_SECURITY"
1014 ,base_column="LIBREF"
1015 ,selectbox_value='*ALL*'
1016 ,selectbox_order=1
1017 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1018 insert into &lib..mpe_selectbox set
1019 selectbox_rk=%mf_increment(rk)
1020 ,ver_from_dttm=0
1021 ,select_lib="&lib"
1022 ,select_ds="MPE_SECURITY"
1023 ,base_column="ACCESS_LEVEL"
1024 ,selectbox_value='AUDIT'
1025 ,selectbox_order=4
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_SCOPE"
1033 ,selectbox_value="ALL"
1034 ,selectbox_order=1
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_SCOPE"
1042 ,selectbox_value="EDIT"
1043 ,selectbox_order=1
1044 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1045 insert into &lib..mpe_selectbox set
1046 selectbox_rk=%mf_increment(rk)
1047 ,ver_from_dttm=0
1048 ,select_lib="&lib"
1049 ,select_ds="MPE_ROW_LEVEL_SECURITY"
1050 ,base_column="RLS_SCOPE"
1051 ,selectbox_value="VIEW"
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_GROUP_LOGIC"
1060 ,selectbox_value="AND"
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_GROUP_LOGIC"
1069 ,selectbox_value="OR"
1070 ,selectbox_order=2
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_SUBGROUP_LOGIC"
1078 ,selectbox_value="AND"
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_SUBGROUP_LOGIC"
1087 ,selectbox_value="OR"
1088 ,selectbox_order=2
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="="
1097 ,selectbox_order=0
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=">"
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="<"
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="<="
1124 ,selectbox_order=1
1125 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1126 insert into &lib..mpe_selectbox set
1127 selectbox_rk=%mf_increment(rk)
1128 ,ver_from_dttm=0
1129 ,select_lib="&lib"
1130 ,select_ds="MPE_ROW_LEVEL_SECURITY"
1131 ,base_column="RLS_OPERATOR_NM"
1132 ,selectbox_value=">="
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_OPERATOR_NM"
1141 ,selectbox_value="BETWEEN"
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_OPERATOR_NM"
1150 ,selectbox_value="IN"
1151 ,selectbox_order=1
1152 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1153 insert into &lib..mpe_selectbox set
1154 selectbox_rk=%mf_increment(rk)
1155 ,ver_from_dttm=0
1156 ,select_lib="&lib"
1157 ,select_ds="MPE_ROW_LEVEL_SECURITY"
1158 ,base_column="RLS_OPERATOR_NM"
1159 ,selectbox_value="NOT IN"
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_ROW_LEVEL_SECURITY"
1167 ,base_column="RLS_OPERATOR_NM"
1168 ,selectbox_value="NE"
1169 ,selectbox_order=1
1170 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1171 insert into &lib..mpe_selectbox set
1172 selectbox_rk=%mf_increment(rk)
1173 ,ver_from_dttm=0
1174 ,select_lib="&lib"
1175 ,select_ds="MPE_ROW_LEVEL_SECURITY"
1176 ,base_column="RLS_OPERATOR_NM"
1177 ,selectbox_value="CONTAINS"
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_EXCEL_CONFIG"
1185 ,base_column="XL_RULE"
1186 ,selectbox_value="FORMULA"
1187 ,selectbox_order=1
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_ROW_LEVEL_SECURITY"
1194 ,base_column="RLS_ACTIVE"
1195 ,selectbox_value="1"
1196 ,selectbox_order=1
1197 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1198 insert into &lib..mpe_selectbox set
1199 selectbox_rk=%mf_increment(rk)
1200 ,ver_from_dttm=0
1201 ,select_lib="&lib"
1202 ,select_ds="MPE_ROW_LEVEL_SECURITY"
1203 ,base_column="RLS_ACTIVE"
1204 ,selectbox_value="0"
1205 ,selectbox_order=2
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_ACTIVE"
1213 ,selectbox_value="1"
1214 ,selectbox_order=1
1215 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1216 insert into &lib..mpe_selectbox set
1217 selectbox_rk=%mf_increment(rk)
1218 ,ver_from_dttm=0
1219 ,select_lib="&lib"
1220 ,select_ds="MPE_COLUMN_LEVEL_SECURITY"
1221 ,base_column="CLS_ACTIVE"
1222 ,selectbox_value="0"
1223 ,selectbox_order=2
1224 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1225 insert into &lib..mpe_selectbox set
1226 selectbox_rk=%mf_increment(rk)
1227 ,ver_from_dttm=0
1228 ,select_lib="&lib"
1229 ,select_ds="MPE_COLUMN_LEVEL_SECURITY"
1230 ,base_column="CLS_SCOPE"
1231 ,selectbox_value="EDIT"
1232 ,selectbox_order=1
1233 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1234 insert into &lib..mpe_selectbox set
1235 selectbox_rk=%mf_increment(rk)
1236 ,ver_from_dttm=0
1237 ,select_lib="&lib"
1238 ,select_ds="MPE_COLUMN_LEVEL_SECURITY"
1239 ,base_column="CLS_SCOPE"
1240 ,selectbox_value="VIEW"
1241 ,selectbox_order=2
1242 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1243 insert into &lib..mpe_selectbox set
1244 selectbox_rk=%mf_increment(rk)
1245 ,ver_from_dttm=0
1246 ,select_lib="&lib"
1247 ,select_ds="MPE_COLUMN_LEVEL_SECURITY"
1248 ,base_column="CLS_SCOPE"
1249 ,selectbox_value="ALL"
1250 ,selectbox_order=3
1251 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1252 insert into &lib..mpe_selectbox set
1253 selectbox_rk=%mf_increment(rk)
1254 ,ver_from_dttm=0
1255 ,select_lib="&lib"
1256 ,select_ds="MPE_COLUMN_LEVEL_SECURITY"
1257 ,base_column="CLS_HIDE"
1258 ,selectbox_value="0"
1259 ,selectbox_order=1
1260 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1261 insert into &lib..mpe_selectbox set
1262 selectbox_rk=%mf_increment(rk)
1263 ,ver_from_dttm=0
1264 ,select_lib="&lib"
1265 ,select_ds="MPE_COLUMN_LEVEL_SECURITY"
1266 ,base_column="CLS_HIDE"
1267 ,selectbox_value="1"
1268 ,selectbox_order=2
1269 ,ver_to_dttm='31DEC5999:23:59:59'dt;
1270
1271/**
1272 * MPE_TABLES
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_COLUMN_LEVEL_SECURITY'
1279 ,num_of_approvals_required=1
1280 ,loadtype='TXTEMPORAL'
1281 ,var_txfrom='TX_FROM'
1282 ,var_txto='TX_TO'
1283 ,buskey='CLS_SCOPE CLS_GROUP CLS_LIBREF CLS_TABLE CLS_VARIABLE_NM'
1284 ,notes='Docs: https://docs.datacontroller.io/column-level-security'
1285 ,post_edit_hook='services/hooks/mpe_column_level_security_postedit'
1286 ;
1287 insert into &lib..mpe_tables
1288 set tx_from=0
1289 ,tx_to='31DEC5999:23:59:59'dt
1290 ,libref="&lib"
1291 ,dsn='MPE_XLMAP_INFO'
1292 ,num_of_approvals_required=1
1293 ,loadtype='TXTEMPORAL'
1294 ,var_txfrom='TX_FROM'
1295 ,var_txto='TX_TO'
1296 ,buskey='XLMAP_ID'
1297 ,notes='Docs: https://docs.datacontroller.io/complex-excel-uploads'
1298 ,post_edit_hook='services/hooks/mpe_xlmap_info_postedit'
1299 ;
1300 insert into &lib..mpe_tables
1301 set tx_from=0
1302 ,tx_to='31DEC5999:23:59:59'dt
1303 ,libref="&lib"
1304 ,dsn='MPE_XLMAP_RULES'
1305 ,num_of_approvals_required=1
1306 ,loadtype='TXTEMPORAL'
1307 ,var_txfrom='TX_FROM'
1308 ,var_txto='TX_TO'
1309 ,buskey='XLMAP_ID XLMAP_RANGE_ID'
1310 ,notes='Docs: https://docs.datacontroller.io/complex-excel-uploads'
1311 ,post_edit_hook='services/hooks/mpe_xlmap_rules_postedit'
1312 ;
1313 insert into &lib..mpe_tables
1314 set tx_from=0
1315 ,tx_to='31DEC5999:23:59:59'dt
1316 ,libref="&lib"
1317 ,dsn='MPE_XLMAP_DATA'
1318 ,num_of_approvals_required=1
1319 ,loadtype='UPDATE'
1320 ,buskey='LOAD_REF XLMAP_ID XLMAP_RANGE_ID ROW_NO COL_NO'
1321 ,notes='Docs: https://docs.datacontroller.io/complex-excel-uploads'
1322 ;
1323 insert into &lib..mpe_tables
1324 set tx_from=0
1325 ,tx_to='31DEC5999:23:59:59'dt
1326 ,libref="&lib"
1327 ,dsn='MPE_LOCKANYTABLE'
1328 ,num_of_approvals_required=1
1329 ,loadtype='UPDATE'
1330 ,buskey='LOCK_LIB LOCK_DS'
1331 ,notes='This table may be edited when a process failed and left a lock'
1332 ;
1333 insert into &lib..mpe_tables
1334 set tx_from=0
1335 ,tx_to='31DEC5999:23:59:59'dt
1336 ,libref="&lib"
1337 ,dsn='MPE_TABLES'
1338 ,num_of_approvals_required=1
1339 ,loadtype='TXTEMPORAL'
1340 ,buskey='LIBREF DSN'
1341 ,var_txfrom='TX_FROM'
1342 ,var_txto='TX_TO'
1343 ,notes='This entry allows the MP Editor to edit itself!'
1344 ,post_edit_hook='services/hooks/mpe_tables_postedit'
1345 ;
1346 insert into &lib..mpe_tables
1347 set tx_from=0
1348 ,tx_to='31DEC5999:23:59:59'dt
1349 ,libref="&lib"
1350 ,dsn='MPE_SECURITY'
1351 ,num_of_approvals_required=1
1352 ,loadtype='TXTEMPORAL'
1353 ,buskey='LIBREF DSN ACCESS_LEVEL SAS_GROUP'
1354 ,var_txfrom='TX_FROM'
1355 ,var_txto='TX_TO'
1356 ,notes='Determines which groups can view/edit/approve which tables'
1357 ,post_edit_hook='services/hooks/mpe_security_postedit'
1358 ;
1359 insert into &lib..mpe_tables
1360 set tx_from=0
1361 ,tx_to='31DEC5999:23:59:59'dt
1362 ,libref="&lib"
1363 ,dsn='MPE_SELECTBOX'
1364 ,num_of_approvals_required=1
1365 ,loadtype='TXTEMPORAL'
1366 ,buskey='SELECTBOX_RK'
1367 ,var_txfrom='VER_FROM_DTTM'
1368 ,var_txto='VER_TO_DTTM'
1369 ,notes='Can configure dropdowns for the front end'
1370 ,rk_underlying='SELECT_LIB SELECT_DS BASE_COLUMN SELECTBOX_VALUE'
1371 ;
1372 insert into &lib..mpe_tables
1373 set tx_from=0
1374 ,tx_to='31DEC5999:23:59:59'dt
1375 ,libref="&lib"
1376 ,dsn='MPE_X_TEST'
1377 ,num_of_approvals_required=1
1378 ,loadtype='UPDATE'
1379 ,buskey='PRIMARY_KEY_FIELD'
1380 ,notes='Test table for controller'
1381 ;
1382 insert into &lib..mpe_tables
1383 set tx_from=0
1384 ,tx_to='31DEC5999:23:59:59'dt
1385 ,libref="&lib"
1386 ,dsn='MPE_EMAILS'
1387 ,num_of_approvals_required=1
1388 ,loadtype='TXTEMPORAL'
1389 ,buskey='USER_NAME'
1390 ,notes='Primary Emails Table (backup is metadata)'
1391 ,var_txfrom='TX_FROM'
1392 ,var_txto='TX_TO'
1393 ;
1394 insert into &lib..mpe_tables
1395 set tx_from=0
1396 ,tx_to='31DEC5999:23:59:59'dt
1397 ,libref="&lib"
1398 ,dsn='MPE_CONFIG'
1399 ,num_of_approvals_required=1
1400 ,loadtype='TXTEMPORAL'
1401 ,buskey='VAR_SCOPE VAR_NAME'
1402 ,notes='Configuration variables for Data Controller'
1403 ,var_txfrom='TX_FROM'
1404 ,var_txto='TX_TO'
1405 ;
1406 insert into &lib..mpe_tables
1407 set tx_from=0
1408 ,tx_to='31DEC5999:23:59:59'dt
1409 ,libref="&lib"
1410 ,dsn='MPE_ALERTS'
1411 ,num_of_approvals_required=1
1412 ,loadtype='TXTEMPORAL'
1413 ,buskey='ALERT_EVENT ALERT_LIB ALERT_DS ALERT_USER'
1414 ,notes='Configuration for alert email events'
1415 ,var_txfrom='TX_FROM'
1416 ,var_txto='TX_TO'
1417 ;
1418 insert into &lib..mpe_tables
1419 set tx_from=0
1420 ,tx_to='31DEC5999:23:59:59'dt
1421 ,libref="&lib"
1422 ,dsn='MPE_GROUPS'
1423 ,num_of_approvals_required=1
1424 ,loadtype='TXTEMPORAL'
1425 ,buskey='GROUP_NAME USER_NAME'
1426 ,notes='Configuration for additional groups within Data Controller'
1427 ,var_txfrom='TX_FROM'
1428 ,var_txto='TX_TO'
1429 ;
1430 insert into &lib..mpe_tables
1431 set tx_from=0
1432 ,tx_to='31DEC5999:23:59:59'dt
1433 ,libref="&lib"
1434 ,dsn='MPE_VALIDATIONS'
1435 ,num_of_approvals_required=1
1436 ,loadtype='TXTEMPORAL'
1437 ,buskey='BASE_LIB BASE_DS BASE_COL RULE_TYPE'
1438 ,notes='Configuration of data quality rules in Editor component'
1439 ,var_txfrom='TX_FROM'
1440 ,var_txto='TX_TO'
1441 ,post_edit_hook='services/hooks/mpe_validations_postedit'
1442 ;
1443 insert into &lib..mpe_tables
1444 set tx_from=0
1445 ,tx_to='31DEC5999:23:59:59'dt
1446 ,libref="&lib"
1447 ,dsn='MPE_DATADICTIONARY'
1448 ,num_of_approvals_required=1
1449 ,loadtype='TXTEMPORAL'
1450 ,buskey='DD_TYPE DD_SOURCE'
1451 ,notes='Configuration of data dictionary'
1452 ,var_txfrom='TX_FROM'
1453 ,var_txto='TX_TO'
1454 ;
1455 insert into &lib..mpe_tables
1456 set tx_from=0
1457 ,tx_to='31DEC5999:23:59:59'dt
1458 ,libref="&lib"
1459 ,dsn='MPE_EXCEL_CONFIG'
1460 ,num_of_approvals_required=1
1461 ,loadtype='TXTEMPORAL'
1462 ,buskey='XL_LIBREF XL_TABLE XL_COLUMN'
1463 ,notes='Configuration of the excel import rules'
1464 ,var_txfrom='TX_FROM'
1465 ,var_txto='TX_TO'
1466 ;
1467 insert into &lib..mpe_tables
1468 set tx_from=0
1469 ,tx_to='31DEC5999:23:59:59'dt
1470 ,libref="&lib"
1471 ,dsn='MPE_ROW_LEVEL_SECURITY'
1472 ,num_of_approvals_required=1
1473 ,loadtype='TXTEMPORAL'
1474 ,buskey='RLS_RK'
1475 ,notes='Configuration of Row Level Security'
1476 ,var_txfrom='TX_FROM'
1477 ,var_txto='TX_TO'
1478 ,rk_underlying='RLS_SCOPE RLS_GROUP RLS_LIBREF RLS_TABLE RLS_GROUP_LOGIC '
1479 !!'RLS_SUBGROUP_LOGIC RLS_SUBGROUP_ID RLS_VARIABLE_NM RLS_OPERATOR_NM '
1480 !!'RLS_RAW_VALUE '
1481 ,post_edit_hook='services/hooks/mpe_row_level_security_postedit'
1482 ;
1483 insert into &lib..mpe_tables
1484 set tx_from=0
1485 ,tx_to='31DEC5999:23:59:59'dt
1486 ,libref="&lib"
1487 ,dsn='MPE_X_CATALOG-FC'
1488 ,num_of_approvals_required=1
1489 ,loadtype='FORMAT_CAT'
1490 ,buskey='TYPE FMTNAME FMTROW'
1491 ,notes='Sample Format Catalog'
1492 ;
1493
1494/* mpe_validations */
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_SCOPE"
1500 ,rule_type='CASE'
1501 ,rule_value='UPCASE'
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_LIBREF"
1509 ,rule_type='CASE'
1510 ,rule_value='UPCASE'
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_LIBREF"
1518 ,rule_type='SOFTSELECT_HOOK'
1519 ,rule_value="services/validations/libraries_all"
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_TABLE"
1527 ,rule_type='CASE'
1528 ,rule_value='UPCASE'
1529 ,rule_active=1
1530 ,tx_to='31DEC5999:23:59:59'dt;
1531insert into &lib..MPE_VALIDATIONS set
1532 tx_from=0
1533 ,base_lib="&lib"
1534 ,base_ds="MPE_COLUMN_LEVEL_SECURITY"
1535 ,base_col="CLS_TABLE"
1536 ,rule_type='SOFTSELECT_HOOK'
1537 ,rule_value="services/validations/tables_all"
1538 ,rule_active=1
1539 ,tx_to='31DEC5999:23:59:59'dt;
1540insert into &lib..MPE_VALIDATIONS set
1541 tx_from=0
1542 ,base_lib="&lib"
1543 ,base_ds="MPE_COLUMN_LEVEL_SECURITY"
1544 ,base_col="CLS_VARIABLE_NM"
1545 ,rule_type='CASE'
1546 ,rule_value='UPCASE'
1547 ,rule_active=1
1548 ,tx_to='31DEC5999:23:59:59'dt;
1549insert into &lib..MPE_VALIDATIONS set
1550 tx_from=0
1551 ,base_lib="&lib"
1552 ,base_ds="MPE_COLUMN_LEVEL_SECURITY"
1553 ,base_col="CLS_VARIABLE_NM"
1554 ,rule_type='SOFTSELECT_HOOK'
1555 ,rule_value="services/validations/columns_in_libds"
1556 ,rule_active=1
1557 ,tx_to='31DEC5999:23:59:59'dt;
1558insert into &lib..MPE_VALIDATIONS set
1559 tx_from=0
1560 ,base_lib="&lib"
1561 ,base_ds="MPE_COLUMN_LEVEL_SECURITY"
1562 ,base_col="CLS_ACTIVE"
1563 ,rule_type='MAXVAL'
1564 ,rule_value='1'
1565 ,rule_active=1
1566 ,tx_to='31DEC5999:23:59:59'dt;
1567insert into &lib..MPE_VALIDATIONS set
1568 tx_from=0
1569 ,base_lib="&lib"
1570 ,base_ds="MPE_COLUMN_LEVEL_SECURITY"
1571 ,base_col="CLS_HIDE"
1572 ,rule_type='MAXVAL'
1573 ,rule_value='1'
1574 ,rule_active=1
1575 ,tx_to='31DEC5999:23:59:59'dt;
1576insert into &lib..MPE_VALIDATIONS set
1577 tx_from=0
1578 ,base_lib="&lib"
1579 ,base_ds="MPE_COLUMN_LEVEL_SECURITY"
1580 ,base_col="CLS_GROUP"
1581 ,rule_type='SOFTSELECT_HOOK'
1582 ,rule_value="services/validations/sas_groups"
1583 ,rule_active=1
1584 ,tx_to='31DEC5999:23:59:59'dt;
1585
1586insert into &lib..MPE_VALIDATIONS set
1587 tx_from=0
1588 ,base_lib="&lib"
1589 ,base_ds="MPE_ALERTS"
1590 ,base_col="ALERT_LIB"
1591 ,rule_type='HARDSELECT_HOOK'
1592 ,rule_value="services/validations/mpe_alerts.alert_lib"
1593 ,rule_active=1
1594 ,tx_to='31DEC5999:23:59:59'dt;
1595
1596insert into &lib..MPE_VALIDATIONS set
1597 tx_from=0
1598 ,base_lib="&lib"
1599 ,base_ds="MPE_XLMAP_INFO"
1600 ,base_col="XLMAP_ID"
1601 ,rule_type='CASE'
1602 ,rule_value='UPCASE'
1603 ,rule_active=1
1604 ,tx_to='31DEC5999:23:59:59'dt;
1605
1606insert into &lib..MPE_VALIDATIONS set
1607 tx_from=0
1608 ,base_lib="&lib"
1609 ,base_ds="MPE_XLMAP_RULES"
1610 ,base_col="XLMAP_ID"
1611 ,rule_type='CASE'
1612 ,rule_value='UPCASE'
1613 ,rule_active=1
1614 ,tx_to='31DEC5999:23:59:59'dt;
1615
1616insert into &lib..MPE_VALIDATIONS set
1617 tx_from=0
1618 ,base_lib="&lib"
1619 ,base_ds="MPE_TABLES"
1620 ,base_col="LIBREF"
1621 ,rule_type='CASE'
1622 ,rule_value='UPCASE'
1623 ,rule_active=1
1624 ,tx_to='31DEC5999:23:59:59'dt;
1625insert into &lib..MPE_VALIDATIONS set
1626 tx_from=0
1627 ,base_lib="&lib"
1628 ,base_ds="MPE_TABLES"
1629 ,base_col="DSN"
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="LIBREF"
1639 ,rule_type='NOTNULL'
1640 ,rule_value=' '
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="DSN"
1648 ,rule_type='NOTNULL'
1649 ,rule_value=' '
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="NUM_OF_APPROVALS_REQUIRED"
1657 ,rule_type='MINVAL'
1658 ,rule_value='1'
1659 ,rule_active=1
1660 ,tx_to='31DEC5999:23:59:59'dt;
1661insert into &lib..MPE_VALIDATIONS set
1662 tx_from=0
1663 ,base_lib="&lib"
1664 ,base_ds="MPE_TABLES"
1665 ,base_col="BUSKEY"
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="BUSKEY"
1675 ,rule_type='NOTNULL'
1676 ,rule_value=" "
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_TABLES"
1683 ,base_col="VAR_TXFROM"
1684 ,rule_type='CASE'
1685 ,rule_value='UPCASE'
1686 ,rule_active=1
1687 ,tx_to='31DEC5999:23:59:59'dt;
1688insert into &lib..MPE_VALIDATIONS set
1689 tx_from=0
1690 ,base_lib="&lib"
1691 ,base_ds="MPE_TABLES"
1692 ,base_col="VAR_TXTO"
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_TABLES"
1701 ,base_col="VAR_BUSFROM"
1702 ,rule_type='CASE'
1703 ,rule_value='UPCASE'
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_TABLES"
1710 ,base_col="VAR_BUSTO"
1711 ,rule_type='CASE'
1712 ,rule_value='UPCASE'
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_SECURITY"
1719 ,base_col="LIBREF"
1720 ,rule_type='CASE'
1721 ,rule_value="UPCASE"
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_TABLES"
1728 ,base_col="VAR_PROCESSED"
1729 ,rule_type='CASE'
1730 ,rule_value='UPCASE'
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_SECURITY"
1737 ,base_col="LIBREF"
1738 ,rule_type='HARDSELECT'
1739 ,rule_value="&lib..MPE_TABLES.LIBREF"
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_SECURITY"
1746 ,base_col="DSN"
1747 ,rule_type='CASE'
1748 ,rule_value="UPCASE"
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_SECURITY"
1755 ,base_col="DSN"
1756 ,rule_type='SOFTSELECT'
1757 ,rule_value="&lib..MPE_TABLES.DSN"
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_SECURITY"
1764 ,base_col="SAS_GROUP"
1765 ,rule_type='SOFTSELECT_HOOK'
1766 ,rule_value="services/validations/sas_groups"
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_VALIDATIONS"
1773 ,base_col="BASE_LIB"
1774 ,rule_type='SOFTSELECT_HOOK'
1775 ,rule_value="services/validations/libraries_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_VALIDATIONS"
1782 ,base_col="BASE_DS"
1783 ,rule_type='SOFTSELECT_HOOK'
1784 ,rule_value="services/validations/tables_editable"
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_VALIDATIONS"
1791 ,base_col="BASE_COL"
1792 ,rule_type='SOFTSELECT_HOOK'
1793 ,rule_value="services/validations/columns_in_libds"
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_VALIDATIONS"
1800 ,base_col="RULE_ACTIVE"
1801 ,rule_type='MINVAL'
1802 ,rule_value="0"
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_VALIDATIONS"
1809 ,base_col="RULE_ACTIVE"
1810 ,rule_type='MAXVAL'
1811 ,rule_value="1"
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_EXCEL_CONFIG"
1818 ,base_col="XL_LIBREF"
1819 ,rule_type='SOFTSELECT_HOOK'
1820 ,rule_value="services/validations/libraries_editable"
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_EXCEL_CONFIG"
1827 ,base_col="XL_TABLE"
1828 ,rule_type='SOFTSELECT_HOOK'
1829 ,rule_value="services/validations/tables_editable"
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_EXCEL_CONFIG"
1836 ,base_col="XL_COLUMN"
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="LIBREF"
1846 ,rule_type='SOFTSELECT_HOOK'
1847 ,rule_value="services/validations/libraries_all"
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_TABLES"
1854 ,base_col="DSN"
1855 ,rule_type='SOFTSELECT_HOOK'
1856 ,rule_value="services/validations/mpe_tables.dsn"
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_TABLES"
1863 ,base_col="VAR_TXFROM"
1864 ,rule_type='SOFTSELECT_HOOK'
1865 ,rule_value="services/validations/columns_in_libds"
1866 ,rule_active=1
1867 ,tx_to='31DEC5999:23:59:59'dt;
1868insert into &lib..MPE_VALIDATIONS set
1869 tx_from=0
1870 ,base_lib="&lib"
1871 ,base_ds="MPE_TABLES"
1872 ,base_col="VAR_TXTO"
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_TABLES"
1881 ,base_col="VAR_BUSFROM"
1882 ,rule_type='SOFTSELECT_HOOK'
1883 ,rule_value="services/validations/columns_in_libds"
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_TABLES"
1890 ,base_col="VAR_BUSTO"
1891 ,rule_type='SOFTSELECT_HOOK'
1892 ,rule_value="services/validations/columns_in_libds"
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_TABLES"
1899 ,base_col="VAR_PROCESSED"
1900 ,rule_type='SOFTSELECT_HOOK'
1901 ,rule_value="services/validations/columns_in_libds"
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_SELECTBOX"
1908 ,base_col="SELECT_LIB"
1909 ,rule_type='SOFTSELECT_HOOK'
1910 ,rule_value="services/validations/libraries_editable"
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_SELECTBOX"
1917 ,base_col="SELECT_DS"
1918 ,rule_type='SOFTSELECT_HOOK'
1919 ,rule_value="services/validations/tables_editable"
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_SELECTBOX"
1926 ,base_col="BASE_COLUMN"
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;
1931insert into &lib..MPE_VALIDATIONS set
1932 tx_from=0
1933 ,base_lib="&lib"
1934 ,base_ds="MPE_ROW_LEVEL_SECURITY"
1935 ,base_col="RLS_GROUP"
1936 ,rule_type='SOFTSELECT_HOOK'
1937 ,rule_value="services/validations/sas_groups"
1938 ,rule_active=1
1939 ,tx_to='31DEC5999:23:59:59'dt;
1940insert into &lib..MPE_VALIDATIONS set
1941 tx_from=0
1942 ,base_lib="&lib"
1943 ,base_ds="MPE_ROW_LEVEL_SECURITY"
1944 ,base_col="RLS_LIBREF"
1945 ,rule_type='SOFTSELECT_HOOK'
1946 ,rule_value="services/validations/libraries_all"
1947 ,rule_active=1
1948 ,tx_to='31DEC5999:23:59:59'dt;
1949insert into &lib..MPE_VALIDATIONS set
1950 tx_from=0
1951 ,base_lib="&lib"
1952 ,base_ds="MPE_ROW_LEVEL_SECURITY"
1953 ,base_col="RLS_TABLE"
1954 ,rule_type='SOFTSELECT_HOOK'
1955 ,rule_value="services/validations/tables_all"
1956 ,rule_active=1
1957 ,tx_to='31DEC5999:23:59:59'dt;
1958insert into &lib..MPE_VALIDATIONS set
1959 tx_from=0
1960 ,base_lib="&lib"
1961 ,base_ds="MPE_ROW_LEVEL_SECURITY"
1962 ,base_col="RLS_SUBGROUP_ID"
1963 ,rule_type='MINVAL'
1964 ,rule_value='0'
1965 ,rule_active=1
1966 ,tx_to='31DEC5999:23:59:59'dt;
1967insert into &lib..MPE_VALIDATIONS set
1968 tx_from=0
1969 ,base_lib="&lib"
1970 ,base_ds="MPE_ROW_LEVEL_SECURITY"
1971 ,base_col="RLS_SUBGROUP_ID"
1972 ,rule_type='NOTNULL'
1973 ,rule_value='0'
1974 ,rule_active=1
1975 ,tx_to='31DEC5999:23:59:59'dt;
1976insert into &lib..MPE_VALIDATIONS set
1977 tx_from=0
1978 ,base_lib="&lib"
1979 ,base_ds="MPE_ROW_LEVEL_SECURITY"
1980 ,base_col="RLS_VARIABLE_NM"
1981 ,rule_type='SOFTSELECT_HOOK'
1982 ,rule_value="services/validations/columns_in_libds"
1983 ,rule_active=1
1984 ,tx_to='31DEC5999:23:59:59'dt;
1985/* test softselect on numeric var (should be ordered numerically) */
1986insert into &lib..MPE_VALIDATIONS set
1987 tx_from=0
1988 ,base_lib="&lib"
1989 ,base_ds="MPE_X_TEST"
1990 ,base_col="SOME_BESTNUM"
1991 ,rule_type='SOFTSELECT'
1992 ,rule_value="&lib..MPE_X_TEST.SOME_BESTNUM"
1993 ,rule_active=1
1994 ,tx_to='31DEC5999:23:59:59'dt;
1995/* test hard regex - SOME_CHAR must contain "the" or "data" */
1996insert into &lib..MPE_VALIDATIONS set
1997 tx_from=0
1998 ,base_lib="&lib"
1999 ,base_ds="MPE_X_TEST"
2000 ,base_col="SOME_CHAR"
2001 ,rule_type='HARDREGEX'
2002 ,rule_value='/the|data/i'
2003 ,rule_active=1
2004 ,tx_to='31DEC5999:23:59:59'dt;
2005/* test soft regex - SOME_CHAR should contain the letter "t" */
2006insert into &lib..MPE_VALIDATIONS set
2007 tx_from=0
2008 ,base_lib="&lib"
2009 ,base_ds="MPE_X_TEST"
2010 ,base_col="SOME_CHAR"
2011 ,rule_type='SOFTREGEX'
2012 ,rule_value='/t/'
2013 ,rule_active=1
2014 ,tx_to='31DEC5999:23:59:59'dt;
2015/* test soft regex - PRIMARY_KEY_FIELD should be an integer (yellow if it
2016contains a decimal point). All generated keys are integers, so no
2017warning shows until a demo user enters a decimal. */
2018insert into &lib..MPE_VALIDATIONS set
2019 tx_from=0
2020 ,base_lib="&lib"
2021 ,base_ds="MPE_X_TEST"
2022 ,base_col="PRIMARY_KEY_FIELD"
2023 ,rule_type='SOFTREGEX'
2024 ,rule_value='/^\d+$/'
2025 ,rule_active=1
2026 ,tx_to='31DEC5999:23:59:59'dt;
2027/* test hard regex - SOME_SHORTNUM must not be between 1 and 5. Generated
2028data starts at 6, so nothing is blocked until a demo user enters 1-5. */
2029insert into &lib..MPE_VALIDATIONS set
2030 tx_from=0
2031 ,base_lib="&lib"
2032 ,base_ds="MPE_X_TEST"
2033 ,base_col="SOME_SHORTNUM"
2034 ,rule_type='HARDREGEX'
2035 ,rule_value='/^(?![1-5](\.\d+)?$).*/'
2036 ,rule_active=1
2037 ,tx_to='31DEC5999:23:59:59'dt;
2038insert into &lib..MPE_VALIDATIONS set
2039 tx_from=0
2040 ,base_lib="&lib"
2041 ,base_ds="MPE_X_TEST"
2042 ,base_col="SOME_NUM"
2043 ,rule_type='HARDSELECT_HOOK'
2044 ,rule_value="services/validations/mpe_x_test.some_num"
2045 ,rule_active=1
2046 ,tx_to='31DEC5999:23:59:59'dt;
2047insert into &lib..MPE_VALIDATIONS set
2048 tx_from=0
2049 ,base_lib="&lib"
2050 ,base_ds="MPE_EXCEL_CONFIG"
2051 ,base_col="XL_ACTIVE"
2052 ,rule_type='MINVAL'
2053 ,rule_value='0'
2054 ,rule_active=1
2055 ,tx_to='31DEC5999:23:59:59'dt;
2056insert into &lib..MPE_VALIDATIONS set
2057 tx_from=0
2058 ,base_lib="&lib"
2059 ,base_ds="MPE_EXCEL_CONFIG"
2060 ,base_col="XL_ACTIVE"
2061 ,rule_type='MAXVAL'
2062 ,rule_value='1'
2063 ,rule_active=1
2064 ,tx_to='31DEC5999:23:59:59'dt;
2065insert into &lib..MPE_VALIDATIONS set
2066 tx_from=0
2067 ,base_lib="&lib"
2068 ,base_ds="MPE_XLMAP_INFO"
2069 ,base_col="XLMAP_ID"
2070 ,rule_type='SOFTSELECT'
2071 ,rule_value="&lib..MPE_XLMAP_RULES.XLMAP_ID"
2072 ,rule_active=1
2073 ,tx_to='31DEC5999:23:59:59'dt;
2074
2075
2076/**
2077 * MPE_X_TEST
2078 */
2079 insert into &lib..mpe_x_test
2080 set primary_key_field=0
2081 ,some_char='this is dummy data'
2082 ,some_dropdown='Option 1'
2083 ,some_num=42
2084 ,some_date=42
2085 ,some_datetime=42
2086 ,some_time=42
2087 ,some_shortnum=8
2088 ,some_bestnum=44;
2089 insert into &lib..mpe_x_test
2090 set primary_key_field=1
2091 ,some_char='more dummy data'
2092 ,some_dropdown='Option 2'
2093 ,some_num=42
2094 ,some_date=42
2095 ,some_datetime=42
2096 ,some_time=422
2097 ,some_shortnum=8
2098 ,some_bestnum=44;
2099 insert into &lib..mpe_x_test
2100 set primary_key_field=2
2101 ,some_char='even more dummy data'
2102 ,some_dropdown='Option 3'
2103 ,some_num=42
2104 ,some_date=42
2105 ,some_datetime=42
2106 ,some_time=142
2107 ,some_shortnum=8
2108 ,some_bestnum=44;
2109 insert into &lib..mpe_x_test
2110 set primary_key_field=3
2111 ,some_char=repeat('It was a dark and stormy night. The wind was blowing'
2112 !!' a gale! The captain said to his mate - mate, tell us a tale. And'
2113 !!' this, is the tale he told: ',3)
2114 ,some_dropdown='Option 2'
2115 ,some_num=1613.001
2116 ,some_date=423
2117 ,some_datetime=423
2118 ,some_time=44
2119 ,some_shortnum=8
2120 ,some_bestnum=44;
2121 insert into &lib..mpe_x_test
2122 set primary_key_field=4
2123 ,some_char='if you can fill the unforgiving minute'
2124 ,some_dropdown='Option 1'
2125 ,some_num=1613.001123456
2126 ,some_date=4231
2127 ,some_datetime=423123123
2128 ,some_time=412
2129 ,some_shortnum=8
2130 ,some_bestnum=44;
2131%do x=10 %to 500;
2132 insert into &lib..mpe_x_test
2133 set primary_key_field=10&x
2134 ,some_char="&x bottles of beer on the wall"
2135 ,some_dropdown='Option 1'
2136 ,some_num=ranuni(0)
2137 ,some_date=round(ranuni(0)*1000,1)
2138 ,some_datetime=round(ranuni(0)*50000,1)
2139 ,some_time=round(ranuni(0)*100,1)
2140 ,some_shortnum=6+round(ranuni(0)*94,1)
2141 ,some_bestnum=round(ranuni(0)*100,1);
2142%end;
2143
2144
2145/* https://support.sas.com/resources/papers/proceedings/proceedings/sugi27/p056-27.pdf */
2146proc format library=&lib..mpe_x_catalog;
2147 value otdate
2148 .Z = 'Some Zs'
2149 .N = 'Some 9s'
2150 other = [date9.]
2151 ;
2152 invalue disc
2153 'ABC' = 0.20
2154 'DEF' = 0.25
2155 'XYZ' = 0.00
2156 other = 0.00
2157 ;
2158 invalue indate
2159 '00000000' = .Z
2160 '99999999' = .N
2161 other = [yymmdd8.]
2162 ;
2163 value age(multilabel)
2164 20 - 29 = '20 - 29'
2165 30 - 39 = '30 - 39'
2166 40 - 49 = '40 - 49'
2167 50 - 59 = '50 - 59'
2168 60 - high = '60 +++'
2169 20 - 35 = '20 - 35'
2170 36 - 55 = '36 - 55'
2171 55 - high = '55 +++'
2172 ;
2173/* https://libguides.library.kent.edu/SAS/UserDefinedFormats */
2174 VALUE $GENDERLABEL
2175 "M" = "Male"
2176 "F" = "Female"
2177 ;
2178 VALUE LIKERT_SEVEN
2179 1 = "Strongly Disagree"
2180 2 = "Disagree"
2181 3 = "Slightly Disagree"
2182 4 = "Neither Agree nor Disagree"
2183 5 = "Slightly Agree"
2184 6 = "Agree"
2185 7 = "Strongly Agree"
2186 ;
2187 VALUE LIKERT7_ELEVEN
2188 1,2,3 = "Disagree"
2189 4 = "Neither Agree nor Disagree"
2190 5,6,7 = "Agree"
2191 ;
2192 VALUE LIKERT7_SISTERS
2193 1-3 = "Disagree"
2194 4 = "Neither Agree nor Disagree"
2195 5-7 = "Agree"
2196 ;
2197 VALUE INCOME
2198 LOW -< 20000 = "Low"
2199 20000 -< 60000 = "Middle"
2200 60000 - HIGH = "High"
2201 ;
2202 VALUE RACE
2203 1 = "White"
2204 2 = "Black"
2205 OTHER = "Other"
2206 ;
2207 VALUE GENDERCODE
2208 0 = 'Male'
2209 1 = 'Female';
2210 VALUE ATHLETECODE
2211 0 = 'Non-athlete'
2212 1 = 'Athlete';
2213 VALUE SMOKINGCODE
2214 0 = 'Nonsmoker'
2215 1 = 'Past smoker'
2216 2 = 'Current smoker';
2217/* https://documentation.sas.com/doc/en/pgmsascdc/v_017/proc/p1upn25lbfo6mkn1wncu4dyh9q91.htm */
2218 value $state
2219 'Delaware'='DE'
2220 'Florida'='FL'
2221 'Ohio'='OH';
2222 value MYfmt
2223 /* Format dates prior to 31DEC2011 using only a year. */
2224 low-'31DEC2011'd=[year4.]
2225
2226 /* Format 2012 dates using the month and year. */
2227 '01jan2012'd-'31DEC12'd=[monyy7.]
2228
2229 /* Format dates 01JAN2013 and beyond using the day, month, and year. */
2230 '01JAN2013'd-high=[date9.]
2231
2232 /* Catch missing values. */
2233 other='n/a';
2234 value newfmt .='N/A' other=[12.1];
2235/* https://www.lexjansen.com/nesug/nesug08/cc/cc14.pdf */
2236 value $genderml (multilabel)
2237 '1'='Male'
2238 '2'='Female'
2239 '1','2',' '='Total people';
2240 value agemla (multilabel)
2241 1-4='Preschool'
2242 1-18='Children'
2243 19-120='Adults';
2244 value agemlb (multilabel)
2245 19-120='Adults'
2246 1-18='Children'
2247 1-4='Preschool';
2248 value agemlc (multilabel notsorted)
2249 19-120='Adults'
2250 1-18='Children'
2251 1-4='Preschool';
2252%mend mpe_makedata;