topic Re: CASE expression not working in Extract Transformation in SAS Data Management

CASE expression not working in Extract Transformation

jwhite — Wed, 03 Dec 2014 13:51:48 GMT

I know I've used CASE in the past, but for some reason in my current job I'm unable to use a CASE statement as an expression in an Extract.

The expression is as simple as:

CASE

WHEN sourcecolumn = 'Y' then targetcolumn = 'Yes'

ELSE sourcecolumn

END

I've tried many variations, but none validate. It seems there is a syntax error at the very beginning. When I look at the validation log, it appears as if the code actually says:

WHERE CASE

WHEN...

Where does that WHERE come from? I think it is causing the error. (I have nothing in the WHERE tab of the Extract Transformation)

Also, I recall in the expression field of the target column in the MAPPINGS tab, if I were to right click a CASE option would be available. A co-worker also sees this option. I don't

Is there something off in my job settings preventing the use of a CASE statement in an Extract? How do I get rid of the WHERE? Any other solutions?

Thanks!

Re: CASE expression not working in Extract Transformation

stat_sas — Wed, 03 Dec 2014 14:02:45 GMT

Try this.

CASE

WHEN sourcecolumn = 'Y' then 'Yes'

ELSE sourcecolumn

END as targetcolumn

Re: CASE expression not working in Extract Transformation

jwhite — Wed, 03 Dec 2014 14:13:35 GMT

Still getting the error.

Here's the CASE statement in the Expression:

Here's the error:

Still, any idea why CASE isn't an option in the expression field?

Re: CASE expression not working in Extract Transformation

stat_sas — Wed, 03 Dec 2014 14:21:06 GMT

Just a small modification in END as statement. Hopefully, this can provide the desired results.

CASE

WHEN group = 'Y' then 'Yes'

ELSE group

END as group

Re: CASE expression not working in Extract Transformation

jwhite — Wed, 03 Dec 2014 14:26:43 GMT

Made the modification to the CASE statement:

Still getting the error:

Like I mentioned, I wonder if it has anything with that WHERE syntax being inserted on line 9. Could it be causing the issue, and where is it coming from?

Re: CASE expression not working in Extract Transformation

Loko — Wed, 03 Dec 2014 14:28:36 GMT

Hello,

If I see correctly from your print screen you are trying to use CASE expression in a where clause. Try the following:

proc sql;

select *, CASE

WHEN sex = 'M' then 'Male'

ELSE sex

END as newsex

from sashelp.class

having newsex='Male' ;

quit;

Re: CASE expression not working in Extract Transformation

jwhite — Wed, 03 Dec 2014 14:30:47 GMT

Well, I'm not trying to use CASE within a WHERE clause. How is this WHERE clause getting generated? Is it by default?

Re: CASE expression not working in Extract Transformation

jwhite — Wed, 03 Dec 2014 14:37:18 GMT

ok, I tried that proc sql statement:

It appeared to validate, but when I opened the validation log...still an error:

Re: CASE expression not working in Extract Transformation

RW9 — Wed, 03 Dec 2014 15:03:08 GMT

Now this is just a guess, but where are you posting that sql code. I assume from the Data Management section that you are using some kind of graphical interface tool, I would assume that you are trying to put into what equates to a Where Condition window. So the "where" is the default text, which then takes the text you put in Expression column to try to evaluate rows, so in Loko's example, the case statement has been moved from the conditional part, up to the selection part. You would need to put the case in another column on your table, not the Expression one.

Sorry, can't be more specific, I avoid all types of graphical interface for these kind's of reason - faster and simpler to write code!

Re: CASE expression not working in Extract Transformation

jwhite — Wed, 03 Dec 2014 15:04:52 GMT

Ha! Great point...I forgot to mention that this is in Data Integration Studio. It is an interface tool.

Re: CASE expression not working in Extract Transformation

LinusH — Wed, 03 Dec 2014 15:58:05 GMT

This is a flaw in DI Studio, when validating an expression, it often (not always) puts the expression in a where clause, even if the expression is in a select clause.

In those cases, just ignore the error. You are better off validating by running the transformation instead - if your syntax is valid, it will succeed.

Tip: DI Studio will default output to a view. Temporarily switch to a table - that will reveal any error at once (instead of when reading the view from succeeding transformations).

Re: CASE expression not working in Extract Transformation

jwhite — Wed, 03 Dec 2014 16:00:02 GMT

LinusH, thanks!

I just found this out...I ignored the validation error, executed the job, and the Extract ran fine.

Odd, but good to know!