The first line of this week's SAS tip grabs your attention, "PROC SQL provides a helpful (though potentially dangerous) tool in the form of the DESCRIBE TABLE statement." SAS author, consultant, and member of the SAS-L Hall of Fame Howard Schreier included this intriguing statement in his book PROC SQL by Example: Using SQL within SAS--and then clarified what he meant. If you flip through Howard's book, you'll find an abundance of programming tips, examples, and glowing reviews from SAS users. You can also read these reviews and a free chapter on his author page.
The following excerpt is from SAS author Howard Schreier and his book "PROC SQL by Example: Using SQL within SAS" Copyright © 2008, SAS Institute Inc., Cary, North Carolina, USA. ALL RIGHTS RESERVED. (please note that results may vary depending on your version of SAS software)
Help from the DESCRIBE TABLE Statement
PROC SQL provides a helpful (though potentially dangerous) tool in the form of the DESCRIBE TABLE statement. It examines an existing table, then displays (in the log) the CREATE TABLE statement that would create that table in an empty state (that is, with no rows of data). For example, if we run:
PROC SQL; DESCRIBE TABLE demolib.fifteenups; QUIT;
we get, in the log:
You can copy this code from the log and use it in your source code. Of course, if you do that without making any changes, and if you do not have the NOREPLACE system option or some other safety mechanism in effect, you can easily destroy the model table (the one referenced by the DESCRIBE TABLE statement) and replace it with an empty clone. So it’s more likely that you will want to edit the code before running it and, in particular, change the name or library location of the new table.
The DESCRIBE TABLE statement is useful in a variety of situations. If you want to code a self-contained CREATE TABLE statement for a table that is to be almost but not exactly like an existing table, you can use DESCRIBE TABLE to generate a first approximation of your CREATE TABLE statement. You can then edit the statement. If you want to pattern a new table on an existing table and then “freeze” that borrowed structure so that your code is insulated from later changes to the model table, you cannot use the CREATE TABLE statement with the LIKE keyword. Instead, use DESCRIBE TABLE to generate the code you need. If you prefer to design a table using the DATA step and then translate the specifications into SQL, use DESCRIBE TABLE to produce the CREATE TABLE statement.
6 Comments
What about:
proc sql noprint ;
create new_table like old_table ;
quit ;
This is simplest way to propagate a table structure. It has advantages and disadvantages vis a vis the technique based on the DESCRIBE statement; see the longest paragraph of the original post.
Just wanted to say that this is a great book. I have spent more of my SAS career in data step rather than SQL. The examples in this book, comparing data step methods to SQL analogs, will be very helpful for anyone who knows SAS, and wants to do more in SQL.
Hi Divyesh, thank you for sharing the code!
The following code can also be handy if we want to create a preliminary version of a new table :
proc sql;
create table newTable as select * from demolib.fifteenups;
quit;
Another variation is to use the OBS=0 data set option to reproduce the structure without the content (rows):
create table newTable as select * from demolib.fifteenups(obs=0) ;