topic PROC EXPAND in SAS Programming

PROC EXPAND

Junyong — Wed, 11 Dec 2019 03:08:52 GMT

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.

Re: PROC EXPAND

Junyong — Wed, 11 Dec 2019 03:28:49 GMT

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

Re: PROC EXPAND

PeterClemmensen — Wed, 11 Dec 2019 07:24:19 GMT

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 🙂