In this article you will learn how you can delete data in SAS. There are two common operations most people do on SAS datasets:
- DELETE the entire data set
- DELETE specific rows from a data set
You can remove data using two SAS procedures such as proc sql and proc datasets. You can also use the SAS data step to delete data from a data set.
1. DELETE the entire SAS data set
In sas you can drop an entire table with its structure as well as you can just delete the data keeping its table structure intact.
PROC SQL:
Use proc sql without any where condition to delete all the rows from sas datasets. This is the simplest and easiest way to delete data from a table. List as many tables you want to delete inside the proc sql procedure.
Example: for demonstration purposes let’s create a temporary work table named “cars” and perform deletion on that table.
data work.cars;
set sashelp.cars;
run;
Delete all the rows from a SAS data set:
proc sql;
delete from work.cars;
quit;
PROC DATASETS
This is also a widely used procedure to delete one or multiple datasets in one go. You could even delete data from the entire library. This is equivalent to dropping a table from database.
Delete one data set from a library
proc datasets library=work;
delete cars ;
quit;
proc contents data=work.cars ; run;
proc datasets procedure drops the entire table hence you don’t find the table structure. Check out the proc contents procedure, it got failed because work.cars data set dropped before running this procedure.
DELETE multiple datasets from a SAS library
Let’s assume you want to delete multiple tables named as cars_10, cars_11, cars_12, cars_13, cars_14, etc.
First, let’s create them in the work library for the demonstration.
data work.cars_10 work.cars_11 work.cars_13 work.cars_14;
set sashelp.cars;
run;
Delete multiple tables using proc datasets:
proc datasets library=work;
delete cars_10 cars_11 cars_12 cars_13 cars_14 ;
quit;
Special Case:
If your dataset list is too long but their names are in sequence (observe the above data set names). You can easily delete them just by specifying the first and last name within the range.
For example,
proc datasets library=work;
delete cars_10-cars_14 ;
quit;
Delete all tables from a library:
This is more wild. If you have a situation where you want to delete all the data present inside any specific library then still it is possible with proc datasets.
proc datasets library=work kill;
quit;
SAS DATA Step
In this method you can perform table recreation with zero rows. In other words you’re deleting all the rows keeping its structure intact by recreating it.
It’s by far the quickest way to empty a huge data set. At least I use it more often. Please note since you’re recreating a table with zero rows, table creation date gets changed.
Carefully observe the below example where we’re using same table in SET statement to recreate it but with zero rows by using obs=0
data work.cars_10;
set work.cars_10(obs=0);
run;
There is another way to empty data or remove all the records from a data set using IF statement in data step.
In the below example the condition 1=2 is always a false hence it’ll not execute the output statement. It means zero records will be written in the work.cars_11 data set.
data work.cars_11;
set work.cars_11;
if 1=2 then output; /* 1=2 is always false condition */
run;
2. DELETE Rows from SAS Dataset
At some point you’ll need to delete a few rows from a huge data set due to various reasons. The more popular reason is wrong data populated in the target table. In many scenarios you might want to delete a group of rows from a SAS data set.
You can achieve this by using different methods. Let’s deep dive into all of them with practical examples.
PROC SQL
Use proc sql if you know exact filter criteria on the basis of that you can filter data and delete those records. This is the easiest method to delete rows from a dataset. Please note if you miss to add where clause in proc sql then you might lose all the rows from the table.
Example: for demonstration purposes let’s create a temporary work table named “cars” and delete all the rows which have type=SUV.
data work.cars;
set sashelp.cars;
run;
proc sql;
delete from work.cars where type='SUV';
quit;
SAS DATA step
SAS data step can be also used to delete specific rows from a sas data set. Here also you need to be super careful with the condition you write. IF statement along with DELETE is really handy to write a simple code to delete records.
data work.cars;
set work.cars;
IF type=”SUV” then DELETE;
run;
You could add multiple conditions to define your deletion criteria. It doesn’t matter how many conditions you add. Let’s say, the type is SUV and Weight is more than 4000.
data work.cars;
set work.cars;
IF type="SUV" AND Weight>4000 then DELETE;
run;
Negative Condition (IF with OUTPUT statement)
This is a bit complicated. You can use negation with the “output” statement. In the below example you’re telling SAS that it only writes into the final data set if type is not SUV and Weight is less than equal to 4000. It is the same condition which we saw in the earlier example.
data work.cars;
set work.cars;
IF type ne "SUV" AND Weight <= 4000 then OUTPUT;
run;
FAQ
Yes. You can delete all/specific rows without dropping table structure. You can either use PROC SQL or DATA Step depending on results you want to achieve.
You can perform delete operation on SAS dataset using either PROC SQL or DATA step. You can use WHERE clause if you’re using proc sql or IF-ELSE condition in case if you’re using Data Step.