mp_getconstraints.sas
Go to the documentation of this file.
1 /**
2  @file mp_getconstraints.sas
3  @brief Get constraint details at column level
4  @details Useful for capturing constraints before they are dropped / reapplied
5  during an update.
6 
7  proc sql;
8  create table work.example(
9  TX_FROM float format=datetime19.,
10  DD_TYPE char(16),
11  DD_SOURCE char(2048),
12  DD_SHORTDESC char(256),
13  constraint pk primary key(tx_from, dd_type,dd_source),
14  constraint unq unique(tx_from, dd_type),
15  constraint nnn not null(DD_SHORTDESC)
16  );
17 
18  %mp_getconstraints(lib=work,ds=example,outds=work.constraints)
19 
20  @param [in] lib= (WORK) The target library
21  @param [in] ds= The target dataset. Leave blank (default) for all datasets.
22  @param [in] mdebug= (0) Set to 1 to preserve temp tables, print var values etc
23  @param [out] outds= (mp_getconstraints) the output dataset
24 
25  <h4> SAS Macros </h4>
26  @li mf_getuniquename.sas
27  @li mp_dropmembers.sas
28 
29  @version 9.2
30  @author Allan Bowe
31 
32 **/
33 
34 %macro mp_getconstraints(lib=WORK
35  ,ds=
36  ,outds=mp_getconstraints
37  ,mdebug=0
38 )/*/STORE SOURCE*/;
39 
40 %let lib=%upcase(&lib);
41 %let ds=%upcase(&ds);
42 
43 /**
44  * Cater for environments where sashelp.vcncolu is not available
45  */
46 %if %sysfunc(exist(sashelp.vcncolu,view))=0 %then %do;
47  proc sql;
48  create table &outds(
49  libref char(8)
50  ,TABLE_NAME char(32)
51  ,constraint_type char(8) label='Constraint Type'
52  ,constraint_name char(32) label='Constraint Name'
53  ,column_name char(32) label='Column'
54  ,constraint_order num
55  );
56  %return;
57 %end;
58 
59 /**
60  * Neither dictionary tables nor sashelp provides a constraint order column,
61  * however they DO arrive in the correct order. So, create the col.
62  **/
63 %local vw;
64 %let vw=%mf_getuniquename(prefix=mp_getconstraints_vw_);
65 data &vw /view=&vw;
66  set sashelp.vcncolu;
67  where table_catalog="&lib";
68 
69  /* use retain approach to reset the constraint order with each constraint */
70  length tmp $1000;
71  retain tmp;
72  drop tmp;
73  if tmp ne catx('|',table_catalog,table_name,constraint_name) then do;
74  constraint_order=1;
75  end;
76  else constraint_order+1;
77  tmp=catx('|',table_catalog, table_name,constraint_name);
78 run;
79 
80 /* must use SQL as proc datasets does not support length changes */
81 proc sql noprint;
82 create table &outds as
83  select upcase(a.TABLE_CATALOG) as libref
84  ,upcase(a.TABLE_NAME) as TABLE_NAME
85  ,a.constraint_type
86  ,a.constraint_name
87  ,b.column_name
88  ,b.constraint_order
89  from dictionary.TABLE_CONSTRAINTS a
90  left join &vw b
91  on upcase(a.TABLE_CATALOG)=upcase(b.TABLE_CATALOG)
92  and upcase(a.TABLE_NAME)=upcase(b.TABLE_NAME)
93  and a.constraint_name=b.constraint_name
94 /**
95  * We cannot apply this clause to the underlying dictionary table. See:
96  * https://communities.sas.com/t5/SAS-Programming/Unexpected-Where-Clause-behaviour-in-dictionary-TABLE/m-p/771554#M244867
97  * cannot use`where calculated libref="&lib"` either as it will STILL execute
98  * all the underlying constraint queries, causing exception errors in some
99  * cases: https://github.com/sasjs/core/issues/283
100  */
101  where a.TABLE_CATALOG="&lib"
102  %if "&ds" ne "" %then %do;
103  and upcase(a.TABLE_NAME)="&ds"
104  and upcase(b.TABLE_NAME)="&ds"
105  %end;
106  order by libref, table_name, constraint_name, constraint_order
107  ;
108 
109 /* tidy up */
110 %mp_dropmembers(
111  &vw,
112  iftrue=(&mdebug=0)
113 )
114 
115 %mend mp_getconstraints;