Macros for SAS Application Developers
https://github.com/sasjs/core
mp_deleteconstraints.sas
Go to the documentation of this file.
1/**
2 @file mp_deleteconstraints.sas
3 @brief Delete constraionts
4 @details Takes the output from mp_getconstraints.sas as input
5
6 proc sql;
7 create table work.example(
8 TX_FROM float format=datetime19.,
9 DD_TYPE char(16),
10 DD_SOURCE char(2048),
11 DD_SHORTDESC char(256),
12 constraint pk primary key(tx_from, dd_type,dd_source),
13 constraint unq unique(tx_from, dd_type),
14 constraint nnn not null(DD_SHORTDESC)
15 );
16
17 %mp_getconstraints(lib=work,ds=example,outds=work.constraints)
18 %mp_deleteconstraints(inds=work.constraints,outds=dropped,execute=YES)
19
20 @param inds= The input table containing the constraint info
21 @param outds= a table containing the drop statements (drop_statement column)
22 @param execute= `YES|NO` - default is NO. To actually drop, use YES.
23
24
25 @version 9.2
26 @author Allan Bowe
27
28**/
29
30%macro mp_deleteconstraints(inds=mp_getconstraints
31 ,outds=mp_deleteconstraints
32 ,execute=NO
33)/*/STORE SOURCE*/;
34
35proc sort data=&inds out=&outds;
36 by libref table_name constraint_name;
37run;
38
39data &outds;
40 set &outds;
41 by libref table_name constraint_name;
42 length drop_statement $500;
43 if _n_=1 and "&execute"="YES" then call execute('proc sql;');
44 if first.constraint_name then do;
45 drop_statement=catx(" ","alter table",libref,".",table_name
46 ,"drop constraint",constraint_name,";");
47 output;
48 if "&execute"="YES" then call execute(drop_statement);
49 end;
50run;
51
52%mend mp_deleteconstraints;