with cte sas proc sql

WITH CTE in SAS PROC SQL: The Complete Guide

By Christopher Hernandez
AD

If you write SAS code, you have probably built a PROC SQL query with nested subqueries that became difficult to read, debug, or hand off to a colleague. The WITH CTE SAS PROC SQL pattern solves that problem by letting you name a temporary result set before the main query runs. A Common Table Expression, or CTE, is not a separate SAS procedure or a permanent data set. It is a query-building tool inside PROC SQL that can make complex logic easier to understand and maintain.

This article explains how the WITH clause works in SAS PROC SQL, where it fits in real-world reporting and ETL jobs, and when a CTE is a better choice than an inline view or a WORK data set. You will see syntax examples, multiple-CTE patterns, and performance considerations that matter when your data grows from thousands of rows to millions.

Understanding the WITH CTE Clause in SAS PROC SQL

In SAS, PROC SQL supports the WITH clause, which defines one or more Common Table Expressions before the main SELECT statement. A CTE behaves like a named inline view that exists only for the duration of that SQL statement. It does not create a SAS data set in the WORK library, and it cannot be referenced later by another PROC SQL step unless you define it again. This scoped behavior is useful because it keeps intermediate logic close to the query that needs it.

AD
SQL Basics Cheat Sheet for Beginners | Learn SQL Fast 💻
SQL Basics Cheat Sheet for Beginners | Learn SQL Fast 💻
SQL Cheat Sheet for Beginners
SQL Cheat Sheet for Beginners
(2) Facebook Data Analyst, Data Analysis, Data Analytics, Data Science, Logic, Writing, Reading
(2) Facebook Data Analyst, Data Analysis, Data Analytics, Data Science, Logic, Writing, Reading

The basic structure places the WITH clause before SELECT. You can use it with a standalone query, or you can use it with CREATE TABLE AS SELECT to build an output data set. The CTE itself contains a valid SELECT statement, which means it can include joins, filters, aggregations, calculated columns, GROUP BY, HAVING, and even references to earlier CTEs in the same WITH block. The main query then treats the CTE like a table or view.

What Is a Common Table Expression in SAS?

A Common Table Expression is a temporary named result set. In SAS PROC SQL, you create it with the WITH keyword, give it a name, and define its query inside parentheses. For example, you might create a CTE named dept_avg that calculates average salary by department, then join that CTE to an employee table. The CTE is not stored on disk or in WORK. It is a logical construct that helps you break a large problem into smaller, readable pieces.

SQL CTE Explained with Examples | Common Table Expression Tutorial
SQL CTE Explained with Examples | Common Table Expression Tutorial
a poster with the words advanced and an image of a computer screen
a poster with the words advanced and an image of a computer screen
a poster with different types of objects and their names on it, including an image of the
a poster with different types of objects and their names on it, including an image of the

The biggest benefit is readability. Instead of writing a main query with deeply nested subqueries, you can define base_data, cleaned_data, and summary_data as separate CTEs, then write a simple final SELECT. This modular approach is especially helpful in regulated environments where code reviewers need to follow the transformation logic quickly. It also reduces the chance of copy-and-paste errors when the same subquery logic is needed more than once.

Basic Syntax of a CTE in PROC SQL

The simplest syntax looks like this: proc sql; with cte_name as (select column1, column2 from source_table where condition) select * from cte_name; quit. The WITH clause comes first, and the main SELECT follows after the closing parenthesis of the CTE. If you are creating a table, the pattern is create table want as with cte_name as (select ...) select * from cte_name; quit. Parentheses and commas matter, so keep the CTE definitions clean and properly closed.

AD

Here is a practical example. Suppose you have a SAS data set called employees with columns name, dept, and salary. You can define a CTE that calculates average salary by department, then join it back to the employee rows to compare each person to the department average. The CTE keeps the aggregation separate from the final join, which makes the query easier to adjust later. You can also alias columns inside the CTE so the main query uses clear, descriptive names.

