Hi Guru,
Hope you can help with this question, do you know how to catx group by in Query builder?
e.g.
Dataset :
Apple A
Apple P
Orange O
Orange R
Expected output
Apple A,P
Orange O,R
Thanks
Its not a simple task in SQL, wrong type of output data structure. Simple in a datastep however:
data want; set have; length fruit_list $2000; retain fruit_list $; by fruit; fruit_list=ifc(first.fruit,list,catx(',',fruit_list,list); run;
Now I have assumed fruit is the first column and is character, list is the second and is character - this is because you have not provided test data in the form of a datastep.
Hi RW9,
thanks, do you know how to do that in Enterprise guide under query builder?
Query builder is SQL, so that is not the best tool for the job - if it is at all possible in the tool. You can add code nodes into your EG project can you not, i.e. process the data before you go to your Query Builder? Personally I avoid front ends for coding, they are limited at the best of times, and downright pain in the regions in others.
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.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.