topic Re: Why doesn't this case-expression work in DI Studio? in SAS Data Management

Why doesn't this case-expression work in DI Studio?

TurnTheBacon — Tue, 11 Dec 2012 10:00:49 GMT

Can someone please tell me why the following code doesn't work as an expression in the mapping tab of a DI Studio transformation?

case EMAIL

when not contains "@" then NULL

when contains "æ" then NULL

when contains "ø" then NULL

when contains "å" then NULL

else EMAIL

end

I get this error message: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, , =, , =, >, >=, ?, AND, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

Thanks.

Re: Why doesn't this case-expression work in DI Studio?

LinusH — Tue, 11 Dec 2012 10:24:58 GMT

When you hace specified a column at the case, you can't use expressions that implies the column at the bueginning of the expression.

Move EMAIL to each when statment to keep the logic.

Re: Why doesn't this case-expression work in DI Studio?

TurnTheBacon — Tue, 11 Dec 2012 10:52:07 GMT

Thanks. I seem to get the exact same error message when I try the following code though. Can you see what's wrong?

case

when EMAIL not contains "@" then NULL

when EMAIL contains "@." then NULL

when EMAIL contains "æ" then then NULL

when EMAIL contains "ø" then NULL

when EMAIL contains "å" then NULL

else EMAIL

end

Re: Why doesn't this case-expression work in DI Studio?

LinusH — Tue, 11 Dec 2012 11:02:39 GMT

NULL has just a meaning when it comes to boolean expression, it's not a value that you can use in an assignment.
...

when email not contains "@" then ' '

...

Re: Why doesn't this case-expression work in DI Studio?

TurnTheBacon — Tue, 11 Dec 2012 11:49:48 GMT

Problem solved. Many thanks!