SAS 9.4 SQL Procedure User's Guide, Third Edition, 3rd Edition

Output: Subset the Query

Output 7.11 Query Results with the Value TA

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;

Example 8: Creating a View from a Query’s Result 289

Figure 7.9 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';

290 Chapter 7 • SQL Procedure

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;

Example 8: Creating a View from a Query’s Result 291

Output: Creating a View from a Query’s Result

Output 7.12 Current Summary Information for Each Job Category

Example 9: Joining Three Tables

Features: FROM clause

joined-table component

WHERE clause

Table names: Proclib.Staff2

Proclib.Schedule2

Proclib.Superv2

Details

This example joins three tables and produces a report that contains columns from each

table.

292 Chapter 7 • SQL Procedure

Example Code 7.1 Proclib.Staff2 Table

data proclib.staff2;

input IdNum $4. @7 Lname $12. @20 Fname $8. @30 City $10.

@42 State $2. @50 Hphone $12.;

datalines;

1106 MARSHBURN JASPER STAMFORD CT 203/781-1457

1430 DABROWSKI SANDRA BRIDGEPORT CT 203/675-1647

1118 DENNIS ROGER NEW YORK NY 718/383-1122

1126 KIMANI ANNE NEW YORK NY 212/586-1229

1402 BLALOCK RALPH NEW YORK NY 718/384-2849

1882 TUCKER ALAN NEW YORK NY 718/384-0216

1479 BALLETTI MARIE NEW YORK NY 718/384-8816

1420 ROUSE JEREMY PATERSON NJ 201/732-9834

1403 BOWDEN EARL BRIDGEPORT CT 203/675-3434

1616 FUENTAS CARLA NEW YORK NY 718/384-3329

;

run;

proc sql;

title 'Proclib.Staff2';

select * from proclib.staff2;

title;

Figure 7.10 Proclib.Staff2

Example Code 7.2 Proclib.Schedule2 Table

data proclib.schedule2;

input flight $3. +5 date date7. +2 dest $3. +3 idnum $4.;

format date date7.;

informat date date7.;

Example 9: Joining Three Tables 293