The SQL Procedure

Example 8: Creating a View from a Query's Result

Procedure features:

CREATE VIEW statement

GROUP BY clause

SELECT clause

COUNT function

HAVING clause

Other features:

AVG summary function

data set option

PW=

Tables:

PROCLIB.PAYROLL, PROCLIB.JOBS

This example creates the PROC SQL view PROCLIB.JOBS from the result of a query-expression.

Input Table

                                PROCLIB.PAYROLL
                               First 10 Rows Only

              Id
              Number  Gender  Jobcode    Salary    Birth    Hired
              ---------------------------------------------------
              1009    M       TA1         28880  02MAR59  26MAR92
              1017    M       TA3         40858  28DEC57  16OCT81
              1036    F       TA3         39392  19MAY65  23OCT84
              1037    F       TA1         28558  10APR64  13SEP92
              1038    F       TA1         26533  09NOV69  23NOV91
              1050    M       ME2         35167  14JUL63  24AUG86
              1065    M       ME2         35090  26JAN44  07JAN87
              1076    M       PT1         66558  14OCT55  03OCT91
              1094    M       FA1         22268  02APR70  17APR91
              1100    M       BCK         25004  01DEC60  07MAY88

Program

libname proclib 'SAS-library';

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

proc sql;
   create view proclib.jobs(pw=red) as

      select Jobcode,
             count(jobcode) as number label='Number',

             avg(int((today()-birth)/365.25)) as avgage
                format=2. label='Average Age',
             avg(salary) as avgsal
                format=dollar8. label='Average Salary'

      from payroll

      group by jobcode
      having avgage ge 30;

   title 'Current Summary Information for Each Job Category';
   title2 'Average Age Greater Than or Equal to 30';

   select * from proclib.jobs(pw=red);

Output: Listing

               Current Summary Information for Each Job Category
                    Average Age Greater Than Or Equal to 30

                                         Average   Average
                      Jobcode    Number      Age    Salary
                      ------------------------------------
                      BCK             9       36   $25,794
                      FA1            11       33   $23,039
                      FA2            16       37   $27,987
                      FA3             7       39   $32,934
                      ME1             8       34   $28,500
                      ME2            14       39   $35,577
                      ME3             7       42   $42,411
                      NA1             5       30   $42,032
                      NA2             3       42   $52,383
                      PT1             8       38   $67,908
                      PT2            10       43   $87,925
                      PT3             2       54   $10,505
                      SCP             7       37   $18,309
                      TA1             9       36   $27,721
                      TA2            20       36   $33,575
                      TA3            12       40   $39,680

Top of Page