Introduction By using PROC SQL, you can create, modify, and drop (delete) tables quickly and efficiently. Many PROC SQL statements are quite versatile, enabling you to perform the same action in several different ways. For example, there are three methods of creating a table by using the CREATE TABLE statement: creating an empty table (a table without rows) by defining columns creating an empty table that has the same columns and attributes as another table creating a table from a query result. The following PROC SQL step uses the CREATE TABLE statement to create an empty table by defining columns, and uses the DESCRIBE TABLE statement to display information about the table's structure in the SAS log:

proc sql; create table work.discount (Destination char(3), BeginDate num Format=date9., EndDate num format=date9., Discount num); describe table work.discount;

1    proc sql;
2       create table work.discount
3              (Destination char(3),
4              BeginDate num Format=date9.,
5              EndDate num format=date9.,
6              Discount num);
NOTE: Table WORK.DISCOUNT created, with 0 
rows and 4 columns.
7       describe table work.discount;
NOTE: SQL table WORK.DISCOUNT was created like:

create table WORK.DISCOUNT( bufsize=4096 )
            (
            Destination char(3),
            BeginDate num format=DATE9.,
            EndDate num format=DATE9.,
            Discount num
            );

In this lesson, you will learn to create and manage tables by using the PROC SQL statements shown above, and many others.

 

Time to Complete This lesson contains pages and takes approximately 3 hours to complete.

Objectives In this lesson, you learn to create a table that has no rows by specifying columns and values create a table that has no rows by copying the columns and attributes from an existing table create a table that has rows, based on a query result display the structure of a table in the SAS log insert rows into a table by listing values insert rows into a table by specifying column-value pairs insert rows into a table, based on a query result create a table that has integrity constraints set the UNDO_POLICY option to control how PROC SQL handles errors in table insertions and updates display the integrity constraints of a table in the SAS log update values in existing rows of a table by using one expression and by using conditional processing with multiple expressions delete rows in a table add, modify, or drop (delete) columns in a table drop (delete) entire tables.

Prerequisites Before taking this lesson, you should complete the following lessons: .