99 lines
2.7 KiB
SAS
99 lines
2.7 KiB
SAS
/**
|
|
@file
|
|
@brief migration script to move from v3.13 to v4.0 of data controller
|
|
|
|
BREAKING CHANGE - deprecate filter_text
|
|
|
|
<h4> SAS Macros </h4>
|
|
@li mm_assigndirectlib.sas
|
|
|
|
**/
|
|
|
|
filename mc url "https://raw.githubusercontent.com/sasjs/core/main/all.sas";
|
|
%inc mc;
|
|
|
|
%let dclib=YOURDCLIB;
|
|
|
|
%mm_assigndirectlib(&dclib)
|
|
|
|
/**
|
|
* Change 1
|
|
* New audit table
|
|
*/
|
|
proc sql;
|
|
create table &dclib..mpe_audit(
|
|
load_ref char(36) label='unique load reference',
|
|
processed_dttm num format=E8601DT26.6 label='Processed at timestamp',
|
|
libref char(8) label='Library Reference (8 chars)',
|
|
dsn char(32) label='Dataset Name (32 chars)',
|
|
key_hash char(32) label=
|
|
'MD5 Hash of primary key values (pipe seperated)',
|
|
move_type char(1) label='Either (A)ppended, (D)eleted or (M)odified',
|
|
is_pk num label='Is Primary Key Field? (1/0)',
|
|
is_diff num label=
|
|
'Did value change? (1/0/-1). Always -1 for appends and deletes.',
|
|
tgtvar_type char(1) label='Either (C)haracter or (N)umeric',
|
|
tgtvar_nm char(32) label='Target variable name (32 chars)',
|
|
oldval_num num label='Old (numeric) value',
|
|
newval_num num label='New (numeric) value',
|
|
oldval_char char(32765) label='Old (character) value',
|
|
newval_char char(32765) label='New (character) value',
|
|
constraint pk_mpe_audit
|
|
primary key(load_ref,libref,dsn,key_hash,tgtvar_nm)
|
|
);
|
|
|
|
|
|
|
|
/**
|
|
* Change 2
|
|
* Filter_text is from the old filtering mechanism where the filter string
|
|
* would be stored in a single variable
|
|
*/
|
|
/* backup old filters */
|
|
proc sql;
|
|
create table &dclib..bkp_filters as
|
|
select * from &dclib..mpe_filteranytable
|
|
where filter_text ne 'N/A';
|
|
|
|
/* create new table without the new column */
|
|
|
|
create table work.mpe_filteranytable(
|
|
filter_rk num not null,
|
|
filter_hash char(32) not null,
|
|
filter_table char(41) not null,
|
|
processed_dttm num not null format=datetime19.,
|
|
constraint pk_mpe_filteranytable
|
|
primary key(filter_rk));
|
|
|
|
/* copy the old data across */
|
|
insert into work.mpe_filteranytable
|
|
select filter_rk, filter_hash, filter_table, processed_dttm
|
|
from &dclib..mpe_filteranytable
|
|
where filter_text ='N/A';
|
|
|
|
/* drop the old table */
|
|
proc delete data=&dclib..mpe_filteranytable;
|
|
run;
|
|
|
|
|
|
/* copy across the new one */
|
|
proc append base=&dclib..mpe_filteranytable
|
|
data=work.mpe_filteranytable;
|
|
run;
|
|
|
|
/* add new loadtype */
|
|
proc sql noprint;
|
|
select max(selectbox_rk) into: maxrk
|
|
from &dclib..mpe_selectbox;
|
|
insert into &dclib..mpe_selectbox set
|
|
selectbox_rk=&maxrk+1
|
|
,ver_from_dttm=0
|
|
,select_lib="&dclib"
|
|
,select_ds="MPE_TABLES"
|
|
,base_column="LOADTYPE"
|
|
,selectbox_value='FORMAT_CAT'
|
|
,selectbox_order=5
|
|
,ver_to_dttm='31DEC5999:23:59:59'dt;
|
|
|
|
/* now update the metadata to match with the removed column */
|