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
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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.