topic Re: Combine Multiple Rows' Values into One Field in SAS Programming

Combine Multiple Rows' Values into One Field

bendsteel6 — Fri, 27 Oct 2017 12:01:09 GMT

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!

Re: Combine Multiple Rows' Values into One Field

Kurt_Bremser — Fri, 27 Oct 2017 12:20:28 GMT

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.

Re: Combine Multiple Rows' Values into One Field

bendsteel6 — Fri, 27 Oct 2017 12:41:20 GMT

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

Re: Combine Multiple Rows' Values into One Field

Kurt_Bremser — Fri, 27 Oct 2017 13:03:03 GMT

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.

Re: Combine Multiple Rows' Values into One Field

bendsteel6 — Fri, 27 Oct 2017 13:05:35 GMT

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

THANK YOU!!!

Re: Combine Multiple Rows' Values into One Field

danidull — Tue, 11 Oct 2022 10:49:37 GMT

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