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; |
NOTE: Table WORK.FLIGHTDELAYS2 created, with 0 rows and 8 columns.
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.
|
proc sql; create table work.discount (Destination char(3), BeginDate num Format=date9., EndDate num format=date9., Discount num); quit;
NOTE: Table WORK.DISCOUNT created, with 0 rows and 4 columns.
proc sql; create table work.discount (Destination char(3), BeginDate num format=date9., EndDate num format=date9., Discount num); quit;
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); quit;
proc sql;
create table work.discount
(Destination char(3),
BeginDate num format=date9.,
EndDate num format=date9.,
Discount num);
quit;
proc sql; create table work.departments (Dept varchar(20) label='Department', Code integer label='Dept Code', Manager varchar(20), AuditDate num format=date9.); quit;
NOTE: Table WORK.DEPARTMENTS created, with 0 rows and 4 columns.