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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.