BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cbonazzo
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

View solution in original post

6 REPLIES 6
Reeza
Super User

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
Super User

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
Fluorite | Level 6

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
Super User

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

cbonazzo
Fluorite | Level 6

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
Onyx | Level 15

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 3390 views
  • 5 likes
  • 4 in conversation