DATA Step, Macro, Functions and more

New field that concatenate values from different rows

Reply
Frequent Contributor
Posts: 95

New field that concatenate values from different rows

Hello

I have to following issue.

I have data that includes few rows for each customer.

data aaa;

infile cards;

input CustomerID  group  $;

cards;

1 a

1 c

2 b

2 a

2 c

3 a

;

run;

 

I want to create a new data set that contain 1 row for each customer with a calculated string field called: CustomerGroups  that concatenate all groups that a customer belong to

CustomerID   CustomerGroups 

1            a_c

2            a_b_c

3            a

Please see that the concatenate of the groups should be from low to high.

For example customer 2 belongs to groups a  b c so the new field should be a_b_c

PROC Star
Posts: 1,190

Re: New field that concatenate values from different rows

like this

 

data aaa;
infile cards;
input CustomerID  group  $;
cards;
1 a
1 c
2 b
2 a
2 c
3 a
;
run;

data want(keep=CustomerID CustomerGroups);
   set aaa;
   by CustomerID;
   if first.CustomerID then CustomerGroups=group;
   else CustomerGroups=catx('_', CustomerGroups, group);
   if last.CustomerID then output;
   retain CustomerGroups;
run;
Contributor
Posts: 20

Re: New field that concatenate values from different rows

[ Edited ]

Hi,

Look at proc transpose.  You could also loop over the data in a data step.  

 

data aaa;
infile cards;
input CustomerID  group  $;
cards;
1 a
1 c
2 b
2 a
2 c
3 a
;
run;

proc sort data=aaa;
	by CustomerID;
run;

proc transpose data=aaa out=aaatp;
	by CustomerID;
	var group;
run;

data aaa_concat;
	set aaatp;
	array col col1-col3;
	attrib CustomrGroups length=$6;
	CustomerGroups = catx(',', of col[*]);
	drop _: col:;	
run;

HTH,

Cameron.

Regards,
Cameron | Selerity
Super User
Posts: 6,543

Re: New field that concatenate values from different rows

[ Edited ]

Because of the requirement to put the items in alphabetical order, it takes a little more programming.  For example:

 

proc transpose data=aaa out=wide (keep=CustomerID col: ) ;

var group;

by CustomerID;

run;

 

data want;

set wide;

array groups {*} col: ;

call sortc(of col: ) ;

length CustomerGroups $ 30;

do k=1 to dim(groups);

   if groups{k} > ' ' then CustomerGroups = catx('_', CustomerGroups, groups{k});

end;

keep CustomerID CustomerGroups;

run;

 

It's untested code, so might need a small tweak.  But it should contain all the proper steps.

PROC Star
Posts: 1,351

Re: New field that concatenate values from different rows

data want;
array temp(100)  $20   ;
call missing(of temp(*));
do _n_=1 by 1 until( last.CustomerID);
set aaa;
by 	CustomerID ;
temp(_n_)= Group;
if last.CustomerID then do;call sortc(of temp(*));
customergroups=trim(catx('_', of temp:)) ;
end;
end;
drop temp:;
run;
Esteemed Advisor
Posts: 5,403

Re: New field that concatenate values from different rows

Until SAS implements an aggregate function to do this in SAS/SQL (hint, hint Smiley Happy) I always do this:

 

proc sort data=aaa; by customerId group; run;

data want;
length customerGroups $24;
do until(last.customerid);
    set aaa; by customerid;
    customerGroups = catx("_", customerGroups,  group);
    end;
drop group;
run;
PG
Ask a Question
Discussion stats
  • 5 replies
  • 153 views
  • 2 likes
  • 6 in conversation