The CREATE TABLE Statement

A Brief Overview

PROC SQL offers you three ways to create a table. The CREATE TABLE statement is used for all three methods, although the statement syntax varies for each method.
Method of Creating a Table
Example
create an empty table by defining columns
 
proc sql;
   create table work.discount
      (Destination char(3),
       BeginDate num Format=date9.,
       EndDate num format=date9.,
       Discount num);
quit;
create an empty table that is like (has the same columns and attributes as) an existing table
 
proc sql;
   create table work.flightdelays2
      like certadv.flightdelays;
quit;
create a populated table (a table with both columns and rows of data) from a query result
 
proc sql;
   create table work.ticketagents as
      select lastname, firstname,
            jobcode, salary
         from certadv.payrollmaster,
            certadv.staffmaster
         where payrollmaster.empid
            = staffmaster.empid
         and jobcode contains 'TA';
quit;
The CREATE TABLE statement generates a table only as output, not as a report. The SAS log displays a message that indicates that the table has been created as well as the number of rows and columns that it contains.
Log 2.1 SAS Log
NOTE: Table WORK.FLIGHTDELAYS2 created, with 0 rows and 8 columns.
Note: You can display additional information about a table's structure in the SAS log by using the DESCRIBE TABLE statement in PROC SQL.

CREATE TABLE Statement Syntax

Use the CREATE TABLE statement to create an empty table and define the table’s columns and attributes. The empty table will not contain any rows.
Syntax, CREATE TABLE statement with column specifications:
CREATE TABLE table-name
(column-specification-1<,
...column-specification-n>);
table-name
specifies the name of the table to be created.
column-specification
specifies a column to be included in the table. The following constraints are available for the columns, using this form:
column-definition consists of the following:
column-name data-type<(column-width)> <column-modifier-1> <column-modifier-n>
column-name
specifies the name of the column. The column name is stored in the table in the same case that is used in column-name.
data-type
is enclosed in parentheses and specifies one of the following: CHARACTER (or CHAR) | VARCHAR | INTEGER (or INT) | SMALLINT | DECIMAL (or DEC) | NUMERIC (or NUM) | FLOAT | REAL | DOUBLE PRECISION | DATE.
column-width
which is enclosed in parentheses, is an integer that specifies the width of the column. (PROC SQL processes this value only for the CHARACTER and VARCHAR data types.)
column-modifier
is one of the following: INFORMAT= | FORMAT= | LABEL= . More than one column-modifier can be specified.
Note: The entire set of column-specifications must be enclosed in parentheses. Multiple column-specifications must be separated by commas. Elements within a column-specifications must be separated by spaces.

Example: Creating an Empty Table by Defining Column Structure

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);
quit;
The SAS log confirms that the table has been created.
Log 2.2 SAS Log
NOTE: Table WORK.DISCOUNT created, with 0 rows and 4 columns.
Tip
In this example, and all other examples in this chapter, you are instructed to save your data to a temporary table (in the library Work) that will be deleted at the end of the SAS session. To save the table permanently in a different library, use the appropriate libref instead of the libref Work in the CREATE TABLE statement.

Specifying Data Types for Columns

When you create a table by defining columns, you must specify a data type for each column, following the column name:
column-name data-type <(column-width)> <column-modifier-1<...column-modifier-n>>
The following PROC SQL step defines four columns: a 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);
quit;
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.
Table 2.1 Character Data Types Supported by PROC SQL
Specified Data Type
SAS Data Type
CHARACTER (or CHAR)
CHARACTER
VARCHAR
CHARACTER
Table 2.2 Numeric Data Types Supported by PROC SQL
Specified Data Type
Description
SAS Data Type
NUMERIC (or NUM)
floating-point
NUMERIC
DECIMAL (or DEC)
floating-point
NUMERIC
FLOAT
floating-point
NUMERIC
REAL
floating-point
NUMERIC
DOUBLE PRECISION
floating-point
NUMERIC
INTEGER (or INT)
integer
NUMERIC
SMALLINT
integer
NUMERIC
DATE
date
NUMERIC with a DATE.7 informat and format
The following PROC SQL 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);
quit;
PROC SQL converts these data types to either character or numeric. Because it supports 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.

Specifying Column Widths

In SAS, the default column width for both character and numeric columns is 8 bytes. However, character and numeric data values are stored differently:
  • Character data is stored one character per byte.
  • Numeric data is stored as floating-point numbers in real binary representation, which allows for 15- or 16-digit precision within 8 bytes.
PROC SQL enables you to specify a column width for character columns but not for numeric columns.
Note: PROC SQL allows the WIDTH and NDEC (decimal places) arguments to be included in the column specification for the DECIMAL, NUMERIC, and FLOAT data types. However, PROC SQL ignores this specification and uses the SAS defaults.
In a column specification, the column width follows the data type and is specified as an integer enclosed in parentheses:
column-name data-type <(column-width)> <column-modifier-1<...column-modifier-n>>
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);
quit;
Because the last three columns are numeric, no width is specified and these columns will have a default column width of 8 bytes.

Specifying Column Modifiers

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.
column-name data-type <(column-width)> <...column-modifier-1 <...column-modifier-n>>
Note: A fourth SAS column modifier, LENGTH=, is not allowed in a CREATE TABLE statement. It can be used in a SELECT clause.

Example: Using Column Modifiers

The following PROC SQL step creates the table Work.Departments by specifying four 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.);
quit;
The SAS log verifies that the table was created.
Log 2.3 SAS Log
NOTE: Table WORK.DEPARTMENTS created, with 0 rows and 4 columns.
Last updated: October 16, 2019
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.119.248.159