Inserting Rows into Tables

Inserting Rows with the SET Clause

With the SET clause, you assign values to columns by name. The columns can appear in any order in the SET clause. The following INSERT statement uses multiple SET clauses to add two rows to NewCountries:

/* Create the newcountries table.   */
proc sql;
   create table newcountries
      like countries;
quit;

/* Insert all of the rows from countries into newcountries based  */
/* on a population of 130000000.                                  */
proc sql;
   insert into newcountries
   select * from countries
      where population ge 130000000;
quit;

/* Insert 2 new rows in the newcountries table.     */
/* Print the table.                                 */
proc sql;
   insert into newcountries
      set name='Bangladesh',
          capital='Dhaka',
          population=126391060
      set name='Japan',
          capital='Tokyo',
          population=126352003;
      
   title "World's Largest Countries";
   select name format=$20., 
          capital format=$15.,
          population format=comma15.0
      from newcountries;
quit;

Rows Inserted with the SET Clause

Note the following features of SET clauses:

• As with other SQL clauses, use commas to separate columns. In addition, you must use a semicolon after the last SET clause only.
• If you omit data for a column, then the value in that column is a missing value.
• To specify that a value is missing, use a blank in single quotation marks for character values and a period for numeric values.

Inserting Rows with the VALUES Clause

With the VALUES clause, you assign values to a column by position. The following INSERT statement uses multiple VALUES clauses to add rows to NewCcountries. Recall that NewCountries has six columns, so it is necessary to specify a value or an appropriate missing value for all six columns. See the results of the DESCRIBE TABLE statement in Creating Tables like an Existing Table for information about the columns of NewCountries.

proc sql;
   insert into newcountries
      values ('Pakistan', 'Islamabad', 123060000, ., ' ', .)
      values ('Nigeria', 'Lagos', 99062000, ., ' ', .); 
   title "World's Largest Countries";
   select name format=$20., 
          capital format=$15.,
          population format=comma15.0
      from newcountries;
quit;

Rows Inserted with the VALUES Clause

Note the following features of VALUES clauses:

• As with other SQL clauses, use commas to separate columns. In addition, you must use a semicolon after the last VALUES clause only.
• If you omit data for a column without indicating a missing value, then you receive an error message and the row is not inserted.
• To specify that a value is missing, use a space in single quotation marks for character values and a period for numeric values.

Inserting Rows with a Query

You can insert the rows from a query result into a table. The following query returns rows for large countries (more than 130 million in population) from table Countries. The INSERT statement adds the data to the empty table NewCountries, which was created earlier in Creating Tables like an Existing Table:

proc sql;
   create table newcountries
      like countries;
quit;

proc sql;
   title "World's Largest Countries";
   insert into newcountries
   select * from countries
      where population ge 130000000;

   select name format=$20., 
          capital format=$15.,
          population format=comma15.0
      from newcountries;
quit;

Rows Inserted with a Query

Because the target table that is specified by the INSERT statement does not specify specific columns to insert in the Newcountries table, you must select all columns in the query. If the query does not select every column that exists in the target table, an error occurs and the row is not inserted. The UNDO_POLICY= option does not prevent the error. For more information about how PROC SQL handles errors during data insertions, see Handling Update Errors.

To insert rows by using a query for a subset of columns from the source table, specify all column names in a comma-separated list, enclosed in parentheses, in the INSERT statement. In the SELECT clause, specify column names that correspond to the columns of the INSERT statement. The order and number of columns must match in the INSERT statement and in the SELECT clause.

proc sql;
  create table newcountries
  like countries;
quit;

proc sql;
   title "World's Largest Countries";
   insert into newcountries (Name,Population)
   select Name,Population from countries
      where population ge 130000000;

   select name format=$35., population format=comma15.0
      from newcountries;
quit;

Smaller Number of Columns in Rows Inserted with a Query

An error occurs if the query selects more columns than what exists for column names in the table that is specified in the INSERT statement.

Using Data Set Options with the INSERT Statement

SAS data set options can be specified in INSERT statements that specify a SQL expression. If table NEWCOUNTRIES_SEC was created with SAS password PW=green, you can specify SAS data set options in the INSERT statement as follows:

proc sql;
   create table newcountries_sec (pw=green)
     like countries;

   insert into newcountries_sec (pw=green)
   select * from countries
      where population ge 130000000;
quit;

When all columns of an input table are selected for insertion, the data set option is specified after the table name.

When values are inserted into specific columns, the data set option is specified in the INSERT column list.

insert into newcountries_sec (pw=green,Name,Population)
select Name,Population from countries
where population ge 130000000;

The location of a SAS data set option also depends on the function of the data set option. For more information, see Data Set Options and the INSERT Statement and SAS Data Set Options: Reference.