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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.