This is a problem that benefits from a solution that uses record order, which is not a strength of PROC SQL:
data have;
year=2017; month=12;
do sex='M','F';
do age=20 to 29;
input perc_0 @;
output;
end;
end;
format perc_0 percent7.1;
datalines;
.11 .13 .11 .07 .03 0 0 0 0 0
.12 .15 .13 .1 .05 0 0 0 0 0
run;
data want (drop=_:);
set have;
by descending sex age;
array prc {0:10} perc_0-perc_10;
retain perc_1-perc_10;
format perc_0-perc_10 percent7.1;
do _m=1 to 10;
if first.sex then prc{_m}=0;
prc{_m}=prc{_m-1}*11/12 + prc{_m}/12;
end;
run;
By retaining the values of PERC_1-PERC_10 from row to row, it's easy to divide by twelve and add 11/12th of the preceding month (i.e. for PERC_2 add 11/12th of PERC_1). Of course when starting a new sex, first reset PERC_1-PERC_10 to zero.
By the way, why do your have PERC_0 through PERC_10, and not through PERC_11?
... View more
