WITH CTE in SAS PROC SQL: The Complete Guide
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.
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.
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.
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.
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.
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.
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.
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.