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> SAS Macros </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
37proc sort data=&inds out=&outds;
38 by libref table_name constraint_name;
39run;
40
41data &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;
65run;
66
67%mend mp_createconstraints;