It often happens in SAS© programming that you need to join two datasets. Usually, we use a MERGE statement in a DATA step or a LEFT/INNER JOIN in SQL via a common key (for example, a customer ID).
But how do you do it when the two tables have no variables in common and the join depends on complex conditions, like values falling within ranges? This is called a Non-Equi Join (Non-Equi Join).
Let's look at a practical case from a user discussion to understand how to solve this problem elegantly with PROC SQL.
The Misleading Good Idea: The Loop in the DATA Step
Faced with this problem, the first intuition is often to try an iterative approach using a DATA step. The idea would be to read a row from the accounts table, then loop through the entire intervals table to find a match.
Although technically possible, this approach is complex to code in SAS©:
It requires manipulating two read pointers.
You have to manage resetting the read of the
intervalstable for each account (which might require thePOINT=option or multipleSETstatements).The code quickly becomes cumbersome and difficult to maintain.
As one contributor in the discussion points out: "I wouldn't waste time on a loop methodology."
The Elegant Solution: PROC SQL and the Cartesian Product
The most effective and readable solution lies in using PROC SQL. Unlike the DATA step, SQL is designed to naturally handle complex relationships between sets without worrying about sort order or explicit loops.
The trick is to perform a cartesian join (all rows from A combined with all rows from B) and immediately filter the result.
