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 
28 proc sql;
29 insert 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 
40 insert 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.';
50 insert 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.';
62 insert 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.';
70 insert 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.';
81 insert 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.';
90 insert 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.';
99 insert 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.';
109 insert 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.';
119 insert 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.';
130 insert 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';
138 insert 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';
147 insert 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';
155 insert 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';
163 insert 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';
171 insert 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';
179 insert 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';
188 insert 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';
196 insert 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 
206 insert 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 
217 insert 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 
228 insert 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 
239 insert 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;
250 insert 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;
261 insert 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  */
276 insert 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  */
286 insert 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';
293 insert 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';
300 insert 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';
307 insert 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';
314 insert 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';
321 insert 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';
328 insert 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)';
335 insert 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)';
363 insert 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)';
370 insert 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)';
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:1a/f'
382  ,xlmap_sheet='KM1'
383  ,xlmap_start='MATCH C R[1]C[6]: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:2/a'
389  ,xlmap_sheet='KM1'
390  ,xlmap_start='ABSOLUTE D10';
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:2/b'
396  ,xlmap_sheet='/3'
397  ,xlmap_start='ABSOLUTE E10';
398 insert 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]';
405 insert 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]';
412 insert 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]';
419 insert 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]';
426 insert 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]';
434 insert 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';
442 insert 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';
450 insert 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';
458 insert 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';
466 insert 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  */
480 insert 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;
486 insert 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;
492 insert 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;
498 insert 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  */
508 insert 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;
523 insert 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;
538 insert 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  */
558 insert 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;
565 insert 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;
572 insert 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;
579 insert 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;
586 insert 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;
593 insert 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;
694 insert 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 */
1397 insert 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;
1406 insert 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;
1415 insert 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;
1424 insert 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;
1433 insert 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;
1442 insert 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;
1451 insert 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;
1460 insert 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;
1469 insert 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;
1478 insert 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 
1488 insert 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 
1498 insert 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 
1508 insert 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 
1518 insert 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;
1527 insert 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;
1536 insert 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;
1545 insert 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;
1554 insert 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;
1563 insert 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;
1572 insert 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;
1581 insert 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;
1590 insert 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;
1599 insert 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;
1608 insert 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;
1617 insert 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;
1626 insert 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;
1635 insert 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;
1644 insert 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;
1653 insert 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;
1662 insert 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;
1671 insert 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;
1680 insert 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;
1689 insert 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;
1698 insert 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;
1707 insert 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;
1716 insert 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;
1725 insert 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;
1734 insert 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;
1743 insert 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;
1752 insert 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;
1761 insert 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;
1770 insert 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;
1779 insert 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;
1788 insert 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;
1797 insert 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;
1806 insert 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;
1815 insert 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;
1824 insert 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;
1833 insert 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;
1842 insert 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;
1851 insert 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;
1860 insert 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;
1869 insert 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) */
1879 insert 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;
1888 insert 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;
1897 insert 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;
1906 insert 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;
1915 insert 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 */
1996 proc 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;