Solved
New Contributor
Posts: 2

# combine values

Hi there,

I would like to combine several results (text) into one row.

Example;

 box_ID contents value 1 shoes 100 1 clothing 200 1 towels 150 2 computer 350 2 DVDs 125 3 books 225 3 music 75 3 magazines 50 3 postcards 50

into

 box_ID contents value 1 shoes, clothing, towels 450 2 computer, DVDs 475 3 books, music, magazines, postcards 400

Can someone help me?

Thanks!

Accepted Solutions
Solution
‎11-15-2016 07:03 AM
Super User
Posts: 9,840

## Re: combine values

Hi,

```data want;
set have;
length contents \$2000;
retain ncontents nvalue;
by box_id;
if first.box_id then do;
ncontents=box_id;
nvalue=value;
end;
else do;
ncontents=catx(", ",ncontents,contents);
nvalue=sum(nvalue,value);
end;
if last.box_id then output;
run;```

Not tested, if you want tested code then please provide test data in the form of a datastep so we can copy/paste/run, not here to type in test data.

All Replies
Solution
‎11-15-2016 07:03 AM
Super User
Posts: 9,840

## Re: combine values

Hi,

```data want;
set have;
length contents \$2000;
retain ncontents nvalue;
by box_id;
if first.box_id then do;
ncontents=box_id;
nvalue=value;
end;
else do;
ncontents=catx(", ",ncontents,contents);
nvalue=sum(nvalue,value);
end;
if last.box_id then output;
run;```

Not tested, if you want tested code then please provide test data in the form of a datastep so we can copy/paste/run, not here to type in test data.

New Contributor
Posts: 2

## Re: combine values

Thanks for your help!

It is very useful

Super User
Posts: 5,917

## Re: combine values

A more data driven approach would be have a separate lookup table (and potentially create a SAS format from it).

You already have an id for the grouping (box_id). You might not always have all individual product names in the box_id label, perhaps "2" would rather say "Electronics and multimedia" and so forth.

Data never sleeps
Posts: 5,625

## Re: combine values

The Do Until() is best suited to this task:

``````data have;
input box_ID	contents :\$12.	value;
datalines;
1	shoes	100
1	clothing	200
1	towels	150
2	computer	350
2	DVDs	125
3	books	225
3	music	75
3	magazines	50
3	postcards	50
;

data want;
length nContents \$2000;
do until (last.box_id);
set have; by box_id;
ncontents = catx(", ", ncontents, contents);
nValue = sum(nValue, value);
end;
drop contents value;
rename nContents=contents nValue=value;
run;``````
PG
☑ This topic is solved.

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

Discussion stats
• 4 replies
• 295 views
• 0 likes
• 4 in conversation