topic Re: common table expression in sas? in SAS Programming

common table expression in sas?

gjs — Wed, 25 Oct 2017 14:54:07 GMT

Is it possible to use common table expression in a proc sql statement in sas?

Re: common table expression in sas?

Shmuel — Wed, 25 Oct 2017 15:14:09 GMT

What do you mean by "common table expression" ?

Can you give an example ?

Re: common table expression in sas?

gjs — Wed, 25 Oct 2017 15:17:36 GMT

WITH WAVE_HIST_GP_25 AS
(
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
)
SELECT WAVE_HIST_GP_25.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 WAVE_HIST_GP_25
WHERE UPPER(WAVE_HIST_GP_25.SEGMENT_DESC) = 'IN'

Re: common table expression in sas?

Shmuel — Wed, 25 Oct 2017 15:24:55 GMT

It seems your "common table expession" is a part of SQL code.

In sas you can use:

proc sql;
    ... your common expression ...
  ;
quit;

TRy it and post your full code / log if you need more help.

Re: common table expression in sas?

Astounding — Wed, 25 Oct 2017 15:39:19 GMT

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.

Re: common table expression in sas?

gjs — Wed, 25 Oct 2017 16:13:49 GMT

It ran. Do you know why this error is appearing?

9963 ,INPUT_IND_1_FILE.EMAIL_ADDRESS

9964 ,CASE WHEN HCC_IND_1.HCC_FIRST_NAME1 IS NULL THEN 'RHONDA' ELSE HCC_IND_1.HCC_FIRST_NAME1

9964! AS HCC_FIRST_NAME

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>,

=, >, >=, ?, AND, BETWEEN, CONTAINS, END, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE,

LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

9965 ,CASE WHEN HCC_IND_1.HCC_LAST_NAME1 IS NULL THEN 'RHONDA' ELSE HCC_IND_1.HCC_LAST_NAME1 AS

9965! HCC_LAST_NAME

9966 FROM INPUT_IND_1_FILE

9967 LEFT JOIN HCC_IND_1 ON INPUT_IND_1_FILE.MEMBER_NBR = HCC_IND_1.MEMBER_NBR

Code for this part:

,INPUT_IND_1_FILE.EMAIL_ADDRESS

,CASE WHEN HCC_IND_1.HCC_FIRST_NAME1 IS NULL THEN 'RHONDA' ELSE HCC_IND_1.HCC_FIRST_NAME1 AS HCC_FIRST_NAME

,CASE WHEN HCC_IND_1.HCC_LAST_NAME1 IS NULL THEN 'RHONDA' ELSE HCC_IND_1.HCC_LAST_NAME1 AS HCC_LAST_NAME

FROM INPUT_IND_1_FILE

LEFT JOIN HCC_IND_1 ON INPUT_IND_1_FILE.MEMBER_NBR = HCC_IND_1.MEMBER_NBR

Re: common table expression in sas?

Reeza — Wed, 25 Oct 2017 16:49:59 GMT

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.

Re: common table expression in sas?

PGStats — Wed, 25 Oct 2017 17:41:10 GMT

Syntax error: Your CASE expression is missing the END keyword at the end.

Re: common table expression in sas?

gjs — Wed, 25 Oct 2017 17:58:15 GMT

Still have error.

14898 ,CASE WHEN HCC_IND_1.HCC_FIRST_NAME1 IS NULL THEN 'RHONDA' ELSE HCC_IND_1.HCC_FIRST_NAME1

14898! END AS HCC_FIRST_NAME

202

14899 ,CASE WHEN HCC_IND_1.HCC_LAST_NAME1 IS NULL THEN 'RHONDA' ELSE HCC_IND_1.HCC_LAST_NAME1

14899! END AS HCC_LAST_NAME

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, ',', -, /, <,

<=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS,

LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

ERROR 76-322: Syntax error, statement will be ignored.

14900 FROM INPUT_IND_1_FILE

14901 LEFT JOIN HCC_IND_1 ON INPUT_IND_1_FILE.MEMBER_NBR = HCC_IND_1.MEMBER_NBR

Re: common table expression in sas?

gjs — Wed, 25 Oct 2017 18:09:14 GMT

The errors actually start at the very beginning. I need to change something before the with statement?

14839 PROC SQL;

14840 CREATE TABLE WW_TAYLOR_DM AS

14841 WITH INPUT_DATE_IND AS

----

202

ERROR 22-322: Syntax error, expecting one of the following: (, SELECT.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

14842 (

14843 SELECT MAX(ORIGINALRUNDATE1) AS ORIGINALRUNDATE1

14844 FROM AAA_CM_FILE_NEW

14845 )

14846 ,INPUT_IND_1_FILE AS

200

ERROR 22-322: Syntax error, expecting one of the following: ;, EXCEPT, INTERSECT, ORDER, OUTER,

UNION.

ERROR 200-322: The symbol is not recognized and will be ignored.

Code:

PROC SQL;

CREATE TABLE WW_TAYLOR_DM AS

WITH INPUT_DATE_IND AS

(

SELECT MAX(ORIGINALRUNDATE1) AS ORIGINALRUNDATE1

FROM AAA_CM_FILE_NEW

)

,INPUT_IND_1_FILE AS

(

SELECT MEMBERNBR AS MEMBER_NBR

Re: common table expression in sas?

Kurt_Bremser — Wed, 25 Oct 2017 18:10:05 GMT

When posting logs, use the {i} icon. It preserves formatting and therefore the horizontal position of error indicators, which is crucial in detecting the cause.

Re: common table expression in sas?

PGStats — Wed, 25 Oct 2017 18:13:22 GMT

The error must originate somewhere before those lines. Please post your log using the {i} icon above to preserve column alignment.

Re: common table expression in sas?

PGStats — Wed, 25 Oct 2017 18:15:13 GMT

There is no WITH clause in SAS/SQL.

Re: common table expression in sas?

gjs — Wed, 25 Oct 2017 18:16:50 GMT

So common table expressions can't be used in SAS?

Re: common table expression in sas?

PGStats — Wed, 25 Oct 2017 18:19:54 GMT

What are common table expressions?

Re: common table expression in sas?

Kurt_Bremser — Wed, 25 Oct 2017 18:29:24 GMT

It seems to me that CTE's are a nonstandard Microsoft extension to the SQL language. If I read it right, they are more or less a sophisticated variant of a subquery.

Re: common table expression in sas?

kiranv_ — Wed, 25 Oct 2017 18:55:09 GMT

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'

Re: common table expression in sas?

Tom — Wed, 25 Oct 2017 19:30:39 GMT

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
...

Re: common table expression in sas?

Josers18 — Tue, 06 Mar 2018 20:15:50 GMT

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;

Re: common table expression in sas?

SuryaKiran — Tue, 06 Mar 2018 20:35:10 GMT

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.