topic Re: Generating DDL for SAS Tables in SAS Programming

Generating DDL for SAS Tables

rmwachter — Thu, 03 Feb 2011 17:59:35 GMT

Is there an easy way to generate the DDL for an existing SAS table? I'm wanting to create an empty clone of an existing SAS table in a different database.

Thanks!

Re: Generating DDL for SAS Tables

Peter_C — Thu, 03 Feb 2011 18:02:33 GMT

Clone:
proc append base= newlib.newtable data= oldlib.oldtable(obs=0) ;
run;

Re: Generating DDL for SAS Tables

Ksharp — Wed, 23 Feb 2011 05:18:31 GMT

Or

[pre]
proc sql feedback;
create table clone like sashelp.class;
run;
[/pre]

Ksharp

Re: Generating DDL for SAS Tables

PGStats — Thu, 10 May 2012 15:48:28 GMT

Hello, PeterC and Ksharp's approaches above create the clone table without giving you the DDL. To get the DDL you can :

proc sql;

describe table sashelp.class;

quit;

and then use the DDL commands written to the log to create the table.

Re: Generating DDL for SAS Tables

FriedEgg — Thu, 10 May 2012 18:09:12 GMT

I would have to recommend using the method presented by Peter. The code below will provide insight into why:

proc sql feedback codegen flow;

create table temp (

a num primary key,

b char check (b in ('A','B','C')),

c num not null,

d num,

e char unique );

* check clause constraints are not represented in ddl;

describe table temp;

* no index or constraints are duplicated;

create table temp2 like temp;

describe table temp2;

describe table constraints temp2;

insert into temp (a,b,c,d,e)

values (1,'A',2,3,'!');

quit;

proc append base=new data=temp(obs=0); run;

proc sql;

*now I have everything cloned;

describe table new;

*I did not get data because obs=0 in proc append;

select * from new;

quit;

There are additional options as well, proc copy and proc datasets should be able to clone as well.

Re: Generating DDL for SAS Tables

FriedEgg — Thu, 10 May 2012 19:05:46 GMT

Here is a nice piece of reference material to what I discuss:

http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000403555.htm