topic Re: Question about setting up a simple expression in an SQL Join in SAS Data Management

Question about setting up a simple expression in an SQL Join

TurnTheBacon — Thu, 07 Feb 2013 08:50:49 GMT

I'm working in an SQL Join transformation in DI Studio, and need to make sure that all rows for one specific bank get the flag-variable set to "N".

Why can't I just map the BANK_ID and BANK_FLAG from the source table, into BANK_FLAG in the target table, then use the following expression?

if BANK_ID = "123456" then do;

BANK_FLAG = "N";

end;

I also tried this:

case BANK_FLAG

when BANK_ID = "123456" then "N"

else BANK_FLAG

end;

I'd appreciate any aid on this rookie problem. Thanks for your time.

LinusH — Thu, 07 Feb 2013 10:50:40 GMT

Well, these expression have errors, which you probably encountered!

IF-THEN-ELSE-logic belongs to the data step, not "permitted" in SQL.

A case statement should not end with a ;, that will end the whole CREATE VIEW/TABLE statement.

Is that correct that you already have a BANK_FLAG in the source table, and you just want to change it's value for a specific bank?

TurnTheBacon — Thu, 07 Feb 2013 11:00:18 GMT

Thanks. Yes, that's correct. I know how to do it in user written code, but apparently not in an SQL Join expression.