SQL procedure User ' s Guide

1. Ordering the SELECT Statement:

1.select

2. From

3. Where

4. Group by

5. Having

6. Order by

Select sum (Population)  from sql.countries Group  by Continent  having inch ('Asia''Europe') Order  by Continent;

2. The outobs=option limits the number of rows in the output. Outobs= is similar
To the Obs=data set option

proc sql Outobs="U.S. Cities with their states and coordinates ' ; Select *  from Sql.uscitycoords;

3. The keyword DISTINCT can eliminate the duplicate rows from the results

PROC SQL;     ' Continents of the States ' ;     Select distinct Continent          from Sql.unitedstates;quit;

4. Determing the structure of a Table:

The DESCRIBE table Statement:obtain a list of all of the columns in a table and

their attributes.

proc SQL;     Table Sql.unitedstates;quit;

Creating New Columns:
Adding Text to Output:

proc sql Outobs="U.S. Postal Codes"; Select ' Postal Code for ' '  is ' , Code  from Sql.postalcodes;quit

Proc SQL Outobs="U.S. Postal Codes';' Postal codefor "is", Code label= ' # ' from sql.postalcodes;quit   

Referring to a calculated Column by Alias (keyword:calculated)

procSQL Outobs= A; title'Range of temperatures in Celsius';SelectCity, (Avghigh-  +)* 5/9  asHIGHC format=5.1, (Avglow-  +)* 5/9  asLOWC format=5.1, (Calculated HIGHC-calculated LOWC) asRange format=4.1 fromSql.worldtemps;

Assigning Values conditionally:

1. Using A simple CASE expression

2. Using the Case-operand from

procSQL Outobs= A; title'Climate Zones of World Cities'; SelectCity , Country, Latitude, Case         whenLatitude GT the  Then 'North Frigid'         when  theGE Latitude GE at  Then 'North Temperate'         when  atGT Latitude GT- at  Then 'Torrid'         when - atGE Latitude GE- the  Then 'South Temperate'        Else 'South Frigid'    End  asClimatezone fromSql.worldcitycoordsOrder  byCity ; procSQL Outobs= A; title'assigning regions to continents'; SelectName, Continent, CaseContinent when 'North America'  Then 'Continental U.S.'         when 'Oceania'  Then 'Pacific Islands'        Else 'None'    End  as Region fromSql.unitedstates;

SQL procedure User ' s Guide