Solved
New Contributor
Posts: 4

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

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:

ORIGINAL: 0=never
1=high
2=middle
3=elementary
4=as long as I can remember

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)

then Think_College=0;
then Think_College=1;
then Think_College=2;
then Think_College=3;

Here is the code I created in an advanced expression:

then Think_College=3

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

Best,

Claude

Accepted Solutions
Solution
‎04-03-2017 11:41 AM
Super User
Posts: 24,010

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

[ Edited ]

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;
then Think_College=1;
then Think_College=2;
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 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...

All Replies
Solution
‎04-03-2017 11:41 AM
Super User
Posts: 24,010

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

[ Edited ]

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;
then Think_College=1;
then Think_College=2;
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 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...

Super User
Posts: 13,941

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

[ Edited ]

Possibly:

```CASE
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)

New Contributor
Posts: 4

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

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
8 WHEN t1.Think_About_Coll in ( '3' , '2' ) OR t1.Grade_Think_About_Coll in ( '1' , '2' ) then '2'
_
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;

Super User
Posts: 24,010

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

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

New Contributor
Posts: 4

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

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!

PROC Star
Posts: 1,334

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

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: