BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
brulard
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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.

View solution in original post

1 REPLY 1
Reeza
Super User

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.

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 752 views
  • 2 likes
  • 2 in conversation