BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
alepage
Barite | Level 11

Hello,

 

It seems possible to generate the DDL from a SAS table as follow:

 

proc sql;

describe table sashelp.class;

quit;

 

Then we go in the log file and we get this:

 

create table SASHELP.CLASS( label='Student Data' bufsize=65536 )

(

Name char(8),

Sex char(1),

Age num,

Height num,

Weight num

);

quit;

 

Can I import the DDL into  SAS to generate the data set and if so, how can we do that?

Regards

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

If your DDL is compatible with PROC SQL syntax then you might be able to do what you want using using %INCLUDE.

proc sql ;
%include 'my_ddl.sas' / source2;
quit;

However that is a big IF as many DDL files from other databases will use data types and options that are not compatible with SAS.

You might have better success for SAS if you just create data step code to define your datasets..

View solution in original post

10 REPLIES 10
ballardw
Super User

@alepage wrote:

Hello,

 

It seems possible to generate the DDL from a SAS table as follow:

 

proc sql;

describe table sashelp.class;

quit;

 

Then we go in the log file and we get this:

 

create table SASHELP.CLASS( label='Student Data' bufsize=65536 )

(

Name char(8),

Sex char(1),

Age num,

Height num,

Weight num

);

quit;

 

Can I import the DDL into  SAS to generate the data set and if so, how can we do that?

Regards


Describe what you want as output.

If you want a data set of the same structure but no actual values, variable names, order, formats, labels of a data set you know skips that SQL completely:

Data want;

    set sashelp.class (obs=0);

run;

If you want values, where would they come from???

novinosrin
Tourmaline | Level 20

I think you may have to use 

proc printto log=logfile;
run;

to direct your log output to an external file and then read the logfile with infile to get the contents in a sas dataset. 

However don't take my word, there might be more straight forward methods

SuryaKiran
Meteorite | Level 14

SAS already got that for you. All the metadata information for the tables is accessed through dictionary tables or view tables.

 

Check these tables: dictionary.tables, dictionary.columns

proc sql;
select * from dictionary.columns
where libname="SASHELP" and memname="CLASS";
quit;

Read more here  or google dictionary tables in SAS

 

Thanks,
Suryakiran
Haikuo
Onyx | Level 15

And Proc Contents with out= option.

 

Reeza
Super User

@alepage wrote:

Hello,

 

It seems possible to generate the DDL from a SAS table as follow:

 

proc sql;

describe table sashelp.class;

quit;

 

Then we go in the log file and we get this:

 

create table SASHELP.CLASS( label='Student Data' bufsize=65536 )

(

Name char(8),

Sex char(1),

Age num,

Height num,

Weight num

);

quit;

 

Can I import the DDL into  SAS to generate the data set and if so, how can we do that?

Regards

 

 

 

 



Use LIKE 

 

Proc sql;
Create table want like SAShelp.class;
Quit;

Then check the table called WANT. 

 

 

Patrick
Opal | Level 21

@alepage

If your new target table structure is a SAS table and if this is about creating the table and not storing away the DDL for later use then besides of the SQL like simple code as below will do the job as well.

data want;
  if 0 set sashelp.class;
  /** below any statements you wish */  
run;

/*To create an empty table structure without any rows*/
data want;
  stop;
  set sashelp.class;
run;
LinusH
Tourmaline | Level 20

To get the SQL, you best shot is the PRINTTO as mentioned by @.

Then you must yourself wrap into a PROC SQL to execute it.

But the DESCRIBE TABLE doesn't unfortunately give you the complete and correct metadata for your tables. Examples are:

This is kinda standard functionality in other DBMS so wouldn't be expected that SAS could implement DDL generation for it's own engines...? Perhaps another Ballot item for this...

Data never sleeps
alepage
Barite | Level 11

Thanks to all of you.


Based on the answers I have received, I believe that my question was not clear.

I have noticed that from a SAS data set it is possible to generate the DDL code using proc SQL; Describe table TableName quit; as below 

 

 

proc sql;

describe table sashelp.class;

quit;

 

Then we go in the log file and we get this:

 

create table SASHELP.CLASS( label='Student Data' bufsize=65536 )

(

Name char(8),

Sex char(1),

Age num,

Height num,

Weight num

);

quit;

 

Now imagine that a DDL has been generated with another software and that I have a code like the one below saved in a text file.

 

create table TableName

(

var 1 char(10),

var2 char(15),

var3 num,

var4 num,

var5 num

);

 

I can easly generate a SAS data Set using the following code:

 

Proc sql:

 

(

var 1 char(10),

var2 char(15),

var3 num,

var4 num,

var5 num

);

Quit;

 

My objective is to get the SAS data set without having to write PROC SQL; paste the content of the text file, then add quit;

 

Now, I would like to know if it would be possible to generate a SAS table by calling or loading the text file which contains a DDL.

Regards,

 

 

 

 

 

 

 

Tom
Super User Tom
Super User

If your DDL is compatible with PROC SQL syntax then you might be able to do what you want using using %INCLUDE.

proc sql ;
%include 'my_ddl.sas' / source2;
quit;

However that is a big IF as many DDL files from other databases will use data types and options that are not compatible with SAS.

You might have better success for SAS if you just create data step code to define your datasets..

alepage
Barite | Level 11

hello

 

It is very simple and it works

Regards

 

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 10 replies
  • 3360 views
  • 2 likes
  • 9 in conversation