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 6.2 PROC SQL Output for Weighted Averages
How It Works
This solution uses an in-line view to create a temporary table that eliminates the negative
data values in the Weight column. The in-line view is a query that performs the
following tasks:
selects the Gender and Value columns.
uses a CASE expression to select the value from the Weight column. If Weight is
greater than zero, then it is retrieved. If Weight is less than zero, then a value of zero
is used in place of the Weight value.
(select Gender, Value,
case
when Weight>0 then Weight
else 0
end as Weight
from Sample)
The first, or outer, SELECT statement in the query, performs the following tasks:
selects the Gender column
constructs a weighted average from the results that were retrieved by the in-line view
The weighted average is the sum of the products of Value and Weight divided by the sum
of the Weights.
select Gender, sum(Value*Weight)/sum(Weight) as WeightedAverage
Finally, the query uses a GROUP BY clause to combine the data so that the calculation
is performed for each gender.
group by Gender;
Comparing Tables
Problem
You have two copies of a table. One of the copies has been updated. You want to see
which rows have been changed.
180 Chapter 6 Practical Problem-Solving with PROC SQL
Background Information
There are two tables, the OldStaff table and NewStaff table. The NewStaff table is a
copy of the OldStaff table. Changes have been made to the NewStaff table. You want to
find out what changes have been made.
Output 6.3 Sample Input Tables for Table Comparison
Solution
To display only the rows that have changed in the new version of the table, use the
EXCEPT set operator between two SELECT statements.
Comparing Tables 181

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