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