Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 37 ยท SQL in SAS

PROC SQL: SELECT Basics

Query SAS data with PROC SQL: SELECT, WHERE, GROUP BY, HAVING, ORDER BY, the CALCULATED keyword, formats and labels, NOPRINT and QUIT, with runnable standard SQL examples.

PROC SQL lets you query SAS data sets with the SQL language. If you already know SQL (see the SQL tutorial), most of what you write is the same; SAS adds a few extensions. It can do in one statement what takes a DATA step plus PROC SORT plus PROC MEANS, and it is often the quickest way to join and summarize tables.

SAS cannot run on this site. The sas blocks show how SAS wraps the query. The TryIts below run standard SQL in SQLite, which understands the same SELECT, WHERE, GROUP BY, HAVING and ORDER BY core. SAS-only features (CALCULATED, FORMAT=, INTO :) are labelled as read-only.

The wrapper

SAS
proc sql;
  select name, score
    from work.students
    where score >= 60
    order by score desc;
quit;

Differences from the PROC steps you met earlier:

  • A PROC SQL step ends with quit;, not run;. The procedure stays active after each statement, so you can run several queries inside one proc sql; ... quit;.
  • Every statement ends with a semicolon, and there is no semicolon between clauses: a single select statement is one sentence.
  • A select with no create table prints the result to the output window as a report.
  • Table names are written library.dataset (here work.students).
  • No sorting happens unless you write order by.

Useful statement options: proc sql noprint; suppresses printing (when you only create tables or macro variables), proc sql outobs=10; limits the output rows, and proc sql feedback; shows how SAS expanded select * in the log.

Selecting and filtering

The next TryIt uses a small table you create with SQL itself, so it runs anywhere.

SQL SELECT, WHERE and ORDER BY

Press Run (or Ctrl + Enter) to see the result here.

Fay has a missing score (NULL). Comparisons with NULL are never true, so Fay does not appear. In SAS, a missing numeric value shows as a period and is treated as the smallest value by sorting and by <; in standard SQL, NULL simply fails the test. SAS's PROC SQL follows the SAS rule in some places, which is why you should test missing values explicitly: where score is missing (SAS) or where score is null (both).

Calculated columns and aliases

You can compute new columns and name them with as:

SAS
proc sql;
  select name,
         score,
         score / 100 as pct format=percent8.,
         case when score >= 90 then "A"
              when score >= 80 then "B"
              else "C" end as grade
    from work.students;
quit;

format=percent8. and label= after a column are SAS extensions to set display format and label. The case expression is standard SQL.

SAS lets you reuse a computed column in the same select or in where by writing the keyword calculated:

SAS
select score, score * 1.1 as boosted
  from work.students
  where calculated boosted > 100;

Standard SQL cannot do that: you must repeat the expression or use a subquery. Reusing a column with calculated is a convenience unique to PROC SQL.

Summarizing

SQL GROUP BY and HAVING

Press Run (or Ctrl + Enter) to see the result here.

Notice COUNT(*) counts every row (3 per class) while COUNT(score) ignores NULL (so class B counts 2). The same distinction exists in SAS, where n(score) counts only nonmissing values. where filters rows before grouping; having filters the groups after summarizing.

An important SAS behavior: if you select a summary function together with a non-grouped column, standard SQL stops with an error, but PROC SQL runs and remerges the summary back onto every row, with a log note ("The query requires remerging summary statistics back with the original data"). That is occasionally handy, for example to show each score next to its class average, but unintentionally triggered remerges are a classic source of wrong results. Read the log.

Distinct values and counting

SAS
proc sql;
  select distinct class from work.students;
  select count(distinct class) as n_classes from work.students;
quit;

select distinct removes duplicate rows. SAS allows count(distinct x) as in standard SQL.

Result options

Need Write
First 5 rows proc sql outobs=5;
Remove row-number column select ... ; (PROC SQL has none by default)
Column header text select score label="Exam score"
Show formats select score format=3.
No printing proc sql noprint;
Sorted output order by ... (the only way to guarantee order)

PROC SQL's order by also accepts the position of the column (order by 2 desc) and a calculated name. Without order by, never rely on any row order.

SAS dictionary tables

PROC SQL can read dictionary tables, which are live metadata about your session:

SAS
proc sql;
  select memname, nobs
    from dictionary.tables
    where libname = "WORK";

  select name, type, length
    from dictionary.columns
    where libname = "WORK" and memname = "STUDENTS";
quit;

dictionary.tables, dictionary.columns and dictionary.macros answer "what data sets do I have?" with a query (library and member names are uppercase there). That is the SQL equivalent of the PROC CONTENTS call from PROC PRINT and PROC CONTENTS.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. How does a PROC SQL step end?

    PROC SQL is interactive, so it stays active until quit; (or another step begins).
  2. 2. What does the CALCULATED keyword do?

    calculated is a SAS extension that references a computed column by its alias inside select or where.
  3. 3. What is the difference between WHERE and HAVING?

    WHERE removes rows first; HAVING works on the grouped results.
  4. 4. Which count ignores missing values?

    COUNT(*) counts rows; COUNT(column) counts only nonmissing values.
  5. 5. Which PROC SQL option suppresses printing of results?

    proc sql noprint; runs the queries without showing a report, useful when creating tables or macro variables.

Summary

  • PROC SQL runs SQL against SAS data sets; end the step with quit;.
  • Core clauses select, from, where, group by, having, order by work as in standard SQL.
  • SAS extensions include calculated, format=, label=, outobs= and noprint.
  • Summary functions with ungrouped columns trigger a remerge; read the log note.
  • dictionary.tables and dictionary.columns expose metadata as queryable tables.

Next: PROC SQL: Joins and Subqueries