topic Re: Using Enterprise Guide, Combine Rows in results in SAS Enterprise Guide

Using Enterprise Guide, Combine Rows in results

RPYee — Fri, 22 Nov 2019 21:53:33 GMT

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

Re: Using Enterprise Guide, Combine Rows in results

Reeza — Fri, 22 Nov 2019 22:52:23 GMT

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

Re: Using Enterprise Guide, Combine Rows in results

RPYee — Fri, 03 Jan 2020 20:34:42 GMT

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;