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!
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.
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.
Thanks for your help!
It is very useful
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.