Does the following do what you want?:

data want; set have; new=ifn(missing(X2),'.', x2+lag(X2)+lag2(X2)+lag3(X2)+lag4(X2)); run;

Art ---------- On Mon, 31 Aug 2009 21:22:03 -0500, OR Stats <stats112@GMAIL.COM> wrote:

>Where in the Data Need step can we add the condition that if *any* of the >last 5 (or winsize) observations is below a threshold, then that sum=. For >example, > >Date X1 X2 New >1 # 1 . >2 # 1 . >3 # 1 . >4 # 1 . >5 # 1 5 >6 # 1 5 >7 # 2 6 >8 # . . > 9 # 3 . >10 # 3 . >11 # 3 . > 12 # 3 . >13 # 4 16 > >So notice that since X2 is missing from row8, it sets to missing 4 >consecutive sums there after b.c. it is part of their array size of 5. >Hence, despite that X2 meets the criteria for every row after row 8, the sum >New is missing until row8 is not longer part of the lookback array of size >5. >On Fri, Jul 24, 2009 at 8:46 PM, Muthia Kachirayan < >muthia.kachirayan@gmail.com> wrote: > >> On Fri, Jul 24, 2009 at 5:51 PM, OR Stats <stats112@gmail.com> wrote: >> >> > Hello: >> > >> > I have a dataset >> > >> > Date X1 X2 >> > >> > >> > I would like to now create a new column in the same dataset that is the >> > moving sum of column x2. For example >> > >> > Date X1 X2 New >> > 1 # 1 . >> > 2 # 1 . >> > 3 # 1 . >> > 4 # 1 . >> > 5 # 1 5 >> > 6 # 1 5 >> > 7 # 2 6 >> > . >> > . >> > . >> > How do I create this new dataset, in which Date, X1, and X2 columns are >> > intact and preserved with the additional new variable from the original >> > table? >> > >> > Thank you! >> > >> >> >> Create a dataset. >> >> data have; >> do Date = 1 to 20; >> X1 = ceil(ranuni(123) * 100); >> X2 = ceil(ranuni(123) * 10); >> output; >> end; >> run; >> >> The moving sum is found for a window size of 5. A sliding array is used to >> hold 5 values at a time. Then sumx is modified by removing one value from >> the array and adding the last value read. This saves time in finding moving >> sum. >> >> %let wsize = 5; >> >> data need(keep = Date X1 X2 New); >> array k[&wsize] (&wsize * 0); >> retain k:; >> hold = k[mod(_n_ - 1, &wsize) + 1]; >> set have; >> k[mod(_n_ - 1, &wsize) + 1] = X2; >> sumx + X2 - hold; >> if _n_ >= &wsize then New = sumx; >> run; >> >> The output on LST. >> >> Obs Date X1 X2 New >> >> 1 1 76 4 . >> 2 2 18 10 . >> 3 3 36 3 . >> 4 4 79 4 . >> 5 5 13 2 23 >> 6 6 78 5 24 >> 7 7 97 3 17 >> 8 8 72 6 20 >> 9 9 54 9 25 >> 10 10 15 9 32 >> 11 11 66 8 35 >> 12 12 71 4 36 >> 13 13 53 10 40 >> 14 14 5 7 38 >> 15 15 66 4 33 >> 16 16 59 4 29 >> 17 17 54 3 28 >> 18 18 53 6 24 >> 19 19 10 2 19 >> 20 20 59 7 22 >> >> Muthia Kachirayan >>