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

Appendix 3
Example Code Shown in Using
the SQL Procedure
This appendix shows the example code for the whole section of “Using the SQL
Procedure.” If you copy and paste the code to a SAS editor, the code spacing is
preserved if you copy the code from an HTML page.
Introduction to the SQL Procedure
Some output tables restrict the number of observations. To list the entire table, remove
the PROC SQL OUTOBS= option.
/*-------------------------------------------------------------------
Output 1.1 Sample SQL Output
-------------------------------------------------------------------*/
proc sql;
title 'Population of Large Countries Grouped by Continent';
select Continent, sum(Population) as TotPop format=comma15.
from sql.countries
where Population gt 1000000
group by Continent
order by TotPop;
quit;
/*-------------------------------------------------------------------
Output 1.2 Sample DATA Step Output
-------------------------------------------------------------------*/
title 'Large Countries Grouped by Continent';
proc summary data=sql.countries;
where Population > 1000000;
class Continent;
var Population;
output out=sumPop sum=TotPop;
run;
proc sort data=SumPop;
by totPop;
run;
proc print data=SumPop noobs;
var Continent TotPop;
format TotPop comma15.;
where _type_=1;
run;
393
/*-------------------------------------------------------------------
Output 1.3 Countries (Partial Output)
-------------------------------------------------------------------*/
options nodate nonumber linesize=84 pagesize=60;
proc sql outobs=15;
title 'Countries';
select Name format=$19., Capital format=$15.,
Population, Area, Continent format=$15., UNDate format=year4.
from sql.countries;
/*-------------------------------------------------------------------
Output 1.4 WorldCityCoords (Partial Output)
-------------------------------------------------------------------*/
proc sql outobs=15;
title 'WorldCityCoords';
select City format=$15., Country format=$12., Latitude, Longitude
from sql.worldcitycoords;
/*-------------------------------------------------------------------
Output 1.5 USCityCoords (Partial Output)
-------------------------------------------------------------------*/
proc sql outobs=15;
title 'USCityCoords';
select City format=$15., State format=$2., Latitude, Longitude
from sql.uscitycoords;
/*-------------------------------------------------------------------
Output 1.6 UnitedStates (Partial Output)
-------------------------------------------------------------------*/
proc sql outobs=15;
title 'UnitedStates';
select Name format=$17., Capital format=$15.,
Population, Area, Continent format=$13., Statehood format=date9.
from sql.unitedstates;
/*-------------------------------------------------------------------
Output 1.7 PostalCodes (Partial Output)
.
-------------------------------------------------------------------*/
proc sql outobs=15;
title 'PostalCodes';
select Name , Code
from sql.postalcodes;
/*-------------------------------------------------------------------
Output 1.8 WorldTemps (Partial Output)
-------------------------------------------------------------------*/
proc sql outobs=15;
title 'WorldTemps';
select City, Country,avghigh, avglow
394 Appendix 3 Example Code Shown in Using the SQL Procedure
from sql.worldtemps;
/*-------------------------------------------------------------------
Output 1.9 OilProd (Partial Output)
-------------------------------------------------------------------*/
proc sql outobs=15;
title 'OilProd';
select Country, BarrelsPerDay
from sql.oilprod;
/*-------------------------------------------------------------------
Output 1.10 OilRsrvs (Partial Output)
-------------------------------------------------------------------*/
proc sql outobs=15;
title 'OilRsrvs';
select Country, Barrels
from sql.oilrsrvs;
/*-------------------------------------------------------------------
Output 1.11 Continents
-------------------------------------------------------------------*/
proc sql outobs=15;
title 'Continents';
select Name format=$15., Area,
Highpoint format =$15., Height, LowPoint format =$15., Depth
from sql. continents;
/*-------------------------------------------------------------------
Output 1.12 Features (Partial Output)
-------------------------------------------------------------------*/
proc sql outobs=15;
title 'Features';
select Name format=$15., Type,Location format =$15.,Area,
Height, Depth, Length
from sql. features;
Retrieving Data from a Single Table
Some output tables restrict the number of observations. To list the entire table, remove
the PROC SQL OUTOBS= option.
/*-------------------------------------------------------------------
Output 2.1 Selecting All Columns in a Table
-------------------------------------------------------------------*/
proc sql outobs=12;
title 'U.S. Cities with Their States and Coordinates';
select *
from sql.uscitycoords;
/*-------------------------------------------------------------------
Output 2.2 Selecting One Column
Example Code: Using the SQL Procedure 395
-------------------------------------------------------------------*/
proc sql outobs=12;
title 'Names of U.S. Cities';
select City
from sql.uscitycoords;
/*-------------------------------------------------------------------
Output 2.3 Selecting Multiple Columns
-------------------------------------------------------------------*/
proc sql outobs=12;
title 'U.S. Cities and Their States';
select City, State
from sql.uscitycoords;
/*-------------------------------------------------------------------
Output 2.4 Selecting a Column with Duplicate Values
-------------------------------------------------------------------*/
proc sql outobs=12;
title 'Continents of the United States';
select Continent
from sql.unitedstates;
/*-------------------------------------------------------------------
Output 2.5 Eliminating Duplicate Values
-------------------------------------------------------------------*/
proc sql;
title 'Continents of the United States';
select distinct Continent
from sql.unitedstates;
/*-------------------------------------------------------------------
Log 2.1 Portion of Log to Determine the Structure of a Table
-------------------------------------------------------------------*/
proc sql;
describe table sql.unitedstates;
/*-------------------------------------------------------------------
Output 2.6 Adding Text to Output
-------------------------------------------------------------------*/
proc sql outobs=12;
title 'U.S. Postal Codes';
select 'Postal code for', Name, 'is', Code
from sql.postalcodes;
/*-------------------------------------------------------------------
Output 2.7 Suppressing Column Headings in Output
-------------------------------------------------------------------*/
proc sql outobs=12;
title 'U.S. Postal Codes';
396 Appendix 3 Example Code Shown in Using the SQL Procedure
select 'Postal code for', Name label='#', 'is', Code label='#'
from sql.postalcodes;
/*-------------------------------------------------------------------
Output 2.8 Calculating Values
-------------------------------------------------------------------*/
proc sql outobs=12;
title 'Low Temperatures in Celsius';
select City, (AvgLow - 32) * 5/9 format=4.1
from sql.worldtemps;
/*-------------------------------------------------------------------
Output 2.9 Assigning a Column Alias to a Calculated Column
-------------------------------------------------------------------*/
proc sql outobs=12;
title 'Low Temperatures in Celsius';
select City, (AvgLow - 32) * 5/9 as LowCelsius format=4.1
from sql.worldtemps;
/*-------------------------------------------------------------------
Output 2.10 Referring to a Calculated Column by Alias
-------------------------------------------------------------------*/
proc sql outobs=12;
title 'Range of High and Low Temperatures in Celsius';
select City, (AvgHigh - 32) * 5/9 as HighC format=5.1,
(AvgLow - 32) * 5/9 as LowC format=5.1,
(calculated HighC - calculated LowC)
as Range format=4.1
from sql.worldtemps;
/*-------------------------------------------------------------------
Output 2.11 Using a Simple CASE Expression
-------------------------------------------------------------------*/
proc sql outobs=12;
title 'Climate Zones of World Cities';
select City, Country, Latitude,
case
when Latitude gt 67 then 'North Frigid'
when 67 ge Latitude ge 23 then 'North Temperate'
when 23 gt Latitude gt -23 then 'Torrid'
when -23 ge Latitude ge -67 then 'South Temperate'
else 'South Frigid'
end as ClimateZone
from sql.worldcitycoords
order by City;
/*-------------------------------------------------------------------
Output 2.12 Using a CASE Expression in the CASE-OPERAND Form
-------------------------------------------------------------------*/
proc sql outobs=12;
title 'Assigning Regions to Continents';
Example Code: Using the SQL Procedure 397

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