topic Re: CASE Statement Not Working in SAS Data Management

CASE Statement Not Working

Daylon_Hunt — Fri, 26 Feb 2016 20:14:55 GMT

I'm trying to create a CASE statement in the Target Table within a job in DI Studio. The field being referenced from the Source Table is a Character Type. My CASE statement is:

CASE WHEN PIFSTOP = 0 THEN '' ELSE 'C' END

This seems like a fairly straight forward Expression, but with no luck.

Re: CASE Statement Not Working

Haikuo — Fri, 26 Feb 2016 20:44:43 GMT

Syntaxwise seems fine to me, unless your '' is actually 1x double quote instead of 2X single quotes. If you could eleborate more on 'no luck', error messages, logs, expected output given input, etc.

Re: CASE Statement Not Working

Daylon_Hunt — Fri, 26 Feb 2016 20:51:49 GMT

The '' is actually two single quotes ' ' that I'm trying to use to essentially indicate a null value. So the statement is supposed to be saying, when PIFSTOP equals 0 (ZERO), then ' ' (Blank), ELSE 'C'.

The error that it's returning is:

ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, (, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, EQ, GE, GT, IN, IS, LE, LIKE, LT, NE, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

Re: CASE Statement Not Working

Reeza — Fri, 26 Feb 2016 21:00:00 GMT

Do you need to give it a name?

CASE WHEN PIFSTOP  = 0 THEN '' ELSE 'C' END AS NEW_VARIABLE

Or is that handled in the DI studio somewhere else?

Re: CASE Statement Not Working

FreelanceReinh — Fri, 26 Feb 2016 21:08:00 GMT

I'm not familiar with DI studio, but the condition PIFSTOP=0 requires that PIFSTOP is a numeric variable. However, you wrote that it "is a Character Type" (in which case a condition like PIFSTOP='0' would be syntactically correct).

That said, your error message is different from what I would anticipate in case of a type mismatch.

Re: CASE Statement Not Working

Daylon_Hunt — Fri, 26 Feb 2016 21:41:29 GMT

Still no luck but this is the error I receive when trying to view the data after using your CASE Statement:

Unable to execute query: SQL passthru expression contained these errors: ERROR: Expression using equals (=) has components that are of different data types.... ERROR: Character expression requires a character format.

Re: CASE Statement Not Working

FreelanceReinh — Fri, 26 Feb 2016 22:20:35 GMT

@Daylon_Hunt wrote:

(...) ERROR: Expression using equals (=) has components that are of different data types. ...

This is the error message indicating that PIFSTOP is of different type (i.e. character vs. numeric) than the constant on the other side of the equals sign (0 or '0').

Re: CASE Statement Not Working

Daylon_Hunt — Fri, 26 Feb 2016 22:24:30 GMT

I would agree with that assessment. So now what's the solution?

Re: CASE Statement Not Working

LinusH — Fri, 26 Feb 2016 23:23:14 GMT

The tricky thing about DI Studio is that you need to check both the Metadata definition (which should be correct) and the physical data. So check that your input column really is numeric.
Also, check that you have defined your target column as char.
You can use Analyze - Contents and the View Data functionality for this.

Re: CASE Statement Not Working

LinusH — Fri, 26 Feb 2016 23:27:12 GMT

@Reeza: the column name is defined in the transformation Metadata. Thus, it's not specified in the expression. This way, you can quite easily change column definition in a whole program flow via a single point and click operation without having to change expressions.

Re: CASE Statement Not Working

Daylon_Hunt — Fri, 26 Feb 2016 23:59:42 GMT

I've confirmed that the Source data is indeed Character, and my Target field will also be Character. With that said, I'm now attempting the following CASE statement, which also is not working:

CASE WHEN PIFSTOP = 0 THEN 'O' ELSE 'C' END

I'm using the exact same Informat and Format types as the Source field, but getting the below error:

Re: CASE Statement Not Working

Reeza — Sat, 27 Feb 2016 02:08:59 GMT

I think the code is then:

 
CASE WHEN PIFSTOP  = '0' THEN 'O' ELSE 'C' END

Re: CASE Statement Not Working

LinusH — Sat, 27 Feb 2016 06:25:54 GMT

If PIFSTOP is char, your constant in the expression must also be defined as char, '0'.

Re: CASE Statement Not Working

Daylon_Hunt — Sun, 28 Feb 2016 18:10:02 GMT

The problem was that I wasn't putting ' ' around the 0, which since zero is a numeric value then I didn't think that it was required but your explanation as to why it has to be, makes perfect sense. Thank you LinusH!!