Skip to main content
v2026.11,986 entries · CC-BY 4.0

SAS SQL: PROC SQL Queries, Joins and Checks

Use PROC SQL in SAS to select, join and summarize research tables, with a worked example that checks rows, missing scores and denominators.

Written and maintained by CASRAI Editorial Board

Published

On this page

SAS SQL usually means PROC SQL, the SAS procedure for selecting, filtering, joining and summarizing tables with SQL statements. In research data preparation, it can turn a participant roster and repeated observations into an analysis table. The essential check is whether each output row represents a participant, an observation or a group.

What SAS SQL is used for in research

PROC SQL works with SAS data sets as tables: observations become rows and variables become columns. It can retrieve data, create tables and views, and calculate summaries. SAS also documents database connections, but the examples here operate on SAS tables within a SAS session. They require no external database connection. [1, 2]

A useful research task is linking a roster to measurement records, retaining people without measurements, and then reporting how many scores contribute to each summary. These are separate decisions. Keeping everyone in the roster does not imply that everyone contributes a measured outcome, and counting joined rows does not necessarily count participants.

SAS vs SQL: software and query language

SQL is a language used by many data systems. SAS is the software environment; PROC SQL is its implementation of that language. Consequently, “SQL in SAS” describes a way to work inside SAS rather than a competing software product. Familiar clauses such as SELECT and WHERE transfer conceptually, while SAS-specific missing-value behavior needs attention. [1, 2, 5]

For example, a researcher reviewing a multisite extract may want a site-level summary. Before writing a query, specify what a row in the input means and what a row in the output should mean. That short description makes later checks concrete: a site summary should not unexpectedly contain a separate result for every measurement.

Install or access SAS

Run these examples in an available SAS programming session. PROC SQL is part of Base SAS, rather than a package installed separately with a SQL package manager. For practice, SAS OnDemand for Academics provides browser-based SAS Studio access for teaching and learning, including independent learners, without a local SAS installation. [1, 6]

The example below uses fictional records that can be typed directly into a program. SAS describes OnDemand’s free access as a learning resource; that description alone does not establish permission to upload an institution’s confidential research files. Use the approved environment for the actual study.

If the real inputs arrive as a CSV file, complete the PROC IMPORT workflow first. Then inspect the imported variable names and types with PROC CONTENTS. Treat that inspection as a preparation step: the join examples assume that both ID columns represent the same identifier.

Step-by-step task: SQL in SAS

This illustrative composite uses four fictional participants and four visit records. Participant 101 has two scores, participant 102 has a visit with a missing score, participant 103 has one score, and participant 104 has no visit. The numbers demonstrate query behavior; they are not study findings. Syntax follows SAS’s SQL reference and current SQL Procedure User’s Guide. [1, 3]

1. Create the two example tables

Start in a fresh practice session. These names are reserved for this example; change them if the session already contains tables you need. The explicit column definitions distinguish a numeric identifier from the one-character site code.

proc sql;
  create table work.demo_roster
    (id num, site char(1));
  insert into work.demo_roster
    values (101, 'A')
    values (102, 'A')
    values (103, 'B')
    values (104, 'B');

  create table work.demo_visits
    (id num, score num);
  insert into work.demo_visits
    values (101, 10)
    values (101, 14)
    values (102, .)
    values (103, 20);
quit;

Each INSERT statement ends after its final VALUES clause. The period is the numeric missing value in SAS. Preserve it as written; replacing it with zero would change the example’s meaning. CREATE TABLE and INSERT are separate statements, each terminated by a semicolon. [1, 3, 5]

2. Select and filter observed scores

This query asks for available scores and puts the displayed result in a defined order. SELECT chooses columns, FROM identifies the input, WHERE limits rows, and ORDER BY controls the presentation. [2, 3]

proc sql;
  select id, score
  from work.demo_visits
  where score is not missing
  order by id, score;
quit;

The expected result has three rows: 101 with 10, 101 with 14, and 103 with 20. Participant 102’s visit remains in the input table but does not satisfy this query’s condition. This is a display query; it does not create a named analysis table.

3. Join visits while preserving the roster

Use CREATE TABLE AS to store a result. The LEFT JOIN retains unmatched roster records as well as matching visits. The aliases r and v identify which table supplies each column. The condition after ON defines a match. [1, 3]

proc sql;
  create table work.demo_joined as
  select r.id, r.site, v.id as visit_id, v.score
  from work.demo_roster as r
  left join work.demo_visits as v
    on r.id = v.id
  order by r.id, v.score;

  select * from work.demo_joined;
quit;

The expected joined table contains five rows. Participant 101 contributes two matching visits. Participants 102 and 103 contribute one each, and participant 104 contributes the unmatched roster row. Keeping visit_id makes the last two kinds of missing score distinguishable: 102 has a matched visit, while 104 does not.

