Sometimes you want to create a new table that is unlike any existing table. In this case, you need to define all of the table’s columns and attributes. To accomplish this, use the CREATE TABLE statement that includes column specifications for the columns that you want to include. This statement creates a table without rows (an empty table).

Suppose you want to create the temporary table Work.Discount, which contains data about discounts that are offered by an airline. There is no existing table that contains the four columns (and column attributes) that you would like to include: Destination, BeginDate, EndDate, and Discount. You use the following PROC SQL step to create the table, based on column definitions that you specify:

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

The SAS log confirms that the table has been created.

NOTE: Table WORK.DISCOUNT created, with 0 rows and 4 columns.

In the next few sections, you learn more about specifying data types and column modifiers in a column specification.

When you create a table by defining columns, you must specify a data type for each column, following the column name:

For example, the following PROC SQL step (shown also in the previous section) defines four columns: one-character column (Destination) and three numeric columns (BeginDate, EndDate, and Discount):

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

SAS tables use two data types: numeric and character. However, PROC SQL supports additional data types (many, but not all, of the data types that SQL-based databases support). Therefore, in the CREATE TABLE statement, you can specify any of 10 different data types. When the table is created, PROC SQL converts the supported data types that are not SAS data types to either numeric or character format.

The following PROC SQL step is very similar to the previous example. The only difference is that this step specifies three supported data types other than CHAR and NUM: VARCHAR, DATE, and FLOAT.

proc sql;
   create table work.discount2 
          (Destination varchar(3),
          BeginDate date,
          EndDate date,
          Discount float);

PROC SQL converts these data types to either character or numeric, as indicated in the charts above. Therefore, the table Work.Discount2 (created by this PROC SQL step) and Work.Discount (created by the previous PROC SQL step) contains identical columns.

By supporting data types other than SAS data types, PROC SQL can save you time. In many cases, you can copy native code from an implementation of SQL that is external to SAS without having to modify the data types.

In SAS, the default column width for both character and numeric columns is 8 bytes. However, character and numeric data values are stored differently:

PROC SQL enables you to specify a column width for character columns but not for numeric columns.

In a column specification, the column width follows the data type and is specified as an integer enclosed in parentheses:

In the following PROC SQL step, the first column specification indicates a column width of 3 for the character column Destination:

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

Because the last three columns are numeric, no width is specified and these columns have the default column width of 8 bytes.

In the CREATE TABLE statement, a column specification might include one or more of the following SAS column modifiers: INFORMAT=, FORMAT=, and LABEL=. Column modifiers, if used, are specified at the end of the column specification.

The following PROC SQL step creates the table Work.Departments by specifying 4 columns. The column modifiers LABEL= and FORMAT= are used to specify additional column attributes.

proc sql;
   create table work.departments 
          (Dept varchar(20) label='Department',
           Code integer label='Dept Code',
           Manager varchar(20), 
           AuditDate num format=date9.);

The SAS log verifies that the table was created.

NOTE: Table WORK.DEPARTMENTS created, with 0 rows and 4 columns.