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

• 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