Desktop productivity for business analysts and programmers

Need help with what in Excel is Concatenate If

Accepted Solution Solved
Reply
Contributor CLE
Contributor
Posts: 21
Accepted Solution

Need help with what in Excel is Concatenate If

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!


Accepted Solutions
Solution
‎02-09-2016 05:21 PM
Super User
Posts: 19,157

Re: Need help with what in Excel is Concatenate If

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


All Replies
Occasional Contributor
Posts: 11

Re: Need help with what in Excel is Concatenate If

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;

 

Solution
‎02-09-2016 05:21 PM
Super User
Posts: 19,157

Re: Need help with what in Excel is Concatenate If

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;
Contributor CLE
Contributor
Posts: 21

Re: Need help with what in Excel is Concatenate If

[ Edited ]

That works wonderful! 

Super User
Posts: 5,386

Re: Need help with what in Excel is Concatenate If

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
Contributor CLE
Contributor
Posts: 21

Re: Need help with what in Excel is Concatenate If

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.  

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 503 views
  • 4 likes
  • 4 in conversation