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
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
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
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
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.