BookmarkSubscribeRSS Feed
gjs
Fluorite | Level 6 gjs
Fluorite | Level 6

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

24 REPLIES 24
Shmuel
Garnet | Level 18

What do you mean by "common table expression" ?

Can you give an example ?

gjs
Fluorite | Level 6 gjs
Fluorite | Level 6

 

 

 

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
Garnet | Level 18

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
Fluorite | Level 6 gjs
Fluorite | Level 6

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
Opal | Level 21

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

PG
gjs
Fluorite | Level 6 gjs
Fluorite | Level 6

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
Fluorite | Level 6 gjs
Fluorite | Level 6

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
Opal | Level 21

There is no WITH clause in SAS/SQL.

PG
gjs
Fluorite | Level 6 gjs
Fluorite | Level 6
So common table expressions can't be used in SAS?
PGStats
Opal | Level 21

What are common table expressions?

PG
Kurt_Bremser
Super User

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
Calcite | Level 5

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

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
Opal | Level 21

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

PG

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
  • 15498 views
  • 7 likes
  • 13 in conversation