BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MWest
Calcite | Level 5

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
1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

MWest
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

FreelanceReinh
Jade | Level 19

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)

 

LinusH
Tourmaline | Level 20

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?

Data never sleeps
MWest
Calcite | Level 5

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

 


expressionBuilder.png
LinusH
Tourmaline | Level 20

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
FreelanceReinh
Jade | Level 19

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.

MWest
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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