By using PROC SQL, you can create, modify, and drop (delete) tables quickly and efficiently. Many PROC SQL statements are quite versatile, enabling you to perform the same action in several ways. For example, there are three methods of creating a table by using the CREATE TABLE statement:

The following PROC SQL step uses the CREATE TABLE statement to create an empty table by defining columns, and uses the DESCRIBE TABLE statement to display information about the table's structure in the SAS log:

proc sql;
   create table work.discount
          (Destination char(3),
          BeginDate num Format=date9.,
          EndDate num format=date9.,
          Discount num);
      describe table work.discount;

Table 5.1. SAS Log

In this chapter, you will learn to create and manage tables by using the PROC SQL statements shown above, and many others.

In this chapter, you learn to

Before beginning this chapter, you should complete the following chapters:

Suppose you want to create the temporary table Work.Discount, which contains data about discounts that are offered by an airline. There is no existing table that contains the four columns (and column attributes) that you would like to include: Destination, BeginDate, EndDate, and Discount. You use the following PROC SQL step to create the table, based on column definitions that you specify:

proc sql;
   create table work.discount 
          (Destination char(3), 
          BeginDate num Format=date9., 
          EndDate num format=date9., 
          Discount num); 

The SAS log confirms that the table has been created.

NOTE: Table WORK.DISCOUNT created, with 0 rows and 4 columns.

When you create a table by defining columns, you must specify a data type for each column, following the column name:

column-name data-type <(column-width)> <column-modifier-1

   <…column-modifier-n>>

For example, the following PROC SQL step (shown also in the previous section) defines four columns: one character column (Destination) and three numeric columns (BeginDate, EndDate, and Discount):

proc sql;
   create table work.discount
          (Destination char(3), 
          BeginDate num format=date9., 
          EndDate num format=date9., 
          Discount num); 

SAS tables use two data types: numeric and character. However, PROC SQL supports additional data types (many, but not all, of the data types that SQL-based databases support). Therefore, in the CREATE TABLE statement, you can specify any of 10 different data types. When the table is created, PROC SQL converts the supported data types that are not SAS data types to either numeric or character format.

The following PROC SQL step is very similar to the previous example. The only difference is that this step specifies three supported data types other than CHAR and NUM: VARCHAR, DATE, and FLOAT.

proc sql;
   create table work.discount2
          (Destination varchar(3), 
          BeginDate date, 
          EndDate date, 
          Discount float); 

PROC SQL will convert these data types to either character or numeric, as indicated in the charts above. Therefore, the table Work.Discount2 (created by this PROC SQL step) and Work.Discount (created by the previous PROC SQL step) will contain identical columns.

By supporting data types other than SAS data types, PROC SQL can save you time. In many cases, you can copy native code from an implementation of SQL that is external to SAS without having to modify the data types.

In SAS, the default column width for both character and numeric columns is 8 bytes. However, character and numeric data values are stored differently:

PROC SQL allows you to specify a column width for character columns but not for numeric columns.

In a column specification, the column width follows the data type and is specified as an integer enclosed in parentheses:

column-name data-type <(column-width)><column-modifier-1

<…column-modifier-n>>

In the following PROC SQL step, the first column specification indicates a column width of 3 for the character column Destination:

proc sql;
   create table work.discount
          (Destination char(3), 
          BeginDate num format=date9.,
          EndDate num format=date9.,
          Discount num);

Because the last three columns are numeric, no width is specified and these columns will have the default column width of 8 bytes.

In the CREATE TABLE statement, a column specification might include one or more of the following SAS column modifiers: INFORMAT=, FORMAT=, and LABEL=. Column modifiers, if used, are specified at the end of the column specification.

column-name data-type <(column-width)> < …column-modifier-1

<…column-modifier-n>>

The following PROC SQL step creates the table Work.Departments by specifying 4 columns. The column modifiers LABEL= and FORMAT= are used to specify additional column attributes.

proc sql;
   create table work.departments
          (Dept varchar(20) label='Department', 
           Code integer label='Dept Code', 
           Manager varchar(20),
           AuditDate num format=date9.); 

The SAS log verifies that the table was created.

NOTE: Table WORK.DEPARTMENTS created, with 0 rows and 4 columns.

Earlier in this chapter, the empty table Work.Discount was created by using the CREATE TABLE statement and column specifications shown below:

