topic Re: Get all values separated by comma in one row by id in SAS Data Management

Get all values separated by comma in one row by id

indox — Wed, 27 Feb 2019 14:56:24 GMT

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

Re: Get all values separated by comma in one row by id

PaigeMiller — Wed, 27 Feb 2019 14:59:11 GMT

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?

Re: Get all values separated by comma in one row by id

RW9 — Wed, 27 Feb 2019 15:01:20 GMT

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!!

Re: Get all values separated by comma in one row by id

novinosrin — Wed, 27 Feb 2019 15:01:20 GMT

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;

Re: Get all values separated by comma in one row by id

PaigeMiller — Wed, 27 Feb 2019 15:09:58 GMT

@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.