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
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.
GREAT! Thanks much! -Adam
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
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.
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.