SQL procedure User's Guide（Single Table ）

1. Ordering the SELECT Statement:

　　1.select

　　2. From

　　3. Where

　　4. Group by

　　5. Having

　　6. Order by

 

select Continent, sum(Population)
from sql.countries
group by Continent
having Continent in ('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=12;
title '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;
    title 'Continents of the United 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;
    describe table sql.unitedstates;
quit;

Creating New Columns:
　　Adding Text to Output:

proc sql outobs=12;
title 'U.S. Postal Codes';
select 'Postal code for', Name, 'is', Code
from sql.postalcodes;
quit

proc sql outobs=12;
title 'U.S. Postal Codes';
select 'Postal code for', Name label='#', 'is', Code label='#'
from sql.postalcodes;
quit

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

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;

 Assigning Values Conditionally:

　　1. Using a simple Case expression

　　2. Using the case-operand from

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;    

proc sql outobs=12;
title 'Assigning Regions to Continents';
    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;

 Replacing Missing Values--> Coalesce function enables you to replace missing

 　　　　　　　　　　　　　　  values in a column with a new value that you specify.

proc sql;
title 'Continental Low Points';
select Name, coalesce(LowPoint, 'Not Available') as LowPoint
from sql.continents;

Specifying Column Attributes:
　　1. FORMAT=

　　2. INFORMAT=

　　3. LABEL=

　　4. LENGTH=

proc sql outobs=12;
title 'Areas of U.S. States in Square Miles';
select Name label='State', Area format=comma10.
from sql.unitedstates;

Sorting Data:
　　ORDER BY Column

proc sql outobs=12;
title 'World Topographical Features';
select Name, Type
    from sql.features
    order by Type desc, Name;

　　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;

 Retrieving Rows That Satisfy a Condition:

　　1.Using a Simple WHERE Clause　　

proc sql outobs=12;
title 'Countries in Europe';
select Name, Population format=comma10.
from sql.countries
where Continent = 'Europe';

　　2.Retrieving Rows Based on a Comparison

　　3. Retrieving Rows That Satisfy Multiple Conditions

　　4.Using Other Conditional Operators:

Using Aggregate Functions

　　1. Using Aggregate Function with Unique Values

　　   Counting Unique Values:You can use DISTINCT with an aggregate function to cause the function to use only unique values from a column.

proc sql;
title 'Number of Continents in the Countries Table';
select count(distinct Continent) as Count
from sql.countries;

 　2.Counting Nonmissing Values:

proc sql;
title 'Countries for Which a Continent is Listed';
select count(Continent) as Count
from sql.countries;

　3.Counting All Rows:

proc sql;
title 'Number of Countries in the Sql.Countries Table';
select count(*) as Number
from sql.countries;

Grouping Data

　　Groupong by One Column

 

Vaildating a Query

　　The VALIDATE statement enables you to check the syntax of a query for correctness without submitting it to PROC SQL. PROC SQL displays a message in the 
　　log to indicate whether the syntax is correct.