BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RPYee
Quartz | Level 8

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

CUSTOMERMODECOLOR
12345CARBLUE
12345CARRED
12345CARWHITE
98765BIKEBLUE
98765BIKERED

 

What I want

CUSTOMERMODECOLOR
12345CARBLUE,RED,WHITE
98765BIKEBLUE,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

 

___________________________________
The fact that I ask for help simply means I am inexperienced and under-educated. Please don't assume I am incompetent and uneducated.
1 ACCEPTED SOLUTION

Accepted Solutions
RPYee
Quartz | Level 8

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;

___________________________________
The fact that I ask for help simply means I am inexperienced and under-educated. Please don't assume I am incompetent and uneducated.

View solution in original post

2 REPLIES 2
Reeza
Super User

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
Quartz | Level 8

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;

___________________________________
The fact that I ask for help simply means I am inexperienced and under-educated. Please don't assume I am incompetent and uneducated.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 576 views
  • 0 likes
  • 2 in conversation