How to Delete Rows from a Dataset in SAS

Oftentimes, you have a dataset with unwanted rows and you want to get rid of them. But, how do you delete one or more rows from a dataset in SAS?

In SAS, you can remove a row from a dataset in two ways:

1. With the DELETE statement in a SQL procedure, or
2. With the DELETE statement in a SAS Data Step

In this article, we explain both methods. If you want how to add rows or delete a complete dataset, you can check these articles:

• 5 Best Ways to Insert a Row into a SAS Dataset
• How to Delete a Dataset in SAS

Sample Data

In the examples below, we use a sample dataset to demonstrate how the 2 methods work. You can run the code below to create this sample data. The dataset has 2 columns (sex and age) and 7 rows.

data work.my_data;
    input sex $ age;
    datalines;
F 45
M 32
M 67
F 31
F 22
F 18
M 39
;
run;

Remove Rows with the DELETE Statement in a SQL Procedure

The first method to remove one or more rows from a table is with the DELETE statement in a SQL procedure.

The DELETE statement removes row(s) from the table mentioned after the FROM clause. Normally, the FROM clause is followed by a WHERE statement to specify which rows to remove.

PROC SQL;
DELETE FROM table-name
WHERE condition(s)
;
QUIT;

The DELETE statement in the SQL procedure removes rows from the specified table and overwrites the original table. Keep this in mind.

Caution: If you omit the WHERE statement, then SAS will remove all rows from the dataset without warning. So, be very careful if you use this method.

Example

In this example, we remove all rows from work.my_data where sex = “M”.

proc sql;
    delete from work.my_data
    where sex = "M";
quit;

Remove Rows with the DELETE Statement in a SAS Data Step

The second method to delete a row from a dataset is with the DELETE statement in a SAS Data Step. The DELETE statement is normally part of an IF statement

DATA table-name;
SET table-name;
IF condition THEN DELETE;
RUN;

An advantage of this method is that the table name in the DATA statement and the SET statement can be different. In other words, this method doesn’t necessarily overwrite the original table.

Example

In the example below, we remove all males from the work.my_data dataset.

data work.my_data;
    set work.my_data;
 
    if sex = "M" then delete;
run;