DECODE function in Proc SQL

[RandomUserFS]

Hello everybody,

I'm not sure how to convert the following Oracle SQL syntax with DECODE function in to Proc SQL syntax within SAS.

Your assistance would be greatly appreciated.

DECODE(( con.AREA_CODE ),NULL,NULL,'('||( con.AREA_CODE )||') ')||( con.CONTACT_NUMBER )||DECODE(( con.EXTENSION ),NULL,NULL,' ext.'||( con.EXTENSION )) as Ind_Home_Phone ,

Thanks

[RW9]

Decode is just another way of doing a case when statement.

So <variable is your variable, code is the short term you might have, and decode is the long term is the long value you want:

decode(<variable>,<code>,<decode>,[<code>,<decode>])

case <variable> when <code> then <decode>
                when <code> then <decode>
                else <value> end as <new_variable>

case when <condition> then <decode>
     when <condition> then <decode>
     else <value> end as <new_variable>

So if you had:

xyz=decode(old_var,"a","something","b","else")

The equivalent would be:

case old_var when "a" then "something"
                      when "b" then "else"
                      else "" end as xyz

I used the first method of case here, but either works.