O'Reilly logo

SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition by SAS Institute

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required