@Kurt_Bremser wrote: The log is clearly not from the code you posted, as your code uses WORK datasets, while the ERROR message comes from an external database. Please post the complete log including your real code. In the code I just adjusted names of Variables and of tables, to be more clear. Table1 is a WORK Dataset, and Table2 is an external database. My goal is to bring value from Table2 to Table1 dataset. ... View more

@LinusH wrote: What is the hit ratio on Table2? If it's small, try to create an index on Table2.Val1. The fastest is usually if proc SQL can performs a hash join, but unfortunately this doesn't happen with outer joins. You could transform it to an inner join, and then maybe append unmatched rows in a second step if necessary? You can increase the chance of a hash join level up the UBEFIZE (and have and index om the join column in the larger table. Remeber to use the proc SQL options _method and _tree to deeper analyse the SQL behaviour. Hi, I'm sorry, but my knowledge of SAS is not so deep to understand you. I can't create any index on Table2 because I haven't DBA permissions, all I can is query the table as is. ... View more

@ FreelanceReinh, I am incredibly amazed by your brilliant solution. I see how much I still have to learn. Thank you! ... View more

@sbxkoenk wrote: Hello,   The FINANCE function has a limitation which checks for (strictly) positive values for the "rate".  If a negative (or zero) value is passed, it will return the message in the SAS log that Argument 2 is invalid.    The PPMT functions do not have this limitation.   Please use the PPMT function instead of the FINANCE function with the PPMT as the first argument.  Here is the link to the documentation on the PPMT function:   https://go.documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=lefunctionsref&docsetTarget=p1m2s8u7i0jfo6n1c0siw2vinb9t.htm&locale=en Good luck, Koen Hi, I have the same problem with Finance function. I did resolve a problem with negative values for Rate by adding *(1+Rate)**Nper; after the function, but still have a problem for Zero value for rate. I'm not sure that PPMT function can be a solution in my case, cause I need make calculation for negative values in Pmt as well, while PPMT function requires this parameter to be positive only. Can you help in my case? Here an example for numbers I made calculation on them. I except to get r = 94 with given numbers: Data AAA; /* Rate = -0.0138067061143984;*/ Rate = 0; /* Rate = 1;*/ Nper = 1; Pmt = -94; Pv = 0; Type = 1; r = Finance('Pv', Rate, Nper, Pmt, Pv, Type)*(1+Rate)**Nper; Run; ... View more

Thank you very much! Also for the solution and especially for the patience. ... View more

@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 "." .     ... View more

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 ... View more

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; ... View more

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. ... View more

Hi Tom! Please, see my reply to @PaigeMiller  ... View more

@PaigeMiller wrote: Since you obviously have the data as a SAS data set, we need the SAS data set and not typed in Excel-clone data. Please provide the data as a working SAS data step code by following these instructions. We don't need all 100 columns, just the relevant ones. Hi, The description is not so clear to me. Anyway, I prepared the code that build first 100 rows of the data set I'm working on. To notice that the full set has: 6 Tranches (From 1 To 6),  Each tranche contains 15000 Iterations (From 1 to 15000), Each Iteration contains 42 years (From 0 to 41) Where Rows with Year=0 must be blank or zero values.   %let Stdev_base = 0.187493224932249; %let weight_to_equities = 0.5; %let Stdev_ToUse = &Stdev_base*&weight_to_equities; %let Term_Interval = 1; Data Test; Infile datalines dlm="09"x dsd; Input Tranche Iteration Year NormInv NetReturn Tresv NGrossReturn; 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; ... View more

@Quentin wrote: Does your log show the message NOTE: Missing values were generated as a result of performing an operation on missing values? Yes. Here is the whole NOTE: NOTE: Missing values were generated as a result of performing an operation on missing values. Each place is given by: (Number of times) at (Line):(Column). 2445000 at 65:36 2445000 at 65:47 2445000 at 65:62 3645000 at 72:27 105000 at 72:30 NOTE: There were 3780000 observations read from the data set WORK.SIMULATOR1. NOTE: The data set WORK.SIMULATOR_FINAL has 3780000 observations and 6 variables. NOTE: DATA statement used (Total process time): real time 1.96 seconds cpu time 1.90 seconds ... View more