SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

DI Studio - CASE statement not working in Data Validation transformation

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

DI Studio - CASE statement not working in Data Validation transformation

Hi

 

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


dataValidationCaseExpression.png

Accepted Solutions
Solution
‎11-18-2015 08:56 AM
Trusted Advisor
Posts: 1,118

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

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.

View solution in original post


All Replies
Super User
Super User
Posts: 7,997

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

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.

New Contributor
Posts: 4

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

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.

Super User
Super User
Posts: 7,997

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

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.

Trusted Advisor
Posts: 1,118

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

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:

  1. abs(Discount)
  2. ifn(Discount<0, -DiscountDiscount)

 

Super User
Posts: 5,441

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

Posted in reply to FreelanceReinhard

I can confirm what @FreelanceReinhard 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?

Data never sleeps
New Contributor
Posts: 4

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

Posted in reply to FreelanceReinhard

@FreelanceReinhard: 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 @FreelanceReinhard. But I'm not sure how one is supposed to do this with more complex corrections when there is no conditional logic available.

 


expressionBuilder.png
Super User
Posts: 5,441

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

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.

Data never sleeps
Solution
‎11-18-2015 08:56 AM
Trusted Advisor
Posts: 1,118

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

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.

New Contributor
Posts: 4

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

Posted in reply to FreelanceReinhard

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 565 views
  • 0 likes
  • 4 in conversation