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

I have a table that has columns for product numbers and maufacturers.  I want it to create a new table based on concatenating all possible manufactuers for a product number. 

 

For example:

Product Number         Manufacturer

1111                           ABC

1111                           DEF

1211                           GHI

1324                           ABC

 

I need it to be a table to join on that shows me this

 

1111                         ABC, DEF

1211                        GHI

1324                         ABC

 

Your help is appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

You're looking for a couple of different idea's here:

RETAIN

BY GROUPS processing.

 

data have;
input Product_Number $         Manufacturer $;
cards;
1111                           ABC
1111                           DEF
1211                           GHI
1324                           ABC
;
run;

data want;
length manufacturer_combined $200.;
set have;
by product_number;
retain manufacturer_combined;
if first.product_number then manufacturer_combined="";
manufacturer_combined = catx(", ", manufacturer_combined, manufacturer);

if last.product_number then output;
run;

proc print data=want;
run;

View solution in original post

5 REPLIES 5
RyanMcGovern
Obsidian | Level 7

You could try to sort and then transpose the variables before concatenating them like:

 

data Products_1;

input Product_Number $5. Manufacturer $3.;

cards;

1111 ABC

1111 DEF

1211 GHI

1324 ABC

;

run;

 

PROC SORT DATA=Products_1 OUT=Products_2;

BY Product_Number;

RUN;

 

PROC TRANSPOSE DATA=Products_2 OUT=Products_3(DROP=_NAME_);

BY Product_Number;

VAR Manufacturer;

RUN;

 

DATA Products_4(KEEP=Product_Number Manufacturers);

SET Products_3;

Manufacturers=CATX(", ", COL1, COL2);

RUN;

 

Reeza
Super User

You're looking for a couple of different idea's here:

RETAIN

BY GROUPS processing.

 

data have;
input Product_Number $         Manufacturer $;
cards;
1111                           ABC
1111                           DEF
1211                           GHI
1324                           ABC
;
run;

data want;
length manufacturer_combined $200.;
set have;
by product_number;
retain manufacturer_combined;
if first.product_number then manufacturer_combined="";
manufacturer_combined = catx(", ", manufacturer_combined, manufacturer);

if last.product_number then output;
run;

proc print data=want;
run;
CLE
Obsidian | Level 7 CLE
Obsidian | Level 7

That works wonderful! 

LinusH
Tourmaline | Level 20
Can you explain what you play to do with the result?
Do the product no need to be in a string, or is it just the layout you are after?
Data never sleeps
CLE
Obsidian | Level 7 CLE
Obsidian | Level 7

The data is for a flat file we have to provide to a group of limited skill employees to use for their job to assist in research. Only updating every 6 months.  

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 5 replies
  • 1314 views
  • 4 likes
  • 4 in conversation