SAS Calculating rolling sum via EXPAND but output incorrect results

Question

I employed the following code to output rolling sum for every three year period, while the results are incorrect by adding inexistence number. Please find the code for rolling sum.

proc expand data=have out=want;
by firmid ;
convert vcinvest_annual=cum_vc/transformout=(movsum 3 trimleft 2); 
run; 

Please find the screenshot for the result.

I find the issue is more frequent when the rolling sum is equal to zero. In order to avoid the hidden number after the decimal point, I used the following code to truncate decimal number.

data have;
set have;
put vcinvest_annual=6.4;
run

Could someone please inform me of the underlying issue and provide guidance on resolving it? Many thanks!

Answer 1

This can happen when you use the default conversion method in proc expand. Add method=none and it will resolve the issue.

proc expand data=have out=want;
by firmid ;
convert vcinvest_annual=cum_vc/ method=none transformout=(movsum 3 trimleft 2); 
run; 

Example:

data have;
    do i = 1 to 10;
        if(1 < i LE 4) then x = 0;
        else x = i;
        output;
    end;

    drop i;
run;

proc expand data=have out=want;
    convert x=x_movsum  / transform=(movsum 3);
    convert x=x_movsum2 / method=none transform=(movsum 3); 
run;

TIME    x_movsum        x_movsum2   x
0       1               1           1
1       1               1           0
2       1               1           0
3       -2.22045E-16    0           0
4       5               5           5
5       11              11          6
6       18              18          7
7       21              21          8
8       24              24          9
9       27              27         10