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

select Name, Continent,

case Continent

when 'North America' then 'Continental U.S.'

when 'Oceania' then 'Pacific Islands'

else 'None'

end as Region

from sql.unitedstates;

/*-------------------------------------------------------------------

Output 2.13 Using the COALESCE Function to Replace Missing Values

Note: Either of the following two SQL statements will

create Output 2.13.

-------------------------------------------------------------------*/

proc sql;

title 'Continental Low Points';

select Name, coalesce(LowPoint, 'Not Available') as LowPoint

from sql.continents;

proc sql;

title 'Continental Low Points';

select Name, case

when LowPoint is missing then 'Not Available'

else Lowpoint

end as LowPoint

from sql.continents;

/*-------------------------------------------------------------------

Output 2.14 Specifying Column Attributes

-------------------------------------------------------------------*/

proc sql outobs=12;

title 'Areas of U.S. States in Square Miles';

select Name label='State', Area format=comma10.

from sql.unitedstates;

/*-------------------------------------------------------------------

Output 2.15 Sorting by Column

-------------------------------------------------------------------*/

proc sql outobs=12;

title 'Country Populations';

select Name, Population format=comma10.

from sql.countries

order by Population;

/*-------------------------------------------------------------------

Output 2.16 Sorting by Multiple Columns

-------------------------------------------------------------------*/

proc sql outobs=12;

title 'Countries, Sorted by Continent and Name';

select Name, Continent

from sql.countries

order by Continent, Name;

/*-------------------------------------------------------------------

396 Appendix 3 • Example Code Shown in Using the SQL Procedure

Output 2.17 Specifying a Sort Order

-------------------------------------------------------------------*/

proc sql outobs=12;

title 'World Topographical Features';

select Name, Type

from sql.features

order by Type desc, Name;

/*-------------------------------------------------------------------

Output 2.18 Sorting by Calculated Column

-------------------------------------------------------------------*/

proc sql outobs=12;

title 'World Population Densities per Square Mile';

select Name, Population format=comma12., Area format=comma8.,

Population/Area as Density format=comma10.

from sql.countries

order by Density desc;

/*-------------------------------------------------------------------

Output 2.19 Sorting by Column Position

-------------------------------------------------------------------*/

proc sql outobs=12;

title 'World Population Densities per Square Mile';

select Name, Population format=comma12., Area format=comma8.,

Population/Area format=comma10. label='Density'

from sql.countries

order by 4 desc;

/*-------------------------------------------------------------------

Output 2.20 Sorting by Columns That Are Not Selected

-------------------------------------------------------------------*/

proc sql outobs=12;

title 'Countries, Sorted by Population';

select Name, Continent

from sql.countries

order by Population;

/*-------------------------------------------------------------------

Output 2.21 Sorting Columns That Contain Missing Values

-------------------------------------------------------------------*/

proc sql;

title 'Continents, Sorted by Low Point';

select Name, LowPoint

from sql.continents

order by LowPoint;

/*-------------------------------------------------------------------

Output 2.22 Using a Simple WHERE Clause

-------------------------------------------------------------------*/

proc sql outobs=12;

Example Code: Using the SQL Procedure 397

title 'Countries in Europe';

select Name, Population format=comma10.

from sql.countries

where Continent = 'Europe';

/*-------------------------------------------------------------------

Output 2.23 Retrieving Rows Based on a Comparison

-------------------------------------------------------------------*/

proc sql;

title 'States with Populations over 5,000,000';

select Name, Population format=comma10.

from sql.unitedstates

where Population gt 5000000

order by Population desc;

/*-------------------------------------------------------------------

Output 2.24 Retrieving Rows That Satisfy Multiple Conditions

-------------------------------------------------------------------*/

proc sql;

title 'Countries in Africa with Populations over 20,000,000';

select Name, Population format=comma10.

from sql.countries

where Continent = 'Africa' and Population gt 20000000

order by Population desc;

/*-------------------------------------------------------------------

Output 2.25 Using the IN Operator

-------------------------------------------------------------------*/

proc sql outobs=12;

title 'World Mountains and Waterfalls';

select Name, Type, Height format=comma10.

from sql.features

where Type in ('Mountain', 'Waterfall')

order by Height;

/*-------------------------------------------------------------------

Output 2.26 Using the IS MISSING Operator

-------------------------------------------------------------------*/

proc sql;

title 'Countries with Missing Continents';

select Name, Continent

from sql.countries

where Continent is missing;

/*-------------------------------------------------------------------

Output 2.27 Using the BETWEEN-AND Operators

-------------------------------------------------------------------*/

proc sql outobs=12;

title 'Equatorial Cities of the World';

select City, Country, Latitude

from sql.worldcitycoords

398 Appendix 3 • Example Code Shown in Using the SQL Procedure

where Latitude between -5 and 5;

/*-------------------------------------------------------------------

Output 2.28 Using the LIKE Operator

-------------------------------------------------------------------*/

proc sql;

title1 'Country Names that Begin with the Letter "Z"';

title2 'or Are 5 Characters Long and End with the Letter "a"';

select Name

from sql.countries

where Name like 'Z%' or Name like '____a';

/*-------------------------------------------------------------------

Output 2.29 Using a Truncated String Comparison Operator

-------------------------------------------------------------------*/

proc sql;

title '"New" U.S. States';

select Name

from sql.unitedstates

where Name eqt 'New ';

/*-------------------------------------------------------------------

Output 2.30 Using a WHERE Clause with Missing Values (Incorrect Output)

-------------------------------------------------------------------*/

/* incorrect output */

proc sql outobs=12;

title 'World Features with a Depth of Less than 500 Feet';

select Name, Depth

from sql.features

where Depth lt 500

order by Depth;

/*-------------------------------------------------------------------

Output 2.31 Using a WHERE Clause with Missing Values (Corrected Output)

-------------------------------------------------------------------*/

/* corrected output */

proc sql outobs=12;

title 'World Features with a Depth of Less than 500 Feet';

select Name, Depth

from sql.features

where Depth lt 500 and Depth is not missing

order by Depth;

/*-------------------------------------------------------------------

Output 2.32 Using the MEAN Function with a WHERE Clause

-------------------------------------------------------------------*/

proc sql outobs=12;

title 'Mean Temperatures for World Cities';

select City, Country, mean(AvgHigh, AvgLow)

Example Code: Using the SQL Procedure 399