How can I combine multiple rows to a single row?

Solved
Occasional Contributor
Posts: 16

How can I combine multiple rows to a single row?

Hello,

I have a table that has multiple rows, and some of the values are missing. I just need to combine them into a single row by year.

I have done it with two data sets. In this case, I only have one data set. What is best way to combine them to a single row?

Input table:

ID Year Value1 Value2 Value3

1  1999              270

1  1999                         350

1  1999

1  2000    20

1  2000             300

1  2000                         320

1  2001             122

1  2001

1  2001                         500

I need the output table as below:

ID Year Value1 Value2 Value3

1  1999            270      350

1  2000   20      300     320

1  2001            122      500

Accepted Solutions
Solution
‎02-27-2012 04:04 PM
Posts: 5,529

Re: How can I combine multiple rows to a single row?

There is this wicked :smileycool: technique learned from Tom recently:

data have;
input ID Year Value1 Value2 Value3;
datalines;
1  1999      .      270 .
1  1999      . .                 350
1  1999   . . .
1  2000    20 . .
1  2000    .         300 .
1  2000    .          .           320
1  2001     .        122 .
1  2001     .         . .
1  2001     .         .           500
;

data want;
update have(obs=0) have;
by id year;
run;

proc print; run;

Works just fine. - PG

PG

All Replies
Solution
‎02-27-2012 04:04 PM
Posts: 5,529

Re: How can I combine multiple rows to a single row?

There is this wicked :smileycool: technique learned from Tom recently:

data have;
input ID Year Value1 Value2 Value3;
datalines;
1  1999      .      270 .
1  1999      . .                 350
1  1999   . . .
1  2000    20 . .
1  2000    .         300 .
1  2000    .          .           320
1  2001     .        122 .
1  2001     .         . .
1  2001     .         .           500
;

data want;
update have(obs=0) have;
by id year;
run;

proc print; run;

Works just fine. - PG

PG
Super Contributor
Posts: 1,636

Re: How can I combine multiple rows to a single row?

another approach:

data have;

input ID Year Value1 Value2 Value3;

datalines;

1  1999      .      270 .

1  1999      . .                 350

1  1999   . . .

1  2000    20 . .

1  2000    .         300 .

1  2000    .          .           320

1  2001     .        122 .

1  2001     .         . .

1  2001     .         .           500

;

proc sql;

create table want as

select distinct id,year,sum(value1) as value1,sum(value2) as value2,sum(value3) as value3

from have

group by id,year;

quit;

proc print; run;

Obs    ID    Year    value1    value2    value3

1      1    1999       .             270       350

2      1    2000      20          300       320

3      1    2001       .            122       500

Linlin

Super User
Posts: 13,542

Re: How can I combine multiple rows to a single row?

And another if the data always matches the shown examples;

proc summary data=have nway;

class id year;

var value1 value2 value3;

output out=have (drop=_type_ _freq) max=;

run;

Posts: 3,167

Re: How can I combine multiple rows to a single row?

OK, now here goes the non-wicked data step approach:

data want (drop=i _;

array tmp(3) _t1-_t3 ;

do _n_=1 by 1 until (last.year);

set have;

by id year;

array val(*) value1-value3;

do i=1 to dim(val);

val(i)=ifn(missing(val(i)),tmp(i),val(i));

tmp(i)=val(i);

end;

end;

run;

Regards,

Haikuo

🔒 This topic is solved and locked.