mp_ds2ddl.sas

Go to the documentation of this file.

/**
  @file mp_ds2ddl.sas
  @brief Extract DDL in various formats, by table or library
  @details Data Definition Language relates to a set of SQL instructions used
    to create tables in SAS or a database.  The macro can be used at table or
    library level.  The default behaviour is to create DDL in SAS format.
 
    Note - views are not currently supported.
 
  Usage:
 
      data test(index=(pk=(x y)/unique /nomiss));
        x=1;
        y='blah';
        label x='blah';
      run;
      proc sql; describe table &syslast;
      %mp_ds2ddl(work,test,flavour=tsql,showlog=YES)
 
  <h4> SAS Macros </h4>
  @li mf_existfileref.sas
  @li mf_getvarcount.sas
  @li mp_getconstraints.sas
 
  @param [in] libref Libref of the library to create DDL for. Should already
    be assigned.
  @param [in] ds dataset to create ddl for (optional)
  @param [in] fref= (getddl) the fileref to which to _append_ the DDL.  If it
    does not exist, it will be created.
  @param [in] flavour= (SAS) The type of DDL to create. Options:
    @li SAS
    @li TSQL
    @li PGSQL
 
  @param [in]showlog= (YES) Set to NO to prevent the DDL showing in the log
  @param [in] schema= () Choose a preferred schema name (default is to use
    actual schema, else libref)
  @param [in] applydttm= (NO) For non SAS DDL, choose if columns are created
    with native datetime2 format or regular decimal type
 
  @version 9.3
  @author Allan Bowe
  @sasjslint {"noNestedMacros": false}
 
**/
 
%macro mp_ds2ddl(libref,ds,fref=getddl,flavour=SAS,showlog=YES,schema=
  ,applydttm=NO
)/*/STORE SOURCE*/;
 
/* check fileref is assigned */
%if %mf_existfileref(&fref)=0 %then %do;
  filename &fref temp ;
%end;
 
%if %length(&libref)=0 %then %let libref=WORK;
%let flavour=%upcase(&flavour);
 
proc sql noprint;
create table _data_ as
  select * from dictionary.tables
  where upcase(libname)="%upcase(&libref)"
    and memtype='DATA' /* views not currently supported */
  %if %length(&ds)>0 %then %do;
    and upcase(memname)="%upcase(&ds)"
  %end;
  ;
%local tabinfo; %let tabinfo=&syslast;
 
create table _data_ as
  select * from dictionary.columns
  where upcase(libname)="%upcase(&libref)"
  %if %length(&ds)>0 %then %do;
    and upcase(memname)="%upcase(&ds)"
  %end;
  ;
%local colinfo; %let colinfo=&syslast;
 
%local dsnlist;
  select distinct upcase(memname) into: dsnlist
  separated by ' '
  from &syslast
;
 
create table _data_ as
  select * from dictionary.indexes
  where upcase(libname)="%upcase(&libref)"
  %if %length(&ds)>0 %then %do;
    and upcase(memname)="%upcase(&ds)"
  %end;
  order by idxusage, indxname, indxpos
  ;
%local idxinfo; %let idxinfo=&syslast;
 
/* Extract all Primary Key and Unique data constraints */
%mp_getconstraints(lib=%upcase(&libref),ds=%upcase(&ds),outds=_data_)
%local colconst; %let colconst=&syslast;
 
%local constraints_used;
%macro addConst();
  data _null_;
    length ctype $11 constraint_name_orig $256 constraints_used $5000;
    set &colconst(
        where=(table_name="&curds" and constraint_type in ('PRIMARY','UNIQUE'))
      ) end=last;
    file &fref mod;
    by constraint_type constraint_name;
    retain constraints_used;
    constraint_name_orig=constraint_name;
    if upcase(strip(constraint_type)) = 'PRIMARY' then ctype='PRIMARY KEY';
    else ctype=strip(constraint_type);
    %if &flavour=TSQL %then %do;
      column_name=catt('[',column_name,']');
      constraint_name=catt('[',constraint_name,']');
    %end;
    %else %if &flavour=PGSQL %then %do;
      column_name=catt('"',column_name,'"');
      constraint_name=catt('"',constraint_name,'"');
    %end;
    if first.constraint_name then do;
      constraints_used = catx(' ', constraints_used, constraint_name_orig);
      put "   ,CONSTRAINT " constraint_name ctype "(" ;
      put '     ' column_name;
    end;
  else put '     ,' column_name;
  if last.constraint_name then do;
    put "   )";
    call symput('constraints_used',strip(constraints_used));
  end;
  run;
  %put &=constraints_used;
