DATA Step, Macro, Functions and more

common table expression in sas?

Reply
Occasional Contributor gjs
Occasional Contributor
Posts: 7

common table expression in sas?

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

Trusted Advisor
Posts: 1,571

Re: common table expression in sas?

What do you mean by "common table expression" ?

Can you give an example ?

Occasional Contributor gjs
Occasional Contributor
Posts: 7

Re: common table expression in sas?

 

 

 

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'

Trusted Advisor
Posts: 1,571

Re: common table expression in sas?

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.

Occasional Contributor gjs
Occasional Contributor
Posts: 7

Re: common table expression in sas?

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

Respected Advisor
Posts: 4,927

Re: common table expression in sas?

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

PG
Occasional Contributor gjs
Occasional Contributor
Posts: 7

Re: common table expression in sas?

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

 

Occasional Contributor gjs
Occasional Contributor
Posts: 7

Re: common table expression in sas?

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

Respected Advisor
Posts: 4,927

Re: common table expression in sas?

There is no WITH clause in SAS/SQL.

PG
Occasional Contributor gjs
Occasional Contributor
Posts: 7

Re: common table expression in sas?

So common table expressions can't be used in SAS?
Respected Advisor
Posts: 4,927

Re: common table expression in sas?

What are common table expressions?

PG
Super User
Posts: 7,809

Re: common table expression in sas?

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 7,809

Re: common table expression in sas?

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Respected Advisor
Posts: 4,927

Re: common table expression in sas?

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

PG
Super User
Posts: 5,513

Re: common table expression in sas?

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.

Ask a Question
Discussion stats
  • 17 replies
  • 149 views
  • 4 likes
  • 8 in conversation