PROC SQL

PROC SQL runs SQL queries against SAS data sets (and databases). If you know SQL, you can use it in SAS right away. The step ends with QUIT;, and each statement inside runs immediately.

Querying

proc sql;
   select sex,
          count(*) as n,
          mean(weight) as avg_wt format=6.2
   from sashelp.class
   group by sex;
quit;

Sex         n  avg_wt
---------------------
F           9   90.11
M          10  108.95

• Columns are separated by commas; statements end with a single semicolon.
• SAS functions and formats work inside SQL: format=, label=, put(), intck(), and so on.
• SELECT prints a report; CREATE TABLE saves a data set.

Creating tables and joining

Using customers and orders from the MERGE lesson:

proc sql;
   create table totals as
   select c.name, sum(o.amount) as total
   from customers as c
        inner join orders as o
        on c.id = o.id
   group by c.name
   order by total desc;

   select * from totals;
quit;

name         total
------------------
Grace           75
Ada             70

Log (abridged):

NOTE: Table WORK.TOTALS created, with 2 rows and 2 columns.

Unlike MERGE, SQL joins don't need sorted inputs, and many-to-many joins behave the standard SQL way. left join, right join, and full join work as usual.

SAS-specific features

• calculated refers to a column computed earlier in the same SELECT: select weight / 2.2 as kg, calculated kg * 2 as double_kg ....
• Mixing a summary with detail columns makes SAS remerge the summary back onto each row, with a note — handy, but check it was intended:

proc sql;
   select name, weight, weight - mean(weight) as diff format=6.1
   from sashelp.class
   where age = 11;
quit;

Name       Weight    diff
-------------------------
Joyce        50.5   -17.3
Thomas         85    17.3

NOTE: The query requires remerging summary statistics back with the original data.

The mean of the two weights is 67.75, so each is 17.25 pounds away, shown rounded to one decimal.

• select distinct, having, subqueries, union, and case when ... end all work.
• describe table sashelp.class; writes the table structure to the log.

Keep long queries readable — the SQL Formatter tidies them.

SQL or DATA step?

Use SQL for joins (especially many-to-many), grouped summaries, and queries against databases. Use the DATA step for row-by-row logic, arrays, FIRST./LAST. processing, and reading raw files. Most SAS programs use both.

Practice

With SQL, list the names and ages of students taller than 65 inches, oldest first.

proc sql;
   select name, age, height
   from sashelp.class
   where height > 65
   order by age desc, name;
quit;

Name           Age    Height
----------------------------
Philip          16        72
Mary            15      66.5
Ronald          15        67
William         15      66.5
Alfred          14        69
Barbara         13      65.3