Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 37 ยท SQL in SAS

PROC SQL: Joins and Subqueries

Join SAS tables in PROC SQL with inner, left, right and full joins, compare with the DATA step MERGE, use subqueries, IN, EXISTS and set operators, with runnable standard SQL examples.

Combining tables is where PROC SQL pays off most. A join matches rows by key without requiring either table to be sorted, handles many-to-many matches correctly, and can bring in columns from several tables in one statement. The equivalent DATA step MERGE needs sorted inputs and behaves surprisingly for many-to-many cases, as you will see below.

SAS cannot run on this site. The sas blocks are read-only. The TryIts run standard SQL in SQLite, which supports INNER, LEFT and (in recent versions) RIGHT and FULL joins, subqueries and set operators. SAS-only syntax is marked read-only.

Inner join

The data used in all the TryIts is two small tables:

SQL Inner join: only matching rows

Press Run (or Ctrl + Enter) to see the result here.

Only customers with orders appear, and order 13 (customer 9 does not exist) is dropped. In SAS you may also write the older comma form:

SAS
proc sql;
  select c.name, o.order_id, o.amount
    from work.customers as c, work.orders as o
    where c.id = o.customer_id;
quit;

It means the same, but the explicit inner join ... on is clearer and avoids accidentally producing a Cartesian product (every row paired with every row) if you forget the where.

Outer joins

An outer join keeps rows that have no match, filling the missing side with missing values:

SQL LEFT JOIN keeps unmatched customers

Press Run (or Ctrl + Enter) to see the result here.

Chen and Dina have no orders but still appear, with 0 orders. COUNT(o.order_id) counts only non-missing order ids, so it correctly gives 0 where COUNT(*) would give 1. In SAS the equivalent for missing numbers is coalesce(sum(o.amount), 0), and coalescec() for character values.

Join Keeps
inner join Rows that match in both tables
left join All left rows plus matches
right join All right rows plus matches
full join All rows from both tables

SAS supports all four (full join included). Suppose you need "customers with no orders": left join, then filter the nulls:

SAS
proc sql;
  select c.name
    from work.customers as c
    left join work.orders as o on c.id = o.customer_id
    where o.order_id is missing;
quit;

Joins vs MERGE

The DATA step version of a join uses merge and by, and requires both data sets sorted by the key:

SAS
proc sort data=work.customers; by id; run;
proc sort data=work.orders out=work.orders_s; by customer_id; run;

data work.joined;
  merge work.customers (in=a rename=(id=customer_id))
        work.orders_s  (in=b);
  by customer_id;
  if a and b;
run;

The in= flags make it an inner join. Differences worth knowing:

MERGE PROC SQL JOIN
Needs sorted data Yes No
Many-to-many matches Pairs rows one by one, not all combinations Correct (all combinations)
Same-named columns Later one overwrites Both kept (use aliases)
Key variable names Must match Any, via on
Very large tables Fast when already sorted May be slower

If both tables repeat the key, merge pairs rows one-by-one and prints a note that cannot be ignored; SQL returns every combination, which is the true many-to-many result.

Subqueries

A subquery is a select nested inside another statement:

SQL Subqueries with IN and a scalar comparison

Press Run (or Ctrl + Enter) to see the result here.

Three common shapes:

  • IN (subquery): a list of values.
  • Scalar subquery: returns one value, used in a comparison.
  • EXISTS (subquery): true when the subquery returns any row, usually correlated with the outer row.
SAS
proc sql;
  select name from work.customers as c
    where exists (select 1 from work.orders as o where o.customer_id = c.id);
quit;

A subquery in the from clause (an inline view) behaves like a temporary table:

SAS
select region, avg_amt
  from (select region, mean(amount) as avg_amt from work.sales group by region)
  where avg_amt > 100;

Because SAS lets you refer to calculated columns, many inline views are unnecessary.

Set operators

union, except, intersect and outer union stack the results of queries:

SAS
proc sql;
  select id from work.customers_2024
  union
  select id from work.customers_2025;     /* distinct ids from both */
quit;
  • union removes duplicates; union all keeps them.
  • except returns rows in the first result but not the second.
  • intersect returns rows in both.
  • outer union is SAS-specific: it stacks by column name, not position, keeping all columns. Use outer union corr to match by name.

Set operators match columns by position (and the types must be compatible) unless you add corr.

Joining on non-equal conditions

A join does not have to use =:

SAS
select p.name, g.band
  from work.people as p
  inner join work.bands as g
    on p.score between g.low and g.high;

This range join assigns a band to each score, which MERGE cannot do. Always check the row count after a join: a join key that is not unique in both tables multiplies rows, and the log message tells you what SAS did.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which join returns all rows from the left table, even without a match?

    A left join keeps every left row and fills missing columns for rows with no match.
  2. 2. Why does COUNT(o.order_id) give 0 for a customer with no orders after a left join?

    COUNT(column) ignores missing values, whereas COUNT(*) would count the unmatched row once.
  3. 3. What is a key advantage of PROC SQL joins over DATA step MERGE?

    SQL joins work on unsorted tables and return all matching combinations.
  4. 4. What does UNION do compared with UNION ALL?

    UNION returns distinct rows; UNION ALL returns every row from both queries.

Summary

  • Join with inner join ... on, left join, right join or full join; avoid the accidental Cartesian product.
  • PROC SQL joins need no sorting and handle many-to-many keys; MERGE does neither.
  • Subqueries can use in, exists, scalar comparisons or sit in the from clause.
  • Set operators union, except, intersect and SAS outer union stack results.
  • Always check row counts after a join.

Next: PROC SQL: Creating Tables and Macro Variables