proc sql;
   create table work.discount
          (Destination char(3),
          BeginDate num format=date9.,
          EndDate num format=date9.,
          Discount num);

The following DESCRIBE TABLE statement writes a CREATE TABLE statement to the SAS log for the table Work.Discount:

proc sql;
   describe table work.discount;

Table 5.7. SAS Log

Suppose you want to create a new table, Work.Flightdelays2, that contains data about flight delays. You would like the new table to contain the same columns and attributes as the existing table Sasuser.Flightdelays, but you do not want to include any of the existing data. The following PROC SQL step uses a CREATE TABLE statement and a LIKE clause to create Work.Flightdelays2:

proc sql;
   create table work.flightdelays2
      like sasuser.flightdelays;

The following DESCRIBE TABLE statement displays the structure of the empty table Work.Flightdelays2:

proc sql;
      describe table work.flightdelays2;

Table 5.8. SAS Log

Work.Flightdelays2 contains eight columns, as listed.

If you want to create an empty table that contains only a specified subset of columns from an existing table, use the SAS data set options DROP= or KEEP=.

In the CREATE TABLE statement, the DROP= or KEEP= option can be inserted in either of the following locations:

The following PROC SQL step creates the new table Work.Flightdelays3 that contains a subset of columns from the table Sasuser.Flightdelays. The DROP= option is used to specify that all columns except DelayCategory and DestinationType will be included in the new table.

proc sql;
      create table work.flightdelays3
          (drop=delaycategory destinationtype) 
      like sasuser.flightdelays;

For comparison, the results of running the DESCRIBE TABLE statement for the original table and the new table are shown below.

Table 5.9. SAS Log

Table 5.10. SAS Log

As these messages show, Sasuser.Flightdelays contains the columns DelayCategory and DestinationType, while Work.Flightdelays3 does not.

Suppose you want to create a new, temporary table that contains data for ticket agents who are employed by an airline. The data that you need is a subset of the data contained in two existing tables, Sasuser.Payrollmaster and Sasuser.Staffmaster. The following PROC SQL step creates the new table Work.Ticketagents from the result of a query on the two existing tables. The WHERE clause selects the subset of rows for employees whose JobCode contains TA.

   proc sql;
      create table work.ticketagents as 
         select lastname, firstname, 
                jobcode, salary
            from sasuser.payrollmaster,
                 sasuser.staffmaster
            where payrollmaster.empid
                  = staffmaster.empid
                  and jobcode contains 'TA';

The new table Work.Ticketagents is not empty; it contains rows of data. Therefore, you can submit a SELECT statement to display Work.Ticketagents as a report:

select *
   from work.ticketagents;

The first four rows of Work.Ticketagents are shown below.

The SAS log also displays a message, indicating that the table has been created.

NOTE: Table WORK.TICKETAGENTS created, with 41 rows and 4 columns.

To copy a table quickly, you can use the CREATE TABLE statement with a query that returns an entire table instead of a subset of columns and rows. The CREATE TABLE statement should contain only the following clauses:

The following PROC SQL step creates the new table Work.Supervisors2, which is an exact duplicate of the source table Sasuser.Supervisors:

proc sql;
   create table work.supervisors2 as
      select *
         from sasuser.supervisors;

The first four rows of the two tables are shown below.

Figure 5.1. Source Table: Sasuser.Supervisors

Figure 5.2. New Table: Work.Supervisors2

Sometimes you need to add rows of data to a table, but the data is not currently contained in any table. In this situation, you can use either the SET clause or the VALUES clause in the INSERT statement to specify the data to be added.

The SET clause in the INSERT statement enables you to insert rows and specify new data to be added to a table. The SET clause specifies column names and values in pairs. PROC SQL reads each column name-value pair and assigns the value to the specified column. A separate SET clause is used for each row to be added to the table.

The syntax of the INSERT statement that contains the SET clause is shown below.

Consider the table Work.Discount, which was presented in the last topic. Work.Discount stores airline discounts for certain flight destinations and time periods in March. By submitting a DESCRIBE TABLE statement, you can see this table's columns and column attributes.

Table 5.13. SAS Log

The following PROC SQL step does both of the following:

In this situation, you do not need to include an optional list of column names.

