PROC SQL in SAS: Aggregate Queries, GROUP BY & Subqueries
Execute ANSI SQL queries directly on SAS datasets for grouping, summarization, and conditional filtering.
📌 Direct Answer / Executive Definition
PROC SQL is an implementation of ANSI SQL within SAS that allows developers to create tables, join data, calculate aggregate summaries, and query dataset metadata without writing iterative DATA step loops.
Why SQL Querying & Aggregation Matter for Enterprise Developers
Enables database developers to leverage their existing SQL knowledge and quickly perform group aggregations with the HAVING clause and dynamic subqueries.
Standard Syntax, Derivation & Framework Pattern
/* Aggregating and Filtering with PROC SQL */
PROC SQL;
CREATE TABLE work.dept_averages AS
SELECT Sex,
COUNT(*) AS Headcount,
AVG(Height) AS Avg_Height FORMAT=5.1,
AVG(Weight) AS Avg_Weight FORMAT=5.1
FROM sashelp.class
GROUP BY Sex
HAVING AVG(Weight) > 90;
QUIT;Core Rules & Certification Takeaways
- PROC SQL statements end with a semicolon and the procedure ends with the QUIT; statement (not RUN;).
- The WHERE clause filters raw rows before grouping; the HAVING clause filters aggregated groups.
- PROC SQL automatically prints query results to the Output window unless the CREATE TABLE or NOPRINT option is used.
- Subqueries can return single values or lists for IN / NOT IN filtering.
🔗 Related Architectural Concepts & Next Steps
Deepen your mastery with connected topics across our curriculum and knowledge hubs.
Frequently Asked Questions (FAQ)
How does PROC SQL end in SAS?
PROC SQL is an interactive procedure and must be terminated with the QUIT; statement.
What is the difference between WHERE and HAVING in PROC SQL?
WHERE filters individual observations before group aggregation, while HAVING filters summary statistics after the GROUP BY calculation.
Want complete video breakdowns & exercises?
Explore full step-by-step masterclass training in The Simplest Guide to SAS Programming.