45 lines
1.3 KiB
SAS
45 lines
1.3 KiB
SAS
/**
|
|
@file
|
|
**/
|
|
libname DC "/YOUR/DC/LIB";
|
|
|
|
proc sql;
|
|
create table backup as select * from dc.mpe_selectbox;
|
|
|
|
/* find the contraint name (need to drop the constraint before the column) */
|
|
proc sql;
|
|
create table CONSTRAINT_COLUMN_USAGE as
|
|
select upcase(column_name) as column_name
|
|
,constraint_name
|
|
from dictionary.CONSTRAINT_COLUMN_USAGE
|
|
where table_catalog='DC' and upcase(table_name)="MPE_SELECTBOX";
|
|
create table TABLE_CONSTRAINTS as
|
|
select *
|
|
from dictionary.TABLE_CONSTRAINTS
|
|
where table_catalog='DC' and upcase(table_name)="MPE_SELECTBOX";
|
|
create table final as select a.column_name
|
|
,b.*
|
|
from CONSTRAINT_COLUMN_USAGE a
|
|
left join TABLE_CONSTRAINTS b
|
|
on a.CONSTRAINT_NAME=b.CONSTRAINT_NAME;
|
|
data _null_;
|
|
set final;
|
|
if upcase(column_name)='BASE_LIBDS' then call symputx('name',CONSTRAINT_NAME);
|
|
run;
|
|
|
|
proc sql;
|
|
alter table dc.mpe_selectbox add select_lib char(17), select_ds char(32);
|
|
update dc.mpe_selectbox
|
|
set select_lib=put(scan(base_libds,1,'.'),$8.)
|
|
, select_ds=put(scan(base_libds,2,'.'),$32.);
|
|
alter table dc.mpe_selectbox drop constraint &name;
|
|
alter table dc.mpe_selectbox drop base_libds;
|
|
|
|
|
|
/**
|
|
THERE IS NOW A MANUAL STEP - open MPE_TABLES
|
|
and change the MPE_SELECTBOX entry in the RK_UNDERLYING column:
|
|
- FROM: BASE_LIBDS BASE_COLUMN SELECTBOX_VALUE
|
|
- TO: SELECT_LIB SELECT_DS BASE_COLUMN SELECTBOX_VALUE
|
|
**/
|