RETAIN statement to collapse rows

data sample;
input id $ var1 var2 var3;
datalines;
A 20 . .
A . 30 40
B 20 . .
B 20 . .
C . 10 .
C 10 . .
C . . 10
;

data sample;
set sample;
by id;
array v(3) var1-var3;
array n(3) v1-v3;
retain v1-v3;
do i = 1 to 3;
if v(i) ne . then n(i)=v(i);
end;
if last.id then do;
output;
do j = 1 to 3;
n(j)=.;
end;
end;
drop i j var1-var3;
rename v1=var1 v2=var2 v3=var3;

proc print;
run;

 

Thanks GP.

Leave a Reply