The INSERT statement uses the VALUES clause to insert a list of values into a table. Unlike the SET clause, the VALUES clause does not specify a column name for each value, so the values must be listed in the correct order. Values must be specified in the order in which the columns appear in the table or, if an optional column list is specified, in the order in which the columns appear in that list.

You can use the VALUES clause to insert a value for all or only some of the columns in the table.

If you want to …	Then …	Example
insert a value for all columns in the table	You can omit the optional list of column names in the INSERT statement. PROC SQL reads values in the order in which they are specified in the VALUES clause inserts the values into columns in the order in which the columns appear in the table.
insert a value for only some of the columns in the table	You must include a list of column names in the INSERT statement. PROC SQL reads values in the order in which they are specified in the VALUES clause inserts the values into columns in the order in which the columns are specified in the column list.

You must list a value for every column into which PROC SQL will insert values (as specified in either the table or the optional list of column names). To specify that a value is missing, use a space enclosed in single quotation marks for character values and a period for numeric values. For example, the following VALUES clause specifies values to be inserted in three columns; the first two values are missing:

values (' ', ., 45)

In this example, the first value specified is a missing value for a character column, and the second value is a missing value for a numeric column.

Suppose you want to insert two more rows into the table Work.Discount, which stores airline discounts for certain flight destinations and time periods in March. In the previous section, you inserted two rows into Work.Discount by using the SET clause, so the table now looks like the following table.

Add two more rows, by using the VALUES clause. The following PROC SQL step adds two rows of new data to Work.Discount and generates a report that displays the updated table:

proc sql;
   insert into work.discount (destination,
          begindate,enddate,discount)
      values ('ORD','05MAR2000'd,'15MAR2000'd,.25) 
      values ('YYZ','06MAR2000'd,'20MAR2000'd,.10); 
      select *
      from work.discount;

The two rows that were just inserted by using the VALUES clause are the third and fourth rows above.

You might have noticed that the INSERT statement in this example includes an optional list of column names. In this example, data is being inserted into all columns of the table, and the values are listed in the order in which the columns appear in the table, so it is not strictly necessary to use a column list. However, including the list of column names makes it easier to read the code and understand what the code is doing.

The fastest way to insert rows of data into a table is to use a query to select existing rows from one or more tables (or views) and to insert the rows into another table. You can insert rows from a query result into either an empty table or a table that already contains rows of data. When you add rows of data to a table that already contains rows, the new rows are added at the end of the table.

To insert rows from a query result, use an INSERT statement that includes the clauses that are used in a query: SELECT, FROM, and any optional clauses, such as ORDER BY. Values from the query result are inserted into columns in the order in which the columns appear in the table or, if an optional column list is specified, in the order in which the columns appear in that list.

A mechanic at a company has been promoted from level 2 to level 3, and you need to add this employee to Sasuser.Mechanicslevel3, a table that lists all level-3 mechanics. Create a temporary copy of Sasuser.Mechanicslevel3 called Work.Mechanicslevel3_New, and display the new table in a report:

Next, you insert a row into Work.Mechanicslevel3_New for the new level-3 employee, whose EmpID is 1653. This employee is currently listed in Sasuser.Mechanicslevel2,so your INSERT statement queries the table Sasuser.Mechanicslevel2. Your PROC SQL step ends with a SELECT statement that outputs the revised table Work.Mechanicslevel3_New to a report.

The row that you have inserted into Work.Mechanicslevel3_New is row 8 above. As you can see, the values for JobCode and Salary for the new employee will have to be changed. Updating existing values in a table is covered later in this chapter.

General integrity constraints enable you to restrict the values of columns within a single table. The following four integrity constraints can be used as general integrity constraints:

A referential integrity constraint is created when a PRIMARY KEY integrity constraint in one table is referenced by a FOREIGN KEY integrity constraint in another table. There are two steps that must be followed to create a referential integrity constraint:

To create a table that has integrity constraints, use a CREATE TABLE statement that specifies both columns and constraints. There are two ways to specify integrity constraints in the CREATE TABLE statement:

You can use either or both of these methods in the same CREATE TABLE statement.

Earlier in this chapter, you learned how to create a table by using a CREATE TABLE statement that contains column specifications:

CREATE TABLE table-name

        (column-specification-1<,

        …column-specification-n>);

You also learned that a column specification consists of these elements:

column-definition <column-constraint-1<, … column-constraint-n>>