%mend addConst;
 
data _null_;
  file &fref mod;
  put "/* DDL generated by &sysuserid on %sysfunc(datetime(),datetime19.) */";
run;
 
%local x curds;
%if &flavour=SAS %then %do;
  %do x=1 %to %sysfunc(countw(&dsnlist));
    %let curds=%scan(&dsnlist,&x);
    data _null_;
      file &fref mod;
      put "/* SAS Flavour DDL for %upcase(&libref).&curds */";
      put "proc sql;";
    run;
    data _null_;
      file &fref mod;
      length lab $1024 typ $20;
      set &colinfo (where=(upcase(memname)="&curds")) end=last;
 
      if _n_=1 then do;
        if memtype='DATA' then do;
          put "create table &libref..&curds(";
        end;
        else do;
          /* just a placeholder - we filter out views at the top */
          put "create view &libref..&curds(";
        end;
        put "    "@@;
      end;
      else put "   ,"@@;
      if length(format)>1 then fmt=" format="!!cats(format);
      if length(label)>1 then
        lab=" label="!!cats("'",tranwrd(label,"'","''"),"'");
      if notnull='yes' then notnul=' not null';
      if type='char' then typ=cats('char(',length,')');
      else if length ne 8 then typ='num length='!!cats(length);
      else typ='num';
      put name typ fmt notnul lab;
    run;
 
    /* Extra step for data constraints */
    %addConst()
 
    data _null_;
      file &fref mod;
      put ');';
    run;
 
    /* Create Unique Indexes, but only if they were not already defined within
      the Constraints section. */
    data _null_;
      /*length ds $128;*/
      set &idxinfo(
        where=(
          memname="&curds"
          and unique='yes'
          and indxname not in (
              %sysfunc(tranwrd("&constraints_used",%str( ),%str(",")))
              )
          )
        );
      file &fref mod;
      by idxusage indxname;
/*       ds=cats(libname,'.',memname); */
      if first.indxname then do;
          put 'CREATE UNIQUE INDEX ' indxname "ON &libref..&curds (" ;
          put '  ' name ;
      end;
      else put '  ,' name ;
      /*else put '    ,' name;*/
      if last.indxname then do;
        put ');';
      end;
    run;
 
/*
    ods output IntegrityConstraints=ic;
    proc contents data=testali out2=info;
    run;
    */
  %end;
%end;
%else %if &flavour=TSQL %then %do;
  /* if schema does not exist, set to be same as libref */
  %local schemaactual;
  proc sql noprint;
  select sysvalue into: schemaactual
    from dictionary.libnames
    where upcase(libname)="&libref" and engine='SQLSVR';
  %let schema=%sysfunc(coalescec(&schemaactual,&schema,&libref));
 
  %do x=1 %to %sysfunc(countw(&dsnlist));
    %let curds=%scan(&dsnlist,&x);
    data _null_;
      file &fref mod;
      put "/* TSQL Flavour DDL for &schema..&curds */";
    data _null_;
      file &fref mod;
      set &colinfo (where=(upcase(memname)="&curds")) end=last;
      if _n_=1 then do;
        if memtype='DATA' then do;
          put "create table [&schema].[&curds](";
        end;
        else do;
          /* just a placeholder - we filter out views at the top */
          put "create view [&schema].[&curds](";
        end;
        put "    "@@;
      end;
      else put "   ,"@@;
      format=upcase(format);
      if 1=0 then; /* dummy if */
      %if &applydttm=YES %then %do;
        else if format=:'DATETIME' then fmt='[datetime2](7)  ';
      %end;
      else if type='num' then fmt='[decimal](18,2)';
      else if length le 8000 then fmt='[varchar]('!!cats(length)!!')';
      else fmt=cats('[varchar](max)');
      if notnull='yes' then notnul=' NOT NULL';
      put "[" name +(-1) "]" fmt notnul;
    run;
 
    /* Extra step for data constraints */
    %addConst()
 
    /* Create Unique Indexes, but only if they were not already defined within
      the Constraints section. */
    data _null_;
      /*length ds $128;*/
      set &idxinfo(
        where=(
          memname="&curds"
          and unique='yes'
          and indxname not in (
            %sysfunc(tranwrd("&constraints_used",%str( ),%str(",")))
          )
        )
      );
      file &fref mod;
      by idxusage indxname;
      /*ds=cats(libname,'.',memname);*/
      if first.indxname then do;
        /* add nonclustered in case of multiple unique indexes */
        put '   ,index [' indxname +(-1) '] UNIQUE NONCLUSTERED (';
        put '     [' name +(-1) ']';
      end;
      else put '     ,[' name +(-1) ']';
      if last.indxname then do;
        put '   )';
      end;
    run;
 
    data _null_;
      file &fref mod;
      put ')';
      put 'GO';
    run;
 
    /* add extended properties for labels */
    data _null_;
      file &fref mod;
      length nm $64 lab $1024;
      set &colinfo (where=(upcase(memname)="&curds" and label ne '')) end=last;
      nm=cats("N'",tranwrd(name,"'","''"),"'");
      lab=cats("N'",tranwrd(label,"'","''"),"'");
      put ' ';
      put "EXEC sys.sp_addextendedproperty ";
      put "  @name=N'MS_Description',@value=" lab ;
      put "  ,@level0type=N'SCHEMA',@level0name=N'&schema' ";
      put "  ,@level1type=N'TABLE',@level1name=N'&curds'";
      put "  ,@level2type=N'COLUMN',@level2name=" nm ;
      if last then put 'GO';
    run;
  %end;
