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

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;
/*-------------------------------------------------------------------
398 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 399
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
400 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 401

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