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 [in] inds= (mp_getconstraints)
21  The input table containing constraint info
22  @param [out] outds= (mp_deleteconstraints)
23  Table containing the drop statements (drop_statement column)
24  @param [in] execute= (NO) `YES|NO` - default is NO. To actually drop, use YES.
25 
26 
27  @version 9.2
28  @author Allan Bowe
29 
30 **/
31 
32 %macro mp_deleteconstraints(inds=mp_getconstraints
33  ,outds=mp_deleteconstraints
34  ,execute=NO
35 )/*/STORE SOURCE*/;
36 
37 proc sort data=&inds out=&outds;
38  by libref table_name constraint_name;
39 run;
40 
41 data &outds;
42  set &outds;
43  by libref table_name constraint_name;
44  length drop_statement $500;
45  if _n_=1 and "&execute"="YES" then call execute('proc sql;');
46  if first.constraint_name then do;
47  drop_statement=catx(" ","alter table",libref,".",table_name
48  ,"drop constraint",constraint_name,";");
49  output;
50  if "&execute"="YES" then call execute(drop_statement);
51  end;
52 run;
53 
54 %mend mp_deleteconstraints;