topic Re: Proc Sql Error: Case When / Is Not Null in SAS Programming

Proc Sql Error: Case When / Is Not Null

belboy — Thu, 21 Feb 2019 03:00:46 GMT

Hi SAS community,

I'm working on some code and getting a weird error.

My SAS Log:

aa1.bky_filing_dt,
57 aa1.bky_remvl_dt,
58 aa1.bky_chapter,
59 aa1.last_ansys_eff_date,
60 aa1.ESCROW_OVERAGE_SHORTAGE,
61
62 case when aa1.bky_filing_dt is not null
____
22
76
ERROR 22-322: Syntax error, expecting one of the following: a name, (, ), ',', '.', ANSIMISS, AS, CROSS, FULL, INNER, JOIN, LEFT,
NATURAL, NOMISS, RIGHT.

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

63 then aa1.bky_filing_dt
64 else Min(aa1.Load_Date) end as sort,

My proc sql code includes this case when statement which seems to be generating the error.

select

aa1.loan_no,
min(aa1.load_date) as min_load_dt,
max(aa1.load_date) as max_load_dt,
aa1.unique_id,
aa1.bky_filing_dt,
aa1.bky_remvl_dt,
aa1.bky_chapter,
aa1.last_ansys_eff_date,
aa1.ESCROW_OVERAGE_SHORTAGE,

case when aa1.bky_filing_dt is not null
then aa1.bky_filing_dt
else Min(aa1.Load_Date) end as sort

from table b as aa1; quit;

I'm simply trying to create a column named 'sort' that will use bky_filing_dt if the column isn't null, or use aa1.load_date. Why is SAS giving me this error?

Thanks for any suggestions that will help.

Re: Proc Sql Error: Case When / Is Not Null

novinosrin — Thu, 21 Feb 2019 03:08:54 GMT

from table b as aa1

in the above which is alias?

if aa1 is alias then what is b?

Re: Proc Sql Error: Case When / Is Not Null

ScottBass — Thu, 21 Feb 2019 03:15:23 GMT

Not exactly an answer to your error, but related...

Investigate the COALESCE function. Your code would change from:

case when aa1.bky_filing_dt is not null
then aa1.bky_filing_dt
else Min(aa1.Load_Date) end as sort

To:

SELECT
...
,COALESCE(aa1.bky_filing_dt, MIN(aa1.Load_Date)) as sort
,...

Re: Proc Sql Error: Case When / Is Not Null

belboy — Thu, 21 Feb 2019 03:19:49 GMT

Sorry. TableB as aa1 ##- Please type your reply above this line. No
attachments. -##

Re: Proc Sql Error: Case When / Is Not Null

PGStats — Thu, 21 Feb 2019 04:30:36 GMT

That code looks correct. You might be stuck with some nonprinting character in your code. try removing a couple of lines and retyping them anew.