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
_
79
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 ;
_
79
ERROR 79-322: Expecting a ).
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?
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.
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.
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
;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.