topic Re: DI Studio - CASE statement not working in Data Validation transformation in SAS Data Management

DI Studio - CASE statement not working in Data Validation transformation

MWest — Tue, 17 Nov 2015 13:33:27 GMT

I'm trying to straighten out some faulty data with the Data Validation transformation in my ETL process.

I'm using the CASE expression to achieve this.

My expression: CASE WHEN Discount < 0 THEN (Discount * (-1)) ELSE Discount END

This syntax seems to work in the SAS Enterprise Guide but it throws an error in my transformation.

Error:

2065                 x_custom_rule = "";                    output etls_Exceptions;                    Discount = CASE WHEN Discount
2065     ! < 0 THEN (Discount * (-1)) ELSE Discount END;                  end;                if error_rows=0 then       do;
                ____
                22
ERROR 388-185: Expecting an arithmetic operator.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, ;, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT,
              LE, LT, MAX, MIN, NE, NG, NL, OR, ^=, |, ||, ~=.

I'm really confused about this problem.

I'm using SAS Integration Studio 4.8

Cheers

Re: DI Studio - CASE statement not working in Data Validation transformation

RW9 — Tue, 17 Nov 2015 13:40:29 GMT

Well, I don't use DI so just some suggestions to try out:

- Is discount a numeric variable

- Have you replace the " < 0" with typed " lt 0". I.e. replace the symbol with lt, and ensure that it is a zero type.

Re: DI Studio - CASE statement not working in Data Validation transformation

MWest — Tue, 17 Nov 2015 13:46:10 GMT

Yes discount is a numeric variable.

Using LT instead of < makes no difference, the error is the same.

But what do you mean by zero type? It is a proper zero not an O or anything if that's what you mean.

Re: DI Studio - CASE statement not working in Data Validation transformation

RW9 — Tue, 17 Nov 2015 13:47:20 GMT

Yes, I meant a zero. They are all the things I could think of just from a coding point of view, must something in DI as the code would be fine then. Sorry, can't be of more use.

Re: DI Studio - CASE statement not working in Data Validation transformation

FreelanceReinh — Tue, 17 Nov 2015 14:03:27 GMT

I don't use DI either, but your code snippet looks like you're mixing PROC SQL code ("CASE WHEN ...") with DATA step code ("if ... then do; ..."). I'd suggest you replace your CASE WHEN ... END expression by one of the following:

abs(Discount)
ifn(Discount<0, -Discount, Discount)

Re: DI Studio - CASE statement not working in Data Validation transformation

LinusH — Tue, 17 Nov 2015 16:32:44 GMT

I can confirm what @FreelanceReinh is pointing at. Daata Validation is data step based transformation.

So the only thin that you can isert here is an expression that is valid in a datastep assignment statment, on the right side of the = .

Not sure what you are trying to do. Dicount sounds like it would contain continues values. Invalid Values is target for situations with discrete values in a look up table. Perhaps you are better off using Custom Validation?

Re: DI Studio - CASE statement not working in Data Validation transformation

MWest — Wed, 18 Nov 2015 12:27:43 GMT

@FreelanceReinh: I don't know about when to use the PROC SQL and when to use the DATA step code. I'm using the Expression Builder of DI Studio and there this function is available for use (see attachment). So I'm not sure why they would list a function that doesn't work.
Your solution with abs(Discount) serves my purpose though. Thanks!

@LinusH: What I'm trying to do is adjusting invalid values only if they fulfill certain criteria.

In my case i want to make the Discount a positive value in case somebody registered a negative value. For this purpose I can use the abs() function as stated by @FreelanceReinh. But I'm not sure how one is supposed to do this with more complex corrections when there is no conditional logic available.

Re: DI Studio - CASE statement not working in Data Validation transformation

LinusH — Wed, 18 Nov 2015 12:34:34 GMT

As I said in my previous post, Custom Validation gives you the ability to do conditional logic, but not SQL case expressions.

The code you insert must work in a data step. Try to fill in some code. Then switch to the "Code" tab, and see how your code snippet is inserted in the Transformation generated code.

If you can't get your logic into the Data Validation transformation, use other transformations.

If you nee to use case expressions, Extract and Join transformations will work.

Re: DI Studio - CASE statement not working in Data Validation transformation

FreelanceReinh — Wed, 18 Nov 2015 13:45:15 GMT

Since I've never used DI studio, I can only guess. I take it that there must be some place in DI Studio where CASE expressions are valid, but the "New Value" field in the "Invalid Values" dialog box does not seem to be such a place. The code typed in there appears to be included into what looks like DATA step code. The first two error messages that you got (388-185 and 202-322) are exactly the same that I get if I try to assign a CASE expression to a variable in a DATA step. Indeed, "New Value" sounds to me more like a literal value or an expression using operators or functions.

Re: DI Studio - CASE statement not working in Data Validation transformation

MWest — Wed, 18 Nov 2015 14:12:39 GMT

I guess that's it. Thank you guys for clearing this issue up!