I need help to calculate variable's value based on the previous value in the same variable

[IgorR]

Ok, I will try to make a sense order.

I'm trying to build data set from scratch, when the goal is to make stochastic simulation of very large ammount of iteration and to get an average value from all results.

In the base of the calculation is random number that you can see in the first row of may data example.

The formula I provided suppose to calculate Variable with name "GrossFund" using additional, predefined parameters - in the table and macro vars.

I updated the code that bring to you the example data set.

And I added the excepted result for first 15 rows only because they must stay constant for each iteration and if I get the same results by the formula i suppose that formula works well.

[IgorR]

The Updated example code:

%let Stdev_base = 0.187493224932249;
%let weight_to_equities = 0.5;
%let Stdev_ToUse = &Stdev_base*&weight_to_equities;
%let Term_Interval = 1;
%let GrossAnnualPremium = 100;
%let ExpenceLoad = 0.06; 

Data Test;
Infile datalines dlm="09"x dsd;
Input Tranche Iteration Year NormInv  NetReturn Tresv NGrossReturn;

/*!!! Pidyon and Siluk - Check these parameters regularly to see
   if they has been changed!!!*/
datalines;
1	1	0	0			0
1	1	1	-0.401683176	-0.192583212	16	-0.181279377
1	1	2	-2.188102767	0.237673136	16	0.25500056
1	1	3	-1.082351454	0.070470576	16	0.085457165
1	1	4	0.502844303	-0.077089014	16	-0.06416826
1	1	5	-0.764272142	0.075668747	16	0.09072811
1	1	6	0.832236652	0.080999439	16	0.096133431
1	1	7	-0.248428708	0.037800642	16	0.052329851
1	1	8	-0.696929109	0.012473564	16	0.026648194
1	1	9	-0.177708172	0.022553857	16	0.036869611
1	1	10	0.361635108	0.060861948	16	0.075714016
1	1	11	0.968456872	-0.03329219	16	-0.019758281
1	1	12	0.507503347	0.100902401	16	0.116315034
1	1	13	0.094918729	0.044654956	16	0.059280125
1	1	14	-0.268100788	0.100029068	16	0.115429475
1	1	15	1.114988618	-0.095820118	16	-0.0831616
1	1	16	0.621812038	0.020710059	16	0.035
1	1	17	-0.240327682			-0.03444714
1	1	18	-0.518506761			-0.05930167
1	1	19	-0.380628296			-0.047063621
1	1	20	0.216490394			0.007800988
1	1	21	-0.865064972			-0.08937257
1	1	22	0.516558723			0.036553334
1	1	23	0.635065672			0.048133258
1	1	24	0.614818545			0.046145683
1	1	25	1.703085114			0.15850912
1	1	26	0.306297412			0.016321587
1	1	27	1.054664304			0.090184211
1	1	28	-0.927067255			-0.094650243
1	1	29	-0.378329899			-0.046858273
1	1	30	1.37836213			0.123773543
1	1	31	0.698802551			0.054414724
1	1	32	1.877871249			0.177648372
1	1	33	-1.801178849			-0.165880781
1	1	34	-2.301837034			-0.204125642
1	1	35	-0.064140869			-0.01836675
1	1	36	-0.011118909			-0.013475268
1	1	37	0.16684725			0.003121704
1	1	38	-0.907010833			-0.092946383
1	1	39	-0.5522686			-0.062274328
1	1	40	-0.926762702			-0.094624394
1	1	41	-0.49304509			-0.057053593
1	2	0	0			0
1	2	1	0.272052109	-0.192583212	16	-0.181279377
1	2	2	-1.281898069	0.237673136	16	0.25500056
1	2	3	-0.647768883	0.070470576	16	0.085457165
1	2	4	-0.565498028	-0.077089014	16	-0.06416826
1	2	5	0.41197637	0.075668747	16	0.09072811
1	2	6	-0.779413048	0.080999439	16	0.096133431
1	2	7	0.855643268	0.037800642	16	0.052329851
1	2	8	-0.866463325	0.012473564	16	0.026648194
1	2	9	1.07588221	0.022553857	16	0.036869611
1	2	10	0.163612115	0.060861948	16	0.075714016
1	2	11	-0.033478564	-0.03329219	16	-0.019758281
1	2	12	-0.260679455	0.100902401	16	0.116315034
1	2	13	1.02590182	0.044654956	16	0.059280125
1	2	14	-2.021439569	0.100029068	16	0.115429475
1	2	15	-0.266694607	-0.095820118	16	-0.0831616
1	2	16	1.394018033	0.020710059	16	0.035
1	2	17	-0.042766779			-0.016397831
1	2	18	0.224547946			0.008562537
1	2	19	-2.84164275			-0.243398743
1	2	20	-0.529668341			-0.060285465
1	2	21	1.249656009			0.110295831
1	2	22	-0.032835878			-0.015481683
1	2	23	-0.336196189			-0.04308602
1	2	24	0.321693245			0.01778951
1	2	25	0.664098464			0.050989875
1	2	26	-0.969997516			-0.098286561
1	2	27	1.234485887			0.108717949
1	2	28	-0.371113375			-0.046213231
1	2	29	-0.217901648			-0.03241506
1	2	30	-0.487483006			-0.056561787
1	2	31	-0.462498733			-0.054349485
1	2	32	-0.425287629			-0.051044903
1	2	33	1.539378448			0.140865279
1	2	34	-0.458411447			-0.053987072
1	2	35	0.871198126			0.071594097
1	2	36	1.278369561			0.11328855
1	2	37	-0.232312352			-0.033721341
1	2	38	0.435834918			0.028738736
1	2	39	1.057741102			0.090498708
1	2	40	0.987034372			0.083294223
1	2	41	0.249422192			0.010917124
1	3	0	0			0
1	3	1	-0.24711478	-0.192583212	16	-0.181279377
1	3	2	0.193853784	0.237673136	16	0.25500056
1	3	3	0.722182005	0.070470576	16	0.085457165
1	3	4	-0.524994456	-0.077089014	16	-0.06416826
1	3	5	0.597940421	0.075668747	16	0.09072811
1	3	6	1.822602911	0.080999439	16	0.096133431
1	3	7	-0.66550602	0.037800642	16	0.052329851
1	3	8	0.229251942	0.012473564	16	0.026648194
1	3	9	0.515539259	0.022553857	16	0.036869611
1	3	10	-0.464778008	0.060861948	16	0.075714016
1	3	11	-2.071355087	-0.03329219	16	-0.019758281
1	3	12	-0.290791577	0.100902401	16	0.116315034
1	3	13	-0.092066762	0.044654956	16	0.059280125
1	3	14	0.564862272	0.100029068	16	0.115429475
1	3	15	-0.251988426	-0.095820118	16	-0.0831616
;
run;

