topic Re: Concatenate through group but ignoring duplicates in SAS Procedures

Concatenate through group but ignoring duplicates

CharlesFowler72 — Thu, 19 Jul 2018 09:20:36 GMT

Starting from a really good post called "concatenate through group by using SAS or PROC SQL, urgent help needed" where there are some great solutions. But I wanted to add in 1 more complication.

Link: https://communities.sas.com/t5/SAS-Procedures/concatenate-through-group-by-using-SAS-or-PROC-SQL-urgent-help/td-p/111702?nobounce

In the original post, the aim is to create a variable which concatenates the groups which a customer is in. For example if Customer A was in Groups 1, 2 and 3 then the variable would read 1/2/3 however it assumed only 1 row per customer per group. What I need is for a customer which has multiple rows in multiple groups with a variable that only reads which groups it is in an not double concatenating any of the groups.

Using table below...

Customer	Group
1	A
1	B
2	A
2	B
2	B
2	C
3	A
3	A

As such, in the example about the additional column for Customer 1 would read A/B, Customer 2 A/B/C and Customer 3 just A.The previous post with the link above, which might be a great starting point, returns A/B/B/C for customer 2. A great solution and really quite close to what I am looking for, but some of my customer have a lot of rows in each group and looks a little silly.

thanks for the help in advance.

Re: Concatenate through group but ignoring duplicates

kiranv_ — Thu, 19 Jul 2018 10:03:51 GMT

if you remove duplicates using proc sort and using the solution given in the link should solve the problem

Re: Concatenate through group but ignoring duplicates

FreelanceReinh — Thu, 19 Jul 2018 10:04:53 GMT

You could create an intermediate table or better a view which contains the unique customer-group pairs and then continue from there.

For example:

data have;
input Customer Group $;
cards;
1 A
1 B
2 A
2 B
2 B
2 C
3 A
3 A
;

proc sql;
create view cust_grp as
select distinct customer, group
from have;
quit;

data want;
length grps $30;
do until(last.customer);
  set cust_grp;
  by customer;
  grps=catx('/',grps, group);
end;
drop group;
run;

Or would you like to distinguish between grps='A/B' and grps='B/A' ?

Re: Concatenate through group but ignoring duplicates

CharlesFowler72 — Thu, 19 Jul 2018 10:12:49 GMT

No, that has worked a charm. Perfect! Thank you.