PROC EXPAND

[Junyong]

The following code computes the cumulative returns of the variable return below.

data return;
t=_n_;
input return @@;
cards;
0.01 0.02 0.03 0.04 0.05 0.06 . 0.08 0.09 0.1
;
run;
proc expand out=return;
id t;
convert return=cumulative/transformout=(+1 nomiss movprod 3 -1 trimleft 2);
run;

To skip any missing observation, I put nomiss above to have the 7-9th cumulative observations missing.

 t    cumulative    return

 1       .           0.01
 2       .           0.02
 3      0.06111      0.03
 4      0.09262      0.04
 5      0.12476      0.05
 6      0.15752      0.06
 7      0.19091       .
 8      0.22494      0.08
 9      0.25960      0.09
10      0.29492      0.10

(1) What is the problem with the nomiss above? It seems the nomiss should skip converting due to the missing. (2) Why the 7th, 8th, and 9th cumulative observations are 0.19091, 0.22494, and 0.25960, respectively? I can compute the 6th (1.04*1.05*1.06-1) and 10th (1.08*1.09*1.1-1) observations, but the 7th to 9th observations are unclear. Thanks.

[Junyong]

It seems EXPAND by default (METHOD=SPLINE) alters the missing as 0.07 and then compute cumulative. METHOD=NONE prevents this.

[PeterClemmensen · Accepted solution]

A small change will give you what you want. Add method=none to the Proc Expand Statement

proc expand out=return method=none;
id t;
convert return=cumulative/transformout=(+1 nomiss movprod 3 -1 trimleft 2);
run;

Edit: I see you figured it out on your own already 🙂