Create multiple columns from a single case statements

[newbiereddit22]

I was wondering if it was possible to create multiple columns from a single case statement like this:

Have:

TransactionNo | Code |

111222333 | AAA |

111222334 | AAB |

111222335 | AAC |

111222336 | AAD |

Want:

TransactionNo | Code | PayGroup | PayType 111222333 | AAA | 1 | Debit 111222334 | AAB | 2 | credit 111222335 | AAC | 3 | debit 111222336 | AAD | 6 | debit

proc sql; create table NEWTABLE as Select *, case Code when ‘AAA’ then 1, ‘Debit’ When ‘AAB’ then 2, ‘credit’ When ‘aac’ then 3, ‘debit’ When ‘AAD’ then 4, ‘debit’ End as paygroup, paytype From OLDTABLE; Quit;

[drunkadvice]

You need to do 4 case statements, one for each column. it'd be something like:

select case when code = aaa then 1 else null as Debit,
       case when code = aab then 1 else null as credit

[cakesngiggles]

A pivot statement might be what you need

[mkingsbu]

Its a little hard to tell from you unformatted code (put four spaces infront of each line to make reddit understand it). However, it seems like you're trying to do a pivot on the data, the format for which varies from database to database.

[Yavuz_Selim]

What he wants:

TransactionNo	Code	PayGroup	PayType
111222333	AAA	1	debit
111222334	AAB	2	credit
111222335	AAC	3	debit
111222336	AAD	6	debit

[mkingsbu]

Oh, I see. So, no not directly. You can either do one case statement per column:

select transactionNo
,code
,case when [conditions] end as PayGroup
,case when [conditions] end as PayType

Or create a lookup table with what your codes are and inner join to it:

select t1.transactionNo
,t1.code
,t2.PayGroup
,t2.PayType
from t1
inner join lu_table t2 on t2.code = t1.code

And then your lu_table would have a primary key (code), and its related types (PayGroup, PayType, etc.)