<MESSAGE='message-string' <MSGTYPE=message-type>>

The column specifications used in earlier examples contained only the column definition. Now we will learn how to create an integrity constraint with a column, by specifying the optional column constraint in the column specification:

Just like a column, an integrity constraint must have a unique name within the table. When you create an integrity constraint by specifying a column constraint in a column specification, then SAS automatically assigns a name to the constraint. The form of the constraint name depends on the type of constraint, as shown below:

Here is an example of a PROC SQL step that creates integrity constraints by specifying one or more column constraints in a column specification.

Suppose you need to create the table Work.Employees to store the identification number, name, gender, and hire date for all employees. In addition, you want to ensure that

The following PROC SQL step creates the table Work.Employees, which contains four columns and integrity constraints for two of those columns:

proc sql;
   create table work.employees
      (ID char (5)  primary key, 
      Name char(10),
      Gender char(1)  not null check(gender in ('M','F')), 
      HDate date label='Hire Date'),

In the column specification for ID, the PRIMARY KEY column constraint ensures that the ID column will contain only values that are nonmissing and unique.

The column specification for Gender defines two integrity constraints:

Here is another method of creating integrity constraints: specifying a constraint specification in the CREATE TABLE statement.

Sometimes you might prefer to create integrity constraints outside of column specifications, by specifying individual constraint specifications in the CREATE TABLE statement:

CREATE TABLE table-name

    (column-specification-1<,

    …column-specification-n><,

    constraint-specification-1><,

    …constraint-specification-n>);

The first specification in the CREATE TABLE statement must be a column specification. However, following the initial column specification in the statement, you can include multiple additional column specifications, constraint specifications, or both. All specifications after the first specification can be listed in any order. The entire list of column specifications and constraint specifications follows the same guidelines that were presented earlier for column specifications:

There are several important differences between specifying an integrity constraint within a column specification and specifying an integrity constraint by using a separate constraint specification. Using a constraint specification offers the following advantages:

The syntax of a constraint specification is shown below.

You might have noticed another difference between the two methods of creating an integrity constraint. When you use a column specification to create a FOREIGN KEY integrity constraint, you use the keyword FOREIGN KEY in addition to the keyword REFERENCES.

Here is an example of a PROC SQL step that uses column specifications to create integrity constraints on a column.

In an example earlier in this chapter, the table Work.Discount was created to hold data about discounts that are offered by an airline. Suppose you now want to ensure that the table

Create a new version of the table Work.Discount, called Work.Discount3, that includes two integrity constraints. One integrity constraint limits the values that can be entered in the Discount column and the other prevents missing values from being entered in the Destination column. The following PROC SQL step creates Work.Discount3 by specifying four columns and two integrity constraints:

proc sql;
   create table work.discount3
          (Destination char(3),
          BeginDate num Format=date9.,
          EndDate num format=date9.,
          Discount num,
          constraint ok_discount check (discount le .5), 
          constraint notnull_dest not null(destination)); 

The CHECK integrity constraint named OK_Discount uses the WHERE expression discount le .5 to limit the values that can be added to the Discount column.

The NOT NULL integrity constraint named NotNull_Dest prevents missing values from being entered in Destination.

In a previous section of this chapter, the following PROC SQL step was used to create the table Work.Discount3 with two integrity constraints, one on the column Discount and the other on the column Destination:

proc sql;
   create table work.discount3
          (Destination char(3),
          BeginDate num Format=date9.,
          EndDate num format=date9.,
          Discount num,
          constraint ok_discount check (discount le .5), 
          constraint notnull_dest not null(destination)); 

This table does not yet contain any rows, so add some data. The following INSERT statement uses the VALUES clause to add two rows of data to the table:

proc sql;
   insert into work.discount3
          values('CDG','03MAR2000'd,'10MAR2000'd,.15)
          values('LHR','10MAR2000'd,'12MAR2000'd,.55);

When this PROC SQL step is submitted, the following messages are displayed in the SAS log.

Table 5.14. SAS Log

The three parts of this message explain what the problem is:

We need to consider why SAS prevented any of the data from being added to the table.

When you use the INSERT or UPDATE statement to add or modify data in a table, you can control how PROC SQL handles updated data if any errors occur during the insertion or update process. You can use the UNDO_POLICY= option in the PROC SQL statement to specify whether PROC SQL will make or undo the changes you submitted up to the point of the error.

