Creating and Updating Tables and Views

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;
   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

                            Population Density Table

                                                               Population
       Name                  Capital               Population     Density
       ------------------------------------------------------------------
       Brazil                Brasilia             160,310,357         .  
       China                 Beijing            1,202,215,077         .  
       India                 New Delhi            929,009,120         .  
       Indonesia             Jakarta              202,393,859         .  
       Russia                Moscow               151,089,979         .  
       United States         Washington           263,294,808         .

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

                            Population Density Table

                                                               Population
       Name                  Capital               Population     Density
       ------------------------------------------------------------------
       Brazil                Brasilia             160,310,357       48.78
       China                 Beijing            1,202,215,077      325.27
       India                 New Delhi            929,009,120      759.86
       Indonesia             Jakarta              202,393,859      273.10
       Russia                Moscow               151,089,979       22.92
       United States         Washington           263,294,808       69.52

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 recreate 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;
   title "World's Largest Countries";
   alter table sql.newcountries
      modify population format=comma15.;
   select name, population from sql.newcountries;

Modifying a Column Format

                           World's Largest Countries

              Name                                      Population
              ----------------------------------------------------
              Brazil                                   160,310,357
              China                                  1,202,215,077
              India                                    929,009,120
              Indonesia                                202,393,859
              Russia                                   151,089,979
              United States                            263,294,808

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

                           World's Largest Countries

          Name
          ------------------------------------------------------------
          The United Nations member country is Brazil                 
          The United Nations member country is China                  
          The United Nations member country is India                  
          The United Nations member country is Indonesia              
          The United Nations member country is Russia                 
          The United Nations member country is United States

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;

Top of Page