The basic SQL concept is still the same in SAS. The DELETE statement deletes only the rows (data) in a table. The DROP TABLE statement removes the table from the database completely, including the table structure.
DELETE Statement in SAS
This is the most commonly used statement to remove a few, specific groups of observations, or in case you want to empty a data set by removing all the rows.
When to use DELETE statement:
- Use DELETE statement when you know the exact condition on the basis of that you want to exclude observations
- You can also use a subsetting IF statement to exclude observations.
- It can also be useful when you want to empty a data set without deleting its table structure.
There are two ways to use DELETE statement.
- PROC SQL
- DATA step
PROC SQL:
Using this procedure you can delete a specific set of rows by specifying the exact condition or else you can delete all the rows in one go.
First, let’s create a sample data set for demonstration:
data work.cars;
set sashelp.cars;
run;
Example 1: DELETE specific rows
The below query will delete rows which are marked as type=SUV from work.CARS data set.
You can confirm the same by looking at the log and checking out the proc contents output where you can verify how many observations remain in the data set..
proc sql;
delete from work.cars where type="SUV";
quit;
Example 2: DELETE all the rows
The below query will delete all the rows from the work.CARS data set.
You can confirm the same by looking at log and checking out the proc contents output where you can verify that table structure remains there with zero observation.
proc sql;
delete from work.cars;
quit;
SAS DATA Step:
You can DELETE statements in SAS Data step using IF-THEN statement. After the IF statement you mention the condition and after THEN statement you write DELETE.
Example 1: In the below example you’re recreating the same data set by removing rows which you do not want. Here you’ll exclude rows which has type=SUV
data work.cars;
set work.cars;
IF type="SUV" then DELETE;
run;
Example 2: If you want to use data step and DELETE all the rows from a data set then still it is possible. You just need to mention ALL-TIME true conditions after the IF statement.
For example, 1=1 is the all-time true condition.
data work.cars;
set work.cars;
IF 1=1 then DELETE;
run;
Example 3: Remove all the rows just by specifying DELETE statement.
data work.cars;
set work.cars;
DELETE;
run;
Example 4: Remove all the rows without using the DELETE statement. You can use obs=0 on either SET or DATA statements. It is more efficient to use the obs=0 option on the SET statement.
data work.cars;
set work.cars(obs=0);
run;
data work.cars (obs=0);
set work.cars;
run;
Above query produces the same output but the first query is more efficient compared to the second one. Check out this detailed article on data deletion in SAS.
DROP Statement in SAS
This is the most commonly used statement when you want to delete all the sas data files including data, its table structure, indexes, etc.
You cannot use the DROP TABLE statement to remove a table that is referenced by a foreign key constraint. You must drop the foreign key constraint first, and then remove the table.
When to use DROP statement:
- Use DROP statement when you want to delete everything (data + table structure, indexes)
- It is not possible to remove a few or specific observations from a data set using DROP statements. Hence you need to be very careful before using DROP statements.
Example:
proc sql;
drop table work.cars;
quit;
FAQ
There are different ways you can delete all the rows from a sas data set. The easiest and fastest way is using SAS Data step with obs=0 option.
For example:
data work.cars;
set work.cars(obs=0);
run;
The DELETE statement deletes only the rows (data) in a table. The DROP TABLE statement removes the table from the database completely, including the table structure.
Free SAS Course
Learn SAS Code — The only SAS programming course available on the internet which you need to master Data Analytics, Business Intelligence (BI) and Cloud technology with tons of real life examples.
Unlock Free SAS Tutorials