The retain *does* retain the values. The set, however, loads a new observation every implicit data step iteration. As it does, it overwrites the retained values.
You need a separate array that is retained. As it turns out, temporary arrays are automatically retained. Using one, you can do this easily like so:
[pre]
data one;
input id $ qn a1 b1 c1 a2 b2 c2 a3 b3 c3;
datalines;
001 1 1 2 3 . . . . . .
001 2 . . . 2 7 2 . . .
001 3 . . . . . . 2 4 6
002 1 1 1 1 . . . . . .
002 2 . . . 2 3 4 . . .
002 3 . . . . . . 2 3 2
;
run;
data stack;
set one;
by id qn;
array old[1:3,1:3] a1--c3;
array new[1:3,1:3] _temporary_; /* automatically retained */
if first.id then do;
do i = 1 to 3; do j = 1 to 3; new[i,j] = .; end; end;
end;
do j = 1 to 3;
new[qn,j] = old[qn,j];
end;
if last.id then do;
do i = 1 to 3; do j = 1 to 3; old[i,j] = new[i,j]; end; end;
output;
keep id a1--c3;
end;
run;
/* check */
title 'Stacked data set';
proc print data = stack noobs;
var id a1--c3;
run;
title;
/* on lst
Stacked data set
id a1 b1 c1 a2 b2 c2 a3 b3 c3
001 1 2 3 2 7 2 2 4 6
002 1 1 1 2 3 4 2 3 2
*/
[/pre]
If you get more experienced, you will have to eventually learn how to use the ever-popular DoW loop (see
http://www.devenezia.com/papers/other-authors/sesug-2002/TheMagnificentDO.pdf) and do something like below. Notice how nicely the code matches the programming logic: initialization comes first, then processing of the observations within a given by-group, followed by the processing to be done when the by-group has ended.
DoW also frees us from retaining and explicitly outputting. Can you see why?
[pre]
data stack2;
if 0 then set one; /* to prep pdv */
array old[1:3,1:3] a1--c3;
array new[1:3,1:3] t1-t9;
/* initialize new */
call missing(of t:);
/* DoW */
do until (last.id);
set one;
by id qn;
do j = 1 to 3;
new[qn,j] = old[qn,j];
end;
end;
/* output */
do i = 1 to 3; do j = 1 to 3; old[i,j] = new[i,j]; end; end;
keep id a1--c3;
run;
/* check */
title stack2;
proc print data=stack2 noobs;
run;
title;
/* on lst
stack2
id a1 b1 c1 a2 b2 c2 a3 b3 c3
001 1 2 3 2 7 2 2 4 6
002 1 1 1 2 3 4 2 3 2
*/
[/pre]
Or, you can simply do update as below. But in this case you are assuming that your data are clean in that every id has one and only one observation with qn=1, and given qn=x, there are no non-missing values other than in ax, bx, cx.
[pre]
data stack3;
update one(where=(qn=1)) one(drop=qn);
by id;
if last.id then output;
drop qn;
run;
/* check */
title stack3;
proc print data=stack3 noobs;
run;
title;
/* on lst
stack3
id a1 b1 c1 a2 b2 c2 a3 b3 c3
001 1 2 3 2 7 2 2 4 6
002 1 1 1 2 3 4 2 3 2
*/
[/pre]
Under the same assumptions, you can calculate maximum of each variable, by id, using some procs like so:
[pre]
proc means data=one;
var a1--c3;
by id;
output out=stack4(drop=_:) max=;
run;
/* check */
title stack4;
proc print data=stack4 noobs;
run;
title;
/*
stack4
id a1 b1 c1 a2 b2 c2 a3 b3 c3
001 1 2 3 2 7 2 2 4 6
002 1 1 1 2 3 4 2 3 2
*/
[/pre]