DATA Step, Macro, Functions and more

Combine Multiple Rows' Values into One Field

Reply
Contributor
Posts: 32

Combine Multiple Rows' Values into One Field

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_NAMECLAIM_CAT
PART1EXT
PART1STD
PART1PURCH

 

To this:

PART_NAMECLAIM_CAT
PART1EXT/STD/PURCH

 

Any help would be appreciated!!!

Thanks!

Super User
Posts: 10,592

Re: Combine Multiple Rows' Values into One Field

Posted in reply to bendsteel6

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 32

Re: Combine Multiple Rows' Values into One Field

Posted in reply to KurtBremser

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_NAMECLAIM_CAT
EGR COOLER, REPLACEMENT KITEXT
EGR COOLER, REPLACEMENT KITSTD
EGR COOLER, REPLACEMENT KITPURCH

 

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_NAMECLAIM_CATconcat
EGR COOLER, REPLACEMENT KITPURCHPURCH
Super User
Posts: 10,592

Re: Combine Multiple Rows' Values into One Field

Posted in reply to bendsteel6

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 32

Re: Combine Multiple Rows' Values into One Field

Posted in reply to KurtBremser

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

 

THANK YOU!!!

 

Ask a Question
Discussion stats
  • 4 replies
  • 1079 views
  • 1 like
  • 2 in conversation