Loading...
Searching...
No Matches
mpe_makedatamodel.sas
Go to the documentation of this file.
1/**
2 @file
3 @brief Creates the empty Data Controller tables
4 @details Creates the empty DC tables during the initial deployment.
5
6 Usage:
7
8 %mpe_makedatamodel(lib=DCxxxx)
9
10 <h4> SAS Macros </h4>
11 @li mf_existfeature.sas
12
13 @version 9.2
14 @author 4GL Apps Ltd
15 @copyright 4GL Apps Ltd. This code may only be used within Data Controller
16 and may not be re-distributed or re-sold without the express permission of
17 4GL Apps Ltd.
18**/
19
20%macro mpe_makedatamodel(lib=);
21%if &syscc ne 0 %then %do;
22 %put syscc=&syscc exiting &sysmacroname;
23 %return;
24%end;
25
26%local notnull;
27%if %mf_existfeature(COLCONSTRAINTS)=1 %then %let notnull=not null;
28%put &=notnull;
29
30proc sql;
31create table &lib..mpe_alerts(
32 tx_from num format=datetime19.3,
33 alert_event char(20),
34 alert_lib char(8),
35 alert_ds char(32),
36 alert_user char(100) ,
37 tx_to num &notnull format=datetime19.3
38);quit;
39proc datasets lib=&lib noprint;
40 modify mpe_alerts;
41 index create
42 pk_mpealerts=(tx_from alert_event alert_lib alert_ds alert_user)
43 /nomiss unique;
44quit;
45
46proc sql;
47create table &lib..mpe_audit(
48 load_ref char(36) label='unique load reference',
49 libref char(8) label='Library Reference (8 chars)',
50 dsn char(32) label='Dataset Name (32 chars)',
51 key_hash char(32) label=
52 'MD5 Hash of primary key values (pipe seperated)',
53 tgtvar_nm char(32) label='Target variable name (32 chars)',
54 move_type char(1) label='Either (A)ppended, (D)eleted or (M)odified',
55 processed_dttm num format=E8601DT26.6 label='Processed at timestamp',
56 is_pk num label='Is Primary Key Field? (1/0)',
57 is_diff num label=
58 'Did value change? (1/0/-1). Always -1 for appends and deletes.',
59 tgtvar_type char(1) label='Either (C)haracter or (N)umeric',
60 oldval_num num format=best32. label='Old (numeric) value',
61 newval_num num format=best32. label='New (numeric) value',
62 oldval_char char(32765) label='Old (character) value',
63 newval_char char(32765) label='New (character) value'
64);quit;
65proc datasets lib=&lib noprint;
66 modify mpe_audit;
67 index create
68 pk_mpe_audit=(load_ref libref dsn key_hash tgtvar_nm)
69 /nomiss unique;
70quit;
71
72proc sql;
73create table &lib..mpe_column_level_security(
74 tx_from num &notnull format=datetime19.3,
75 tx_to num &notnull format=datetime19.3,
76 CLS_SCOPE char(4) &notnull,
77 CLS_GROUP char(64) &notnull,
78 CLS_LIBREF char(8) &notnull,
79 CLS_TABLE char(32) &notnull,
80 CLS_VARIABLE_NM char(32) &notnull,
81 CLS_ACTIVE num &notnull,
82 CLS_HIDE num
83);quit;
84proc datasets lib=&lib noprint;
85 modify mpe_column_level_security;
86 index create
87 pk_mpe_column_level_security=
88 (tx_to CLS_SCOPE CLS_GROUP CLS_LIBREF CLS_TABLE CLS_VARIABLE_NM)
89 /nomiss unique;
90quit;
91
92proc sql;
93create table &lib..mpe_config(
94 tx_from num &notnull format=datetime19.3
95 ,tx_to num &notnull format=datetime19.3
96 ,var_scope varchar(10) &notnull
97 ,var_name varchar(32) &notnull
98 ,var_value varchar(5000)
99 ,var_active num
100 ,var_desc varchar(300)
101);quit;
102proc datasets lib=&lib noprint;
103 modify mpe_config;
104 index create
105 pk_mpe_config=(tx_to var_scope var_name)
106 /nomiss unique;
107quit;
108proc sql;
109create table &lib..mpe_datacatalog_CATS(
110 TX_FROM float &notnull format=datetime19.,
111 TX_TO float format=datetime19.,
112 libref char(8) label='Library Name',
113 memname char(64) label='Member Name'
114);quit;
115proc datasets lib=&lib noprint;
116 modify mpe_datacatalog_CATS;
117 index create
118 pk_mpe_datacatalog_CATS=(tx_to libref memname)
119 /nomiss unique;
120quit;
121proc sql;
122create table &lib..mpe_datacatalog_libs(
123 TX_FROM num &notnull format=datetime19.3,
124 TX_TO num &notnull format=datetime19.3,
125 libref char(8) label='Library Ref',
126 engine char(32) label='Library Engine',
127 libname char(256) format=$256. label='Library Name',
128 paths char(8192) label='Library Paths',
129 perms char(500) label='Library Permissions (if BASE)',
130 owners char(500) label='Library Owners (if BASE)',
131 schemas char(500) label='Library Schemas (if DB)',
132 libid char(17) label='LibraryId'
133);quit;
134proc datasets lib=&lib noprint;
135 modify mpe_datacatalog_libs;
136 index create
137 pk_mpe_datacatalog_libs=(libref tx_to)
138 /nomiss unique;
139quit;
140proc sql;
141create table &lib..mpe_datacatalog_OBJS(
142 TX_FROM num &notnull format=datetime19.,
143 TX_TO num &notnull format=datetime19.,
144 libref char(8) &notnull label='Library Name',
145 memname char(64) &notnull label='Member Name',
146 objname char(32) &notnull label='Object Name',
147 objtype char(8) &notnull label='Object Type',
148 objdesc char(256) label='Object Description',
149 alias char(32) label='Object Alias'
150);quit;
151proc datasets lib=&lib noprint;
152 modify mpe_datacatalog_OBJS;
153 index create
154 pk_mpe_datacatalog_OBJS=(libref memname objname objtype tx_to)
155 /nomiss unique;
156quit;
157proc sql;
158create table &lib..mpe_datacatalog_TABS(
159 TX_FROM num &notnull format=datetime19.3,
160 TX_TO num &notnull format=datetime19.3,
161 libref char(8) label='Library Name',
162 dsn char(64) label='Member Name',
163 memtype char(8) label='Member Type',
164 dbms_memtype char(32) label='DBMS Member Type',
165 memlabel char(512) label='Data Set Label',
166 typemem char(8) label='Data Set Type',
167 nvar num label='Number of Variables',
168 compress char(8) label='Compression Routine',
169 pk_fields char(512)
170 label='Primary Key Fields - in a constraint being both Unique and Not Null'
171);quit;
172proc datasets lib=&lib noprint;
173 modify mpe_datacatalog_TABS;
174 index create
175 pk_mpe_datacatalog_TABS=(libref dsn tx_to)
176 /nomiss unique;
177quit;
178proc sql;
179create table &lib..mpe_datacatalog_vars(
180 TX_FROM num &notnull format=datetime19.3,
181 TX_TO num &notnull format=datetime19.3,
182 libref char(8) label='Library Name',
183 dsn char(64) label='Table Name',
184 name char(64) label='Column Name',
185 memtype char(8) label='Member Type',
186 type char(16) label='Column Type',
187 length num label='Column Length',
188 varnum num label='Column Number in Table',
189 label char(512) label='Column Label',
190 format char(49) label='Column Format',
191 idxusage char(9) label='Column Index Type',
192 notnull char(3) label='Not NULL?',
193 pk_ind num label='Primary Key Indicator (1=Primary Key field)'
194);quit;
195proc datasets lib=&lib noprint;
196 modify mpe_datacatalog_vars;
197 index create
198 pk_mpe_datacatalog_vars=(libref dsn name tx_to)
199 /nomiss unique;
200quit;
201proc sql;
202create table &lib..mpe_datastatus_CATS(
203 TX_FROM float format=datetime19.,
204 TX_TO float format=datetime19.,
205 libref char(8) label='Library Name',
206 memname char(64) label='Member Name',
207 nobjs num &notnull label='Number of objects',
208 created num &notnull format=DATETIME. label='Date Created',
209 modified num format=DATETIME. label='Date Modified'
210);quit;
211proc datasets lib=&lib noprint;
212 modify mpe_datastatus_CATS;
213 index create
214 pk_mpe_datastatus_cats=(libref memname tx_to)
215 /nomiss unique;
216quit;
217proc sql;
218create table &lib..mpe_datastatus_libs(
219 TX_FROM num &notnull format=datetime19.3,
220 TX_TO num &notnull format=datetime19.3,
221 libref char(8) label='Library Name',
222 libsize num format=SIZEKMG. label='Size of library',
223 table_cnt num label='Number of Tables',
224 catalog_cnt num label='Number of Catalogs'
225);quit;
226proc datasets lib=&lib noprint;
227 modify mpe_datastatus_libs;
228 index create
229 pk_mpe_datastatus_libs=(libref tx_to)
230 /nomiss unique;
231quit;
232proc sql;
233create table &lib..mpe_datastatus_OBJS(
234 TX_FROM float &notnull format=datetime19.,
235 TX_TO float &notnull format=datetime19.,
236 libref char(8) label='Library Name',
237 memname char(64) label='Member Name',
238 objname char(32) label='Object Name',
239 objtype char(8) label='Object Type',
240 created num &notnull format=DATETIME. label='Date Created',
241 modified num format=DATETIME. label='Date Modified',
242 level num label='Library Concatenation Level'
243);quit;
244proc datasets lib=&lib noprint;
245 modify mpe_datastatus_OBJS;
246 index create
247 pk_mpe_datastatus_OBJS=(libref memname objname objtype tx_to)
248 /nomiss unique;
249quit;
250proc sql;
251create table &lib..mpe_datastatus_tabs(
252 TX_FROM num &notnull format=datetime19.3,
253 TX_TO num &notnull format=datetime19.3,
254 libref char(8) label='Library Name',
255 dsn char(64) label='Member Name',
256 filesize num format=SIZEKMG. label='Size of file',
257 crdate num format=DATETIME. label='Date Created',
258 modate num format=DATETIME. label='Date Modified',
259 nobs num label='Number of Physical (Actual, inc. deleted) Observations'
260);quit;
261proc datasets lib=&lib noprint;
262 modify mpe_datastatus_tabs;
263 index create
264 pk_mpe_datastatus_tabs=(libref dsn tx_to)
265 /nomiss unique;
266quit;
267proc sql;
268create table &lib..mpe_datadictionary
269 (
270 TX_FROM num &notnull format=datetime19.3,
271 TX_TO num &notnull format=datetime19.3,
272 DD_TYPE char(16),
273 DD_SOURCE char(1024),
274 DD_SHORTDESC char(256),
275 DD_LONGDESC char(32767),
276 DD_OWNER char(128),
277 DD_RESPONSIBLE char(128),
278 DD_SENSITIVITY char(64)
279);quit;
280proc datasets lib=&lib noprint;
281 modify mpe_datadictionary;
282 index create
283 pk_mpe_datadictionary=(tx_to dd_type dd_source)
284 /nomiss unique;
285quit;
286proc sql;
287create table &lib..mpe_dataloads(
288 libref varchar(8) &notnull,
289 dsn varchar(32) &notnull,
290 etlsource varchar(100) &notnull,
291 loadtype varchar(20) &notnull,
292 changed_records int,
293 new_records int,
294 deleted_records int,
295 duration num,
296 user_nm varchar(50) &notnull,
297 processed_dttm num format=datetime19.3,
298 mac_ver varchar(5)
299);quit;
300proc datasets lib=&lib noprint;
301 modify mpe_dataloads;
302 index create
303 pk_mpe_dataloads=(processed_dttm libref dsn etlsource)
304 /nomiss unique;
305quit;
306proc sql;
307create table &lib..mpe_emails(
308 tx_from num &notnull format=datetime19.3,
309 tx_to num &notnull format=datetime19.3,
310 user_name char(50) &notnull,
311 user_displayname char(100),
312 user_email char(100) &notnull
313);quit;
314proc datasets lib=&lib noprint;
315 modify mpe_emails;
316 index create
317 pk_mpe_emails=(tx_to user_name)
318 /nomiss unique;
319quit;
320proc sql;
321create table &lib..mpe_excel_config(
322 tx_from num &notnull format=datetime19.3,
323 tx_to num &notnull format=datetime19.3,
324 xl_libref char(8),
325 xl_table char(32),
326 xl_column char(32),
327 xl_rule char(32),
328 xl_active num
329);quit;
330proc datasets lib=&lib noprint;
331 modify mpe_excel_config;
332 index create
333 pk_mpe_excel_config=(tx_to xl_libref xl_table xl_column)
334 /nomiss unique;
335quit;
336
337proc sql;
338create table &lib..MPE_XLMAP_DATA(
339 LOAD_REF char(32) &notnull,
340 XLMAP_ID char(32) &notnull,
341 XLMAP_RANGE_ID char(32) &notnull,
342 ROW_NO num &notnull,
343 COL_NO num &notnull,
344 VALUE_TXT char(4000)
345);quit;
346proc datasets lib=&lib noprint;
347 modify MPE_XLMAP_DATA;
348 index create
349 pk_MPE_XLMAP_DATA=(load_ref xlmap_id xlmap_range_id row_no col_no)
350 /nomiss unique;
351quit;
352
353proc sql;
354create table &lib..mpe_xlmap_info(
355 tx_from num &notnull,
356 tx_to num &notnull,
357 XLMAP_ID char(32) &notnull,
358 XLMAP_DESCRIPTION char(1000) &notnull,
359 XLMAP_TARGETLIBDS char(41) &notnull
360);quit;
361proc datasets lib=&lib noprint;
362 modify mpe_xlmap_info;
363 index create
364 pk_mpe_xlmap_info=(tx_to xlmap_id)
365 /nomiss unique;
366quit;
367
368proc sql;
369create table &lib..mpe_xlmap_rules(
370 tx_from num &notnull,
371 tx_to num &notnull,
372 XLMAP_ID char(32) &notnull,
373 XLMAP_RANGE_ID char(32) &notnull,
374 XLMAP_SHEET char(32) &notnull,
375 XLMAP_START char(1000) &notnull,
376 XLMAP_FINISH char(1000)
377);quit;
378proc datasets lib=&lib noprint;
379 modify mpe_xlmap_rules;
380 index create
381 pk_mpe_xlmap_rules=(tx_to xlmap_id xlmap_range_id)
382 /nomiss unique;
383quit;
384
385proc sql;
386create table &lib..mpe_filteranytable(
387 filter_rk num &notnull,
388 filter_hash char(32) &notnull,
389 filter_table char(41) &notnull,
390 processed_dttm num &notnull format=datetime19.
391);quit;
392proc datasets lib=&lib noprint;
393 modify mpe_filteranytable;
394 index create filter_rk /nomiss unique;
395quit;
396proc sql;
397create table &lib..mpe_filtersource(
398 filter_hash char(32) &notnull,
399 filter_line num &notnull,
400 group_logic char(3) &notnull,
401 subgroup_logic char(3) &notnull,
402 subgroup_id num &notnull,
403 variable_nm varchar(32) &notnull,
404 operator_nm varchar(12) &notnull,
405 raw_value varchar(4000) &notnull,
406 processed_dttm num &notnull format=datetime19.
407);quit;
408proc datasets lib=&lib noprint;
409 modify mpe_filtersource;
410 index create
411 pk_mpe_filtersource=(filter_hash filter_line)
412 /nomiss unique;
413quit;
414proc sql;
415create table &lib..mpe_groups(
416 tx_from num &notnull format=datetime19.3,
417 tx_to num &notnull format=datetime19.3,
418 group_name char(100) &notnull,
419 user_name char(50) &notnull,
420 group_desc char(256)
421);quit;
422proc datasets lib=&lib noprint;
423 modify mpe_groups;
424 index create
425 pk_mpe_groups=(tx_to group_name user_name)
426 /nomiss unique;
427quit;
428proc sql;
429create table &lib..mpe_lineage_cols
430 (
431 col_id char(32),
432 direction char(1),
433 sourcecoluri char(256),
434 map_type char(256),
435 map_transform char(256),
436 jobname char(256),
437 sourcetablename char(256),
438 sourcecolname char(256),
439 targettablename char(256),
440 targetcolname char(256),
441 targetcoluri char(256),
442 Derived_Rule char(500),
443 level int,
444 modified_dttm num format=datetime19.3,
445 modified_by char(64)
446);quit;
447proc datasets lib=&lib noprint;
448 modify mpe_lineage_cols;
449 index create
450 pk_mpe_lineage_cols=(col_id direction sourcecoluri targetcoluri map_type map_transform)
451 /nomiss unique;
452quit;
453proc sql;
454create table &lib..MPE_LINEAGE_TABS
455 (
456 tx_from num &notnull format=datetime19.3,
457 jobid char(17),
458 srctableid char(17),
459 tgttableid char(17),
460 jobname char(128),
461 srctabletype char(16),
462 srctablename char(64),
463 srclibref char(8),
464 tgttabletype char(16),
465 tgttablename char(64),
466 tgtlibref char(8),
467 tx_to num &notnull format=datetime19.3
468);quit;
469proc datasets lib=&lib noprint;
470 modify mpe_lineage_tabs;
471 index create
472 pk_mpe_lineage_tabs=(tx_to jobid srctableid tgttableid)
473 /nomiss unique;
474quit;
475proc sql;
476create table &lib..mpe_loads(
477 csv_dir char(255),
478 user_nm char(50) ,
479 status char(15) ,
480 duration num ,
481 processed_dttm num format=datetime19.3,
482 reason_txt char(2048) ,
483 approvals char(64)
484);quit;
485proc datasets lib=&lib noprint;
486 modify mpe_loads;
487 index create csv_dir /nomiss unique;
488quit;
489proc sql;
490create table &lib..mpe_lockanytable(
491 lock_lib varchar(8) &notnull ,
492 lock_ds varchar(32) &notnull,
493 lock_status_cd varchar(10) &notnull,
494 lock_user_nm varchar(100) &notnull ,
495 lock_ref varchar(200),
496 lock_pid varchar(10),
497 lock_start_dttm num format=E8601DT26.6,
498 lock_end_dttm num format=E8601DT26.6
499);quit;
500proc datasets lib=&lib noprint;
501 modify mpe_lockanytable;
502 index create
503 pk_mpe_lockanytable=(lock_lib lock_ds)
504 /nomiss unique;
505quit;
506proc sql;
507create table &lib..mpe_maxkeyvalues(
508 keytable varchar(41) label='Base table in libref.dataset format',
509 keycolumn char(32) format=$32.
510 label='The Surrogate / Retained key field containing the key values.',
511 max_key num label=
512 'Integer value representing current max RK or SK value in the KEYTABLE',
513 processed_dttm num format=E8601DT26.6
514 label='Datetime this value was last updated'
515);quit;
516proc datasets lib=&lib noprint;
517 modify mpe_maxkeyvalues;
518 index create keytable /nomiss unique;
519quit;
520/* no PK defined as it is a transaction table */
521proc sql;
522create table &lib..mpe_requests(
523 request_dttm num &notnull format=datetime19.,
524 request_user char(64) &notnull,
525 request_service char(64) &notnull,
526 request_params char(128)
527 );
528proc sql;
529create table &lib..mpe_review(
530 table_id varchar(32) &notnull,
531 reviewed_by_nm varchar(100) &notnull,
532 base_table varchar(41) &notnull,
533 review_status_id varchar(10) &notnull,
534 reviewed_on_dttm num &notnull format=datetime19.3,
535 review_reason_txt varchar(400)
536);quit;
537proc datasets lib=&lib noprint;
538 modify mpe_review;
539 index create
540 pk_mpe_review=(table_id reviewed_by_nm)
541 /nomiss unique;
542quit;
543proc sql;
544create table &lib..mpe_row_level_security(
545 tx_from num &notnull format=datetime19.3,
546 tx_to num &notnull format=datetime19.3,
547 RLS_RK num &notnull,
548 RLS_SCOPE char(8) &notnull,
549 RLS_GROUP char(128) &notnull,
550 RLS_LIBREF char(8) &notnull,
551 RLS_TABLE char(32) &notnull,
552 RLS_GROUP_LOGIC char(3) &notnull,
553 RLS_SUBGROUP_LOGIC char(3) &notnull,
554 RLS_SUBGROUP_ID num &notnull,
555 RLS_VARIABLE_NM varchar(32) &notnull,
556 RLS_OPERATOR_NM varchar(12) &notnull,
557 RLS_RAW_VALUE varchar(4000) &notnull,
558 RLS_ACTIVE num &notnull
559);quit;
560proc datasets lib=&lib noprint;
561 modify mpe_row_level_security;
562 index create
563 pk_mpe_row_level_security=(tx_to RLS_RK)
564 /nomiss unique;
565quit;
566proc sql;
567create table &lib..mpe_security(
568 tx_from num &notnull format=datetime19.3,
569 tx_to num &notnull format=datetime19.3,
570 libref char(8) &notnull,
571 dsn char(32) &notnull,
572 access_level char(10) &notnull,
573 sas_group char(100) &notnull
574);quit;
575proc datasets lib=&lib noprint;
576 modify mpe_security;
577 index create
578 pk_mpe_security=(tx_to libref dsn access_level sas_group)
579 /nomiss unique;
580quit;
581proc sql;
582create table &lib..mpe_selectbox(
583 ver_from_dttm num &notnull format=datetime19.3,/* timestamp for versioning*/
584 ver_to_dttm num &notnull format=datetime19.3, /* timestamp for versioning */
585 selectbox_rk num &notnull, /* surrogate key */
586 select_lib varchar(17) &notnull, /* libref (big enough for uri)*/
587 select_ds varchar(32) &notnull,
588 base_column varchar(36) &notnull, /* variable name against which to apply selectbox */
589 selectbox_value varchar(500) &notnull, /* selectbox value */
590 selectbox_order num , /* optional ordering (1 comes before 2) */
591 selectbox_type varchar(32) /* column type (blank for default, else
592 sas or js to indicate relevant system functions)*/
593);quit;
594proc datasets lib=&lib noprint;
595 modify mpe_selectbox;
596 index create
597 pk_mpe_selectbox=(ver_to_dttm selectbox_rk)
598 /nomiss unique;
599quit;
600proc sql;
601create table &lib..mpe_signoffs(
602 tech_from_dttm num &notnull format=datetime19.3,
603 tech_to_dttm num &notnull format=datetime19.3,
604 signoff_table varchar(50) &notnull,
605 signoff_section_rk num &notnull,
606 signoff_version_rk num &notnull,
607 signoff_name varchar(100) &notnull
608);quit;
609proc datasets lib=&lib noprint;
610 modify mpe_signoffs;
611 index create
612 pk_mpe_signoffs=(tech_to_dttm signoff_table signoff_section_rk)
613 /nomiss unique;
614quit;
615/* mpe_submit */
616proc sql;
617create table &lib..mpe_submit(
618 table_id varchar(32) &notnull,
619 submit_status_cd varchar(10) &notnull,
620 base_lib char(8) &notnull,
621 base_ds char(32) &notnull,
622 submitted_by_nm varchar(100) &notnull,
623 submitted_on_dttm num &notnull format=datetime19.3,
624 submitted_reason_txt varchar(400),
625 input_obs num,
626 input_vars num,
627 num_of_approvals_required num &notnull ,
628 num_of_approvals_remaining num &notnull ,
629 reviewed_by_nm char(100),
630 reviewed_on_dttm num
631);quit;
632proc datasets lib=&lib noprint;
633 modify mpe_submit;
634 index create table_id /nomiss unique;
635quit;
636proc sql;
637create table &lib..mpe_tables(
638 tx_from num &notnull format=datetime19.3,
639 tx_to num &notnull format=datetime19.3,
640 libref char(8) &notnull,
641 dsn char(32) &notnull,
642 num_of_approvals_required int,
643 loadtype char(12) ,
644 buskey char(1000) ,
645 var_txfrom char(32) ,
646 var_txto char(32) ,
647 var_busfrom char(32) ,
648 var_busto char(32) ,
649 var_processed char(32) ,
650 close_vars varchar(500),
651 pre_edit_hook char(200),
652 post_edit_hook char(200),
653 pre_approve_hook char(200) ,
654 post_approve_hook char(200) ,
655 signoff_cols varchar(500),
656 signoff_hook varchar(200),
657 notes char(1000) ,
658 rk_underlying char(1000) ,
659 audit_libds char(41)
660);quit;
661proc datasets lib=&lib noprint;
662 modify mpe_tables;
663 index create
664 pk_mpe_tables=(tx_to libref dsn)
665 /nomiss unique;
666quit;
667proc sql;
668create table &lib..mpe_users(
669 user_id char(50) &notnull,
670 last_seen_dt num &notnull format=date9.,
671 registered_dt num &notnull format=date9.
672);quit;
673proc datasets lib=&lib noprint;
674 modify mpe_users;
675 index create user_id /nomiss unique;
676quit;
677proc sql;
678create table &lib..MPE_VALIDATIONS
679 (
680 TX_FROM num &notnull format=datetime19.3,
681 BASE_LIB varchar(8),
682 BASE_DS varchar(32),
683 BASE_COL varchar(32),
684 RULE_TYPE varchar(32),
685 RULE_VALUE varchar(128),
686 RULE_ACTIVE num ,
687 TX_TO num &notnull format=datetime19.3
688);quit;
689proc datasets lib=&lib noprint;
690 modify mpe_validations;
691 index create
692 pk_mpe_validations=(tx_from base_lib base_ds base_col rule_type)
693 /nomiss unique;
694quit;
695proc sql;
696create table &lib..mpe_x_test(
697 primary_key_field num &notnull,
698 some_char char(32767) ,
699 some_dropdown char(128),
700 some_num num ,
701 some_date num format=date9.,
702 some_datetime num format=datetime19. informat=ANYDTDTM19.,
703 some_time num format=time8.,
704 some_shortnum num length=4,
705 some_bestnum num format=best.
706);quit;
707proc datasets lib=&lib noprint;
708 modify mpe_x_test;
709 index create primary_key_field /nomiss unique;
710quit;
711
712%mend mpe_makedatamodel;