a sheet of paper that has some notes on it with words and symbols in the middle
a sheet of paper that has some notes on it with words and symbols in the middle
a book with the title proc soil beyond the basics using sass
a book with the title proc soil beyond the basics using sass
SQL Shortcuts Cheat Sheet
SQL Shortcuts Cheat Sheet
SQL Roadmap for Beginners 2026
SQL Roadmap for Beginners 2026
the 20 % of sgl that does 80 % of the work poster is shown
the 20 % of sgl that does 80 % of the work poster is shown
an info poster showing the top 20 essentials for successful web development and sales success
an info poster showing the top 20 essentials for successful web development and sales success

Practical Applications and Performance Considerations

CTEs are most valuable when a query has layered transformations. A typical SAS reporting job might need to filter raw transactions, aggregate them by month, join them to a customer dimension, and then apply a final business rule. With a CTE, each layer gets a name and a clear purpose. That structure makes the query easier to test because you can inspect one CTE at a time by temporarily changing the final SELECT to select from that CTE.

Performance, however, is not guaranteed by using a CTE. SAS may merge the CTE logic into the main query or process it like an inline view, depending on the query and the optimizer. A CTE can improve developer productivity without improving runtime, and in some cases it can even be slower than a well-indexed temporary table. Always test with realistic data volumes and review the SAS log and query plan when performance matters.

AD

Building Modular Queries with Multiple CTEs

Multiple CTEs are defined with commas between them. Later CTEs can reference earlier CTEs, which lets you build a pipeline inside a single PROC SQL statement. For example, you can define base as a filtered version of a raw table, customer_totals as an aggregation from base, and final_data as a join between customer_totals and a customer lookup table. The main SELECT then reads from final_data. This pattern is excellent for documenting ETL logic without creating several intermediate WORK data sets.

(2) Facebook Sql Cheat Sheet, Coding Tutorials, Learn Programming, Parent Child Relationship, Web Technology, Programming Languages, Data Analysis, Cloud Computing, Data Analytics
(2) Facebook Sql Cheat Sheet, Coding Tutorials, Learn Programming, Parent Child Relationship, Web Technology, Programming Languages, Data Analysis, Cloud Computing, Data Analytics
a blue and white poster with text on it
a blue and white poster with text on it
HOW TO USE SQL CHEAT SHEET IN DATA ANALYST
HOW TO USE SQL CHEAT SHEET IN DATA ANALYST
a white paper with writing on it and a laptop in the background, surrounded by other information
a white paper with writing on it and a laptop in the background, surrounded by other information

Consider an orders table with customer_id, order_date, and amount. You can create base to keep only orders from a specific year, customer_totals to sum amount by customer_id, and then join customer_totals to a customers table for names. The final query can sort by total amount descending. This approach keeps the filtering, aggregation, and enrichment steps separate. It also makes it easy to add another CTE for returns, refunds, or tax adjustments later without rewriting the entire query.

CTE vs. Inline Views and Temporary Tables in SAS

An inline view is a subquery placed directly in the FROM clause. A CTE is similar, but it moves the subquery to the top of the statement and gives it a name. That naming makes complex SQL easier to scan and reuse within the same query. If you need the same intermediate result multiple times in the main query, a CTE can reduce repetition and make the intent clearer. If the subquery is short and used only once, an inline view may be perfectly fine.

AD

A temporary table is different because it is an actual SAS data set, usually created in WORK. It persists across multiple PROC SQL steps, can be indexed, and can be reused by other procedures. Choose a CTE for one-off complex queries where readability is the main goal. Choose a temporary table when the same intermediate result must be used by several steps, when you need indexes or sorting, or when testing shows that materializing the intermediate result improves performance. In practice, many SAS programmers use both: CTEs for query clarity and temporary tables for reusable performance-critical stages.

Next time you face a tangled PROC SQL query, try lifting the first few subqueries into a WITH clause and give each step a meaningful name. You will likely find that the query becomes easier to explain, easier to review, and easier to extend when the business rules change. Keep an eye on performance as your data grows, and use CTEs as one more tool in a balanced SAS programming strategy.

AD