topic Error using With and Row_Number Over Partition in SAS Programming

Error using With and Row_Number Over Partition

Q1983 — Thu, 04 Nov 2021 14:34:08 GMT

You wont be able to run this code as a test however Im attemtping to determine why I am getting this error

proc sql;
create table test1 as

WITH  FCSCHED 		AS (/*CRITERIA: Pull FC sep Details, Use Row number and Setup date to Find most recent actions for FC Sales Scheduled */ 
		SELECT ROW_NUMBER() over(Partition by T_FORECLSURE_STEP.LN_NO Order by T_FORECLSURE_STEP.FC_SETUP_DT DESC) AS RN
				,T_FORECLSURE_STEP.LN_NO
				,T_FORECLSURE_STEP.FC_SETUP_DT
				,T_FORECLSURE_STEP.FS_STEP_CD
				,T_FORECLSURE_STEP.FS_SCHED_COMPL_DT
				,T_FORECLSURE_STEP.FS_ACTUAL_COMPL_DT
		FROM _dbeappl.T_FORECLSURE_STEP T_FORECLSURE_STEP
		WHERE (T_FORECLSURE_STEP.FS_STEP_CD In ('075'))	
	)
	,FCHELD 		AS (/*CRITERIA: Pull FC sep Details, Use Row number and Setup date to Find most recent actions for FC Sales Held */ 
		SELECT ROW_NUMBER() over(Partition by T_FORECLSURE_STEP.LN_NO Order by T_FORECLSURE_STEP.FC_SETUP_DT DESC) AS RN
				,T_FORECLSURE_STEP.LN_NO
				,T_FORECLSURE_STEP.FC_SETUP_DT
				,T_FORECLSURE_STEP.FS_STEP_CD
				,T_FORECLSURE_STEP.FS_SCHED_COMPL_DT
				,T_FORECLSURE_STEP.FS_ACTUAL_COMPL_DT
		FROM _dbeappl.T_FORECLSURE_STEP T_FORECLSURE_STEP
		WHERE (T_FORECLSURE_STEP.FS_STEP_CD In ('078','080'))
	)

	SELECT  a.LN_NO
           ,FCSCHED.FS_ACTUAL_COMPL_DT AS FC_SALE_SCHED
	       ,FCHELD.FS_ACTUAL_COMPL_DT AS FC_SALE_HELD

		   FROM 

		   (select distinct LN_NO from _dbeappl.T_LONG_COMMENT
            where LONG_COMM_CD IN ('DFCRND','DFCRD6','DFCORN','DFCRN6') 
            AND LN_NO <> '0121133333') a 
           LEFT JOIN FCSCHED ON (a.LN_NO = FCSCHED.LN_NO AND FCSCHED.RN = 1)
		   LEFT JOIN FCHELD ON (a.LN_NO = FCHELD.LN_NO AND FCHELD.RN = 1)


;quit;

47 proc sql;
48 create table test1 as
49
50 WITH FCSCHED AS (/*CRITERIA: Pull FC sep Details, Use Row number and Setup date to Find most recent actions for FC Sales Scheduled */
____
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.

51 SELECT ROW_NUMBER() over(Partition by T_FORECLSURE_STEP.LN_NO Order by T_FORECLSURE_STEP.FC_SETUP_DT DESC) AS RN
____
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

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

Re: Error using With and Row_Number Over Partition

Tom — Thu, 04 Nov 2021 14:41:14 GMT

You are not using valid SQL. Neither WITH nor OVER and not part of the ANSI standard from 1992 that PROC SQL implements. SAS does not have a function named ROW_NUMBER.

Re: Error using With and Row_Number Over Partition

Tom — Thu, 04 Nov 2021 14:45:22 GMT

You don't have to use SQL in SAS to get things done. Unlike a lot of DBMS systems SAS has its own language for manipulating data.

Please explain what you are trying to do.

Then you can get it done using the DATA step and/or procedures such as PROC FREQ, PROC SUMMARY, PROC RANK, etc..

Re: Error using With and Row_Number Over Partition

ballardw — Thu, 04 Nov 2021 16:08:46 GMT

Please place LOG entries in a text box. Then the diagnostic characters that appear in your multiple error messages will appear in the relative place showing exactly the problem location. Such as WITH is not a SAS Proc SQL statement.

Re: Error using With and Row_Number Over Partition

Reeza — Thu, 04 Nov 2021 16:37:02 GMT

Are you using an Oracle DB? Can you use explicit sql instead? In that case your SQL is passed directly to the server so you can use the Oracle SQL code.