Desktop productivity for business analysts and programmers

How to create a sas data set from data definition language.

Accepted Solution Solved
Reply
Regular Contributor
Posts: 164
Accepted Solution

How to create a sas data set from data definition language.

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

 

 

 

 


Accepted Solutions
Solution
yesterday
Super User
Super User
Posts: 8,260

Re: How to create a sas data set from data definition language.

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


All Replies
Super User
Posts: 13,868

Re: How to create a sas data set from data definition language.


@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???

Super User
Posts: 2,037

Re: How to create a sas data set from data definition language.

[ Edited ]

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

PROC Star
Posts: 617

Re: How to create a sas data set from data definition language.

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
Respected Advisor
Posts: 3,179

Re: How to create a sas data set from data definition language.

And Proc Contents with out= option.

 

Highlighted
Super User
Posts: 23,928

Re: How to create a sas data set from data definition language.


@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. 

 

 

Respected Advisor
Posts: 4,776

Re: How to create a sas data set from data definition language.

[ Edited ]

@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;
Super User
Posts: 5,911

Re: How to create a sas data set from data definition language.

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
Regular Contributor
Posts: 164

Re: How to create a sas data set from data definition language.

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,

 

 

 

 

 

 

 

Solution
yesterday
Super User
Super User
Posts: 8,260

Re: How to create a sas data set from data definition language.

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..

Regular Contributor
Posts: 164

Re: How to create a sas data set from data definition language.

hello

 

It is very simple and it works

Regards

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 139 views
  • 2 likes
  • 9 in conversation