common table expression in sas?

[Astounding]

If I understand you correctly (and that's a huge IF in this case), it seems like you want to take the code that you supplied, and utilize it in a number of different SELECT statements.  If that's the idea, a SAS macro would come in handy:

%macro GP25;

*** All the code you supplied goes here ... do not add a semicolon ***

%mend GP25;

Now any time you want to add that code as part of a SELECT statement or clause, just insert:

%GP25

That inserts the code, character by character.

If you intend to do this across multiple programs, there are ways to save the macro definition and easily make it available to many programs.  First, let's see if that's what you hope to accomplish.

[kiranv_]

the CTE you have shown can be easily emulated by a derived table

SELECT MEMBER_NBR
,WAVE_HIST_GP_25.MASTER_MEMBER_ID
,WAVE_HIST_GP_25.WAVE_ID
,WAVE_HIST_GP_25.CURR_WAVE
,WAVE_HIST_GP_25.CHANNEL_DESC
,WAVE_HIST_GP_25.TEST_CELL
,WAVE_HIST_GP_25.LOAD_DATE
,WAVE_HIST_GP_25.SEGMENT_DESC
,WAVE_HIST_GP_25.NEW_WAVE_DT
,WAVE_HIST_GP_25.CODE

 from 
(
SELECT DISTINCT MEMBER_NBR
,MASTER_MEMBER_ID
,WAVE_ID
,WAVE_ID + 1 AS CURR_WAVE
,CHANNEL_DESC
,TEST_CELL
,LOAD_DATE
,SEGMENT_DESC
,NEW_WAVE_DT
,CODE
FROM hist_file
WHERE NEW_WAVE_DT = SYSDATE
)WAVE_HIST_GP_25

WHERE UPPER(SEGMENT_DESC) = 'IN'

[Reeza]

If you have it set up you can also use explicit pass through, where the DB query is sent directly to the DB rather than having to convert to SAS at all.

The code, as written will not run. It does need modifications, but it's possible to replicate within SAS. However, it's probably easier if you tell us what you're trying to accomplish and we can suggest the most appropriate SAS methodology to achieve those results. 

[Tom]

No.  PROC SQL uses SQL 92 standard. The WITH clause is not supported.

Instead of coding something like

with X as (select ....)
select ...
from X
...

You will need to use something like

select ...
from (select ....) X
...

[Paul_de_Barros]

Tom, your explanation that one would have to switch from a Common Table Expression (aka WITH clause) to a Subquery was very helpful. Also, here is reference I found about how it is possible to use a CTE in a PROC SQL step, as long as you are using it a in a pass-through.
http://torsas.ca/attachments/File/20200228/SAS%20SQL%20Under%20the%20Hood-Debby-Gear.pdf

[Josers18]

The only way I know to use CTEs in SAS is to do a passthrough query

proc sql;
connect to ODBC as myconn
(datasrc="SourceName" user=xxx password=xxx);
create table work.test as
select *
from connection to myconn
(
with ....
etc etc

);
disconnect from myconn;
quit;

[SuryaKiran]

Pass-Through queries have nothing to do within SAS. These queries run in-database. Since WITH clause is native to database your passing-through its works fine. If you want to run the query in SAS environment you need to convert to SAS syntax style. 

I see in your query your have SASDATE, is this some variable in your table or your want the SYSTEM Date. If looking for SYSTEM Date then use &SYSDATE macro variable. 

[DebG]

Unfortunately SAS uses SQL92 which does not support CTE aka the with clause.  CTE was introduced into SQL in the updated SQL1999.  This is a feature I wish would be introduced in SAS

[DebG]

I understood that proc sql is SQL92 standard and proc fedsql is SQL:1999 standard. It sure would be a great addition to SAS.

[Reeza]

Probably for compatibility sake.  But there's many other ways to accomplish something similar in SAS and these is an area's where SAS data step and macro's are more easy to use and understand than CTE's.