DATA Step, Macro, Functions and more

Generating DDL for SAS Tables

Reply
Occasional Contributor
Posts: 9

Generating DDL for SAS Tables

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!
Valued Guide
Posts: 2,177

Re: Generating DDL for SAS Tables

Posted in reply to rmwachter
Clone:
proc append base= newlib.newtable data= oldlib.oldtable(obs=0) ;
run;
Super User
Posts: 10,028

Re: Generating DDL for SAS Tables

Posted in reply to rmwachter
Or


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



Ksharp
Respected Advisor
Posts: 4,920

Re: Generating DDL for SAS Tables

Posted in reply to rmwachter

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.

PG

PG
Trusted Advisor
Posts: 1,301

Re: Generating DDL for SAS Tables

Posted in reply to rmwachter

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.

Trusted Advisor
Posts: 1,301

Re: Generating DDL for SAS Tables

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

Ask a Question
Discussion stats
  • 5 replies
  • 2270 views
  • 1 like
  • 5 in conversation