Tips for Using SQL Procedure Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135
Using SQL Procedure Views in SAS Software . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
This chapter shows you how to perform the following tasks:
create a table
update tables
alter existing tables
delete a table
create indexes
use integrity constraints in table creation
create views
Creating Tables
The CREATE TABLE statement enables you to create tables without rows from column
definitions or to create tables from a query result. You can also use CREATE TABLE to
copy an existing table.
Creating Tables from Column Definitions
You can create a new table without rows by using the CREATE TABLE statement to
define the columns and their attributes. You can specify a column's name, type, length,
informat, format, and label.
The following CREATE TABLE statement creates the NewStates table:
proc sql;
create table sql.newstates
(state char(2), /* 2–character column for */
/* state abbreviation */
date num /* column for date of entry into the US */
informat=date9. /* with an informat */
format=date9., /* and format of DATE9. */
population num); /* column for population */
The table NewStates has three columns and 0 rows. The char(2) modifier is used to
change the length for State.
Use the DESCRIBE TABLE statement to verify that the table exists and to see the
column attributes. The following DESCRIBE TABLE statement writes a CREATE
TABLE statement to the SAS log:
proc sql;
describe table sql.newstates;
112 Chapter 4 Creating and Updating Tables and Views
Log 4.1 Table Created from Column Definitions
1 proc sql;
2 describe table sql.newstates;
NOTE: SQL table SQL.NEWSTATES was created like:
create table SQL.NEWSTATES( bufsize=4096 )
state char(2),
date num format=DATE9. informat=DATE9.,
population num
DESCRIBE TABLE writes a CREATE TABLE statement to the SAS log even if you did
not create the table with the CREATE TABLE statement. You can also use the
CONTENTS statement in the DATASETS procedure to get a description of NewStates.
Creating Tables from a Query Result
To create a PROC SQL table from a query result, use a CREATE TABLE statement with
the AS keyword, and place it before the SELECT statement. When a table is created this
way, its data is derived from the table or view that is referenced in the query's FROM
clause. The new table's column names are as specified in the query's SELECT clause list.
The new table’s column attributes (the type, length, informat, format, and extended
attributes) are the same as the selected source columns.
Note: Extended attributes are not copied to tables that are created using multi-table joins
or outer joins. When UNION, INTERSECT, or minus operators are used, extended
attributes are copied only if the table that is listed before the UNION, INTERSECT,
or minus operator has extended attributes.
The following CREATE TABLE statement creates the Densities table from the Countries
table. The newly created table is not displayed in SAS output unless you query the table.
Note the use of the OUTOBS option, which limits the size of the Densities table to 10
libname sql 'SAS-library';
proc sql outobs=10;
title 'Densities of Countries';
create table sql.densities as
select Name 'Country' format $15.,
Population format=comma10.0,
Area as SquareMiles,
Population/Area format=6.2 as Density
from sql.countries;
select * from sql.densities;
Creating Tables 113
Output 4.1 Table Created from a Query Result
The following DESCRIBE TABLE statement writes a CREATE TABLE statement to the
SAS log:
proc sql;
describe table sql.densities;
Log 4.2 SAS Log for DESCRIBE TABLE Statement for DENSITIES
NOTE: SQL table SQL.DENSITIES was created like:
create table SQL.DENSITIES( bufsize=8192 )
Name char(35) format=$15. informat=$35. label='Country',
Population num format=COMMA10. informat=BEST8. label='Population',
SquareMiles num format=BEST8. informat=BEST8.,
Density num format=6.2
In this form of the CREATE TABLE statement, assigning an alias to a column renames
the column, but assigning a label does not. In this example, the Area column has been
renamed to SquareMiles, and the calculated column has been named Density. However,
the Name column retains its name, and its display label is Country.
114 Chapter 4 Creating and Updating Tables and Views

Get SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.