BookmarkSubscribeRSS Feed
bendsteel6
Obsidian | Level 7

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!

5 REPLIES 5
Kurt_Bremser
Super User

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
Obsidian | Level 7

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
Kurt_Bremser
Super User

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
Obsidian | Level 7

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

 

THANK YOU!!!

 

danidull
Calcite | Level 5

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 13010 views
  • 1 like
  • 3 in conversation