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.
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.
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.
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);
Example 8: Creating a View from a Query’s Result 291