Need help with what in Excel is Concatenate If

[CLE]

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!

[RyanMcGovern]

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 · Accepted solution]

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]

That works wonderful! 

[LinusH]

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?

[CLE]

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.