Do not add a nonmissing-score WHERE condition here if the purpose is to preserve the complete roster. Such a condition would discard both the visit with an unrecorded score and the unmatched participant. Retaining a population and selecting observed outcomes answer different questions.

4. Summarize by site with explicit denominators

Report the joined row count, matched visit count and available-score count together. COUNT(*) counts rows; COUNT(column) counts nonmissing values in that column. GROUP BY requests a summary for each site. [4]

proc sql;
  create table work.demo_site_summary as
  select site,
         count(*) as joined_rows,
         count(visit_id) as matched_visits,
         count(score) as observed_scores,
         avg(score) as mean_score
  from work.demo_joined
  group by site
  order by site;

  select * from work.demo_site_summary;
quit;

Notice the distinct roles of the two queries: CREATE TABLE AS stores the result, and the following SELECT displays it. A stored table need not appear automatically as a printed report. Also, the selection contains only the grouping variable and aggregate expressions; it does not request individual IDs alongside the site summaries. [1, 4]

5. Filter summaries with HAVING

To display only sites with at least two observed scores, apply a condition to the grouped result with HAVING. This illustrative threshold is a query demonstration, not a research reporting rule. [1, 3]

proc sql;
  select site, count(score) as observed_scores,
         avg(score) as mean_score
  from work.demo_joined
  group by site
  having count(score) ge 2
  order by site;
quit;

Only site A should remain. Applying an aggregate such as COUNT(score) inside WHERE is not the equivalent operation: SAS’s summary-function reference excludes aggregate functions from WHERE and ON. [4]

Output and how to read it

The expected site summary is shown below. These values are calculated from the fictional inputs, rather than reproduced from vendor output. The data table after the Sources section separately compiles documented query operations and their validation checks.

Site Joined rows Matched visits Observed scores Mean score
A 3 3 2 12
B 2 1 1 20

For site A, the mean is (10 + 14) / 2 = 12. Its third joined row is participant 102’s missing score, which contributes to matched_visits but not observed_scores. For site B, 20 is the only observed score. Participant 104 contributes a joined row without adding a visit or score.

This summary describes observed scores, with participant 101 contributing twice. It is not a mean giving every participant equal weight. If equal participant weighting is required, define a participant-level reduction first, then summarize those participant results. The example deliberately leaves that scientific decision open rather than selecting an arbitrary visit.

Reconcile the output against three separate expectations: five joined rows, four matched visits and three nonmissing scores. A single total could look plausible while concealing the wrong denominator. When comparing a revised extraction with a previous version, PROC COMPARE for data-set checks can support a separate comparison of the saved outputs.

Troubleshooting SAS SQL

A left join produces more rows than the roster

Start with the matching records. In this example, five rows are correct because participant 101 has two visits. If a real query should return one row per participant, inspect repeated keys before removing rows. A repeated key may represent valid longitudinal observations rather than an accidental duplicate. The relevant question is whether the chosen join keys identify the intended relationship.

Write the expected unit beside each intermediate table name in the analysis notes. For this example, demo_roster is participant-level, demo_joined preserves repeated observations, and demo_site_summary is site-level. Compare each result with that expectation before passing it to a statistical model.

Missing scores pass a numeric comparison

SAS evaluates numeric missing values as smaller than nonmissing numbers. Therefore, a low-score filter can retain missing scores unless it excludes them explicitly. Use a nonmissing test together with the substantive comparison. The following example selects only the observed score of 10. [1, 5]

proc sql;
  select id, score
  from work.demo_visits
  where score is not missing and score lt 12
  order by id;
quit;

The cutoff of 12 has no substantive interpretation here. Its purpose is to expose the difference between “recorded and below the cutoff” and a bare comparison that also accepts missing values.

A grouped query repeats the summary on detail rows

SAS can remerge summary statistics with original rows when a query requests additional detail columns. Inspect the log if adding an ID changes the expected one-row-per-site result. For a grouped-summary check, PROC SQL NOREMERGE prevents queries that require this behavior. It is a guard against an unintended query shape, not a correction to the data. [4]

proc sql noremerge;
  select site, avg(score) as mean_score
  from work.demo_joined
  group by site;
quit;

The result table is incomplete or nothing is displayed

Check for an OUTOBS option left over from exploration. SAS documents that it can limit the table created by CREATE TABLE AS, not just a displayed preview. Remove an exploratory limit before producing the full result. If the table exists but no report appears, explicitly SELECT from it, as in the walkthrough. [1]

Check statement boundaries too: PROC SQL executes queries without a RUN statement, and QUIT ends the procedure. A missing semicolon can make the next line part of the preceding statement. Review the first failed statement before interpreting any downstream output. [2, 3]

Free alternatives

