I have excel table and my goal is to convert this table to SAS. The table has almost 100 columns with different statistic formula in each column, when some formulas depend on other columns. This is the small sample of the table:               A                        B                       C                                D                    E 5  |Random number| Year | Normal Gross return | Gross Fund | Gross IRR |     | ------------------|------|-------------------------|--------------|------------| 6  |                               |        0|                                         |                        |                    | 7  |     1.294000103 |        1 |                         -18.13%  |              76.96|     -18.13% | 8  |   -0.330857931 |        2 |                        25.50%  |            214.55|         9.14% | 9  |  -0.003978838 |        3 |                          8.55%  |            334.92|        8.85% | 10|  -0.130874637  |        4 |                        -6.42%  |            401.40|        2.63% | 11 | -0.508842621   |       5  |                          9.07%  |            540.35|       4.69% |   The excel formula to calculate column D (Gross Fund) is: Cell(D7)=D6*(1+C7)+Premium*(1-expenseload)*(1+C7) Niotice, that in this column I have to take value from previous row. "Premium" and "ExpenceLoad" are named ranges in excel. I saw previous topics about Lag function and tried different approaches to solve the problem, but nothing gone well and I don't get the correct result. The current SAS code in my program (Within Data step) is: GrossFund = Lag(GrossFund)*(1+NGrossReturn)+&GrossAnnualPremium*(1-&ExpenceLoad)*(1+NGrossReturn); It returns Nulls only.   I need help to find the simplest solution to make the calculation with less steps as possible, because, as I noticed, i need to calculate almost a hundred additional variables. and i don't want the code to be to complicated and unreadable.   Thanks in advance!     ... View more

Hi, I'm trying to make the following code to be shorter: If Period = 40 Then Do; If Years > 20 and Years < 30 Then Effect_on_Guarantee = 1; Else Effect_on_Guarantee = Lapse_Multipliers; End; Else Effect_on_Guarantee = Lapse_Multipliers; To achieve this I'm trying to use IFN function like this: IFN(a.Period = 40, IFN((a.Period > 20 and a.Period < 30),1, Lapse_Multipliers),Lapse_Multipliers) As Effect_on_Guarantee In This way IFN doesn't return any error or warning but doesn't the work as along version of code does. Where is a mistake? ... View more

Hi, When I run Proc Import , SAS demands to close the excel file I'm importing from. Is there a possibility to import when keeping the excel open? Thanks   ... View more

Hi, My question has two parts: 1. I import data from excel and it comes to SAS in this format: 2008 | 1 | (18.13%) | (19.3%) 2009 | 1 | 25.50% | 23.8% 2010 | 1 | 8.55% | 7.0% 2011 | 1 | ( 6.42%) | ( 7.7%) I need to convert the percents to real numbers format so it will look like this (precision does important!!!): 2008 | 1 | -0.1812793769191860| -0.1925832119518600 2009 | 1 | 0.2550005600450760| 0.2376731361391290 2010 | 1 | 0.0854571645604569| 0.0704705764896025 2011 | 1 | -0.0641682597261736| -0.0770890135366603 The Question - How to do this?   2. I will glad to get all real numbers formats i can use in format statement. I can't find them in net.   Thanks in advance. ... View more

Hi! I'm trying to create a table with data. where the first column must be numerical(integer) and second column must be float number. The following code does it except first 9 rows where int number is less then 10. The code: DATA Lapse_Multipliers; input Years 1-2 Lapse_Multipliers 3-6; DATALINES; 1 1 2 1 3 1 4 1 5 1 6 0.9 7 0.8 8 0.7 9 0.5 10 1 11 1 12 1 13 1 14 1 15 1 16 0.9 17 0.8 18 0.7 19 0.5 20 1 ; run; It returns: . 1 . 1 . 1 . 1 . 1 . 0.9 . 0.8 . 0.7 . 0.5 10 1 11 1 12 1 13 1 14 1 15 1 16 0.9 17 0.8 18 0.7 19 0.5 20 1 What am I missing? ... View more