Here are three different ways you can delete rows from SAS dataset. Each method has different ways to write a code, specify conditions based on that SAS deletes the rows from a dataset.
Method 1: Use DATA STEP To Delete Rows In SAS
/* delete rows if id>= 10 */
data new_dataset;
set original_dataset;
if ID>=10 then delete;
run;
Method 2: Use PROC SQL To Delete Rows In SAS
/* delete rows if id>= 10 */
proc sql;
delete from original_dataset where ID>= 10 ;
quit;
Method 3: Use PROC SORT To Delete (Duplicate) Rows In SAS
/* delete duplicate rows */
proc sort data=original_dataset nodupkey;
by ID;
quit;
There are different ways you could use these methods to delete rows in SAS. The following sample dataset will be used to demonstrate how to delete rows in SAS (Simplest Ways:)
/* create a dataset */
data myData;
input ID Name $ Dept $;
datalines;
1 Jan Sales
2 John Finance
3 Anna Admin
4 Harry Sales
4 Harry Admin
5 Kat Sales
6 Gill Sales
7 Tomy Finance
7 Tomy Admin
8 Mary Finance
9 Peter Sales
10 Kim Finance
;
run;
/* view dataset*/
proc print data=myData;
run;
Method 1: Use DATA STEP To Delete Rows In SAS
You can use the data step to delete rows from a sas dataset. You can decide if you want to update and delete rows from the original dataset or create a new dataset to store the output data.
Example 1: DATA STEP – Delete Rows Based On One Condition
The following code shows how to delete all the rows where the department is equal to “Sales” and store the data in the new dataset.
/* create a dataset */
data new_myData;
set myData;
if dept="Sales" then delete;
run;
/* view dataset */
proc print data=new_myData; run;
Example 2: DATA STEP – Delete Rows Based On Multiple Conditions
The following code shows how to delete all the rows where the department is equal to “Sales” and ID is greater than equal to 5, and store the data in the new dataset.
/* create a new dataset */
data new_myData;
set myData;
if dept="Sales" and id>=5 then delete;
run;
/* view dataset */
proc print data=new_myData; run;
If you observe the output, the only rows deleted which have ID values 5 or more and department is equal to Sales.
Example 3: DATA STEP – Delete Rows Based On One Of Multiple Conditions
The following code shows how to delete all the rows where the department is equal to “Sales” OR ID values are 5 or more, and store the data in the new dataset.
Any of the above conditions satisfies, it deletes the rows from a dataset.
/* create a new dataset */
data new_myData;
set myData;
if dept="Sales" or id>=5 then delete;
run;
/* view dataset */
proc print data=new_myData; run;
Example 4: DATA STEP- DELETE Rows From The Original Dataset
As explained in all the above examples, it deletes the rows and stores the output data in the new dataset. But you could also directly delete rows and update the original dataset instead of creating a new dataset.
The following example shows how to delete rows from the dataset without creating a new dataset.
/* delete rows and update original dataset */
data myData;
set myData;
if dept="Sales" or id>=5 then delete;
run;
/* view dataset */
proc print data=myData; run;
Method 2: Use PROC SQL To Delete Rows In SAS
You can use the proc sql query to delete rows from a sas dataset. You can decide if you want to update and delete rows from the original dataset or create a new dataset to store the output data.
Example 5: PROC SQL – Delete Rows Based On One Condition
The following code shows how to delete all the rows from the original dataset where the department is equal to “Sales”.
/* delete rows based on one condition */
proc sql;
delete from myData where dept="Sales";
quit;Example 6: PROC SQL – Delete Rows Based On Multiple Conditions
The following code shows how to delete all the rows from the original dataset where the department is equal to “Sales” and ID is greater than equal to 5.
/* delete rows based on multiple conditions */
proc sql;
delete from myData where dept="Sales" and id>=5;
quit;Example 7: PROC SQL – Delete Rows Based On One Of Multiple Conditions
The following code shows how to delete all the rows from the original dataset where the department is equal to “Sales” OR ID values are 5 or more.
Any of the above conditions satisfies, it deletes the rows from a dataset.
/* delete rows based on one of multiple conditions */
proc sql;
delete from myData where dept="Sales" or id>=5;
quit;Example 8: PROC SQL- DELETE Rows And Create A New Dataset To Store The Output Data
As explained in all the above examples, it deletes the rows from the original dataset. But you could also delete the rows and create a new dataset using proc sql query to store the output data instead of updating the original dataset.
The following example shows how to delete the rows and store the output data in the new dataset.
/* create a new dataset */
proc sql;
create table new_myData as
select * from myData where dept="Sales" or id>=5;
quit;
/* view dataset */
proc print data=new_myData;
run;
Method 3: Use PROC SORT To Delete (Duplicate) Rows In SAS
You can use the proc sort query to delete duplicate rows from a sas dataset. With the OUT= <option> you can create a new dataset instead of deleting rows from the original dataset.
The following example demonstrates how to delete duplicate rows using the proc sort procedure.
/* delete duplicate rows */
proc sort data=myData out=new_myData nodupkey;
by ID;
quit;
/* view dataset */
proc print data=new_myData; run;
FAQ – How To Delete Rows In SAS
DROP and DELETE are two different ways to remove data from a SAS dataset. DROP is used to remove one or more variables (columns) from a dataset, while DELETE is used to remove one or more observations (rows) from a dataset.
There are different ways you can delete all the rows from a SAS dataset. The easiest and fastest way is using a DATA step with obs=0 option. This tells SAS to read zero observations from the input dataset and create an empty output dataset.
For example, if you want to delete all the rows from the dataset work.cars, you can use this code:
data work.cars;
set work.cars (obs=0);
run;
You can also use the proc sql DELETE statement to delete all the rows from a SAS dataset.
You can use where clause to delete or exclude rows from the dataset and store the output in the new dataset. It can be done using DATA STEP or PROC SQL procedure.