topic Re: How to create a sas data set from data definition language. in SAS Enterprise Guide

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

alepage — Fri, 10 Aug 2018 19:49:47 GMT

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

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

ballardw — Fri, 10 Aug 2018 19:59:56 GMT

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

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

novinosrin — Fri, 10 Aug 2018 20:00:47 GMT

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

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

SuryaKiran — Fri, 10 Aug 2018 20:02:37 GMT

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

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

Haikuo — Fri, 10 Aug 2018 20:06:00 GMT

And Proc Contents with out= option.

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

Reeza — Fri, 10 Aug 2018 20:17:21 GMT

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

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

Patrick — Mon, 13 Aug 2018 01:32:43 GMT

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

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

LinusH — Sun, 12 Aug 2018 08:17:19 GMT

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

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:

NUM doesn't carry length (see this ballot entry https://communities.sas.com/t5/SASware-Ballot-Ideas/SQL-Let-DESCRIBE-generate-the-actual-length-of-NUM-columns/idi-p/220516)
PK is referred to as unique index (which is similar, but not the same)

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

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

alepage — Mon, 13 Aug 2018 13:02:52 GMT

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,

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

Tom — Mon, 13 Aug 2018 13:54:52 GMT

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

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

alepage — Mon, 13 Aug 2018 15:21:25 GMT

hello

It is very simple and it works

Regards