You can specify one of the following values for the UNDO_POLICY= option.

In the last example, the INSERT step was used to insert two rows of data into the table Work.Discount3, which has two integrity constraints. Because the UNDO_POLICY= option was not specified in the code, PROC SQL used the default policy, which is UNDO_POLICY=REQUIRED. When PROC SQL encountered a value in the INSERT statement that violated an integrity constraint, none of the new values specified in the INSERT statement were added to the table.

Consider what happens when we submit the same INSERT statement and specify the option UNDO_POLICY=NONE.

The following PROC SQL step creates the table Work.Discount4, which has four columns and two integrity constraints, and inserts the same two rows of data that were inserted in the earlier example. In this case, however, the option UNDO_POLICY=NONE is specified.

proc sql undo_policy=none; 
   create table work.discount4
          (Destination char(3),
          BeginDate num Format=date9.,
          EndDate num format=date9.,
          Discount num,
          constraint ok_discount check (discount le .5),
          constraint notnull_dest not null(destination));
   insert into work.discount4
          values('CDG','03MAR2000'd,'10MAR2000'd,.15)
          values('LHR','10MAR2000'd,'12MAR2000'd,.55);

As you know, one of the data values for the column Discount violates the specified constraint. When this step is submitted, the SAS log displays the following messages.

Table 5.15. SAS Log

The four parts of this message explain what the problem is and how PROC SQL will handle UNDO processing:

To display only the table constraints for the table Work.Discount4 that was created earlier, you submit the following PROC SQL step:

proc sql;
   describe table constraints work.discount4;

Table 5.16. SAS Log

As shown, Work.Discount4 has two integrity constraints: NotNull_Dest and OK_Discount.

To update all (or a subset of) rows in a column with the same expression, use an UPDATE statement that contains a SET clause and (optionally) a WHERE clause.

Suppose a company is considering giving all level-1 employees a 5% raise. Employee salaries are stored in the table Sasuser.Payrollmaster. You do not want to update the original table, so you create a temporary copy of Sasuser.Payrollmaster, called Work.Payrollmaster_New. The following PROC SQL step creates

Work.Payrollmaster_New based on a query result and generates an output report of the new table:

proc sql;
   create table work.payrollmaster_new as
      select *
         from sasuser.payrollmaster;
      select *
         from work.payrollmaster_new;

The first 10 rows of Work.Payrollmaster_New, the table in which you will update salaries, are shown below.

Next, you write a PROC SQL step that updates the specified rows. The UPDATE statement contains both of the following:

Finally, you can use a SELECT statement to display the updated table as a report. The first 10 rows of Work.Payrollmaster_New, with updates, are shown below.

The third row lists data for a level-1 employee, and that person's salary has been updated.

If you wanted to increase all of the salaries, you would simply remove the WHERE clause from the UPDATE statement:

proc sql;
   update work.payrollmaster_new
      set salary=salary*1.05

Sometimes you want to use different expressions to modify values for different subsets of rows within a column.

For example, instead of only raising the salary of level-1 employees by 5%, you might also want to raise the salaries of level-2 employees by 10%, and so on, using a different percentage increase for each group of employees.

There are two possible ways to use different expressions to update different subsets of rows.

Method of Updating Table	Example
use multiple UPDATE statements, one for each subset of rows A single UPDATE statement can contain only a single WHERE clause, so multiple UPDATE statements are needed to specify expressions for multiple subsets of rows.
use a single UPDATE statement that contains a CASE expression

The first method, which requires the use of multiple UPDATE statements, is cumbersome because the SET statement and expression must be repeated in each UPDATE statement. In this example, the first method is inefficient because the table Work.Payrollmaster_New must be read three times.

The second method, which uses conditional processing (the CASE expression), is recommended. We will now consider the second method.

To update different subsets of rows in a table in different ways, you can incorporate conditional processing by using the CASE expression in the SET clause of an UPDATE statement. The CASE expression selects result values that satisfy specified conditions.

In the following UPDATE statement, the CASE expression contains three WHEN-THEN clauses that specify three different subsets of rows in the table Work.Insure_New:

