BookmarkSubscribeRSS Feed
Astounding
PROC Star

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_
Rhodochrosite | Level 12

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
Super User

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
Super User Tom
Super User

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
Obsidian | Level 7
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
Obsidian | Level 7

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
Meteorite | Level 14

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. 

Thanks,
Suryakiran
DebG
Obsidian | Level 7

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
Obsidian | Level 7
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
Super User

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 24 replies
  • 15497 views
  • 7 likes
  • 13 in conversation