SAS 9.4 SQL Procedure User's Guide, Third Edition, 3rd Edition

Tips for Using SQL Procedure Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135

Using SQL Procedure Views in SAS Software . . . . . . . . . . . . . . . . . . . . . . . . . . . 136

Introduction

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

rows.

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