SQL Procedure

Example 2: Creating a Table from a Query's Result

Features:

CREATE TABLE statement: AS query expression

SELECT clause:: column alias; FORMAT= column-modifier; object-item

Other features:

data set option: OBS=

Table names:

PROCLIB.PAYROLL

PROCLIB.BONUS

Details

This example builds a column with an arithmetic expression and creates the PROCLIB.BONUS table from the query's result.

proc sql outobs=10;
   title 'PROCLIB.PAYROLL';
   title2 'First 10 Rows Only';
   select * from proclib.payroll;
   title;

Query Result from PROCLIB.PAYROLL

Program

libname proclib 'SAS-library';

proc sql;
   create table proclib.bonus as

 select IdNumber, Salary format=dollar8.,
          salary*.025 as Bonus format=dollar8.
      from proclib.payroll;

title 'BONUS Information';

select *
      from proclib.bonus(obs=10);

Program Description

Declare the PROCLIB library.The PROCLIB library is used in these examples to store created tables.

libname proclib 'SAS-library';

Create the PROCLIB.BONUS table.The CREATE TABLE statement creates the table PROCLIB.BONUS from the result of the subsequent query.

proc sql;
   create table proclib.bonus as

Select the columns to include.The SELECT clause specifies that three columns will be in the new table: IdNumber, Salary, and Bonus. FORMAT= assigns the DOLLAR8. format to Salary. The Bonus column is built with the SQL expression salary*.025.

 select IdNumber, Salary format=dollar8.,
          salary*.025 as Bonus format=dollar8.
      from proclib.payroll;

Specify the title.

title 'BONUS Information';

Display the first 10 rows of the PROCLIB.BONUS table.The SELECT clause selects columns from PROCLIB.BONUS. The asterisk (*) selects all columns. The FROM clause specifies PROCLIB.BONUS as the table to select from. The OBS= data set option limits the printing of the output to 10 rows.

select *
      from proclib.bonus(obs=10);

Output

Creating a Table from a Query