Best practice or short cut on multiple SQL CASE statements

brulard — Wed, 11 Apr 2018 19:55:12 GMT

hi,

I'm writing a query involving multiple left joins using several months' worth of data for same group of customers.

To help end user understand arbitrary value, I add a CASE statement to include the Value description.

Can someone suggest a way for me to only have to write the case description ONCE, so then each subsequent month/ and case the value need not be re written? Should this be done via MACRO, and then referring to it using the INCLUDE statement?

End result would look like:

Acct Status_mon01 Desc_mon01 Status_mon02 Desc_mon02 /*and so on */

001 S No mail S No Mail

002 C Vacation N Pending

sample code:

proc sql; create table base

select

t1.*,

t2.Status as Status_mon01,

case t2.status

when 'A' then 'MAIL'

when 'S' then 'No mail'

/* and so one until end of when-then */

end as Desc_mon01,

t3.status as status_mon02,

case <...>

from table1 as t1 left join <...> on

t1.id=t2.id

left join

<...>

thank you

Re: Best practice or short cut on multiple SQL CASE statements

Reeza — Wed, 11 Apr 2018 20:12:09 GMT

Store your data in a long format.

Do the calculation once

Use TRANSPOSE/REPORT to flip your data.

Or do it in a data step in an array for the calculations. That's likely the easiest/quickest fix.

topic Re: Best practice or short cut on multiple SQL CASE statements in SAS Programming

Best practice or short cut on multiple SQL CASE statements

Re: Best practice or short cut on multiple SQL CASE statements