Using Enterprise Guide, Combine Rows in results

[RPYee]

I have a table with a one-to-many design.  I'd like to convert it to a one-to-one table.  See below:

What I have

CUSTOMER	MODE	COLOR
12345	CAR	BLUE
12345	CAR	RED
12345	CAR	WHITE
98765	BIKE	BLUE
98765	BIKE	RED

What I want

CUSTOMER	MODE	COLOR
12345	CAR	BLUE,RED,WHITE
98765	BIKE	BLUE,RED

Is there a way I can accomplish this in  EG?  Coding isn't my forte 😉

Thanks for any help you can provide!

Rita Yee

[Reeza]

1. Use a Transpose task to flip your data to a wide format. 

2. Use Query Builder and a computed column with the CATT function to concatenate all the results into one field. 

Here are coding solutions by the way, if you run the last option, it will be what I'm suggesting you do, except you can use Tasks to do it. You can run the code and test it - check the intermediary tables as well. 

https://gist.github.com/statgeek/d583cfa992bf56da51d435165b07e96a

---

@RPYee wrote:

I have a table with a one-to-many design.  I'd like to convert it to a one-to-one table.  See below:

What I have

CUSTOMER	MODE	COLOR
12345	CAR	BLUE
12345	CAR	RED
12345	CAR	WHITE
98765	BIKE	BLUE
98765	BIKE	RED

What I want

CUSTOMER	MODE	COLOR
12345	CAR	BLUE,RED,WHITE
98765	BIKE	BLUE,RED

Is there a way I can accomplish this in  EG?  Coding isn't my forte 😉

Thanks for any help you can provide!

Rita Yee

---

[RPYee · Accepted solution]

Learned how to run a program to accomplish what I wanted!

data OUTPUT_NEW (drop=cage_reason_cd);
set OUTPUT;
by employee_nbr;
length cage_reason_cd_new $200;
retain cage_reason_cd_new;
cage_reason_cd_new=ifc(first.employee_nbr,cage_reason_cd,catx(',',cage_reason_cd_new,cage_reason_cd));
if last.employee_nbr then output;
run;