topic Re: Need help with what in Excel is Concatenate If in SAS Enterprise Guide

Need help with what in Excel is Concatenate If

CLE — Tue, 09 Feb 2016 18:11:10 GMT

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!

Re: Need help with what in Excel is Concatenate If

RyanMcGovern — Tue, 09 Feb 2016 18:23:09 GMT

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;

Re: Need help with what in Excel is Concatenate If

Reeza — Tue, 09 Feb 2016 18:27:01 GMT

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;

Re: Need help with what in Excel is Concatenate If

LinusH — Tue, 09 Feb 2016 19:53:33 GMT

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?

Re: Need help with what in Excel is Concatenate If

CLE — Tue, 09 Feb 2016 22:22:00 GMT

That works wonderful!

Re: Need help with what in Excel is Concatenate If

CLE — Tue, 09 Feb 2016 21:01:40 GMT

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.