Desktop productivity for business analysts and programmers

combine values

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

combine values

Hi there,

 

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

 

Example;

 

box_IDcontentsvalue
1shoes100
1clothing200
1towels150
2computer350
2DVDs125
3books

225

3music

75

3magazines50
3postcards50

 

into

 

box_IDcontentsvalue
1shoes, clothing, towels450
2computer, DVDs475
3books, music, magazines, postcards400

 

Can someone help me?

Thanks!

 


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

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.

View solution in original post


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

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 Smiley Happy

Super User
Posts: 5,382

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
Respected Advisor
Posts: 4,811

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
  • 272 views
  • 0 likes
  • 4 in conversation