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 
30 proc sql;
31 create 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;
39 proc 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;
44 quit;
45 
46 proc sql;
47 create 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;
65 proc 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;
70 quit;
71 
72 proc sql;
73 create 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;
84 proc 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;
90 quit;
91 
92 proc sql;
93 create 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;
102 proc datasets lib=&lib noprint;
103  modify mpe_config;
104  index create
105  pk_mpe_config=(tx_to var_scope var_name)
106  /nomiss unique;
107 quit;
108 proc sql;
109 create 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;
115 proc datasets lib=&lib noprint;
116  modify mpe_datacatalog_CATS;
117  index create
118  pk_mpe_datacatalog_CATS=(tx_to libref memname)
119  /nomiss unique;
120 quit;
121 proc sql;
122 create 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;
134 proc datasets lib=&lib noprint;
135  modify mpe_datacatalog_libs;
136  index create
137  pk_mpe_datacatalog_libs=(libref tx_to)
138  /nomiss unique;
139 quit;
140 proc sql;
141 create 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;
151 proc 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;
156 quit;
157 proc sql;
158 create 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;
172 proc datasets lib=&lib noprint;
173  modify mpe_datacatalog_TABS;
174  index create
175  pk_mpe_datacatalog_TABS=(libref dsn tx_to)
176  /nomiss unique;
177 quit;
178 proc sql;
179 create 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;
195 proc 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;
200 quit;
201 proc sql;
202 create 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;
211 proc datasets lib=&lib noprint;
212  modify mpe_datastatus_CATS;
213  index create
214  pk_mpe_datastatus_cats=(libref memname tx_to)
215  /nomiss unique;
216 quit;
217 proc sql;
218 create 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;
226 proc datasets lib=&lib noprint;
227  modify mpe_datastatus_libs;
228  index create
229  pk_mpe_datastatus_libs=(libref tx_to)
230  /nomiss unique;
231 quit;
232 proc sql;
233 create 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;
244 proc 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;
249 quit;
250 proc sql;
251 create 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;
261 proc datasets lib=&lib noprint;
262  modify mpe_datastatus_tabs;
263  index create
264  pk_mpe_datastatus_tabs=(libref dsn tx_to)
265  /nomiss unique;
266 quit;
267 proc sql;
268 create 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;
280 proc datasets lib=&lib noprint;
281  modify mpe_datadictionary;
282  index create
283  pk_mpe_datadictionary=(tx_to dd_type dd_source)
284  /nomiss unique;
285 quit;
286 proc sql;
287 create 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;
300 proc datasets lib=&lib noprint;
301  modify mpe_dataloads;
302  index create
303  pk_mpe_dataloads=(processed_dttm libref dsn etlsource)
304  /nomiss unique;
305 quit;
306 proc sql;
307 create 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;
314 proc datasets lib=&lib noprint;
315  modify mpe_emails;
316  index create
317  pk_mpe_emails=(tx_to user_name)
318  /nomiss unique;
319 quit;
320 proc sql;
321 create 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;
330 proc 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;
335 quit;
336 
337 proc sql;
338 create 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;
346 proc 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;
351 quit;
352 
353 proc sql;
354 create 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;
361 proc datasets lib=&lib noprint;
362  modify mpe_xlmap_info;
363  index create
364  pk_mpe_xlmap_info=(tx_to xlmap_id)
365  /nomiss unique;
366 quit;
367 
368 proc sql;
369 create 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;
378 proc 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;
383 quit;
384 
385 proc sql;
386 create 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;
392 proc datasets lib=&lib noprint;
393  modify mpe_filteranytable;
394  index create filter_rk /nomiss unique;
395 quit;
396 proc sql;
397 create 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;
408 proc datasets lib=&lib noprint;
409  modify mpe_filtersource;
410  index create
411  pk_mpe_filtersource=(filter_hash filter_line)
412  /nomiss unique;
413 quit;
414 proc sql;
415 create 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;
422 proc datasets lib=&lib noprint;
423  modify mpe_groups;
424  index create
425  pk_mpe_groups=(tx_to group_name user_name)
426  /nomiss unique;
427 quit;
428 proc sql;
429 create 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;
447 proc 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;
452 quit;
453 proc sql;
454 create 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;
469 proc 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;
474 quit;
475 proc sql;
476 create 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;
485 proc datasets lib=&lib noprint;
486  modify mpe_loads;
487  index create csv_dir /nomiss unique;
488 quit;
489 proc sql;
490 create 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;
500 proc datasets lib=&lib noprint;
501  modify mpe_lockanytable;
502  index create
503  pk_mpe_lockanytable=(lock_lib lock_ds)
504  /nomiss unique;
505 quit;
506 proc sql;
507 create 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;
516 proc datasets lib=&lib noprint;
517  modify mpe_maxkeyvalues;
518  index create keytable /nomiss unique;
519 quit;
520 /* no PK defined as it is a transaction table */
521 proc sql;
522 create 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  );
528 proc sql;
529 create 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;
537 proc datasets lib=&lib noprint;
538  modify mpe_review;
539  index create
540  pk_mpe_review=(table_id reviewed_by_nm)
541  /nomiss unique;
542 quit;
543 proc sql;
544 create 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;
560 proc 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;
565 quit;
566 proc sql;
567 create 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;
575 proc 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;
580 quit;
581 proc sql;
582 create 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;
594 proc datasets lib=&lib noprint;
595  modify mpe_selectbox;
596  index create
597  pk_mpe_selectbox=(ver_to_dttm selectbox_rk)
598  /nomiss unique;
599 quit;
600 proc sql;
601 create 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;
609 proc 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;
614 quit;
615 /* mpe_submit */
616 proc sql;
617 create 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;
632 proc datasets lib=&lib noprint;
633  modify mpe_submit;
634  index create table_id /nomiss unique;
635 quit;
636 proc sql;
637 create 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;
661 proc datasets lib=&lib noprint;
662  modify mpe_tables;
663  index create
664  pk_mpe_tables=(tx_to libref dsn)
665  /nomiss unique;
666 quit;
667 proc sql;
668 create 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;
673 proc datasets lib=&lib noprint;
674  modify mpe_users;
675  index create user_id /nomiss unique;
676 quit;
677 proc sql;
678 create 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;
689 proc 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;
694 quit;
695 proc sql;
696 create 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;
707 proc datasets lib=&lib noprint;
708  modify mpe_x_test;
709  index create primary_key_field /nomiss unique;
710 quit;
711 
712 %mend mpe_makedatamodel;