topic Re: Problem with IF Statement in SAS Enterprise Guide

Problem with IF Statement

thegraduate — Mon, 23 Aug 2010 10:23:33 GMT

Hi All,

I have built a simple IF statement but seem to be getting errors;

(IF t3.6MthAvgPayTerms IsNull then 'N/A';
__
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, '.', /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

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

36 IF t2.Account_Status ='Live' AND t3.'6MthAvgPayTerms'n<60 then
__
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

37 'Good';
37 ! IF t2.Account_Status='Live' AND t3.'6MthAvgPayTerms'n >=60 AND t3.'6MthAvgPayTerms'n <90 then
__
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

2 The SAS System 09:18 Monday, August 23, 2010

38 'Indeterminate';
38 ! IF t3.'6MthAvgPayTerms'n >=90 then 'Bad';
__
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

38 ) AS 'Good Bad 'n
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

Can someone help?

Statement:

IF t3.6MthAvgPayTerms IsNull then 'N/A'; IF t2.Account_Status ='Live' AND t3.'6MthAvgPayTerms'n<60 then 'Good'; IF t2.Account_Status='Live' AND t3.'6MthAvgPayTerms'n >=60 AND t3.'6MthAvgPayTerms'n <90 then 'Indeterminate'; IF t3.'6MthAvgPayTerms'n >=90 then 'Bad';

Thanks,

Re: Problem with IF Statement

Peter_C — Mon, 23 Aug 2010 12:22:17 GMT

assuming your use of "is Null" indicates this syntax problem arises in sql, in which context IF is not valid, use the SQL equivalent
case when something then result1
when something_more then result2
else final_result
end
as new_column

Re: Problem with IF Statement

thegraduate — Mon, 23 Aug 2010 13:11:19 GMT

i see,

so what is the replacement for "isnull" in sas?

Thanks,

Re: Problem with IF Statement

Doc_Duke — Mon, 23 Aug 2010 14:30:42 GMT

RTFM. "IS NULL" works in SQL, "IF" does not. If you are in the data step, then the syntax is "= ." (numeric) or "= ' '" (character).

Re: Problem with IF Statement

thegraduate — Mon, 23 Aug 2010 15:26:05 GMT

so buy changing the IsNull to =" ", this will mean the statement will work?

Thanks,

Re: Problem with IF Statement

Cynthia_sas — Mon, 23 Aug 2010 16:49:42 GMT

Hi:
It is generally useful if you post your WHOLE program...not just the bits where you have issues...and provide a bit of context. For example, if you tried to put an IF statement into the SQL query built by EG, I would not expect this to work at all. If you tried to put an IF statement into PROC SQL code in a code node, then I would not expect -that- to work either.

If you are writing a DATA step program and want to put an IF statement into a DATA step program, then you need to understand how SAS internally stores missing values and how the internal storage of missing values is different between SAS datasets and, an RDBMS (such as Oracle or DB2). This Tech Support note provides some background:
http://support.sas.com/kb/23/226.html and
http://support.sas.com/resources/papers/resultsets.pdf

For the documentation on working with SAS variables, this site has the basic concepts on missing values:
http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#/documentation/cdl/en/lrcon/62955/HTML/default/a001292604.htm

Without understanding more about how you're using your IF statement (PROC SQL or DATA step program), the only help that can be offered are suggestions to read the documentation and look for examples.

cynthia

Re: Problem with IF Statement

thegraduate — Tue, 24 Aug 2010 09:13:11 GMT

I decided to do it via CASE statements, but still get an error. I have underlined and bolded where it finds an error.

Statement:

CASE WHEN t3.'6MthAvgPayTerms'n ="" Then 'N/A' ELSE CASE WHEN t2.Account_Status = 'Live' When t3.'6MthAvgPayTerms'n<=60 THEN 'Good' When t3.'6MthAvgPayTerms'n >60 And t3.'6MthAvgPayTerms'n <=90 THEN 'Indeterminate' WHEN t3.'6MthAvgPayTerms'n >90 THEN 'BAD' ELSE CASE When t2.Account_Status ='Final' WHEN t3.'6MthAvgPayTerms'n >=30 Or t2.Account_Balance >=50 Or t3.'6MthAvgPayTerms'n =-1 Or t2.Pending_WO=-1 Or t2.Litigation_Account =-1 Or t2.Trident_Account=-1 THEN 'BAD' WHEN t3.'6MthAvgPayTerms'n=0 THEN 'GOOD' WHEN t3.'6MthAvgPayTerms'n>=0 And t3.'6MthAvgPayTerms'n <30 THEN 'Indeterminate' ELSE 'N/A' end

42 /* Good Bad 1 */
43 (CASE WHEN t3.'6MthAvgPayTerms'n ="" Then 'N/A' ELSE CASE WHEN t2.Account_Status = 'Live' When t3.
____
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, GE, GET,
GT, GTT, LE, LET, LT, LTT, NE, NET, NOT, OR, THEN, ^, ^=, |, ||, ~, ~=.

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

44 '6MthAvgPayTerms'n<=60 THEN 'Good' When t3.'6MthAvgPayTerms'n >60 And t3.'6MthAvgPayTerms'n <=90 THEN
45 'Indeterminate' WHEN t3.'6MthAvgPayTerms'n >90 THEN 'BAD' ELSE CASE When t2.Account_Status ='Final' WHEN t3.
46 '6MthAvgPayTerms'n >=30 Or t2.Account_Balance >=50 Or t3.'6MthAvgPayTerms'n =-1 Or t2.Pending_WO=-1 Or
47 t2.Litigation_Account =-1 Or t2.Trident_Account=-1 THEN 'BAD' WHEN t3.'6MthAvgPayTerms'n=0 THEN 'GOOD' WHEN
2 The SAS System 14:10 Monday, August 23, 2010

48 t3.'6MthAvgPayTerms'n>=0 And t3.'6MthAvgPayTerms'n <30 THEN 'Indeterminate' ELSE 'N/A' end ) AS
49 'Good Bad 1'n Message was edited by: thegraduate

Re: Problem with IF Statement

Peter_C — Tue, 24 Aug 2010 12:17:49 GMT

Hi

markup for the forum uses some of the characters in your program so it isn't presented clearly

(I used "Quote Original" button on the message entry frame to display more of your message)

The area causing the error might be just after the good/bad comment.
At that point the syntax appears to be
[pre]
THEN 'Indeterminate' ELSE 'N/A' end
/* Good Bad 1 */
(CASE WHEN t3.'6MthAvgPayTerms'n =""
Then 'N/A'
ELSE
CASE WHEN [/pre]
Your syntax seems to go from "end" to "(CASE" without anything in between.
That looks like a mistake to me.
I would expect at least the comma (between column definitions). However, the syntax still seems to be in the middle of CASE nesting.

If there is some way of (or somewhere to) upload the code so we could see the original syntax, we might be better able to avoid the forum-markup difficulties and make better informed judgements.

Forum markup explanations are at http://support.sas.com/forums/thread.jspa?messageID=27609毙

good luck
peterC

> t3.'6MthAvgPayTerms'n>=0 And t3.'6MthAvgPayTerms'n
> <30 THEN 'Indeterminate' ELSE 'N/A' end
>
>
> 42 /* Good Bad 1 */
> 43 (CASE WHEN
> t3.'6MthAvgPayTerms'n ="" Then 'N/A' ELSE CASE WHEN
> t2.Account_Status = 'Live' When t3.
>