Do loop counter

[6071]

I have 94 years of data; I want to cumulate the data in groups of 35 years, and then calculate my metrics for years 2-36, then 3-37, etc.  I don’t know how to write macros, so I was looking at a do loop with n=1 to 35, but I don’t know how to  make it reset the variable back to the original values and work through the calculations again.  For instance, I have a withdrawal amount of $73,600.  I want it adjusted for inflation during each of the 35 years, but at the conclusion of the 35 years, I want the calculations done, and then return the withdrawal to $73,600 to start the calculations for years 2-36.

Here’s what I was trying:

data merg2; 

    by year;

do n = 1 to 35;  

Retain avg1 inflate portwith portwithtot savings dcatotbal DCAnewsav dcatotval1 saving totbal ;

  N+1;

  If N eq 1 then savings=1840000 ;

portwith=73600;

if avg1>0

then inflate = portwith*(1+(avg1/100));

else inflate = portwith;

portwithtot + inflate - portwith;

*monthly withdrawals;

bjan= savings*(lgwt*jan+govwt*gjan)-((portwith+portwithtot)/time);

bfeb= savings*(lgwt*feb+govwt*gfeb)-((portwith+portwithtot)/time);

bmar= savings*(lgwt*mar+govwt*gmar)-((portwith+portwithtot)/time);

bapr= savings*(lgwt*apr+govwt*gapr)-((portwith+portwithtot)/time);

bmay= savings*(lgwt*may+govwt*gmay)-((portwith+portwithtot)/time);

bjun= savings*(lgwt*jun+govwt*gjun)-((portwith+portwithtot)/time);

bjul= savings*(lgwt*jul+govwt*gjul)-((portwith+portwithtot)/time);

baug= savings*(lgwt*aug+govwt*gaug)-((portwith+portwithtot)/time);

bsep= savings*(lgwt*sep+govwt*gsep)-((portwith+portwithtot)/time);

boct= savings*(lgwt*oct+govwt*goct)-((portwith+portwithtot)/time);

bnov= savings*(lgwt*nov+govwt*gnov)-((portwith+portwithtot)/time);

bdec= savings*(lgwt*dec+govwt*gdec)-((portwith+portwithtot)/time);

dcaBalance = bjan + bfeb + bmar + bapr + bmay + bjun + bjul + baug + bsep + boct + bnov + bdec;

dcatotbal + dcaBalance;

*savings +dcatotbal;

*DCAnewsav = dcatotbal + savings;

savings + dcabalance;

end;

run;

What is wrong with my setup?  Thank you.

[Tom · Accepted solution]

I have no idea what you are trying to do, but to answer your question you can either store the original value or re-read that observation from the dataset.

Store it:

initial=withdrawal;
do year=1 to 35;
   ... some calculations that change withdrawal ...;
end;
withdrawal = initial;

Re-read it;

data want;
  set have;
   current=_n_;
   ... do some calculations that might change withdrawal ...
   set have(keep=withdrawal) point=current;
  ....
run;

[Kurt_Bremser]

From your description, it looks like you want to calculate a rolling window of 35 years.

I would do this by filling an array of 35 elements, the index calculated with mod(_n_,35). At any given timepoint, the array holds the last 35 years.

[PaigeMiller]

PROC EXPAND does running sums (I suppose that's what you mean by "cumulate") or averages. Example: https://documentation.sas.com/doc/en/pgmmvacdc/9.4/etsug/etsug_expand_examples04.htm You want to use:

convert x = x_movsum / transformout=(movsum 35);