Sentry Page Protection
Proc SQL II [16-17]

Deleting Rows and Tables
In this section, we will discuss how to delete rows from a table or drop the table altogether.

Let's look at an example.​

Copy and run the code from the yellow box below:

The PROFILE table, again, contains a list of five subjects with the following three columns:
  • ID
  • GENDER
  • DOB (Date of Birth)

We will now delete the subjects who were born before January 1, 1985.
proc sql;
delete from profile
where dob < '01JAN1985'd;
quit;

The DELETE statement tells SAS to delete the rows from the PROFILE table.

The WHERE clause specifies the condition(s) to delete the rows.

There were two subjects who were born before January 1, 1985 in the original PROFILE table:

These two rows are removed from the updated PROFILE table:

The PROFILE table is created in the Work library:

You can use the DROP statement in the SQL procedure to completely delete it.

Example
proc sql;
drop table profile;
quit;

The table is now deleted from the library!
Exercise

Copy and run the CARS table from the yellow box below:

The CARS table is a subset of the built-in sashelp.cars table.

Write a Proc SQL step to delete the cars with horsepower below 400.

​How many cars are left in the table?
Need some help? 


HINT:
Use the DELETE statement to delete rows from the table.


SOLUTION:

proc sql;
delete from cars
where horsepower < 400;
quit;

There are only seven cars left in the table.

Fill out my online form.