Altering Columns in a Table

Overview

You have seen how to delete rows in a table using the DELETE statement. To add, drop (delete), or modify columns in a table, use the ALTER TABLE statement.
General form, ALTER TABLE statement:
ALTER TABLE table-name
<ADD column-definition-1<, ... column-definition-n>>
<DROP column-name-1<, ... column-name-n>>
<MODIFY column-definition-1<, ... column-definition-n>>;
Here is an explanation of the syntax:
table-name
specifies the name of the table in which columns will be added, dropped, or modified.
<ADD, DROP, MODIFY>
at least one of the following clauses must be specified:
ADD
specifies one or more column-definitions for columns to be added.
DROP
specifies one or more column-names for columns to be dropped (deleted).
MODIFY
specifies one or more column-definitions for columns to be modified, where column-definition specifies a column to be added or modified, and is formatted as follows:
column-name data-type <(column-width)> <column-modifier-1
<...column-modifier-n>>
In all three clauses, multiple column-definitions or column-names must be separated by commas.
Note: You cannot use the ALTER TABLE statement with views.
Note: The ALTER TABLE statement also supports similar clauses that add, drop, and modify integrity constraints in an existing table. These clauses are not discussed in this chapter. To find out more about adding, dropping, and modifying integrity constraints, see the SAS documentation for the SQL procedure.
Consider each type of modification that can be made to a column by using the ALTER TABLE statement.

Adding Columns to a Table

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.

Example

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.
Work.Payrollmaster4
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, are shown below.
Work.Payrollmaster4
Use the UPDATE statement to populate the new columns.

Dropping Columns from a Table

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.

Example

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.
Work.Payrollmaster4
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, without Bonus and Level, are shown below.
Work.Payrollmaster4

Modifying Columns in a Table

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
  • length (column width) — for a character column only
  • informat
  • format
  • label.
Note: You cannot use the MODIFY clause to do the following:
  • change a character column to numeric or vice versa. To change a column's data type, drop the column and then add it (and its data) again, or use the DATA step.
  • change a column's name. You cannot change this attribute by using the ALTER TABLE statement. Instead, you can use the SAS data set option RENAME= or the DATASETS procedure with the RENAME statement. You can find out more about the DATASETS procedure with the RENAME statement in Creating Indexes.
Like the ADD clause, the MODIFY clause specifies one or more column definitions, each of which consists of the following:
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.
Note: When you use a column definition to add a new column by using the ADD clause in the ALTER TABLE statement, or to specify a new column in the CREATE TABLE statement, data-type is a required element. However, when you are using a column definition in the MODIFY clause in the ALTER TABLE statement, as shown in the following example, data-type is never required for numeric columns and is optional for character columns. You must specify data-type (column-width) only if you want to modify the column width of a character column.
Note: When modifying the width of a character variable, it is possible to truncate the variable's value if the length specification is too small.
alter table work.payrollmaster
modify jobcode char(2);
select * from payrollmaster;

Example

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.
Modifying Columns
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.
Modifying Columns

Adding, Dropping, and Modifying Columns in a Single Statement

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.

Example

Suppose you want to use a single ALTER TABLE statement to make all of the following alterations to the table Work.Payrollmaster4:
  • add the new column Age, by using the ADD clause
  • change the format of the DateOfHire column (which is currently DATE9.) to MMDDYY10., by using the MODIFY clause
  • drop the DateOfBirth and Gender columns, by using the DROP clause.
The first 10 rows of Work.Payrollmaster4, as it was at the end of the last example, are shown below.
Adding, Dropping, and Modifying Columns in a Single Statement
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.
Adding, Dropping, and Modifying Columns in a Single Statement
Use the UPDATE statement to populate the new columns.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.133.138.177