%end;
%else %if &flavour=PGSQL %then %do;
  /* if schema does not exist, set to be same as libref */
  %local schemaactual;
  proc sql noprint;
  select sysvalue into: schemaactual
    from dictionary.libnames
    where upcase(libname)="&libref" and engine='POSTGRES';
  %let schema=%sysfunc(coalescec(&schemaactual,&schema,&libref));
  data _null_;
    file &fref mod;
    put "CREATE SCHEMA &schema;";
  %do x=1 %to %sysfunc(countw(&dsnlist));
    %let curds=%scan(&dsnlist,&x);
    %local curdsvarcount;
    %let curdsvarcount=%mf_getvarcount(&libref..&curds);
    %if &curdsvarcount>1600 %then %do;
      data _null_;
        file &fref mod;
        put "/* &libref..&curds contains &curdsvarcount vars */";
        put "/* Postgres cannot create tables with over 1600 vars */";
        put "/* No DDL will be generated for this table";
      run;
    %end;
    %else %do;
      data _null_;
        file &fref mod;
        put "/* Postgres Flavour DDL for &schema..&curds */";
      data _null_;
        file &fref mod;
        set &colinfo (where=(upcase(memname)="&curds")) end=last;
        length fmt $32;
        if _n_=1 then do;
          if memtype='DATA' then do;
            put "CREATE TABLE &schema..&curds (";
          end;
          else do;
            /* just a placeholder - we filter out views at the top */
            put "CREATE VIEW &schema..&curds (";
          end;
          put "    "@@;
        end;
        else put "   ,"@@;
        format=upcase(format);
        if 1=0 then; /* dummy if */
        %if &applydttm=YES %then %do;
          else if format=:'DATETIME' then fmt=' TIMESTAMP ';
        %end;
        else if type='num' then fmt=' DOUBLE PRECISION';
        else fmt='VARCHAR('!!cats(length)!!')';
        if notnull='yes' then notnul=' NOT NULL';
        /* quote column names in case they represent reserved words */
        name2=quote(trim(name));
        put name2 fmt notnul;
      run;
 
      /* Extra step for data constraints */
      %addConst()
 
      data _null_;
        file &fref mod;
        put ');';
      run;
 
      /* Create Unique Indexes, but only if they were not already defined within
        the Constraints section. */
      data _null_;
        /*length ds $128;*/
        set &idxinfo(
          where=(
            memname="&curds"
            and unique='yes'
            and indxname not in (
              %sysfunc(tranwrd("&constraints_used",%str( ),%str(",")))
            )
          )
        );
        file &fref mod;
        by idxusage indxname;
        if first.indxname then do;
          put 'CREATE UNIQUE INDEX "' indxname +(-1) '" ' "ON &schema..&curds(";
          put '  "' name +(-1) '"' ;
        end;
        else put '  ,"' name +(-1) '"';
        if last.indxname then do;
          put ');';
        end;
      run;
    %end;
  %end;
%end;
%if %upcase(&showlog)=YES %then %do;
  options ps=max;
  data _null_;
    infile &fref;
    input;
    putlog _infile_;
  run;
%end;
 
%mend mp_ds2ddl;