Production Ready Macros for SAS Application Developers
https://github.com/sasjs/core
mp_createconstraints.sas
Go to the documentation of this file.
1 /**
2  @file mp_createconstraints.sas
3  @brief Creates constraints
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  %mp_createconstraints(inds=work.constraints,outds=created,execute=YES)
20 
21  @param inds= The input table containing the constraint info
22  @param outds= a table containing the create statements (create_statement column)
23  @param execute= `YES|NO` - default is NO. To actually create, use YES.
24 
25  <h4> Dependencies </h4>
26 
27  @version 9.2
28  @author Allan Bowe
29 
30 **/
31 
32 %macro mp_createconstraints(inds=mp_getconstraints
33  ,outds=mp_createconstraints
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 create_statement $500;
45  if _n_=1 and "&execute"="YES" then call execute('proc sql;');
46  if first.constraint_name then do;
47  if constraint_type='PRIMARY' then type='PRIMARY KEY';
48  else type=constraint_type;
49  create_statement=catx(" ","alter table",libref,".",table_name
50  ,"add constraint",constraint_name,type,"(");
51  if last.constraint_name then
52  create_statement=cats(create_statement,column_name,");");
53  else create_statement=cats(create_statement,column_name,",");
54  if "&execute"="YES" then call execute(create_statement);
55  end;
56  else if last.constraint_name then do;
57  create_statement=cats(column_name,");");
58  if "&execute"="YES" then call execute(create_statement);
59  end;
60  else do;
61  create_statement=cats(column_name,",");
62  if "&execute"="YES" then call execute(create_statement);
63  end;
64  output;
65 run;
66 
67 %mend;