Support WITH clauses in SQL

[ChrisNZ]

It'd be nice if SAS supported WITH clauses in SQL, such as:

with Q1 as (select ... ),
select .. from Q1
union
select ... from Q1

[jimbarbour]

Agreed.  That could come in quite handy for complex queries.

Jim

[yabwon]

@ChrisNZ ,

Chris,

1) You have my vote on it!

2) From purely programming point of view you can always "fake" WITH with macros:

Proc SQL;
%macro withQ1;
(select ... )
%mend;

select .. from %withQ1
union
select ... from %withQ1
;
Quit;

It is just an inline view, in fact two or more... but the code is "shorter".

Of course implementing such feature as WITH clause could allow such query to be much more optimised by the engine.

All the best

Bart

[ChrisNZ]

All good comments, thank you. My hope is that a WITH clause would be treated more efficiently than using a view or a macro as the parser wouldn't have to guess that this piece of data is used several times. It's also cleaner code of course.                          

[r_behata]

I think CTE ( Common Table Expressions ) is the name of this SQL feature offered by some popular RDBMS vendors .

You have my vote on this.

Thanks