35 %macro mp_getdbml(liblist=SASHELP,outref=getdbml,showlog=NO
39 %
if %sysfunc(fileref(&outref)) > 0 %then %
do;
40 filename &outref temp;
43 %let liblist=%upcase(&liblist);
46 create 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;
52 create 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;
59 select distinct upcase(cats(libname,
'.',memname)) into: dsnlist
64 create 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;
71 %mp_getconstraints(lib=%scan(&liblist,1),outds=_data_)
72 %local colconst; %let colconst=&syslast;
74 %
do x=2 %to %sysfunc(countw(&liblist));
75 %mp_getconstraints(lib=%scan(&liblist,&x),outds=_data_)
76 proc append base=&colconst data=&syslast;
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'";
98 if first.libname then put
"TableGroup " libname
"{";
99 ds=quote(cats(libname,
'.',memname));
101 if last.libname then put
"}";
106 length curds
const col $39;
107 call missing (of _all_);
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=;
119 length lab $1024 typ $20;
120 set &colinfo (where=(
121 libname=
"%scan(&curds,1,.)" and upcase(memname)=
"%scan(&curds,2,.)"
125 table='Table "'!!"&curds"!!'"{
';
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,')
');
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 put ' ' name typ '[
' notnul ']
';
137 else if notnull='no
' then put ' ' name typ '[
' lab ']
';
138 else put ' ' name typ '[
' notnul ',
' lab ']
';
142 data _data_(keep=curds const col);
143 length ctype $11 cols constraints_used $5000;
144 set &colconst (where=(
145 upcase(libref)="%scan(&curds,1,.)"
146 and upcase(table_name)="%scan(&curds,2,.)"
147 and constraint_type in ('PRIMARY
','UNIQUE
')
150 by constraint_type constraint_name;
152 column_name=upcase(column_name);
154 if _n_=1 then put / ' indexes {
';
156 if upcase(strip(constraint_type)) = 'PRIMARY
' then ctype='[pk]
';
157 else ctype='[unique]
';
159 if first.constraint_name then cols = cats('(
',column_name);
160 else cols=cats(cols,',
',column_name);
162 if last.constraint_name then do;
163 cols=cats(cols,')
',ctype)!!'
165 constraints_used=catx(
' ',constraints_used, constraint_name);
166 call symputx(
'constcheck',1);
169 if last then call symputx(
'constraints_used',cats(upcase(constraints_used)));
171 length curds
const col $39;
173 const=constraint_name;
177 proc append base=&pkds data=&syslast;run;
180 data _data_(keep=curds
const col);
181 set &idxinfo (where=(
182 libname=
"%scan(&curds,1,.)"
183 and upcase(memname)=
"%scan(&curds,2,.)"
185 and upcase(indxname) not in (%mf_getquotedstr(&constraints_used))
188 by idxusage indxname;
190 if &constcheck=1 then stop;
191 if _n_=1 and &constcheck=0 then put /
' indexes {';
195 if first.indxname then cols = cats(
'(',name);
196 else cols=cats(cols,
',',name);
198 if last.indxname then
do;
199 cols=cats(cols,
')[unique]')!!
' //'!!indxname;
201 call symputx(
'constcheck',1);
204 length curds
const col $39;
209 proc append base=&pkds data=&syslast;run;
213 if &constcheck =1 then put
' }';
224 proc sort data=&pkds nodupkey;
228 data &pkds.1 (keep=curds col)
229 &pkds.2 (keep=curds cols);
232 length retconst $39 cols $5000;
233 retain retconst cols;
234 if first.curds then
do;
238 else cols=catx(
' ',cols,upcase(col));
239 if retconst=
const then
do;
241 if last.const then output &pkds.2;
246 %
do x=1 %to %sysfunc(countw(&dsnlist,%str( )));
247 %let curds=%scan(&dsnlist,&x,%str( ));
251 set &pkds.2(where=(curds=
"&curds"));
252 call symputx(
'pkcols',cols);
254 %
if &pkcols ne 0 %then %
do;
255 %let curdslist=&curdslist,
"&curds";
260 set &pkds.2(where=(cols=
"&pkcols" and curds not in (&curdslist)));
261 line=
'Ref: "'!!
"&curds"
262 !!cats(
'".(',
"%mf_getquotedstr(&pkcols,dlm=%str(,),quote=%str( ))",
')')
264 !!cats(quote(trim(curds)),
'.(',
"%mf_getquotedstr(&pkcols,dlm=%str(,),quote=%str( ))",
')');
271 set &pkds.1(where=(curds=
"&curds"));
275 create table &pkds.5a as
276 select upcase(cats(b.libname,
'.',b.memname)) as curds
279 inner join &colinfo b
280 on a.col=upcase(b.name);
282 create table &pkds.5b as
283 select curds,count(*) as cnt
285 where curds not in (select curds from &pkds.2 where cols="&pkcols")
286 and curds ne "&curds"
288 create table &pkds.6 as
298 colcnt=%sysfunc(countw(&pkcols));
299 if cnt=colcnt then do;
301 line='Ref: "'!!"&curds"
303 !!"%mf_getquotedstr(&pkcols,dlm=%str(,),quote=%str( ))"
305 !!cats(quote(trim(curds))
307 ,"%mf_getquotedstr(&pkcols,dlm=%str(,),quote=%str( ))"
317 %if %upcase(&showlog)=YES %then %do;