Altering Columns

The ALTER TABLE statement adds, modifies, and deletes columns in existing tables. You can use the ALTER TABLE statement with tables only; it does not work with views. A note appears in the SAS log that describes how you have modified the table.

Adding a Column

The ADD clause adds a new column to an existing table. You must specify the column name and data type. You can also specify a length (LENGTH=), format (FORMAT=), informat (INFORMAT=), and a label (LABEL=). The following ALTER TABLE statement adds the numeric data column Density to the NEWCOUNTRIES table:

proc sql;
   delete from sql.newcountries;
   insert into sql.newcountries
   select * from sql.countries
      where population ge 130000000;

proc sql;
   alter table sql.newcountries
      add density num label='Population Density' format=6.2;

   title "Population Density Table";
   select name format=$20., 
          capital format=$15.,
          population format=comma15.0,
          density
      from sql.newcountries;

Adding a New Column

The new column is added to NEWCOUNTRIES, but it has no data values. The following UPDATE statement changes the missing values for Density from missing to the appropriate population densities for each country:

proc sql;
   update sql.newcountries
      set density=population/area;

   title "Population Density Table";
   select name format=$20., 
          capital format=$15.,
          population format=comma15.0,
          density
      from sql.newcountries;

Filling in the New Column's Values

For more information about how to change data values, see Updating Data Values in a Table.

You can accomplish the same update by using an arithmetic expression to create the Population Density column as you re-create the table:

proc sql;
   create table sql.newcountries as
   select *, population/area as density
             label='Population Density' 
             format=6.2
      from sql.newcountries;

See Calculating Values for another example of creating columns with arithmetic expressions.

Modifying a Column

You can use the MODIFY clause to change the width, informat, format, and label of a column. To change a column's name, use the RENAME= data set option. You cannot change a column's data type by using the MODIFY clause.

The following MODIFY clause permanently changes the format for the Population column:

proc sql;
   delete from sql.newcountries;
   create table sql.newcountries as
   select * from sql.countries
      where population ge 130000000;

proc sql;
   title "World's Largest Countries";
   alter table sql.newcountries
      modify population format=comma15.;
   select name, population from sql.newcountries;

Modifying a Column Format

You might have to change a column's width (and format) before you can update the column. For example, before you can prefix a long text string to Name, you must change the width and format of Name from 35 to 60. The following statements modify and update the Name column:

proc sql;
   title "World's Largest Countries";
   alter table sql.newcountries
      modify name char(60) format=$60.;
   update sql.newcountries
      set name='The United Nations member country is '||name;
  
   select name from sql.newcountries;

Changing a Column's Width

Deleting a Column

The DROP clause deletes columns from tables. The following DROP clause deletes UNDate from NEWCOUNTRIES:

proc sql;
   alter table sql.newcountries
      drop undate;