O'Reilly logo

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

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required