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
- SAS Institute, SQL Procedure User’s Guide, 2020.1–2026.09, especially table creation, joins, grouping and missing values. Checked 2026-11-15.
- SAS Institute, Overview: SQL Procedure. Checked 2026-11-15.
- SAS Institute, Syntax: SQL Procedure. Checked 2026-11-15.
- SAS Institute, summary-function: counting, grouping and remerging. Checked 2026-11-15.
- SAS Institute, IS condition and SAS missing values. Checked 2026-11-15.
- SAS Institute, SAS OnDemand for Academics. Checked 2026-11-15.
- SQLite, About SQLite. Checked 2026-11-15.
- 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.
| Operation | Documented effect | Validation check | Primary source |
|---|---|---|---|
| CREATE TABLE AS SELECT | Stores 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 ON | Returns 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 WHERE | Retains 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 |
| NOREMERGE | Prevents 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 AS | Limits 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
- SAS SQL Procedure User's Guide, 2020.1-2026.09 (accessed 2026-11-15)
- SAS summary-function reference (accessed 2026-11-15)
- SAS IS condition reference (accessed 2026-11-15)
Compiled by CASRAI from the sources above. Free to reuse under CC BY 4.0 with attribution.







