70 lines
1.7 KiB
SAS
70 lines
1.7 KiB
SAS
/**
|
|
@file
|
|
@brief migration script to move from v4 to v5.0 of data controller
|
|
|
|
BREAKING CHANGE - deprecate MPE_APPROVALS and MPE_REVIEW (now MPE_SUBMIT does
|
|
both)
|
|
|
|
<h4> SAS Macros </h4>
|
|
@li mm_assigndirectlib.sas
|
|
|
|
**/
|
|
|
|
%let dclib=YOURDCLIB;
|
|
|
|
libname &dclib "/your/dc/path";
|
|
|
|
/**
|
|
* Change 1
|
|
* New MPE_SUBMIT table
|
|
*/
|
|
proc sql;
|
|
create table &dclib..mpe_submit(
|
|
table_id varchar(32) not null,
|
|
submit_status_cd varchar(10) not null,
|
|
base_lib char(8) not null,
|
|
base_ds char(32) not null,
|
|
submitted_by_nm varchar(100) not null,
|
|
submitted_on_dttm num not null format=datetime19.3,
|
|
submitted_reason_txt varchar(400),
|
|
input_obs num,
|
|
input_vars num,
|
|
num_of_approvals_required num not null,
|
|
num_of_approvals_remaining num not null,
|
|
reviewed_by_nm char(100),
|
|
reviewed_on_dttm num,
|
|
constraint pk_mpe_submit
|
|
primary key(table_id)
|
|
);
|
|
|
|
/* change 2 - col level security */
|
|
%let notnull=not null;
|
|
proc sql;
|
|
create table &dclib..mpe_column_level_security(
|
|
tx_from num ¬null format=datetime19.3,
|
|
tx_to num ¬null format=datetime19.3,
|
|
CLS_SCOPE char(4) ¬null,
|
|
CLS_GROUP char(64) ¬null,
|
|
CLS_LIBREF char(8) ¬null,
|
|
CLS_TABLE char(32) ¬null,
|
|
CLS_VARIABLE_NM char(32) ¬null,
|
|
CLS_ACTIVE num ¬null,
|
|
CLS_HIDE num
|
|
);quit;
|
|
proc datasets lib=&dclib noprint;
|
|
modify mpe_column_level_security;
|
|
index create
|
|
pk_mpe_column_level_security=
|
|
(tx_to CLS_SCOPE CLS_GROUP CLS_LIBREF CLS_TABLE CLS_VARIABLE_NM)
|
|
/nomiss unique;
|
|
quit;
|
|
|
|
/* change 3 - drop helpful_link and add audit_libds */
|
|
|
|
proc sql;
|
|
alter table &dclib..mpe_tables drop helpful_link;
|
|
alter table &dclib..mpe_tables add audit_libds char(41);
|
|
|
|
|
|
/* now update the metadata if running SAS 9 EBI */
|