Updating a Table with Values from Another Table :: SAS(R) 9.3 SQL Procedure User's Guide

Problem

You want to update the SQL.UNITEDSTATES 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;

Table with Updated Population Data

Solution

Use the following PROC SQL code to update the population information for each state in the SQL.UNITEDSTATES 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
			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 work.unitedstates
;

SQL.UNITEDSTATES with Updated Population Data (Partial Output)

How It Works

The UPDATE statement updates values in the SQL.UNITEDSTATES table (here with the alias U). For each row in the SQL.UNITEDSTATES table, the in-line view in the SET clause returns a single value. For rows that have a corresponding row in SQL.NEWPOP, this value is the value of the Population column from SQL.NEWPOP. For rows that do not have a corresponding row in SQL.NEWPOP, this value is missing. In both cases, the returned value is assigned to the Population column.

The WHERE clause ensures that only the rows in SQL.UNITEDSTATES that have a corresponding row in SQL.NEWPOP are updated, by checking each value of Name against the list of state names that is returned from the in-line view. Without the WHERE clause, rows that do not have a corresponding row in SQL.NEWPOP would have their Population values updated to missing.