Desktop productivity for business analysts and programmers

Catx and Group by in QB

Reply
Contributor
Posts: 37

Catx and Group by in QB

[ Edited ]

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

Super User
Super User
Posts: 7,668

Re: Catx and Group by in QB

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.

Contributor
Posts: 37

Re: Catx and Group by in QB

Hi RW9,

 

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

 

 

Super User
Super User
Posts: 7,668

Re: Catx and Group by in QB

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.

Ask a Question
Discussion stats
  • 3 replies
  • 147 views
  • 0 likes
  • 2 in conversation