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.

Sample Input Data to Conditionally Change a Table

                       Sales Data for Incentives Program

          Name                Department   Payrate   Gadgets  Whatnots
          ------------------------------------------------------------
          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.

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
                       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;

PROC SQL Output for Conditionally Updating a Table

            Adjusted Payrates Based on Sales of Gadgets and Whatnots

          Name                Department   Payrate   Gadgets  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

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;

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;

Top of Page