update work.insure_new
   set pctinsured=pctinsured*
      case 
         when company='ACME' 
              then 1.10 

      when company='RELIABLE' 
         then 1.15 
      when company='HOMELIFE' 
         then 1.25 
      else 1 
end;

PROC SQL updates each specified subset of rows differently, according to the corresponding WHEN-THEN (or ELSE) clause.

When you specify a CASE expression, PROC SQL updates each row as follows:

The use of the CASE expression is efficient because of the way PROC SQL processes the WHEN-THEN clauses. The WHEN-THEN clauses in the CASE expression are evaluated sequentially. When a matching case is found, the THEN expression is evaluated and set, and the remaining WHEN cases are not considered.

In the next few sections, you will learn about the use of the CASE expression in the UPDATE statement, without and with the optional case operand:

CASE <case-operand>

Here is an example of an UPDATE statement that uses the CASE expression for conditional processing. This example shows the form of the CASE expression that does not include the optional case operand.

Suppose a company is considering giving raises to all of its employees, with a different percentage for each employee level:

First, you create the temporary table Work.Payrollmaster3, which is a copy of Sasuser.Payrollmaster, the table containing the employee salary data. The first 10 rows of Work.Payrollmaster3 are shown below.

Next, you create a PROC SQL step that updates rows by using an UPDATE statement that contains a SET clause and a CASE expression:

   proc sql;
      update work.payrollmaster3 
         set salary=salary* 
            case 
               when substr(jobcode,3,1)='1' 
                    then 1.05
               when substr(jobcode,3,1)='2' 
                    then 1.10
               when substr(jobcode,3,1)='3' 
                    then 1.15
               else 1.08 
           end;

In this example, the CASE expression contains three WHEN clauses, one for each subset of rows (level-1, level-2, and level-3 employees), followed by an ELSE clause to handle any rows that do not meet the expected conditions.

The first 10 rows of Work.Payrollmaster3, after the rows have been updated, are shown below.

By comparing the values of Salary in the original and updated versions of Work.Payrollmaster3 (as shown above), you can see how the values have changed according to the job level indicated in the JobCode.

If the expression in the SET clause uses an equals (=) comparison operator, you might use the optional case operand in the CASE expression. Consider PROC SQL step that was shown in the preceding example, and see how the CASE expression in the UPDATE statement can be rewritten by using the alternate syntax.

In the following PROC SQL step, which was shown earlier, the CASE expression contains three WHEN-THEN clauses. These clauses contain similar expressions, each of which specifies the same SUBSTR function:

proc sql;
   update work.payrollmaster_new2
      set salary=salary*
         case
            when substr(jobcode,3,1)='1' 
                   then 1.05
            when substr(jobcode,3,1)='2' 
                   then 1.10
            when substr(jobcode,3,1)='3' 
                   then 1.15
            else 1.08
         end;

Because the expression in this SET clause uses an equals (=) operator, you can restructure the CASE expression for more efficient processing. In the alternate syntax, the repeated SUBSTR function is removed from each WHEN-THEN clause and is placed after the keyword CASE, as an operand:

proc sql;
   update work.payrollmaster_new2
      set salary=salary*
         case substr(jobcode,3,1) 
            when '1'
                 then 1.05
            when '2'
                 then 1.10
            when '3'
                 then 1.15
            else 1.08
         end;

Using the alternate syntax, the SUBSTR function is evaluated only once, so this PROC SQL step is more efficient than the original version.

You can use the CASE expression in three different PROC SQL statements: UPDATE, INSERT, and SELECT. In the SELECT statement, you can use the CASE expression within a new column definition to specify different values for different subsets of rows.

Suppose you want to generate an output report that displays employee names, job codes, and job levels. Your PROC SQL query selects LastName and FirstName from Sasuser.Staffmaster, and JobCode from Sasuser.Payrollmaster. The SELECT statement must define JobLevel as a new column, because it does not exist as a separate column in either table.

You want to assign the values of JobLevel, based on the number at the end of each jobcode. (The number at the end of each JobCode value is expected to be 1, 2, or 3.) To create JobLevel, you can use the case operand form of the CASE expression to specify the three possible conditions (plus an ELSE condition, just in case).

The PROC SQL query is shown below:

The SELECT clause uses the CASE expression to assign a value of junior, intermediate, senior, or none to each row in the new JobLevel column.

Suppose you want to delete the records for all frequent-flyer program members who have used up all of their frequent flyer miles or have spent more miles than they had in their accounts.

