Production Ready Macros for SAS Application Developers
https://github.com/sasjs/core
mp_getddl.sas
Go to the documentation of this file.
1/**
2 @file mp_getddl.sas
3 @brief Extract DDL in various formats, by table or library
4 @details Data Definition Language relates to a set of SQL instructions used
5 to create tables in SAS or a database. The macro can be used at table or
6 library level. The default behaviour is to create DDL in SAS format.
7
8 Note - views are not currently supported.
9
10 Usage:
11
12 data test(index=(pk=(x y)/unique /nomiss));
13 x=1;
14 y='blah';
15 label x='blah';
16 run;
17 proc sql; describe table &syslast;
18 %mp_getddl(work,test,flavour=tsql,showlog=YES)
19
20 <h4> SAS Macros </h4>
21 @li mf_existfileref.sas
22 @li mf_getvarcount.sas
23 @li mp_getconstraints.sas
24
25 @param lib libref of the library to create DDL for. Should be assigned.
26 @param ds dataset to create ddl for (optional)
27 @param fref= the fileref to which to _append_ the DDL. If it does not exist,
28 it will be created.
29 @param flavour= The type of DDL to create (default=SAS). Supported=TSQL
30 @param showlog= Set to YES to show the DDL in the log
31 @param schema= Choose a preferred schema name (default is to use actual schema
32 ,else libref)
33 @param applydttm= for non SAS DDL, choose if columns are created with native
34 datetime2 format or regular decimal type
35 @version 9.3
36 @author Allan Bowe
37**/
38
39%macro mp_getddl(libref,ds,fref=getddl,flavour=SAS,showlog=NO,schema=
40 ,applydttm=NO
41)/*/STORE SOURCE*/;
42
43/* check fileref is assigned */
44%if %mf_existfileref(&fref)=0 %then %do;
45 filename &fref temp ;
46%end;
47
48%if %length(&libref)=0 %then %let libref=WORK;
49%let flavour=%upcase(&flavour);
50
51proc sql noprint;
52create table _data_ as
53 select * from dictionary.tables
54 where upcase(libname)="%upcase(&libref)"
55 and memtype='DATA' /* views not currently supported */
56 %if %length(&ds)>0 %then %do;
57 and upcase(memname)="%upcase(&ds)"
58 %end;
59 ;
60%local tabinfo; %let tabinfo=&syslast;
61
62create table _data_ as
63 select * from dictionary.columns
64 where upcase(libname)="%upcase(&libref)"
65 %if %length(&ds)>0 %then %do;
66 and upcase(memname)="%upcase(&ds)"
67 %end;
68 ;
69%local colinfo; %let colinfo=&syslast;
70
71%local dsnlist;
72 select distinct upcase(memname) into: dsnlist
73 separated by ' '
74 from &syslast
75;
76
77create table _data_ as
78 select * from dictionary.indexes
79 where upcase(libname)="%upcase(&libref)"
80 %if %length(&ds)>0 %then %do;
81 and upcase(memname)="%upcase(&ds)"
82 %end;
83 order by idxusage, indxname, indxpos
84 ;
85%local idxinfo; %let idxinfo=&syslast;
86
87/* Extract all Primary Key and Unique data constraints */
88%mp_getconstraints(lib=%upcase(&libref),ds=%upcase(&ds),outds=_data_)
89%local colconst; %let colconst=&syslast;
90
91%macro addConst();
92 %global constraints_used;
93 data _null_;
94 length ctype $11 constraint_name_orig $256 constraints_used $5000;
95 set &colconst(
96 where=(table_name="&curds" and constraint_type in ('PRIMARY','UNIQUE'))
97 ) end=last;
98 file &fref mod;
99 by constraint_type constraint_name;
100 retain constraints_used;
101 constraint_name_orig=constraint_name;
102 if upcase(strip(constraint_type)) = 'PRIMARY' then ctype='PRIMARY KEY';
103 else ctype=strip(constraint_type);
104 %if &flavour=TSQL %then %do;
105 column_name=catt('[',column_name,']');
106 constraint_name=catt('[',constraint_name,']');
107 %end;
108 %else %if &flavour=PGSQL %then %do;
109 column_name=catt('"',column_name,'"');
110 constraint_name=catt('"',constraint_name,'"');
111 %end;
112 if first.constraint_name then do;
113 constraints_used = catx(' ', constraints_used, constraint_name_orig);
114 put " ,CONSTRAINT " constraint_name ctype "(" ;
115 put ' ' column_name;
116 end;
117 else put ' ,' column_name;
118 if last.constraint_name then do;
119 put " )";
120 call symput('constraints_used',strip(constraints_used));
121 end;
122 run;
123 %put &=constraints_used;
124%mend addConst;
125
126data _null_;
127 file &fref mod;
128 put "/* DDL generated by &sysuserid on %sysfunc(datetime(),datetime19.) */";
129run;
130
131%local x curds;
132%if &flavour=SAS %then %do;
133 data _null_;
134 file &fref mod;
135 put "/* SAS Flavour DDL for %upcase(&libref).&curds */";
136 put "proc sql;";
137 run;
138 %do x=1 %to %sysfunc(countw(&dsnlist));
139 %let curds=%scan(&dsnlist,&x);
140 data _null_;
141 file &fref mod;
142 length nm lab $1024 typ $20;
143 set &colinfo (where=(upcase(memname)="&curds")) end=last;
144
145 if _n_=1 then do;
146 if memtype='DATA' then do;
147 put "create table &libref..&curds(";
148 end;
149 else do;
150 /* just a placeholder - we filter out views at the top */
151 put "create view &libref..&curds(";
152 end;
153 put " "@@;
154 end;
155 else put " ,"@@;
156 if length(format)>1 then fmt=" format="!!cats(format);
157 if length(label)>1 then
158 lab=" label="!!cats("'",tranwrd(label,"'","''"),"'");
159 if notnull='yes' then notnul=' not null';
160 if type='char' then typ=cats('char(',length,')');
161 else if length ne 8 then typ='num length='!!left(length);
162 else typ='num';
163 put name typ fmt notnul lab;
164 run;
165
166 /* Extra step for data constraints */
167 %addConst()
168
169 data _null_;
170 file &fref mod;
171 put ');';
172 run;
173
174 /* Create Unique Indexes, but only if they were not already defined within
175 the Constraints section. */
176 data _null_;
177 *length ds $128;
178 set &idxinfo(
179 where=(
180 memname="&curds"
181 and unique='yes'
182 and indxname not in (
183 %sysfunc(tranwrd("&constraints_used",%str( ),%str(",")))
184 )
185 )
186 );
187 file &fref mod;
188 by idxusage indxname;
189/* ds=cats(libname,'.',memname); */
190 if first.indxname then do;
191 put 'CREATE UNIQUE INDEX ' indxname "ON &libref..&curds (" ;
192 put ' ' name ;
193 end;
194 else put ' ,' name ;
195 *else put ' ,' name ;
196 if last.indxname then do;
197 put ');';
198 end;
199 run;
200
201/*
202 ods output IntegrityConstraints=ic;
203 proc contents data=testali out2=info;
204 run;
205 */
206 %end;
207%end;
208%else %if &flavour=TSQL %then %do;
209 /* if schema does not exist, set to be same as libref */
210 %local schemaactual;
211 proc sql noprint;
212 select sysvalue into: schemaactual
213 from dictionary.libnames
214 where upcase(libname)="&libref" and engine='SQLSVR';
215 %let schema=%sysfunc(coalescec(&schemaactual,&schema,&libref));
216
217 %do x=1 %to %sysfunc(countw(&dsnlist));
218 %let curds=%scan(&dsnlist,&x);
219 data _null_;
220 file &fref mod;
221 put "/* TSQL Flavour DDL for &schema..&curds */";
222 data _null_;
223 file &fref mod;
224 set &colinfo (where=(upcase(memname)="&curds")) end=last;
225 if _n_=1 then do;
226 if memtype='DATA' then do;
227 put "create table [&schema].[&curds](";
228 end;
229 else do;
230 /* just a placeholder - we filter out views at the top */
231 put "create view [&schema].[&curds](";
232 end;
233 put " "@@;
234 end;
235 else put " ,"@@;
236 format=upcase(format);
237 if 1=0 then; /* dummy if */
238 %if &applydttm=YES %then %do;
239 else if format=:'DATETIME' then fmt='[datetime2](7) ';
240 %end;
241 else if type='num' then fmt='[decimal](18,2)';
242 else if length le 8000 then fmt='[varchar]('!!cats(length)!!')';
243 else fmt=cats('[varchar](max)');
244 if notnull='yes' then notnul=' NOT NULL';
245 put "[" name +(-1) "]" fmt notnul;
246 run;
247
248 /* Extra step for data constraints */
249 %addConst()
250
251 /* Create Unique Indexes, but only if they were not already defined within
252 the Constraints section. */
253 data _null_;
254 *length ds $128;
255 set &idxinfo(
256 where=(
257 memname="&curds"
258 and unique='yes'
259 and indxname not in (
260 %sysfunc(tranwrd("&constraints_used",%str( ),%str(",")))
261 )
262 )
263 );
264 file &fref mod;
265 by idxusage indxname;
266 *ds=cats(libname,'.',memname);
267 if first.indxname then do;
268 /* add nonclustered in case of multiple unique indexes */
269 put ' ,index [' indxname +(-1) '] UNIQUE NONCLUSTERED (';
270 put ' [' name +(-1) ']';
271 end;
272 else put ' ,[' name +(-1) ']';
273 if last.indxname then do;
274 put ' )';
275 end;
276 run;
277
278 data _null_;
279 file &fref mod;
280 put ')';
281 put 'GO';
282 run;
283
284 /* add extended properties for labels */
285 data _null_;
286 file &fref mod;
287 length nm $64 lab $1024;
288 set &colinfo (where=(upcase(memname)="&curds" and label ne '')) end=last;
289 nm=cats("N'",tranwrd(name,"'","''"),"'");
290 lab=cats("N'",tranwrd(label,"'","''"),"'");
291 put ' ';
292 put "EXEC sys.sp_addextendedproperty ";
293 put " @name=N'MS_Description',@value=" lab ;
294 put " ,@level0type=N'SCHEMA',@level0name=N'&schema' ";
295 put " ,@level1type=N'TABLE',@level1name=N'&curds'";
296 put " ,@level2type=N'COLUMN',@level2name=" nm ;
297 if last then put 'GO';
298 run;
299 %end;
300%end;
301%else %if &flavour=PGSQL %then %do;
302 /* if schema does not exist, set to be same as libref */
303 %local schemaactual;
304 proc sql noprint;
305 select sysvalue into: schemaactual
306 from dictionary.libnames
307 where upcase(libname)="&libref" and engine='POSTGRES';
308 %let schema=%sysfunc(coalescec(&schemaactual,&schema,&libref));
309 data _null_;
310 file &fref mod;
311 put "CREATE SCHEMA &schema;";
312 %do x=1 %to %sysfunc(countw(&dsnlist));
313 %let curds=%scan(&dsnlist,&x);
314 %local curdsvarcount;
315 %let curdsvarcount=%mf_getvarcount(&libref..&curds);
316 %if &curdsvarcount>1600 %then %do;
317 data _null_;
318 file &fref mod;
319 put "/* &libref..&curds contains &curdsvarcount vars */";
320 put "/* Postgres cannot create tables with over 1600 vars */";
321 put "/* No DDL will be generated for this table";
322 run;
323 %end;
324 %else %do;
325 data _null_;
326 file &fref mod;
327 put "/* Postgres Flavour DDL for &schema..&curds */";
328 data _null_;
329 file &fref mod;
330 set &colinfo (where=(upcase(memname)="&curds")) end=last;
331 length fmt $32;
332 if _n_=1 then do;
333 if memtype='DATA' then do;
334 put "CREATE TABLE &schema..&curds (";
335 end;
336 else do;
337 /* just a placeholder - we filter out views at the top */
338 put "CREATE VIEW &schema..&curds (";
339 end;
340 put " "@@;
341 end;
342 else put " ,"@@;
343 format=upcase(format);
344 if 1=0 then; /* dummy if */
345 %if &applydttm=YES %then %do;
346 else if format=:'DATETIME' then fmt=' TIMESTAMP ';
347 %end;
348 else if type='num' then fmt=' DOUBLE PRECISION';
349 else fmt='VARCHAR('!!cats(length)!!')';
350 if notnull='yes' then notnul=' NOT NULL';
351 /* quote column names in case they represent reserved words */
352 name2=quote(trim(name));
353 put name2 fmt notnul;
354 run;
355
356 /* Extra step for data constraints */
357 %addConst()
358
359 data _null_;
360 file &fref mod;
361 put ');';
362 run;
363
364 /* Create Unique Indexes, but only if they were not already defined within
365 the Constraints section. */
366 data _null_;
367 *length ds $128;
368 set &idxinfo(
369 where=(
370 memname="&curds"
371 and unique='yes'
372 and indxname not in (
373 %sysfunc(tranwrd("&constraints_used",%str( ),%str(",")))
374 )
375 )
376 );
377 file &fref mod;
378 by idxusage indxname;
379 if first.indxname then do;
380 put 'CREATE UNIQUE INDEX "' indxname +(-1) '" ' "ON &schema..&curds(";
381 put ' "' name +(-1) '"' ;
382 end;
383 else put ' ,"' name +(-1) '"';
384 if last.indxname then do;
385 put ');';
386 end;
387 run;
388 %end;
389 %end;
390%end;
391%if %upcase(&showlog)=YES %then %do;
392 options ps=max;
393 data _null_;
394 infile &fref;
395 input;
396 putlog _infile_;
397 run;
398%end;
399
400%mend mp_getddl;