The second update is similar, though simpler. All sales of whatnots more than 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;
Updating a Table with Values from Another Table
Problem
You want to update the Sql.United States table with updated population data.
Background Information
The Sql.NewPop table contains updated population data for some of the U.S. states.
libname sql 'SAS-library';
proc sql;
title 'Updated U.S. Population Data';
select state, population format=comma10. label='Population' from sql.newpop;
Updating a Table with Values from Another Table 203
Output 6.21 Table with Updated Population Data
Solution
Use the following PROC SQL code to update the population information for each state
in the Sql.United States table:
proc sql;
title 'UnitedStates';
update sql.unitedstates as u
set population=(select population from sql.newpop as n
where u.name=n.state)
where u.name in (select state from sql.newpop);
select Name format=$17., Capital format=$15.,
Population, Area, Continent format=$13., Statehood format=date9.
from sql.unitedstates;
/* use this code to generate output so you don't
overwrite the sql.unitedstates table */
options ls=84;
proc sql outobs=10;
title 'UnitedStates';
create table work.unitedstates as
select * from sql.unitedstates;
update work.unitedstates as u
set population=(select population from sql.newpop as n
204 Chapter 6 Practical Problem-Solving with PROC SQL

Get SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.