Convert IF THEN statement to a CASE WHEN in an Advanced Expression

[cbonazzo]

Hell Everyone,

Thank you for your time to help me with this road block I am having. I am working on someone else's code for recoding a combination of two variables and trying to create something similar in an advance expression. I keep getting an error.

The original code was:

*Create Think\_College column by recoding Think_about_College and Grade_when_Think_Coll as
Think_about_College:
ORIGINAL: 0=never
1=high
2=middle
3=elementary
4=as long as I can remember

Grade_when_Think_Coll

1=9
2=10 
3=11 
4=12

NEW: 0=never
1=as long as I can remember
2=by grade 10 (includes elementary, middle, and high school grades 9 & 10)
3=after grade 10 (includes high school grades 10 and 11);

if Think_about_College=0
then Think_College=0;
if Think_about_College=4
then Think_College=1;
if Think_about_College=3 OR Think_about_College=2 OR Grade_when_Think_Coll=1 OR Grade_when_Think_Coll=2
then Think_College=2;
if Grade_when_Think_Coll=3 OR Grade_when_Think_Coll=4
then Think_College=3;

Here is the code I created in an advanced expression:

CASE WHEN t1.Think_About_Coll=0 then Think_College=0
CASE WHEN t1.Think_About_Coll=4 then Think_College=1
CASE WHEN t1.Think_About_Coll=3 OR t1.Think_About_Coll=2 OR t1.Grade_Think_About_Coll=1 OR t1.Grade_Think_About_Coll=2 then Think_College=2
CASE WHEN t1.Grade_Think_About_Coll=3 OR t1.Grade_Think_About_Coll=4
then Think_College=3

I know it is probably something simple but I need your help. Thank you for your time!

Best,

Claude

[Reeza · Accepted solution]

That's a bit garbled in how it looks and what you're trying to do. 

I'm going to use this as your starting point:

if Think_about_College=0
then Think_College=0;
if Think_about_College=4
then Think_College=1;
if Think_about_College=3 OR Think_about_College=2 OR Grade_when_Think_Coll=1 OR Grade_when_Think_Coll=2
then Think_College=2;
if Grade_when_Think_Coll=3 OR Grade_when_Think_Coll=4
then Think_College=3;

The SQL equivalent would be:

case when think_about_college =1 then 0
        when think_about_college = 4 then 1
        when think_about_college in (3, 2) or Grade_when_Think_Coll=1 OR Grade_when_Think_Coll=2 then 2
        when Grade_when_Think_Coll in (3, 4) then 3
        else -9
       end as think_college

EDIT: change otherwise to else

SAS note regarding this with details and instructions:

http://support.sas.com/kb/32/160.html

Documentation with examples:

https://support.sas.com/documentation/cdl/en/sqlproc/69822/HTML/default/viewer.htm#n0a85s0ijz65irn1h...

[ballardw]

Possibly:

CASE 
   WHEN t1.Think_About_Coll=0 then 0
   WHEN t1.Think_About_Coll=4 then 1
   WHEN t1.Think_About_Coll in (3, 2) OR t1.Grade_Think_About_Coll in (1, 2) then 2
   WHEN t1.Grade_Think_About_Coll in(3, 4) then =3 
   else . 
end as Think_College

Case starts the statement,  END AS resultvariable ends it. Each sub-clause is When conditon then Value (value could be a variable, a calculation or even subquery that yields a single value)

[cbonazzo]

I think I am getting close!! I forgot to mention it is a character variable. I made the change. But got another error message:

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 OPTION DEBUG=DBMS_SELECT SQL_IP_TRACE=(NOTE, SOURCE);

4 PROC SQL NOEXEC;
5 SELECT (CASE
6 WHEN t1.Think_About_Coll='0' then '0'
7 WHEN t1.Think_About_Coll='4' then '1'
8 WHEN t1.Think_About_Coll in ( '3' , '2' ) OR t1.Grade_Think_About_Coll in ( '1' , '2' ) then '2'
9 WHEN t1.Grade_Think_About_Coll in( '3' , '4' ) then = '3'
_
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.

10 else '.'
11 end as Think_College) AS CALCULATION
__
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

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

12 FROM WORK.SELECTION_RENAMING_RECODING t1;
13 QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

14 %PUT SQL_IPTRACE_RESULT=&SYS_SQL_IP_ALL;
SQL_IPTRACE_RESULT=-1
15 OPTIONS SQL_IP_TRACE=(NONE);
16 QUIT; RUN;

[Reeza]

You forgot to remove an = sign on one of the WHEN (3) statement. 

[cbonazzo]

Yes I did. Thanks for the catch. Very grateful to all of you! The SQL code worked! Have a wonderful rest of the day everyone!

[TomKari]

Easy, common mistake. SQL is a pain!

In a SQL select, the result variable name comes at the end, after an AS clause.

In the IF/THEN clauses, you just put the conditions and the desired result.

so:

CASE WHEN t1.Think_About_Coll=0 then 0
CASE WHEN t1.Think_About_Coll=4 then 1
etc.

and EG will fill in the AS Think_College when you supply the variable name.