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 (Partial Listing)

Program

libname proclib 'SAS-data-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

Chapter Contents
Previous
Next
Top of Page