Creating and Updating Tables and Views

Creating Tables

The CREATE TABLE statement enables you to create tables without rows from column definitions or to create tables from a query result. You can also use CREATE TABLE to copy an existing table.

Creating Tables from Column Definitions

You can create a new table without rows by using the CREATE TABLE statement to define the columns and their attributes. You can specify a column's name, type, length, informat, format, and label.

The following CREATE TABLE statement creates the NEWSTATES table:

proc sql;
   create table sql.newstates
          (state char(2),           /* 2-character column for               */
                                    /* state abbreviation                   */

           date num                 /* column for date of entry into the US */
                informat=date9.     /* with an informat                     */  
                format=date9.,      /* and format of DATE9.                 */
                  
           population num);         /* column for population                */

The table NEWSTATES has three columns and 0 rows. The char(2) modifier is used to change the length for State.

Use the DESCRIBE TABLE statement to verify that the table exists and to see the column attributes. The following DESCRIBE TABLE statement writes a CREATE TABLE statement to the SAS log:

proc sql;
   describe table sql.newstates;

Table Created from Column Definitions

1  proc sql;
2     describe table sql.newstates;
NOTE: SQL table SQL.NEWSTATES was created like:

create table SQL.NEWSTATES( bufsize=8192 )
  (
   state char(2),
   date num format=DATE9. informat=DATE9.,
   population num
  );

DESCRIBE TABLE writes a CREATE TABLE statement to the SAS log even if you did not create the table with the CREATE TABLE statement. You can also use the CONTENTS statement in the DATASETS procedure to get a description of NEWSTATES.

Creating Tables from a Query Result

To create a PROC SQL table from a query result, use a CREATE TABLE statement, and place it before the SELECT statement. When a table is created this way, its data is derived from the table or view that is referenced in the query's FROM clause. The new table's column names are as specified in the query's SELECT clause list. The column attributes (the type, length, informat, and format) are the same as those of the selected source columns.

The following CREATE TABLE statement creates the DENSITIES table from the COUNTRIES table. The newly created table is not displayed in SAS output unless you query the table. Note the use of the OUTOBS option, which limits the size of the DENSITIES table to 10 rows.

proc sql outobs=10;   
   title 'Densities of Countries';
   create table sql.densities as
      select Name 'Country' format $15.,
             Population format=comma10.0,
             Area as SquareMiles,
             Population/Area format=6.2 as Density
         from sql.countries;

   select * from sql.densities;

Table Created from a Query Result

                             Densities of Countries

               Country          Population  SquareMiles  Density
               -------------------------------------------------
               Afghanistan      17,070,323       251825    67.79
               Albania           3,407,400        11100   306.97
               Algeria          28,171,132       919595    30.63
               Andorra              64,634          200   323.17
               Angola            9,901,050       481300    20.57
               Antigua and Bar      65,644          171   383.88
               Argentina        34,248,705      1073518    31.90
               Armenia           3,556,864        11500   309.29
               Australia        18,255,944      2966200     6.15
               Austria           8,033,746        32400   247.96

The following DESCRIBE TABLE statement writes a CREATE TABLE statement to the SAS log:

proc sql;
   describe table sql.densities;

SAS Log for DESCRIBE TABLE Statement for DENSITIES

NOTE: SQL table SQL.DENSITIES was created like:

create table SQL.DENSITIES( bufsize=8192 )
  (
   Name char(35) format=$15. informat=$35. label='Country',
   Population num format=COMMA10. informat=BEST8. label='Population',
   SquareMiles num format=BEST8. informat=BEST8. label='SquareMiles',
   Density num format=6.2
  );

In this form of the CREATE TABLE statement, assigning an alias to a column renames the column, while assigning a label does not. In this example, the Area column has been renamed to SquareMiles, and the calculated column has been named Densities. However, the Name column retains its name, and its display label is Country .

Creating Tables Like an Existing Table

To create an empty table that has the same columns and attributes as an existing table or view, use the LIKE clause in the CREATE TABLE statement. In the following example, the CREATE TABLE statement creates the NEWCOUNTRIES table with six columns and 0 rows and with the same column attributes as those in COUNTRIES. The DESCRIBE TABLE statement writes a CREATE TABLE statement to the SAS log:

proc sql;
   create table sql.newcountries
      like sql.countries;

   describe table sql.newcountries;

SAS Log for DESCRIBE TABLE Statement for NEWCOUNTRIES

NOTE: SQL table SQL.NEWCOUNTRIES was created like:

create table SQL.NEWCOUNTRIES( bufsize=16384 )
  (
   Name char(35) format=$35. informat=$35.,
   Capital char(35) format=$35. informat=$35. label='Capital',
   Population num format=BEST8. informat=BEST8. label='Population',
   Area num format=BEST8. informat=BEST8.,
   Continent char(35) format=$35. informat=$35. label='Continent',
   UNDate num format=YEAR4.
  );

Copying an Existing Table

A quick way to copy a table using PROC SQL is to use the CREATE TABLE statement with a query that returns an entire table. This example creates COUNTRIES1, which contains a copy of all the columns and rows that are in COUNTRIES:

create table countries1 as 
   select * from sql.countries;

Using Data Set Options

You can use SAS data set options in the CREATE TABLE statement. The following CREATE TABLE statement creates COUNTRIES2 from COUNTRIES. The DROP= option deletes the UNDate column, and UNDate does not become part of COUNTRIES2:

create table countries2 as 
   select * from sql.countries(drop=UNDate);

Top of Page