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. The following DELETE statement deletes the names of countries that begin with the letter R:

proc sql;
   delete from sql.newcountries;

   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.

SAS Log for 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.