SQL procedure User's Guide

　　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;

SQL procedure User's Guide

标签：avg   roc   guide   add   min   express   scribe   val   group by