query - recoding with CASE statement

[Isaima0]

Hey guys, hope everyone is doing great. I'm having a problem with the following query builder step-

CASE
WHEN 'APPLICATION SCORE'n>41 THEN 'Credit_Grade'n="A"
WHEN ('APPLICATION SCORE'n<=40) AND ('APPLICATION SCORE'n>30) THEN 'Credit_Grade'n="B"
WHEN ('APPLICATION SCORE'n<=30) AND ('APPLICATION SCORE'n>20) THEN 'Credit_Grade'n="C"
ELSE * 'Credit_Grade'n="D" *
END

Can anyone please help??

[PeterClemmensen]

Hi and welcome to the SAS communities 🙂

Please be more specific about your problem. Did you try to run it? What did the log say?

[Isaima0]

Hi draycut,

Yes I have run it several times in my query builder, but getting the following Syntax errors-

Please advise what to do?

Many thanks

[Jagadishkatam]

Could you please let us know what is the error your are getting and the sample data

[Isaima0]

Hi Jag,

I ran the code and this is the sytax error I'm getting-

44 PROC SQL;
45 CREATE TABLE WORK.QUERY_FOR_LOAN_PORTFOLIO AS
46 SELECT /* Credit_Grade */
47 CASE
48 WHEN 'APPLICATION SCORE'n>41 THEN "A"
49 WHEN ('APPLICATION SCORE'n<=40) AND ('APPLICATION SCORE'n>30) THEN "B"
50 WHEN ('APPLICATION SCORE'n<=30) AND ('APPLICATION SCORE'n>20) THEN "C"
51 ELSE * "D" * '.' END
_
22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,
a missing value, BTRIM, INPUT, PUT, SUBSTRING, USER.

52 AS /*Credit_Grade*/
53 FROM WORK.LOAN_PORTFOLIO t1'.';
____
78
76
ERROR 78-322: Expecting a ','.

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

[Sathish_jammy]

In your code Grade A values estimates from 42,43,44 and so on. The value 41 lied in Grade 'D'

CASE
WHEN colname>40 THEN 'Credit_Grade'n="A"
WHEN colname>30 AND colname<=40) THEN 'Credit_Grade'n="B"
WHEN colname>20 AND colname<=30) THEN 'Credit_Grade'n="C"
ELSE * 'Credit_Grade'n="D" *
END 

[Isaima0]

Hi Satish,

Thanks for your answer, I tried running your given query command, the attached screenshot is the log I'm getting

Please advice what to do

Many thanks.

[Kurt_Bremser]

case
  when 'APPLICATION SCORE'n>41 then "A"
  when ('APPLICATION SCORE'n<=40) and ('APPLICATION SCORE'n>30) then "B"
  when ('APPLICATION SCORE'n<=30) and ('APPLICATION SCORE'n>20) then "C"
  else "D"
end as credit_grade

is the correct syntax of a SQL case.

[Isaima0]

Hi Kurt,

Thanks for your code. I tried running it in the query builder, and the following screenshot is my syntax errors,

Syntax errors

Please advise what to do? how should I avoid such Syntax?

Many thanks

[Kurt_Bremser]

There is no opening bracket in my code. The case has to be on the first level if the select.

I suggest you invest some time and read the documentation of proc sql: https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.3&docsetId=sqlproc&docsetTarget=titl...

[Isaima0]

Hi Kurt,

The opening bracket is created when I'm using the Query option to create the calculated column. It closes after End AS Credi_Grade, as you can see in my screenshot. I'll try coding it in a new program to see if runs or not, will update you after. I'm also going through the documentation.

Thank you

[Isaima0]

Hi Kurt,

I just ran your code right now, this is the syntax error I'm getting:-

PROC SQL;

CREATE TABLE WORK.QUERY_FOR_LOAN_PORTFOLIO AS
SELECT /* Credit_Grade */
CASE
WHEN 'APPLICATION SCORE'n>41 THEN "A"
WHEN ('APPLICATION SCORE'n<=40) AND ('APPLICATION SCORE'n>30) THEN "B"
WHEN ('APPLICATION SCORE'n<=30) AND ('APPLICATION SCORE'n>20) THEN "C"
ELSE * "D" * '.' END
_
22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,
a missing value, BTRIM, INPUT, PUT, SUBSTRING, USER.

52 AS /*Credit_Grade*/
53 FROM WORK.LOAN_PORTFOLIO t1'.';
____
78
76
ERROR 78-322: Expecting a ','.

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

[Tom · Accepted solution]

Please use the Insert Code and/or Insert SAS code buttons on the menu bar to paste logs and/or code TEXT into pop-up windows.

The asterisk in your code is probably being read as an attempt to perform multiplication.  That operation requires two operands.

Why do you have them there?

Also your range of values have gaps in them. A CASE statement will evaluate the conditions in order so you can simplify.

case
  when ('application score'n>41) then 'A'
  when ('application score'n>30) then 'B'
  when ('application score'n>20) then 'C'
  else 'D'
end

[Isaima0]

Hi Tom,

I tried your code and here's the following syntax I'm seeing-

PROC SQL;
45            SELECT 'APPLICATION SCORE'n
46                     case
                       ____
                       22
                       76
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, (, *, **, +, ',', -, '.', /, <, <=, <>, =, 
              >, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, 
              LEN, LENGTH, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.  

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

47           when ('application score'n>41) then 'A'
48           when ('application score'n>30) then 'B'
49           when ('application score'n>20) then 'C'
50           else 'D'
51         end
52         AS Credit_Grade
53         from Work.loan_portfolio;
54         QUIT;

Where am I going wrong?

[Isaima0]

Hi Tom,

Thank you so much for your help, truly appreciate it! I have figured out what was wrong! You were right I was coding the wrong way. Your code now worked for me! Thanks a bunch