How can I combine multiple rows to a single row?

[Roger]

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

[PGStats · Accepted solution]

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

[Linlin]

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

[ballardw]

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;

[Haikuo]

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