SQL Procedure

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

Features:

CREATE VIEW statement

GROUP BY clause

SELECT clause : COUNT function

HAVING clause

Other features:

AVG summary function

data set option : PW=

Table names:

PROCLIB.PAYROLL

PROCLIB.JOBS

Details

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

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

PROCLIB.PAYROLL

Program

libname proclib 'SAS-library';

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

title2;

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.JOBS view.CREATE VIEW creates the PROC SQL view PROCLIB.JOBS. The PW= data set option assigns password protection to the data that is generated by this view.

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

Select the columns.The SELECT clause specifies four columns for the view: Jobcode and three columns, Number, AVGAGE, and AVGSAL, whose values are the products functions. COUNT returns the number of nonmissing values for each job code because the data is grouped by Jobcode. LABEL= assigns a label to the column.

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

Calculate the Avgage and Avgsal columns.The AVG summary function calculates the average age and average salary for each job code.

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

Specify the table from which the data is obtained.The FROM clause specifies PAYROLL as the table to select from. PROC SQL assumes the libref of PAYROLL to be PROCLIB because PROCLIB is used in the CREATE VIEW statement.

from payroll

Organize the data into groups and specify the groups to include in the output.The GROUP BY clause groups the data by the values of Jobcode. Thus, any summary statistics are calculated for each grouping of rows by value of Jobcode. The HAVING clause subsets the grouped data and returns rows for job codes that contain an average age of greater than or equal to 30.

group by jobcode
      having avgage ge 30;

Specify the titles.

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

Display the entire PROCLIB.JOBS view.The SELECT statement selects all columns from PROCLIB.JOBS. PW=RED is necessary because the view is password protected.

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

title2;

Output

View Created from the Results of a Query

Current Summary Information for Each Job Category