topic Re: IS NULL Syntax Error in SAS Programming

IS NULL Syntax Error

awmeyertimmy — Wed, 23 Mar 2016 20:02:50 GMT

I am running the following proc sql code within base SAS 9.3. However, I keep getting the error mentioned below. Can someone offer some suggestions to fix the code in question please?

PROC SQL;
drop table AWM.temp4;
create table AWM.temp4 as
SELECT
DISTINCT r.*
,SUM(CASE WHEN ScoreModelDimId = '35' THEN IS NULL(c.CreditScore,'0') END) AS TS
,SUM(CASE WHEN ScoreModelDimId = '24' THEN IS NULL(c.CreditScore,'0') END) AS EX2
,SUM(CASE WHEN ScoreModelDimId = '32' THEN IS NULL(c.CreditScore,'0') END) AS HF18
,SUM(CASE WHEN ScoreModelDimId = '12' THEN IS NULL(c.CreditScore,'0') END) AS XPN1
,SUM(CASE WHEN ScoreModelDimId = '20' THEN IS NULL(c.CreditScore,'0') END) AS JM1
,SUM(CASE WHEN ScoreModelDimId = '34' THEN IS NULL(c.CreditScore,'0') END) AS DMS2
FROM AWM.temp3 r
LEFT JOIN Portrevw.VwCreditScoreFact c
ON r.AccountDimId = c.AccountDimId
AND c.ScoreModelDimId IN (35,24,32,12,20,34)
AND c.ValidCalc = '1'
AND c.ScoreDt = (SELECT MAX(c1.ScoreDt)
FROM Portrevw.VwCreditScoreFact c1
JOIN AWM.Temp3 j1
ON c1.AccountDimId = j1.AccountDimId
WHERE c1.AccountDimId = c.AccountDimId
AND c1.ScoreModelDimId = c.ScoreModelDimId)
GROUP BY
r.debtdimid
,r.AccountDimId
,r.FDROpenDt;
QUIT;

ERROR MESSAGE FROM LOG:

68 ,SUM(CASE WHEN ScoreModelDimId = '35' THEN IS NULL(c.CreditScore,'0') END) AS TS
----
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, /, <, <=,
<>, =, >, >=, ?, AND, BETWEEN, CONTAINS, ELSE, EQ, EQT, GE, GET, GT, GTT, LE, LET,
LIKE, LT, LTT, NE, NET, OR, WHEN, ^=, |, ||, ~=.

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

Thanks,

Adam

Re: IS NULL Syntax Error

LinusH — Wed, 23 Mar 2016 20:26:47 GMT

It's simply a syntax error. IS NULL is not a function, it's a kind of operator.
I'm guessing that you might want the result that the coalesce function offers.

Re: IS NULL Syntax Error

PGStats — Thu, 24 Mar 2016 02:23:49 GMT

I guess you'll want to replace

SUM(CASE WHEN ScoreModelDimId = '35' THEN IS NULL(c.CreditScore,'0') END) AS TS

with either

SUM( (ScoreModelDimId = '35') * coalesce(c.CreditScore, 0) ) AS TS

/* Or */

SUM(CASE WHEN ScoreModelDimId = '35' THEN c.CreditScore ELSE . END) AS TS

I would go for the CASE expression.

Re: IS NULL Syntax Error

awmeyertimmy — Thu, 24 Mar 2016 14:24:15 GMT

GREAT! Thanks much! -Adam

Re: IS NULL Syntax Error

awmeyertimmy — Thu, 24 Mar 2016 18:41:50 GMT

My code just runs and runs when I use the following. It should take less than a minute. Any ideas why this keeps spinning?

PROC SQL;
Drop Table AWM.temp4;
create table AWM.temp4 as
SELECT
r.DebtDimId
,r.AccountDimId
,r.FDROpenDt
,SUM(CASE WHEN ScoreModelDimId = 35 THEN c.CreditScore ELSE . END) AS TS
,SUM(CASE WHEN ScoreModelDimId = 24 THEN c.CreditScore ELSE . END) AS EX2
,SUM(CASE WHEN ScoreModelDimId = 32 THEN c.CreditScore ELSE . END) AS HF18
,SUM(CASE WHEN ScoreModelDimId = 12 THEN c.CreditScore ELSE . END) AS XPN1
,SUM(CASE WHEN ScoreModelDimId = 20 THEN c.CreditScore ELSE . END) AS JM1
,SUM(CASE WHEN ScoreModelDimId = 34 THEN c.CreditScore ELSE . END) AS DMS2
FROM AWM.temp3 r
LEFT JOIN Portrevw.VwCreditScoreFact c ON r.AccountDimId = c.AccountDimId
AND c.ScoreModelDimId IN (35,24,32,12,20,34)
AND c.ValidCalc = 1
AND c.scoredt = (SELECT MAX(c1.scoredt)
FROM Portrevw.VwCreditScoreFact c1
JOIN AWM.temp3 j1 ON c1.AccountDimId = j1.AccountDimId
WHERE c1.accountdimid = c.accountdimid
AND c1.ScoreModelDimId = c.ScoreModelDimId)
GROUP BY r.DebtDimId, r.AccountDimId, r.FDROpenDt;
QUIT;

Thanks!

Re: IS NULL Syntax Error

SASKiwi — Fri, 25 Mar 2016 03:01:00 GMT

Without having your tables and data it is near impossible to precisely pinpoint your problem.

Having said that, if I was faced with a similar problem I would try removing parts of the query until it works to isolate where the problem. I'd start by taking out the SUM/CASE lines and put in a COUNT(*) instead. If that doesn't help I'd remove your sub-query which is where I suspect your problem could lie.