BookmarkSubscribeRSS Feed
awmeyertimmy
Fluorite | Level 6

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

5 REPLIES 5
LinusH
Tourmaline | Level 20
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.
Data never sleeps
PGStats
Opal | Level 21

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.

PG
awmeyertimmy
Fluorite | Level 6

GREAT!  Thanks much!   -Adam

awmeyertimmy
Fluorite | Level 6

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!

a

SASKiwi
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1344 views
  • 3 likes
  • 4 in conversation