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
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 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???
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
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
And Proc Contents with out= option.
@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.
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;
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:
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...
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,
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..
hello
It is very simple and it works
Regards
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.