topic Re: combine values in SAS Enterprise Guide

combine values

Nynke — Fri, 11 Nov 2016 13:32:10 GMT

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!

Re: combine values

RW9 — Fri, 11 Nov 2016 13:48:40 GMT

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.

Re: combine values

LinusH — Fri, 11 Nov 2016 15:00:48 GMT

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.

Re: combine values

PGStats — Fri, 11 Nov 2016 21:17:52 GMT

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;

Re: combine values

Nynke — Tue, 15 Nov 2016 12:03:39 GMT

Thanks for your help!

It is very useful