O'Reilly logo

SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition by SAS Institute

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required