topic Re: How can I combine multiple rows to a single row? in SAS Programming

How can I combine multiple rows to a single row?

Roger — Mon, 27 Feb 2012 20:46:14 GMT

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?

Thanks team in advance!

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

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

PGStats — Mon, 27 Feb 2012 21:04:40 GMT

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

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

Linlin — Mon, 27 Feb 2012 23:19:28 GMT

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

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

ballardw — Tue, 28 Feb 2012 00:11:59 GMT

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;

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

Haikuo — Tue, 28 Feb 2012 01:06:42 GMT

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;

run;

Regards,

Haikuo