First, you create the temporary table Work.Frequentflyers2 by copying a subset of columns from the existing table Sasuser.Frequentflyers:

proc sql;
   create table work.frequentflyers2 as
      select ffid, milestraveled,
             pointsearned, pointsused
         from sasuser.frequentflyers;

The first 10 rows of Work.Frequentflyers2 are shown below.

Next, you write a PROC SQL step that deletes the specified rows:

proc sql;
   delete from work.frequentflyers2 
      where pointsearned-pointsused <= 0;

A message in the SAS log tells you how many rows were deleted.

NOTE: 13 rows were deleted from WORK.FREQUENTFLYERS2

To add columns to a table, use the ADD clause in the ALTER TABLE statement. The ADD clause specifies one or more column definitions. The syntax of a column definition is the same as in the CREATE TABLE statement:

column-name data-type <(column-width)> < column-modifier-1

< …column-modifier-n>>

However, in the ALTER statement, the entire group of column definitions is not enclosed in parentheses.

Suppose you are working with the temporary table Work.Payrollmaster4, which is an exact copy of the existing table Sasuser.Payrollmaster. The first 10 rows of Work.Payrollmaster4 are shown below.

The following PROC SQL step uses the ADD clause in the ALTER TABLE statement to add the columns Bonus and Level to Work.Payrollmaster4:

proc sql;
   alter table work.payrollmaster4 
      add Bonus num format=comma10.2, 
          Level char(3);

The first 10 rows of Work.Payrollmaster4, with the two new columns added, are shown below.

To drop (delete) existing columns from a table, use the DROP clause in the ALTER TABLE statement. The DROP clause specifies one or more column names, and multiple column names are separated by commas.

Suppose you want to drop the existing columns Bonus and Level from the temporary table Work.Payrollmaster4. (These two columns were added to the table in the example in the previous section.) The first 10 rows of Work.Payrollmaster4 are shown below.

The following PROC SQL step uses the DROP clause in the ALTER TABLE statement to drop the columns Bonus and Level from Work.Payrollmaster4:

proc sql;
   alter table work.payrollmaster4 
      drop bonus, level; 

The first 10 rows of Work.Payrollmaster4, with Bonus and Level deleted, are shown below.

To modify the attributes of one or more existing columns in a table, use the MODIFY clause in the ALTER TABLE statement. You can use the MODIFY clause to change a column's

Like the ADD clause, the MODIFY clause specifies one or more column definitions, each of which consists of

column-name <data-type (column-width)> <column-modifier-1

<…column-modifier-n>>

In each column definition, you specify the required element (the column name), followed by any of the optional attributes that you want to modify.

Suppose you want to modify the attributes of the existing column Salary in the temporary table Work.Payrollmaster4. The first 10 rows of Work.Payrollmaster4 (as it existed at the end of the previous example) are shown below.

The column Salary is a numeric field that currently has the format DOLLAR9. The following PROC SQL step modifies the format and adds a label for Salary:

proc sql;
   alter table work.payrollmaster4 
      modify salary format=dollar11.2 label="Salary Amt"; 

The first 10 rows of Work.Payrollmaster4, with the new column attributes for Salary, are shown below.

In the last few examples, the ALTER TABLE statement has made only one alteration to columns in a table, by using just one clause. However, you can include multiple clauses in a single ALTER TABLE statement to add, drop, and modify columns all at once.

Suppose you want to use a single ALTER TABLE statement to make all of the following alterations to the table Work.Payrollmaster4:

The first 10 rows of Work.Payrollmaster4, as it was at the end of the last example, are shown below.

The following PROC SQL step uses multiple clauses in the ALTER TABLE statement to make all three of the alterations listed above:

proc sql;
   alter table work.payrollmaster4 
      add Age num 
      modify dateofhire date format=mmddyy10. 
      drop dateofbirth, gender; 

The first 10 rows of Work.Payrollmaster4, with the three alterations, are shown below.

In the last few examples, you made several alterations to the temporary table Work.Payrollmaster4. Now you decide that you do not need this table anymore. The following PROC SQL step uses the DROP TABLE statement to drop Work.Payrollmaster4:

proc sql;
   drop table work.payrollmaster4; 

The SAS log displays a message indicating that the table has been dropped: