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

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;

391

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

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

392 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 393

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

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';

394 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 395