General form, basic
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, and consists of
column-definition <column-constraint-1<,
...column-constraint-n>>
<MESSAGE='message-string'<MSGTYPE=message-type>>
Here is an explanation
of the syntax:
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 might
be specified.
column-constraint
specifies an integrity
constraint.
MESSAGE= and MSGTYPE=
specify an error message
that is related to an integrity constraint. (Integrity constraints,
the column-constraint, MESSAGE=, and MSGTYPE= are not elaborated here,
but are discussed in detail later in this chapter.)
|
proc sql; create table work.discount (Destination char(3), BeginDate num Format=date9., EndDate num format=date9., Discount num);
proc sql; create table work.discount (Destination char(3), BeginDate num format=date9., EndDate num format=date9., Discount num);
Specified Data Type
|
SAS Data Type
|
---|---|
CHARACTER (or CHAR)
|
CHARACTER
|
VARCHAR
|
CHARACTER
|
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
|
proc sql; create table work.discount2 (Destination varchar(3), BeginDate date, EndDate date, Discount float);
proc sql;
create table work.discount
(Destination char(3),
BeginDate num format=date9.,
EndDate num format=date9.,
Discount num);
proc sql; create table work.departments (Dept varchar(20) label='Department', Code integer label='Dept Code', Manager varchar(20), AuditDate num format=date9.);