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