Practical Problem-Solving with PROC SQL

Updating a Table with Values from Another Table

You want to update the SQL.UNITEDSTATES table with updated population data.

The SQL.NEWPOP table contains updated population data for some of the U.S. states.

                          Updated U.S. Population Data

                state                                Population
                -----------------------------------------------
                Texas                                20,851,820
                Georgia                               8,186,453
                Washington                            5,894,121
                Arizona                               5,130,632
                Alabama                               4,447,100
                Oklahoma                              3,450,654
                Connecticut                           3,405,565
                Iowa                                  2,926,324
                West Virginia                         1,808,344
                Idaho                                 1,293,953
                Maine                                 1,274,923
                New Hampshire                         1,235,786
                North Dakota                            642,200
                Alaska                                  626,932

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;

SQL.UNITEDSTATES with Updated Population Data (Partial Output)

                                    UNITEDSTATES

Name               Capital          Population      Area  Continent        Statehood
------------------------------------------------------------------------------------
Alabama            Montgomery          4447100     52423  North America    14DEC1819
Alaska             Juneau               626932    656400  North America    03JAN1959
Arizona            Phoenix             5130632    114000  North America    14FEB1912
Arkansas           Little Rock         2447996     53200  North America    15JUN1836
California         Sacramento         31518948    163700  North America    09SEP1850
Colorado           Denver              3601298    104100  North America    01AUG1876
Connecticut        Hartford            3405565      5500  North America    09JAN1788
Delaware           Dover                707232      2500  North America    07DEC1787
District of Colum  Washington           612907       100  North America    21FEB1871
Florida            Tallahassee        13814408     65800  North America    03MAR1845

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.

Top of Page