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!
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;
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;
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;
That works wonderful!
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.
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.