[Tom]

You still seem to be missing a step in explaining what you are trying to do.

But this is looking more and more like the goal is NOT to use LAG().

The goal appears to be to generate (simulated) data.  So no need for LAG().  You don't need to LOOK UP some older values.  You just need to REMEMBER them.

You are getting closer to describing your inputs with this:

%let Stdev_base = 0.187493224932249;
%let weight_to_equities = 0.5;
%let Stdev_ToUse = &Stdev_base*&weight_to_equities;
%let Term_Interval = 1;
%let GrossAnnualPremium = 100;
%let ExpenceLoad = 0.06; 

So it looks like the first three macro variables will somehow be used in the generation of the simulated data. Are you assuming a NORMAL distribution when generating the data?  So you had SD , but what is the MEAN you want to use?

The 4th macro variable seems related to how many observations to generate?

The last two look like some of the inputs to the formula you started with for calculating the next value of GROSSFUND.

new_GrossFund = GrossFund*(1+NGrossReturn)
              + &GrossAnnualPremium*(1-&ExpenceLoad)*(1+NGrossReturn)
;

But you have not defined NGROSSRETURN.

Is that another constant, like EXPENCELOAD?

Is it the value that is going to be simulated using the value of Stdev_ToUse ?

[IgorR]

---

@Tom wrote:

You still seem to be missing a step in explaining what you are trying to do.

But this is looking more and more like the goal is NOT to use LAG().

The goal appears to be to generate (simulated) data.  So no need for LAG().  You don't need to LOOK UP some older values.  You just need to REMEMBER them.

You are getting closer to describing your inputs with this:

%let Stdev_base = 0.187493224932249;
%let weight_to_equities = 0.5;
%let Stdev_ToUse = &Stdev_base*&weight_to_equities;
%let Term_Interval = 1;
%let GrossAnnualPremium = 100;
%let ExpenceLoad = 0.06; 

So it looks like the first three macro variables will somehow be used in the generation of the simulated data. Are you assuming a NORMAL distribution when generating the data?  So you had SD , but what is the MEAN you want to use?

