Production Ready 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 
35 proc sort data=&inds out=&outds;
36  by libref table_name constraint_name;
37 run;
38 
39 data &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;
50 run;
51 
52 %mend;