topic Re: Syntax error in code - assistance needed in SAS Programming

Syntax error in code - assistance needed

Q1983 — Thu, 18 Apr 2019 18:40:47 GMT

proc sql;

create table test as

SELECT DISTINCT

FC_ATTORNEY_ID, FC_ATTORNEY_NM, REC_CHNG_DT

FROM T_FORECLOSURE T_FORECLOSURE

WHERE (FC_ATTORNEY_ID, REC_CHNG_DT) IN

(

SELECT

FC_ATTORNEY_ID

, MAX (REC_CHNG_DT) AS LST_DT

FROM T_FORECLOSURE T_FORECLOSURE

WHERE REC_CHNG_CD In ('A','C')

GROUP BY FC_ATTORNEY_ID

) AND REC_CHNG_CD In ('A','C')

;quit;

I received the above code from another dept. They use TOAD. When I run this in sas I get the following errors;

22 proc sql;

23 create table test as

24 SELECT DISTINCT

25 FC_ATTORNEY_ID, FC_ATTORNEY_NM, REC_CHNG_DT

26 FROM RDX.T_FORECLOSURE T_FORECLOSURE

27 WHERE (FC_ATTORNEY_ID, REC_CHNG_DT) IN

ERROR 79-322: Expecting a (.

28 (

29 SELECT

30 FC_ATTORNEY_ID

31 , MAX (REC_CHNG_DT) AS LST_DT

32 FROM RDX.T_FORECLOSURE T_FORECLOSURE

33 WHERE REC_CHNG_CD In ('A','C')

34 GROUP BY FC_ATTORNEY_ID

35 ) AND REC_CHNG_CD In ('A','C')

36 ;

ERROR 79-322: Expecting a ).

Re: Syntax error in code - assistance needed

Kurt_Bremser — Thu, 18 Apr 2019 18:45:37 GMT

Ask the "other department" what they expected this syntactically invalid code to do. The where condition makes no sense.

What should this

(FC_ATTORNEY_ID, REC_CHNG_DT)

be?

Re: Syntax error in code - assistance needed

Q1983 — Thu, 18 Apr 2019 19:17:03 GMT

thanks. I only posted this to see if anyone else had used this combination. I will reach out to the dept that provided this in the first place.

Re: Syntax error in code - assistance needed

Patrick — Fri, 19 Apr 2019 03:29:53 GMT

On top of what @Kurt_Bremser wrote:

I guess that "they use TOAD" means that the SQL you're trying to execute is in a database flavor. To execute such code unchanged you need to use explicit pass-through in SAS.

Re: Syntax error in code - assistance needed

Tom — Fri, 19 Apr 2019 04:15:38 GMT

You cannot use the IN operation to compare multiple variables. Just single values.

But it looks like you are just trying to find the MAX date.

SAS will allow this syntax to find the maximum based on just the ID variable.

proc sql;
create table test as
SELECT DISTINCT
  FC_ATTORNEY_ID
, FC_ATTORNEY_NM
, max(REC_CHNG_DT) as REX_CHNG_DT
FROM T_FORECLOSURE T_FORECLOSURE
WHERE REC_CHNG_CD in ('A','C')
GROUP BY FC_ATTORNEY_ID
;
quit;

But I suspect that Attorney's only have one name so even what ever database your Toad user is connecting to will allow this syntax.

create table test as
SELECT DISTINCT
  FC_ATTORNEY_ID
, FC_ATTORNEY_NM
, max(REC_CHNG_DT) as REX_CHNG_DT
FROM T_FORECLOSURE T_FORECLOSURE
WHERE REC_CHNG_CD in ('A','C')
GROUP BY FC_ATTORNEY_ID, FC_ATTORNEY_NM
;