The 4th macro variable seems related to how many observations to generate?

The last two look like some of the inputs to the formula you started with for calculating the next value of GROSSFUND.

new_GrossFund = GrossFund*(1+NGrossReturn)
              + &GrossAnnualPremium*(1-&ExpenceLoad)*(1+NGrossReturn)
;

But you have not defined NGROSSRETURN.

Is that another constant, like EXPENCELOAD?

Is it the value that is going to be simulated using the value of Stdev_ToUse ?

---

I need to calculate new variable - "GrossFund".

The formula to calculate "GrossFund" :

If Year = 0 Then GrossFund = 0;
Else GrossFund = Lag(GrossFund)*(1+NGrossReturn) + &GrossAnnualPremium*(1-&ExpenceLoad)*(1+NGrossReturn);

In Formula I use value from already existed column (Variable) - "NGrossReturn". You can see it in the example data (the code to get the example i posted before).

Also I use two constansts - &GrossAnnualPremium and &ExpenceLoad.

GrossFund within the formula must be previous value from row above - like If I need value for row 4 I take the value form row 3 to the formula.

In Addition, I have in the data variable "Years". It's values are between 0 To 41, Where If Year" = 0 Then GrossFund = 0;

When I run the code, I get . in GrossFund instead of numbers.

So, I undeerstand that I use Lag(GrossFund) the wrong way, but I can't figure it out what to do to get values in the formula, not "." .

[Kurt_Bremser]

---

@IgorR wrote:

---

If Year = 0 Then GrossFund = 0;
Else GrossFund = Lag(GrossFund)*(1+NGrossReturn) + &GrossAnnualPremium*(1-&ExpenceLoad)*(1+NGrossReturn);

---

This can not work. Since you do not call LAG when year is 0, the queue will still be empty when you call it when year is not 0, so you start the iterations with a missing value and proceed from there.

Instead of using LAG, use RETAIN:

retain GrossFund;
If Year = 0 Then GrossFund = 0;
Else GrossFund = GrossFund*(1+NGrossReturn) + &GrossAnnualPremium*(1-&ExpenceLoad)*(1+NGrossReturn);

[Tom · Accepted solution]

So your inputs that vary per observation are YEAR and NGROSSFUND .

data have;
  input Year NGrossReturn;
datalines;
0 0
1 -0.181279377
2 0.25500056
3 0.085457165
4 -0.06416826
;

And your inputs that do not vary are:

  retain GrossAnnualPremium 100 ExpenceLoad 0.06  ;

So your data step to calculate GROSSFUND could be something like this:

data want;
  set have;
  retain GrossAnnualPremium 100 ExpenceLoad 0.06 GrossFund 0;
  if year=0 then grossfund=0;
  else GrossFund = GrossFund*(1+NGrossReturn)
            + GrossAnnualPremium*(1-ExpenceLoad)*(1+NGrossReturn)
  ;
run;

Result:

                            Gross
                NGross      Annual    Expence      Gross
Obs    Year     Return     Premium      Load        Fund

 1       0      0.00000      100        0.06       0.000
 2       1     -0.18128      100        0.06      76.960
 3       2      0.25500      100        0.06     214.555
 4       3      0.08546      100        0.06     334.923
 5       4     -0.06417      100        0.06     401.400

NOTE: For the RETAIN of GROSSFUND to work it cannot be in the input dataset.  Because then when the SET statement reads the next observation the value read from the input dataset will overwrite the retained value.

[IgorR]

Thank you very much!

Also for the solution and especially for the patience.

[IgorR]

Updated wanted result:

Random number	Year	Normal_Gross_return	Gross Fund
-0.049364577	1	-18.13%	                76.96
0.560315127	2	25.50%	                214.55
0.502450342	3	8.55%	                334.92
-0.061151989	4	-6.42%	                401.40
1.640308957	5	9.07%	                540.35
0.345543548	6	9.61%	                695.33
-0.953935106	7	5.23%	                830.63
0.53277643	8	2.66%	                949.27
-0.956756512	9	3.69%	                1081.74
0.598816648	10	7.57%	                1264.76
-0.321960944	11	-1.98%	                1331.91
1.057035832	12	11.63%	                1591.77
0.087963633	13	5.93%	                1785.70
-1.423907034	14	11.54%	                2096.67
0.450617369	15	-8.32%	                2008.49
-0.085762382	16	3.50%	                2176.08