*create sample data for demonstration;
data have;
infile cards dlm='09'x;
input OrgID Product $ States $;
cards;
1 football DC
1 football VA
1 football MD
2 football CA
3 football NV
3 football CA
;
run;
*Sort - required for both options;
proc sort data=have;
by orgID;
run;
**********************************************************************;
*Use RETAIN and BY group processing to combine the information;
**********************************************************************;
data want_option1;
set have;
by orgID;
length combined $100.;
retain combined;
if first.orgID then
combined=states;
else
combined=catx(', ', combined, states);
if last.orgID then
output;
run;
**********************************************************************;
*Transpose it to a wide format and then combine into a single field;
**********************************************************************;
proc transpose data=have out=wide prefix=state_;
by orgID;
var states;
run;
data want_option2;
set wide;
length combined $100.;
combined=catx(', ', of state_:);
run;
https://gist.github.com/statgeek/d583cfa992bf56da51d435165b07e96a
@Smitha9 wrote:
Hi,
I have a dataset
ID SubID Name
1 20 car
1 20 dog
1 20 deer
2 32 fox
2 32 goat
I want to concatenate the Name with comma.
ID SubID Name
1 20 car, dog, deer
2 32 fox, goat
thank you.