If the task is learning SQL or building relational tables outside SAS, these projects provide free SQL engines. Their official descriptions support the capabilities below. Choosing one changes the execution environment; the SAS program wrapper and missing-value assumptions still need review.

Alternative Documented capability Research task to consider
SQLite Public-domain, embedded SQL engine; reads and writes a database in one disk file without a separate server. A self-contained relational extract or SQL practice database. [7]
PostgreSQL Free, open-source relational database with SQL, primary and foreign keys, and integrity constraints. A database-backed project where explicit relationships and constraints are useful. [8]

These are alternatives for the data task, rather than replacements that promise unchanged execution of PROC SQL programs. Use the fictional example as a small migration check: reproduce its inputs, join population and denominators before moving a real extraction.

Sources

  1. SAS Institute, SQL Procedure User’s Guide, 2020.1–2026.09, especially table creation, joins, grouping and missing values. Checked 2026-11-15.
  2. SAS Institute, Overview: SQL Procedure. Checked 2026-11-15.
  3. SAS Institute, Syntax: SQL Procedure. Checked 2026-11-15.
  4. SAS Institute, summary-function: counting, grouping and remerging. Checked 2026-11-15.
  5. SAS Institute, IS condition and SAS missing values. Checked 2026-11-15.
  6. SAS Institute, SAS OnDemand for Academics. Checked 2026-11-15.
  7. SQLite, About SQLite. Checked 2026-11-15.
  8. PostgreSQL Global Development Group, About PostgreSQL. Checked 2026-11-15.

Data: SAS SQL operations and research-data validation checks

An original cross-reference of PROC SQL operations, their documented effects and checks for a research extraction. Compiled from SAS documentation on table creation, missing values, grouping and summary functions; checks describe the implications of the documented behavior.

SAS SQL operations and research-data validation checks
OperationDocumented effectValidation checkPrimary source
CREATE TABLE AS SELECTStores a query result as a SAS table; does not automatically display it.SELECT from the saved table to inspect it.https://documentation.sas.com/api/collections/pgmsascdc/v_045/docsets/sqlproc/content/sqlproc.pdf?locale=en
LEFT JOIN with ONReturns matching rows and unmatched rows from the left table.Reconcile retained roster records and repeated matches.https://documentation.sas.com/api/collections/pgmsascdc/v_045/docsets/sqlproc/content/sqlproc.pdf?locale=en
IS NOT MISSING in WHERERetains rows whose tested expression is not missing.Distinguish a complete roster from an observed-outcome subset.https://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a002473690.htm
COUNT(*) and COUNT(score)The first counts rows; the second counts nonmissing score values.Report both row counts and observed-score denominators.https://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a002473699.htm
GROUP BY with AVG(score)Calculates the summary for each group; AVG ignores missing scores.Define the observation unit contributing to each group mean.https://documentation.sas.com/api/collections/pgmsascdc/v_045/docsets/sqlproc/content/sqlproc.pdf?locale=en
NOREMERGEPrevents processing of queries requiring remerging.Investigate an error when detail columns enter a grouped summary.https://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a002473699.htm
OUTOBS with CREATE TABLE ASLimits the number of rows written to the result table.Remove exploratory limits before a complete extraction.https://documentation.sas.com/api/collections/pgmsascdc/v_045/docsets/sqlproc/content/sqlproc.pdf?locale=en

Sources

Compiled by CASRAI from the sources above. Free to reuse under CC BY 4.0 with attribution.

Follow CASRAI

Research-administration guidance, standards updates and independent tool reviews.

Ask CASRAI · free to try

Ask about SAS SQL: PROC SQL Queries, Joins and Checks

2 free questions a day, no account.

An AI assistant for research: funder rules, compliance, methods and publishing. It links the sources behind its answers and labels answers that come from a web search.

Answers draw on CASRAI's guides and dictionary plus the federal and funder documents we index: Federal Register, Grants.gov, Regulations.gov and UKRI.

Works on this site and inside Claude, Cursor and the AI tools you already use.

Everything CASRAI publishes — this page, the dictionary, the guides and the news — stays open to read, with no account and no card.

Referenced across the research world

University of Cambridge logoColumbia University logoCrossref logoUniversity of Edinburgh logoHarvard University logoUniversity of Oxford logoPrinceton University logoStanford School of Medicine logoUniversity College London logoORCID logoUniversity of Cambridge logoColumbia University logoCrossref logoUniversity of Edinburgh logoHarvard University logoUniversity of Oxford logoPrinceton University logoStanford School of Medicine logoUniversity College London logoORCID logo
  • University of Cambridge logo
  • Columbia University logo
  • Crossref logo
  • University of Edinburgh logo
  • Harvard University logo
  • University of Oxford logo
  • Princeton University logo
  • Stanford School of Medicine logo
  • University College London logo
  • ORCID logo

View CASRAI adoption →