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
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;
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.