Get all values separated by comma in one row by id

[indox]

hello,

I have this

ID    Marks

1    25

1    30

2    75

2    98

I want this:

ID    Marks

1    25, 30

2    75, 98

thanks for help

[PaigeMiller]

Are you doing this for reporting purposes? Or for some other purpose? There are ways to do this, but it really depends on why you want the output to appear this way. Also, does it have to be a comma separating the marks, or can the marks be in separate columns? Are there always 2 marks per ID, or does the solution have to allow for three (or more) marks per ID?

[RW9]

Two ways:

proc transpose data=have out=want prefix=col;
  by id;
  var marks;
run;

data want;
  set want;
  length result $200;
  result=catx(',',of col:);
run;

Or:

data want;
  length result $200;
  set have;
  retain result;
  by id;
  result=ifc(first.id,marks,catx(',',result,marks));
  if last.id then output;
run;

Note, unless this is for a repeat, its never a good idea to keep multiple data items in one variable!!

[PaigeMiller]

---

@RW9 wrote:

Two ways:

proc transpose data=have out=want prefix=col;
  by id;
  var marks;
run;

data want;
  set want;
  length result $200;
  result=catx(',',of col:);
run;

---

I like this.

But it really begs the question of @indox , what are you going to do with the data in this form with commas separating different data values? Unless you have some very strict reporting requirements, I don't see this data transformation as useful in any way. Certainly, if you are going to do further analysis of this data in SAS, having commas separating the values is an unnecessary complication.

[novinosrin]

data have;
input ID    Marks;
cards;
1    25
1    30
2    75
2    98
;
data want;
set have;
by id;
retain want ;
length want $50;
if first.id then call missing(want);
want=catx(',',want,marks);
if last.id;
run;