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

Note: Alternatively, you can set the SQLUNDOPOLICY system option. For more

information, see “SQLUNDOPOLICY= System Option” on page 383.

Deleting Rows

The DELETE statement deletes one or more rows in a table or in a table that underlies a

PROC SQL or SAS/ACCESS view. For more information about deleting rows from

views, see “Updating a View” on page 132. The following DELETE statement deletes

the names of countries that begin with the letter R:

/* Create and populate Newcountries */

proc sql;

create table sql.newcountries like sql.countries;

insert into sql.newcountries

select * from sql.countries

where population ge 130000000;

proc sql;

delete

from sql.newcountries

where name like 'R%';

A note in the SAS log tells you how many rows were deleted.

Log 4.4 SAS Log for the DELETE Statement

NOTE: 1 row was deleted from SQL.NEWCOUNTRIES.

Note: For PROC SQL tables, SAS deletes the data in the rows but retains the space in

the table.

CAUTION:

If you omit a WHERE clause, then the DELETE statement deletes all the rows

from the specified table or the table that is described by a view. The rows are

not deleted from the table until it is re-created.

Altering Columns

The ALTER TABLE statement adds, modifies, and deletes columns in existing tables.

You can use the ALTER TABLE statement with tables only; it does not work with views.

A note appears in the SAS log that describes how you have modified the table.

Adding a Column

The ADD clause adds a new column to an existing table. You must specify the column

name and data type. You can also specify a length (LENGTH=), format (FORMAT=),

informat (INFORMAT=), and a label (LABEL=). The following ALTER TABLE

statement adds the numeric data column Density to the NewCountries table:

proc sql;

create table sql.newcountries like sql.countries;

insert into sql.newcountries

Altering Columns 123

select * from sql.countries

where population ge 130000000;

proc sql;

alter table sql.newcountries

add density num label='Population Density' format=6.2;

title "Population Density Table";

select name format=$20.,

capital format=$15.,

population format=comma15.0,

density

from sql.newcountries;

Output 4.8 Adding a New Column

The new column is added to NewCountries, but it has no data values. The following

UPDATE statement changes the missing values for Density from missing to the

appropriate population densities for each country:

proc sql;

update sql.newcountries

set density=population/area;

title "Population Density Table";

select name format=$20.,

capital format=$15.,

population format=comma15.0,

density

from sql.newcountries;

124 Chapter 4 • Creating and Updating Tables and Views

Output 4.9 Filling in the New Column's Values

For more information about how to change data values, see “Updating Data Values in a

Table” on page 120.

You can accomplish the same update by using an arithmetic expression to create the

Population Density column as you re-create the table:

proc sql;

create table sql.newcountries as

select *, population/area as density

label='Population Density'

format=6.2

from sql.newcountries;

See “Calculating Values” on page 29 for another example of creating columns with

arithmetic expressions.

Modifying a Column

You can use the MODIFY clause to change the width, informat, format, and label of a

column. To change a column's name, use the RENAME= data set option. You cannot

change a column's data type by using the MODIFY clause.

The following MODIFY clause permanently changes the format for the Population

column:

proc sql;

create table sql.newcountries like sqlcountries;

create table sql.newcountries as

select * from sql.countries

where population ge 130000000;

proc sql;

title "World's Largest Countries";

alter table sql.newcountries

modify population format=comma15.;

select name, population from sql.newcountries;

Altering Columns 125

Output 4.10 Modifying a Column Format

You might have to change a column's width (and format) before you can update the

column. For example, before you can prefix a long text string to Name, you must change

the width and format of Name from 35 to 60. The following statements modify and

update the Name column:

proc sql;

title "World's Largest Countries";

alter table sql.newcountries

modify name char(60) format=$60.;

update sql.newcountries

set name='The United Nations member country is '||name;

select name from sql.newcountries;

Output 4.11 Changing a Column's Width

Deleting a Column

The DROP clause deletes columns from tables. The following DROP clause deletes

UNDate from NewCountries:

126 Chapter 4 • Creating and Updating Tables and Views