Catx and Group by in QB

[SASNE]

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

[RW9]

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.

[SASNE]

Hi RW9,

thanks, do you know how to do that in Enterprise guide under query builder?

[RW9]

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.