topic Re: Concatenating a column based on another column in SAS Enterprise Guide

Concatenating a column based on another column

gfs9311 — Thu, 17 Aug 2017 13:09:54 GMT

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

Re: Concatenating a column based on another column

kiranv_ — Thu, 17 Aug 2017 13:30:48 GMT

@Haikuo and @Ksharp solutions in below link perfect for this scenario

https://communities.sas.com/t5/Base-SAS-Programming/Concatenate-multiple-rows-into-a-single-value/td-p/132882?nobounce

Re: Concatenating a column based on another column

Ksharp — Thu, 17 Aug 2017 13:47:01 GMT

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;

Re: Concatenating a column based on another column

Reeza — Thu, 17 Aug 2017 14:55:20 GMT

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.