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

orders rows by the values that were assigned to the seasons in the Sorter column that
was created with the in-line view
Notice that the Sorter column is not included in the SELECT statement. That causes a
note to be written to the log indicating that you have used a column in an ORDER BY
statement that does not appear in the SELECT statement. In this case, that is exactly
what you wanted to do.
Conditionally Updating a Table
Problem
You want to update values in a column of a table, based on the values of several other
columns in the table.
Background Information
There is one table, called Incentives, that contains information about sales data. There is
one record for each salesperson that includes a department code, a base pay rate, and
sales of two products, gadgets and whatnots.
data incentives;
input @1 Name $18. @20 Department $2. Payrate
Gadgets Whatnots;
datalines;
Lao Che M2 8.00 10193 1105
Jack Colton U2 6.00 9994 2710
Mickey Raymond M1 12.00 6103 1930
Dean Proffit M2 11.00 3000 1999
Antoinette Lily E1 20.00 2203 4610
Sydney Wade E2 15.00 4205 3010
Alan Traherne U2 4.00 5020 3000
Elizabeth Bennett E1 16.00 17003 3003
;
proc sql;
title 'Sales Data for Incentives Program';
select * from incentives;
quit;
200 Chapter 6 Practical Problem-Solving with PROC SQL
Output 6.19 Sample Input Data to Conditionally Change a Table
You want to update the table by increasing each salesperson's pay rate (based on the total
sales of gadgets and whatnots) and taking into consideration some factors that are based
on department code.
Specifically, anyone who sells more than 10,000 gadgets merits an extra $5 per hour.
Anyone selling between 5,000 and 10,000 gadgets also merits an incentive pay, but E
Department salespersons are expected to be better sellers than those in the other
departments, so their gadget sales incentive is $2 per hour compared to $3 per hour for
those in other departments. Good sales of whatnots also entitle sellers to added incentive
pay. The algorithm for whatnot sales is that the top level (level 1 in each department)
salespersons merit an extra $.50 per hour for whatnot sales more than 2,000, and level 2
salespersons merit an extra $1 per hour for sales more than 2,000.
Solution
Use the following PROC SQL code to create a new value for the Payrate column.
Actually Payrate is updated twice for each row, once based on sales of gadgets, and
again based on sales of whatnots:
proc sql;
update incentives
set payrate = case
when gadgets > 10000 then
payrate + 5.00
when gadgets > 5000 then
case
when department in ('E1', 'E2') then
payrate + 2.00
else payrate + 3.00
end
else payrate
end;
update incentives
set payrate = case
when whatnots > 2000 then
Conditionally Updating a Table 201
case
when department in ('E2', 'M2', 'U2') then
payrate + 1.00
else payrate + 0.50
end
else payrate
end;
title 'Adjusted Payrates Based on Sales of Gadgets and Whatnots';
select * from incentives;
Output 6.20 PROC SQL Output for Conditionally Updating a Table
How It Works
This solution performs consecutive updates to the Payrate column of the incentive table.
The first update uses a nested case expression, first determining a bracket that is based
on the amount of gadget sales: greater than 10,000 calls for an incentive of $5, between
5,000 and 10,000 requires an additional comparison. That is accomplished with a nested
case expression that checks department code to choose between a $2 and $3 incentive.
update incentives
set payrate = case
when gadgets > 10000 then
payrate + 5.00
when gadgets > 5000 then
case
when department in ('E1', 'E2') then
payrate + 2.00
else payrate + 3.00
end
else payrate
end;
202 Chapter 6 Practical Problem-Solving with PROC SQL

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