common table expression in sas?

[gjs]

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

[Shmuel]

What do you mean by "common table expression" ?

Can you give an example ?

[gjs]

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'

[Shmuel]

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.

[gjs]

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

      --

      22

      76

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

[PGStats]

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

[gjs]

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

--

22

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

--

22

76

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

[gjs]

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

----

22

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

-

22

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

[PGStats]

There is no WITH clause in SAS/SQL.

[gjs]

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

[PGStats]

What are common table expressions?

[Kurt_Bremser]

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.

[NaveenKha]

There is no common table expression in PROC SQL, instead use subquery. The only difference is CTE's can be recursive and subqueries are not. CTE's can be used over and over  as a different table. The advantage of a Subquery is that it can be used as column and only subqueries can be used in the WHERE clause!.

[Kurt_Bremser]

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.

[PGStats]

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