Production Ready Macros for SAS Application Developers
https://github.com/sasjs/core
mm_getlibmetadiffs.sas
Go to the documentation of this file.
1/**
2 @file
3 @brief Compares the metadata of a library with the physical tables
4 @details Creates a series of output tables that show the differences between
5 metadata and physical tables.
6 Each output can be created with an optional prefix.
7
8 Credit - Paul Homes
9 https://platformadmin.com/blogs/paul/2012/11/sas-proc-metalib-ods-output
10
11 Usage:
12
13 %* create (and assign) a library for testing purposes ;
14 %mm_createlibrary(
15 libname=My Temp Library,
16 libref=XXTEMPXX,
17 tree=/User Folders/&sysuserid,
18 directory=%sysfunc(pathname(work))
19 )
20
21 %* create some tables;
22 data work.table1 table2 table3;
23 a=1;b='two';c=3;
24 run;
25
26 %* register the tables;
27 proc metalib;
28 omr=(library="My Temp Library");
29 report(type=detail);
30 update_rule (delete);
31 run;
32
33 %* modify the tables;
34 proc sql;
35 drop table table3;
36 alter table table2 drop c;
37 alter table table2 add d num;
38
39 %* run the macro;
40 %mm_getlibmetadiffs(libname=My Temp Library)
41
42 %* delete the library ;
43 %mm_deletelibrary(name=My Temp Library)
44
45 The program will create four output tables, with the following structure (and
46 example data):
47
48 #### &prefix.added
49 |name:$32.|metaID:$17.|SAStabName:$32.|
50 |---|---|---|
51 | | |DATA1|
52
53 #### &prefix.deleted
54 |name:$32.|metaID:$17.|SAStabName:$32.|
55 |---|---|---|
56 |TABLE3|A5XLSNXI.BK0001HO|TABLE3|
57
58 #### &prefix.updated
59 |tabName:$32.|tabMetaID:$17.|SAStabName:$32.|metaName:$32.|metaID:$17.|sasname:$32.|metaType:$16.|change:$64.|
60 |---|---|---|---|---|---|---|---|
61 |TABLE2|A5XLSNXI.BK0001HN|TABLE2|c|A5XLSNXI.BM000MA9|c|Column|Deleted|
62 | | | |d| |d|Column|Added|
63
64 #### &prefix.meta
65 |Label1:$28.|cValue1:$1.|nValue1:D12.3|
66 |---|---|---|
67 |Total tables analyzed|4|4|
68 |Tables to be Updated|1|1|
69 |Tables to be Deleted|1|1|
70 |Tables to be Added|1|1|
71 |Tables matching data source|1|1|
72 |Tables not processed|0|0|
73
74 If you are interested in more functionality like this (checking the health of
75 SAS metadata and your SAS 9 environment) then do contact [Allan Bowe](
76 https://www.linkedin.com/in/allanbowe) for details of our SAS 9 Health Check
77 service.
78
79 Our system scan will perform hundreds of checks to identify common issues,
80 such as dangling metadata, embedded passwords, security issues and more.
81
82 @param [in] libname= the metadata name of the library to be compared
83 @param [out] outlib=(work) The library in which to store the output tables.
84 @param [out] prefix=(metadiff) The prefix for the four tables created.
85
86 @version 9.3
87 @author Allan Bowe
88
89**/
90
91%macro mm_getlibmetadiffs(
92 libname= ,
93 prefix=metadiff,
94 outlib=work
95)/*/STORE SOURCE*/;
96
97 /* create tempds */
98 data;run;
99 %local tempds;
100 %let tempds=&syslast;
101
102 /* save options */
103 proc optsave out=&tempds;
104 run;
105
106 options VALIDVARNAME=ANY VALIDMEMNAME=EXTEND;
107
108 ods output
109 factoid1=&outlib..&prefix.meta
110 updtab=&outlib..&prefix.updated
111 addtab=&outlib..&prefix.added
112 deltab=&outlib..&prefix.deleted
113 ;
114
115 proc metalib;
116 omr=(library="&libname");
117 noexec;
118 report(type=detail);
119 update_rule (delete);
120 run;
121
122 ods output close;
123
124 /* restore options */
125 proc optload data=&tempds;
126 run;
127
128%mend mm_getlibmetadiffs;