BookmarkSubscribeRSS Feed
rmwachter
Calcite | Level 5
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!
5 REPLIES 5
Peter_C
Rhodochrosite | Level 12
Clone:
proc append base= newlib.newtable data= oldlib.oldtable(obs=0) ;
run;
Ksharp
Super User
Or


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



Ksharp
PGStats
Opal | Level 21

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
FriedEgg
SAS Employee

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 8993 views
  • 1 like
  • 5 in conversation