Desktop productivity for business analysts and programmers

Concatenating a column based on another column

Reply
New User
Posts: 1

Concatenating a column based on another column

We are trying to concantenate column Items based on another column, Shopper_ID. Any suggestions on how we can achieve this?

 

Current result

Shopper_ID        Items                                                

1                         keyboard                                           

1                         mouse                                             

1                         notebook

2                         keyboard                                            

2                         screen protector

3                         case

 

Desired result

Shopper_ID       Items

1                        keyboard, mouse, notebook

2                        keyboard, screen protector

3                        case

PROC Star
Posts: 499

Re: Concatenating a column based on another column

[ Edited ]
Super User
Posts: 10,611

Re: Concatenating a column based on another column

data have;
input Shopper_ID        Items  $40.;
cards; 
1                         keyboard                                           
1                         mouse                                             
1                         notebook
2                         keyboard                                            
2                         screen protector
3                         case
;
run;
data want;
 length want $ 2000;
 do until(last.Shopper_ID);
  set have;
  by Shopper_ID ;
  want=catx(',',want,items);
 end;
 drop items;
 run;
Super User
Posts: 22,827

Re: Concatenating a column based on another column

If you're working with the GUI, then you would first Transpose the data using a TRANSPOSE task and then you can aggregate them using the function CATX in a query and using a calculated column.

Ask a Question
Discussion stats
  • 3 replies
  • 130 views
  • 0 likes
  • 4 in conversation