The SQL Procedure

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

Procedure features:

CREATE TABLE statement

AS query-expression

SELECT clause

	column alias
	FORMAT= column-modifier
	object-item

Other features:

data set option

OBS=

Tables:

PROCLIB.PAYROLL, PROCLIB.BONUS

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

Input Table

                               PROCLIB.PAYROLL
                              First 10 Rows Only

             Id
             Number  Gender  Jobcode    Salary    Birth    Hired
             ---------------------------------------------------
             1919    M       TA2         34376  12SEP60  04JUN87
             1653    F       ME2         35108  15OCT64  09AUG90
             1400    M       ME1         29769  05NOV67  16OCT90
             1350    F       FA3         32886  31AUG65  29JUL90
             1401    M       TA3         38822  13DEC50  17NOV85
             1499    M       ME3         43025  26APR54  07JUN80
             1101    M       SCP         18723  06JUN62  01OCT90
             1333    M       PT2         88606  30MAR61  10FEB81
             1402    M       TA2         32615  17JAN63  02DEC90
             1479    F       TA3         38785  22DEC68  05OCT89

Program

libname proclib 'SAS-library';

options nodate pageno=1 linesize=80 pagesize=40;

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

Output: Listing

                               BONUS Information

                           Id
                           Number    Salary     Bonus
                           --------------------------
                           1919     $34,376      $859
                           1653     $35,108      $878
                           1400     $29,769      $744
                           1350     $32,886      $822
                           1401     $38,822      $971
                           1499     $43,025    $1,076
                           1101     $18,723      $468
                           1333     $88,606    $2,215
                           1402     $32,615      $815
                           1479     $38,785      $970

Top of Page