Editor's note: this topic is very popular. Thanks to @peterz, @Hima, and @AskoLötjönen and others who contributed useful replies. We have consolidated some of these into this single reply so that future readers may benefit.
/* input data */
data input_table;
infile cards missover dsd;
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
;
run;
/* Method #1 using PROC MEANS */
proc means data=input_table noprint nway;
class id year;
var value1 value2 value3;
output
out=output_table(drop=_type_ _freq_) max=;
quit;
/* Method #2 using the UPDATE statement */
data want;
update input_table (obs=0) input_table;
by id year;
run;
/* Method 3 using PROC SQL */
proc sql;
create table test2 as
select id,
year,
max(value1) as value1,
max(value2) as value2,
max(value3) as value3
from input_table
group by id, year;
quit;
/* Resulting data set */
Obs ID Year value1 value2 value3
1 1 1999 . 270 350
2 1 2000 20 300 320
3 1 2001 . 122 500
... View more