Production Ready Macros for SAS Application Developers
https://github.com/sasjs/core
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 lib= The target library (default=WORK)
21 @param ds= The target dataset. Leave blank (default) for all datasets.
22 @param outds the output dataset
23
24 <h4> SAS Macros </h4>
25
26 @version 9.2
27 @author Allan Bowe
28
29**/
30
31%macro mp_getconstraints(lib=WORK
32 ,ds=
33 ,outds=mp_getconstraints
34)/*/STORE SOURCE*/;
35
36%let lib=%upcase(&lib);
37%let ds=%upcase(&ds);
38
39/* must use SQL as proc datasets does not support length changes */
40proc sql noprint;
41create table &outds as
42 select upcase(a.TABLE_CATALOG) as libref
43 ,upcase(a.TABLE_NAME) as TABLE_NAME
44 ,a.constraint_type
45 ,a.constraint_name
46 ,b.column_name
47 from dictionary.TABLE_CONSTRAINTS a
48 left join dictionary.constraint_column_usage b
49 on upcase(a.TABLE_CATALOG)=upcase(b.TABLE_CATALOG)
50 and upcase(a.TABLE_NAME)=upcase(b.TABLE_NAME)
51 and a.constraint_name=b.constraint_name
52/**
53 * We cannot apply this clause to the underlying dictionary table. See:
54 * https://communities.sas.com/t5/SAS-Programming/Unexpected-Where-Clause-behaviour-in-dictionary-TABLE/m-p/771554#M244867
55 */
56 where calculated libref="&lib"
57 %if "&ds" ne "" %then %do;
58 and upcase(a.TABLE_NAME)="&ds"
59 and upcase(b.TABLE_NAME)="&ds"
60 %end;
61 ;
62
63%mend mp_getconstraints;