In this post you’ll learn how to delete datasets in SAS using 3 simple ways. It also explains how you can drop the entire SAS dataset and/or entire data from a specific library.
Short Summary: Delete table v/s Drop table
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.
In the event of refreshing data usually drop table technique is being used. Whereas in the event of update, upsert, or append data usually delete table technique is being used.
#3 Ways to Delete Datasets in SAS
As stated earlier delete statement removes data (that is rows) from a SAS data set. You can either conditionally delete a set of rows or you can delete all the rows in one go.
In fact, you can perform delete operation on a single as well multiple datasets. Furthermore you can go wild and delete all the rows from all the datasets in any SAS library.
Let’s get straight into multiple ways to delete datasets in SAS. There are 3 simple ways:
- Delete one data set
- Delete multiple datasets
- Delete all the datasets in library (kill)
1. DELETE One data set in SAS
Let’s create a new work data set to demonstrate how the DELETE statement works with proc datasets procedure.
data work.class_1;
set sashelp.class;
run;
DELETE data set using PROC DATASETS
Here is an example of how you can delete class_1 data set from the work library. Please note that the proc datasets procedure will perform a hard delete on the data set.
The entire table will be dropped hence table structure will not be retained after delete operation.
There is one more downfall that you can’t add any condition to delete data from a sas data set. Though it is very effective when it comes to delete the entire data set.
proc datasets library=work;
delete class_1 ;
quit;
DELETE data set using PROC SQL
In most cases you might want to keep table structure as-is. If you just want to delete rows and keep the table structure intact then you need to use PROC SQL procedure with DELETE statement.
proc sql;
delete from work.class_1;
quit;
You can easily confirm this by running proc contents procedure which shows details about data set structure. Look at the observation number from the top right corner. It says zero observations.
proc contents data=work.class_1; run; 
Another use case:
The best part of using proc SQL is you can conditionally delete data (or rows) from sas dataset by adding WHERE clause.
Let me try to delete class_1 data by adding filter Sex=’M’. It means all the rows will be deleted that have SEX=’M’ entries in the data set.
proc sql;
delete from work.class_1 where SEX='M';
quit;
2. DELETE Multiple datasets
To demonstrate delete operation on multiple datasets, let me first create a few more work datasets.
data work.class_1 work.class_2 work.class_3;
set sashelp.class;
run;
DELETE Multiple datasets using PROC DATASETS
As I earlier stated proc datasets perform a hard delete hence the entire table gets deleted, even the table structure also gets dropped.
Example:
proc datasets library=work;
delete class_1 class_2 ;
quit;
In the above example I’m deleting only two datasets (class_1, class_2) from the work library. Please note that I’m not touching the class_3 data set. Hence it should not be affected by this delete operation.
You can verify this by using the following code which lists down all the available datasets in the mentioned library. In our case it will list down datasets available in the WORK library.
* list datasets from WORK library;
proc datasets library=work memtype=data;
quit;
Special CASE:
If you have datasets with sequential names and want to delete those datasets then you can use the range option. In this method you do not need to mention the name of all the datasets you want to delete. You are only required to mention the first and last data set to delete.
Our work dataset names are sequential. Look at the data set names: class_1, class_2, class_3, class_4, etc.
data work.class_1 work.class_2 work.class_3 work.class_4;
set sashelp.class;
run;
proc datasets library=work nolist ;
delete class_1-class_4 ;
run;
Another example would be datasets with periodic names such as class_202301, class_202502, class_202503, class_202504, class_202505, class_202506, etc.
data work.class_202501 work.class_202502 work.class_202503 work.class_202504 work.class_202505 work.class_202506;
set sashelp.class;
run;
proc datasets library=work nolist ;
delete class_202501-class_202506 ;
run;
DELETE Multiple datasets using PROC SQL
The proc sql gives you more flexibility and control while deleting data from a sas data set. You can mention all the tables that you want to delete data from without deleting their structure. You can also add multiple conditions to avoid specific rows from deletion.
proc sql;
delete from work.class_1 ;
delete from work.class_2 where SEX='M';
delete from work.class_3 where SEX='M' and AGE=12;
quit;
3. Delete all the datasets in library (kill)
This is wild but very useful when you want to delete everything from a library. To perform this operation you don’t need to list down all the datasets present in the library. With the help of PROC DATASETS procedure you can easily achieve this.
CAUTION: Be 100% sure that you want to delete EVERYTHING from the specified library.
data work.class_1 work.class_2 work.class_3 work.class_4;
set sashelp.class;
run;
proc datasets library=work kill;
quit; /* delete all the datasets */
FAQ
You can easily delete data in SAS using PROC DATASETS or PROC SQL procedures. It is also possible to delete all the datasets from a specific library.
Yes, you can scrap entire library by using PROC DATASETS with KILL keyword. It deletes all the datasets from a specified SAS library.
For example:
proc datasets library=work KILL;
quit;
Learn SAS Code (Free Course)
Learn SAS Code — The only premium SAS programming course for beginners to advanced available on the internet for completely free.
Unlock Free Access