BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Nynke
Calcite | Level 5

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Nynke
Calcite | Level 5

Thanks for your help!

It is very useful Smiley Happy

LinusH
Tourmaline | Level 20

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
PGStats
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

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