Search the site...

SASCRUNCH TRAINING
  • Home
  • Member's Area
  • How to Start
  • SAS Interface
  • Creating a Data Set
  • Practical SAS Training Course
  • SAS Certified Specialist Training Program
  • Proc SQL Course
  • Introduction to Time Series Analysis
  • SAS Project Training Course
  • Full Training / Membership
  • Sign up
  • About us
  • Contact us
  • Home
  • Member's Area
  • How to Start
  • SAS Interface
  • Creating a Data Set
  • Practical SAS Training Course
  • SAS Certified Specialist Training Program
  • Proc SQL Course
  • Introduction to Time Series Analysis
  • SAS Project Training Course
  • Full Training / Membership
  • Sign up
  • About us
  • Contact us
Sentry Page Protection
Please Wait...
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)
Picture

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.
Picture

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

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

These two rows are removed from the updated PROFILE table:
Picture


The PROFILE table is created in the Work library:
Picture

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!
Picture

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?
Next

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.
Already a member? Go to member's area.