topic Re: query in SAS Programming

query - recoding with CASE statement

Isaima0 — Sun, 09 Dec 2018 02:08:48 GMT

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??

Re: query

PeterClemmensen — Sat, 08 Dec 2018 08:52:46 GMT

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?

Re: query

Jagadishkatam — Sat, 08 Dec 2018 08:52:50 GMT

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

Re: query

Sathish_jammy — Sat, 08 Dec 2018 09:04:45 GMT

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

Re: query

Kurt_Bremser — Sat, 08 Dec 2018 09:19:31 GMT

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.

Re: query

Isaima0 — Sat, 08 Dec 2018 18:22:01 GMT

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.

Re: query

Isaima0 — Sat, 08 Dec 2018 18:26:45 GMT

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

Re: query

Isaima0 — Sat, 08 Dec 2018 18:31:30 GMT

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

Re: query

Kurt_Bremser — Sat, 08 Dec 2018 19:33:21 GMT

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=titlepage.htm&locale=en

Re: query

Isaima0 — Sat, 08 Dec 2018 21:56:09 GMT

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

Re: query

Isaima0 — Sat, 08 Dec 2018 22:00:17 GMT

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.

Re: query

Isaima0 — Sat, 08 Dec 2018 22:19:08 GMT

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.

Re: query

Tom — Sat, 08 Dec 2018 22:55:35 GMT

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

Re: query

Isaima0 — Sat, 08 Dec 2018 23:10:56 GMT

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?

Re: query

Isaima0 — Sat, 08 Dec 2018 23:24:53 GMT

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