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.
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.
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, ^, ^=, |, ||, ~, ~=.
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?
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.
@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').
I would agree with that assessment. So now what's the solution?
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.
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:
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, ^, ^=, |, ||, ~, ~=.
I think the code is then:
CASE WHEN PIFSTOP = '0' THEN 'O' ELSE 'C' END
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!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.