SAS 9.4 SQL Procedure User's Guide, Third Edition, 3rd Edition

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