Combine Multiple Rows' Values into One Field

[bendsteel6]

Hi All,

This should be pretty simple but I'm having difficulty figuring it out.  I'm trying to combine values from multiple observations into one field.  From the first table below to the 2nd.  Note that in some instances 'PART1' could have only one record, sometimes 2 and sometimes 3 so the code would have to be written to accommodate this.  I've shown it with PART1 having 3 records below.

PART_NAME	CLAIM_CAT
PART1	EXT
PART1	STD
PART1	PURCH

To this:

PART_NAME	CLAIM_CAT
PART1	EXT/STD/PURCH

Any help would be appreciated!!!

Thanks!

[Kurt_Bremser]

First of all, you have to create a new variable with sufficient length.

In a data step, retain this new variable.

Use by part_name;

At first.part_name, set the new variable to empty

Concatenate by using newvar = catx('/',trim(newvar),claim_cat);

At last.part_name, output

That should do it.

[bendsteel6]

Hi.  Thanks for your input.  I'm trying this but I just keep getting the same value in the new field as in the old...Here's the code (with some variable name changes to suit my actual data).  I must be doing something wrong...

Data:

PART_NAME	CLAIM_CAT
EGR COOLER, REPLACEMENT KIT	EXT
EGR COOLER, REPLACEMENT KIT	STD
EGR COOLER, REPLACEMENT KIT	PURCH

Code:

data testdave1;

set testdave;

retain PART_NAME;

by PART_NAME;

length concat $30.;

if first.PART_NAME then concat="";

concat=catx('/',trim(concat),trim(claim_cat));

if last.PART_NAME then output;

run;

Results:

PART_NAME	CLAIM_CAT	concat
EGR COOLER, REPLACEMENT KIT	PURCH	PURCH

[Kurt_Bremser]

You need to retain the new variable:

data testdave1;
set testdave;
retain concat;
by PART_NAME;
length concat $30.;
if first.PART_NAME then concat = "";
concat = catx('/',concat,claim_cat);
if last.PART_NAME then output;
drop claim_cat;
run;

The trim() functions are actually not necessary, as catx() does that automatically.

[bendsteel6]

Ahhhhhhh...yes!  Duhhh!!!  I should've seen that.  Works like a charm, saves me so much code writing.

THANK YOU!!!

[danidull]

How would you do this but prevent duplicates in the output column.