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:
- With the DELETE statement in a SQL procedure, or
- 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:
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.
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.
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;