Practical Problem-Solving with PROC SQL

# Conditionally Updating a Table

You want to update values in a column of a table, based on the values of several other columns in the table.

There is one table, called INCENTIVES, that contains information on 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.

```                       Sales Data for Incentives Program

------------------------------------------------------------
Lao Che             M2                 8     10193      1105
Jack Colton         U2                 6      9994      2710
Mickey Raymond      M1                12      6103      1930
Dean Proffit        M2                11      3000      1999
Antoinette Lily     E1                20      2203      4610
Sydney Wade         E2                15      4205      3010
Alan Traherne       U2                 4      5020      3000
Elizabeth Bennett   E1                16     17003      3003
```

You want to update the table by increasing each salesperson's payrate (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 over 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 over 2,000, and level 2 salespersons merit an extra \$1 per hour for sales over 2,000.

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
payrate + 5.00
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
case
when department in ('E2', 'M2', 'U2') then
payrate + 1.00
else payrate + 0.50
end
else payrate
end;
select * from incentives;```

```            Adjusted Payrates Based on Sales of Gadgets and Whatnots

------------------------------------------------------------
Lao Che             M2                13     10193      1105
Jack Colton         U2                10      9994      2710
Mickey Raymond      M1                15      6103      1930
Dean Proffit        M2                11      3000      1999
Antoinette Lily     E1              20.5      2203      4610
Sydney Wade         E2                16      4205      3010
Alan Traherne       U2                 8      5020      3000
Elizabeth Bennett   E1              21.5     17003      3003
```

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
payrate + 5.00
case
when department in ('E1', 'E2') then
payrate + 2.00
else payrate + 3.00
end
else payrate
end;
```

The second update is similar, though simpler. All sales of whatnots over 2,000 merit an incentive, either \$.50 or \$1 depending on the department level, that again is accomplished by means of a nested case expression.

```update incentives
set payrate = case
when whatnots > 2000 then
case
when department in ('E2', 'M2', 'U2') then
payrate + 1.00
else payrate + 0.50
end
else payrate
end;```

 Previous Page | Next Page | Top of Page