Problem with IF Statement

[thegraduate]

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,

[Peter_C]

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

[thegraduate]

i see,

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

Thanks,

[Doc_Duke]

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

[thegraduate]

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

Thanks,

[Cynthia_sas]

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

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

[thegraduate]

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

[Peter_C]

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