Macros for SAS Application Developers
https://github.com/sasjs/core
Loading...
Searching...
No Matches
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_);
65data &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);
78run;
79
80/* must use SQL as proc datasets does not support length changes */
81proc sql noprint;
82create 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;