It’s always the best practice followed that beforeing creating or loading SAS dataset you should first check if it already exists and delete SAS data sets. It’s a standard procedure.
There are multiple ways you can delete SAS data sets. You can either delete or drop the dataset. It works more or like the same.
Difference Between DELETE and DROP in SAS
DELETE statement with PROC SQL procedure delete rows from the dataset whereas DELETE statement with PROC DATASETS and DROP statement with PROC SQL procedure delete entire dataset.
- Delete All the Rows: proc sql with DELETE keyword
- Delete Entire Dataset: proc sql with DROP, or proc datasets with DELETE keeyword
5+ Methods To Delete SAS Data Sets
- Method 1: Delete One Dataset Using proc datasets
- Method 2: Delete Multiple Datasets Using proc datasets
- Method 3: Delete Multiple Datasets Using proc sql.
- Method 4: Drop One or More Dataset Using proc sql.
- Method 5: Delete ALL the Datasets from SAS Library.
- Method 6: Delete SAS Data sets using proc delete procedure
The following sample data sets will be used to demonstrate 5 examples that show how you can delete datasets in SAS.
/* create sample datasets */
data mydata1;
input var1 var2 $;
datalines;
1 A
3 B
5 C
7 D
9 E
;
run;
data mydata2;
input var1 var2 $;
datalines;
2 X
4 Y
6 X
8 Z
10 N
;
run;
/* view dataset*/
proc print data=mydata1; title 'work.mydata1'; run;
proc print data=mydata2; title 'work.mydata2'; run;
Method 1: Delete One Dataset Using Proc Datasets
You can use the PROC DATASETS procedure to delete a sas dataset using DELETE statement. Now we have already created two datasets, work.mydata1 and work.mydata2 under the work library.
Let’s try to delete one dataset work.mydata1 using proc datasets procedure.
/* Example 1: delete one dataset */
proc datasets library=work nolist;
delete mydata1;
quit;
Method 2: Delete Multiple Datasets Using Proc Datasets
You can also use the proc datasets procedure to delete multiple datasets. You just need to list dataset names which you want to delete from the specific SAS library.
The below example demonstrates how you can delete multiple datasets using proc datasets.
/* Example 2: delete multiple datasets */
proc datasets library=work nolist;
delete mydata1 mydata2;
quit;
Method 3: Delete Multiple Datasets Using Proc Sql
You can use the PROC SQL procedure to delete one or more dataset like shown in the previous example. Here also you need to list down dataset names which you want to delete along with the lib reference.
The only difference between using proc datasets and proc sql is that with the proc datasets you could only delete datasets from any specific library at a time. Whereas with the proc sql you can delete datasets from the different libraries.
In the below example we are deleting both the datasets using proc sql procedure.
/* Example 3: delete multiple datasets using proc sql*/
proc sql;
delete from work.mydata1;
delete from work.mydata2;
quit;
If you observe above result, you can see all the rows were deleted from the datasets. The metadata still exists with the empty dataset.
It can be clearly visible when you run proc contents on those two deleted datasets and read the logs.
/* view dataset*/
proc print data=mydata1; title 'work.mydata1'; run;
proc print data=mydata2; title 'work.mydata2'; run;
Method 4: Drop One or More Dataset Using proc sql
This is yet another powerful method to drop or delete multiple datasets using proc sql but using DROP statements. It also allows you to drop datasets from the different SAS libraries.
/* Example 4: drop multiple datasets using proc sql*/
proc sql;
drop table work.mydata1;
drop table work.mydata2;
quit;
Method 5: Delete ALL the Datasets from SAS Library
There might be a situation where you want to delete all the datasets from the specific library. Here technically you could use any of the above listed methods, list down all the dataset names and delete the datasets.
But there is one more efficient way to delete all the datasets without even listing down dataset names. You can use the proc datasets procedure with the KILL statement.
The following code shows how you can delete all the datasets from the WORK library with a single line of code.
/* Example 5: delete ALL datasets from SAS library */
proc datasets library=work kill;
quit;
Method 6: Delete SAS Data sets using PROC DELETE Procedure
This is a new SAS procedure can also be used to delete sas data sets. The DELETE procedure deletes SAS files from the disk or tape on which it is stored.
Use PROC DELETE to do the following:
- delete either permanent or temporary SAS files
- delete a list of data sets with a numeric suffix, such as data=x1-x3
- delete all SAS files of the same type using MEMTYPE=
One of the benefits of using PROC DELETE instead of the DELETE statement in the DATASETS procedure is that it does not use the in-memory directory to delete SAS data sets.
The DELETE procedure produces no printed output. As a result, the DELETE procedure is faster.
/* Example 6: delete sas datasets using proc delete*/
proc delete library=work data=mydata1 mydata2;
quit;
FAQ
The simplest way to delete a SAS data set is to use PROC DELETE, which has one job and does it efficiently.
Alternatively you can use PROC DATASETS or PROC SQL procedures to delete sas data sets.
You can delete multiple SAS data sets at once by using PROC DATASETS with the DELETE statement. You can list the names of the data sets to be deleted after the DELETE keyword.
You can also use PROC SQL with DELETE or DROP statement and list data set names which you want to delete it.