topic Re: Catx and Group by in QB in SAS Enterprise Guide

Catx and Group by in QB

SASNE — Tue, 14 Feb 2017 13:46:28 GMT

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

Re: Catx and Group by in QB

RW9 — Tue, 14 Feb 2017 14:10:31 GMT

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.

Re: Catx and Group by in QB

SASNE — Tue, 14 Feb 2017 14:12:39 GMT

Hi RW9,

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

Re: Catx and Group by in QB

RW9 — Tue, 14 Feb 2017 14:47:15 GMT

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.