Deleting Rows in a Table
Overview
To delete some or all of the rows
in a table, use the DELETE statement. When the statement is successfully
executed, the SAS log shows a message that indicates the number of
rows that have been deleted.
|
General form, DELETE
statement for deleting rows in a table:
DELETE FROM table-name
<WHERE expression>;
table-name
specifies the name
of the table in which rows will be deleted.
WHERE
is used to specify
an expression that subsets
the rows to be deleted.
|
CAUTION:
If you want to delete
only a subset of rows in the table, you must specify a WHERE clause
or all rows in the table will be deleted.
Note: You can also use the DELETE
statement to delete rows in a table that underlies a PROC SQL view.
For more information about referencing a PROC SQL view in a DELETE
statement, see
Creating and Managing Views Using PROC SQL.
Example
Suppose you want to
delete the records for all frequent-flyer program members who have
used up all of their frequent flyer miles or have spent more miles
than they had in their accounts.
First, you create the
temporary table Work.Frequentflyers2 by copying a subset of columns
from the existing table Sasuser.Frequentflyers:
proc sql;
create table work.frequentflyers2 as
select ffid, milestraveled,
pointsearned, pointsused
from sasuser.frequentflyers;The first 10 rows of
Work.Frequentflyers2 are shown below.
Next, you write a PROC
SQL step that deletes the specified rows:
proc sql;
delete from work.frequentflyers2
where pointsearned-pointsused <= 0;A message in the SAS
log tells you how many rows were deleted.
Tip
To delete all of the rows
in the table, remove the WHERE clause from the DELETE statement.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.