Production Ready Macros for SAS Application Developers
https://github.com/sasjs/core
mp_getdbml.sas
Go to the documentation of this file.
1/**
2 @file
3 @brief Extract DBML from SAS Libraries
4 @details DBML is an open source markup format to represent databases.
5 More details: https://www.dbml.org/home/
6
7 Usage:
8
9
10 %mp_getdbml(liblist=SASHELP WORK,outref=mydbml,showlog=YES)
11
12 Take the log output and paste it into the renderer at https://dbdiagram.io
13 to view your data model diagram. The code takes a "best guess" at
14 the one to one and one to many relationships (based on constraints
15 and indexes, and assuming that the column names would match).
16
17 You may need to adjust the rendered DBML to suit your needs.
18
19 ![dbml for sas](https://i.imgur.com/8T1tIZp.gif)
20
21
22 <h4> SAS Macros </h4>
23 @li mf_getquotedstr.sas
24 @li mp_getconstraints.sas
25
26 @param liblist= Space seperated list of librefs to take as
27 input (Default=SASHELP)
28 @param outref= Fileref to contain the DBML (Default=getdbml)
29 @param showlog= set to YES to show the DBML in the log (Default is NO)
30
31 @version 9.3
32 @author Allan Bowe
33**/
34
35%macro mp_getdbml(liblist=SASHELP,outref=getdbml,showlog=NO
36)/*/STORE SOURCE*/;
37
38/* check fileref is assigned */
39%if %sysfunc(fileref(&outref)) > 0 %then %do;
40 filename &outref temp;
41%end;
42
43%let liblist=%upcase(&liblist);
44
45proc sql noprint;
46create table _data_ as
47 select * from dictionary.tables
48 where upcase(libname) in (%mf_getquotedstr(&liblist))
49 order by libname,memname;
50%local tabinfo; %let tabinfo=&syslast;
51
52create table _data_ as
53 select * from dictionary.columns
54 where upcase(libname) in (%mf_getquotedstr(&liblist))
55 order by libname,memname,varnum;
56%local colinfo; %let colinfo=&syslast;
57
58%local dsnlist;
59 select distinct upcase(cats(libname,'.',memname)) into: dsnlist
60 separated by ' '
61 from &syslast
62;
63
64create table _data_ as
65 select * from dictionary.indexes
66 where upcase(libname) in (%mf_getquotedstr(&liblist))
67 order by idxusage, indxname, indxpos;
68%local idxinfo; %let idxinfo=&syslast;
69
70/* Extract all Primary Key and Unique data constraints */
71%mp_getconstraints(lib=%scan(&liblist,1),outds=_data_)
72%local colconst; %let colconst=&syslast;
73
74%do x=2 %to %sysfunc(countw(&liblist));
75 %mp_getconstraints(lib=%scan(&liblist,&x),outds=_data_)
76 proc append base=&colconst data=&syslast;
77 run;
78%end;
79
80
81
82
83/* header info */
84data _null_;
85 file &outref;
86 put "// DBML generated by &sysuserid on %sysfunc(datetime(),datetime19.) ";
87 put "Project sasdbml {";
88 put " database_type: 'SAS'";
89 put " Note: 'Generated by the mp_getdbml() macro'";
90 put "}";
91run;
92
93/* create table groups */
94data _null_;
95 file &outref mod;
96 set &tabinfo;
97 by libname;
98 if first.libname then put "TableGroup " libname "{";
99 ds=quote(cats(libname,'.',memname));
100 put ' ' ds;
101 if last.libname then put "}";
102run;
103
104/* table for pks */
105data _data_;
106 length curds const col $39;
107 call missing (of _all_);
108 stop;
109run;
110%let pkds=&syslast;
111
112%local x curds constraints_used constcheck;
113%do x=1 %to %sysfunc(countw(&dsnlist,%str( )));
114 %let curds=%scan(&dsnlist,&x,%str( ));
115 %let constraints_used=;
116 %let constcheck=0;
117 data _null_;
118 file &outref mod;
119 length lab $1024 typ $20;
120 set &colinfo (where=(
121 libname="%scan(&curds,1,.)" and upcase(memname)="%scan(&curds,2,.)"
122 )) end=last;
123
124 if _n_=1 then do;
125 table='Table "'!!"&curds"!!'"{';
126 put table;
127 end;
128 name=upcase(name);
129 lab=" note:"!!quote(trim(tranwrd(label,'"',"'")));
130 if upcase(format)=:'DATETIME' then typ='datetime';
131 else if type='char' then typ=cats('char(',length,')');
132 else typ='num';
133
134 if notnull='yes' then notnul=' not null';
135 if notnull='no' and missing(label) then put ' ' name typ;
136 else if notnull='yes' and missing(label) then do;
137 put ' ' name typ '[' notnul ']';
138 end;
139 else if notnull='no' then put ' ' name typ '[' lab ']';
140 else put ' ' name typ '[' notnul ',' lab ']';
141
142 run;
143
144 data _data_(keep=curds const col);
145 length ctype $11 cols constraints_used $5000;
146 set &colconst (where=(
147 upcase(libref)="%scan(&curds,1,.)"
148 and upcase(table_name)="%scan(&curds,2,.)"
149 and constraint_type in ('PRIMARY','UNIQUE')
150 )) end=last;
151 file &outref mod;
152 by constraint_type constraint_name;
153 retain cols;
154 column_name=upcase(column_name);
155
156 if _n_=1 then put / ' indexes {';
157
158 if upcase(strip(constraint_type)) = 'PRIMARY' then ctype='[pk]';
159 else ctype='[unique]';
160
161 if first.constraint_name then cols = cats('(',column_name);
162 else cols=cats(cols,',',column_name);
163
164 if last.constraint_name then do;
165 cols=cats(cols,')',ctype)!!' //'!!constraint_name;
166 put ' ' cols;
167 constraints_used=catx(' ',constraints_used, constraint_name);
168 call symputx('constcheck',1);
169 end;
170
171 if last then call symput('constraints_used',cats(upcase(constraints_used)));
172
173 length curds const col $39;
174 curds="&curds";
175 const=constraint_name;
176 col=column_name;
177 run;
178
179 proc append base=&pkds data=&syslast;run;
180
181 /* Create Unique Indexes, but only if they were not already defined within
182 the Constraints section. */
183 data _data_(keep=curds const col);
184 set &idxinfo (where=(
185 libname="%scan(&curds,1,.)"
186 and upcase(memname)="%scan(&curds,2,.)"
187 and unique='yes'
188 and upcase(indxname) not in (%mf_getquotedstr(&constraints_used))
189 ));
190 file &outref mod;
191 by idxusage indxname;
192 name=upcase(name);
193 if &constcheck=1 then stop; /* we only care about PKs so stop if we have */
194 if _n_=1 and &constcheck=0 then put / ' indexes {';
195
196 length cols $5000;
197 retain cols;
198 if first.indxname then cols = cats('(',name);
199 else cols=cats(cols,',',name);
200
201 if last.indxname then do;
202 cols=cats(cols,')[unique]')!!' //'!!indxname;
203 put ' ' cols;
204 call symputx('constcheck',1);
205 end;
206
207 length curds const col $39;
208 curds="&curds";
209 const=indxname;
210 col=name;
211 run;
212 proc append base=&pkds data=&syslast;run;
213
214 data _null_;
215 file &outref mod;
216 if &constcheck =1 then put ' }';
217 put '}';
218 run;
219
220%end;
221
222/**
223 * now we need to figure out the relationships
224 */
225
226/* sort alphabetically so we can have one set of unique cols per table */
227proc sort data=&pkds nodupkey;
228 by curds const col;
229run;
230
231data &pkds.1 (keep=curds col)
232 &pkds.2 (keep=curds cols);
233 set &pkds;
234 by curds const;
235 length retconst $39 cols $5000;
236 retain retconst cols;
237 if first.curds then do;
238 retconst=const;
239 cols=upcase(col);
240 end;
241 else cols=catx(' ',cols,upcase(col));
242 if retconst=const then do;
243 output &pkds.1;
244 if last.const then output &pkds.2;
245 end;
246run;
247
248%let curdslist="0";
249%do x=1 %to %sysfunc(countw(&dsnlist,%str( )));
250 %let curds=%scan(&dsnlist,&x,%str( ));
251
252 %let pkcols=0;
253 data _null_;
254 set &pkds.2(where=(curds="&curds"));
255 call symputx('pkcols',cols);
256 run;
257 %if &pkcols ne 0 %then %do;
258 %let curdslist=&curdslist,"&curds";
259
260 /* start with one2one */
261 data &pkds.4;
262 file &outref mod;
263 set &pkds.2(where=(cols="&pkcols" and curds not in (&curdslist)));
264 line='Ref: "'!!"&curds"
265 !!cats('".(',"%mf_getquotedstr(&pkcols,dlm=%str(,),quote=%str( ))",')')
266 !!' - '
267 !!cats(quote(trim(curds))
268 ,'.('
269 ,"%mf_getquotedstr(&pkcols,dlm=%str(,),quote=%str( ))"
270 ,')'
271 );
272 put line;
273 run;
274
275 /* now many2one */
276 /* get table with one row per col */
277 data &pkds.5;
278 set &pkds.1(where=(curds="&curds"));
279 run;
280 /* get tables which contain the PK columns */
281 proc sql;
282 create table &pkds.5a as
283 select upcase(cats(b.libname,'.',b.memname)) as curds
284 ,b.name
285 from &pkds.5 a
286 inner join &colinfo b
287 on a.col=upcase(b.name);
288 /* count to make sure those tables contain ALL the columns */
289 create table &pkds.5b as
290 select curds,count(*) as cnt
291 from &pkds.5a
292 where curds not in (
293 select curds from &pkds.2 where cols="&pkcols"
294 ) /* not a one to one match */
295 and curds ne "&curds" /* exclude self */
296 group by 1;
297 create table &pkds.6 as
298 select a.*
299 ,b.cols
300 from &pkds.5b a
301 left join &pkds.4 b
302 on a.curds=b.curds;
303
304 data _null_;
305 set &pkds.6;
306 file &outref mod;
307 colcnt=%sysfunc(countw(&pkcols));
308 if cnt=colcnt then do;
309 /* table contains all the PK cols, and was not a direct / 121 match */
310 line='Ref: "'!!"&curds"
311 !!'".('
312 !!"%mf_getquotedstr(&pkcols,dlm=%str(,),quote=%str( ))"
313 !!') > '
314 !!cats(quote(trim(curds))
315 ,'.('
316 ,"%mf_getquotedstr(&pkcols,dlm=%str(,),quote=%str( ))"
317 ,')'
318 );
319 put line;
320 end;
321 run;
322 %end;
323%end;
324
325
326%if %upcase(&showlog)=YES %then %do;
327 options ps=max;
328 data _null_;
329 infile &outref;
330 input;
331 putlog _infile_;
332 run;
333%end;
334
335%mend mp_getdbml;