Suppose you have a data table with some coded values, and two tables expanding the coded values:

===== data airlines; input @1 alcode $2. @4 alname $20.; cards; DL Delta UA United AA American ;;;;;

data planes; input @1 pcode $4. @6 pname $20.; cards; 1011 Lockheed L1011 737 Boeing 737 DC10 Douglas DC-10 ;;;;

data flights; input @1 flight $2. @4 alcode $2. @7 pcode $4.; cards; 1 DL 1011 2 DL 737 3 HP 1011 4 AA 777 ; =====

Now suppose you want to create an expanded version of Flights where the airline and plane codes are mapped to their longer text names when possible.

You can do it in two statements, with a left join in each (not shown here), or you can do it with a nested left join, which is how I've usually seen it done:

===== proc sql; * Works, but is cumbersome ; create table expanded1 as select fa.*, p.pname from (select f.*, a.alname from flights as f left join airlines as a on f.alcode = a.alcode) as fa left join planes as p on fa.pcode = p.pcode order by 1, 2, 3 ; quit; =====

When you have multiple tables to join this way, it quickly becomes awkward.

But you can also, I recently learned, do it like this:

===== proc sql; * Easier ; create table expanded2 as select f.*, a.alname, p.pname from flights as f left join airlines as a on f.alcode = a.alcode left join planes as p on f.pcode = p.pcode order by 1, 2, 3 ; quit; =====

This is easier to understand, and adding new tables is not complicated. I would have been using it for years if I had know it worked, but I never thought to try it.

Did everyone but me know about this already?

(The examples above could be simplified by using natural left join and omitting the ON clauses, but the results would be the same.)