I have attached the entire code, Brief summary: Theres a funding pool that gets optimized by using certain constraints. the code below runs fine but I have replace the data in "...eligibility file" in excel to get newer results. But whenever i make updates in excel and run the code, the result only shows me old bunch of pool. KL2 '\\Dfs.com\root\Dept-Accounting\Capital Markets\Personal Folders\VJetti\Pool Cuts Training'; import ='\\dfs.com\root\Dept-Accounting\Capital Markets\Personal Folders\VJetti\Pool Cuts Training\SDART Constraints.xlsx' /*Specify File Location*/ = constraints /*Output sas data table*/ = excel REPLACE; /*Replace option replaces previous output*/ ='con'; /*Named range in workbook*/ /*RANGE="Invoice$B4:D10";*/ ="Sas"; /*Worksheet name*/ =YES; /*Pull in first row as Variable Names or not*/ ; /*LB <350 reading in as char..*/ import ='\\dfs.com\root\Dept-Accounting\Capital Markets\Personal Folders\VJetti\Pool Cuts Training\SDART Total Eligible_103114.xlsx' /*Specify File Location*/ = KL2_Pool_Exercise /*Output sas data table*/ = excel REPLACE; /*Replace option replaces previous output*/ ="Sheet1$A1:G219839"; /*can use Named range in workbook*/ =YES; /*Pull in first row as Variable Names or not*/ ; contents data=kl2_pool_exercise varnum; run; kl2_Pool_Exercise ; set kl2_Pool_Exercise; 100,6); if origterm=75 then Term75=1; else Term75=0; if pmtsmade=0 then Pmt0=1; else Pmt0=0; if LFS<350 then LFS350=1; else LFS350=0; if LFS>=351 and LFS<=400 then LFS400=1; else LFS400=0; if LFS>=401 and LFS<=450 then LFS450=1; else LFS450=0; if LFS>=451 and LFS<=500 then LFS500=1; else LFS500=0; if LFS>=501 and LFS<=550 then LFS550=1; else LFS550=0; if LFS>=551 and LFS<=600 then LFS600=1; else LFS600=0; if LFS>=601 and LFS<=650 then LFS650=1; else LFS650=0; if LFS>=651 and LFS<=700 then LFS700=1; else LFS700=0; if LFS>=701 and LFS<=750 then LFS750=1; else LFS750=0; if LFS>750 then LFS750plus=1; else LFS750plus=0; /*LTV_sq=LTV**-0.5;*/ if LTV=. then delete/*LTV=0*/; ; Pool_Exercise5 /*(obs=10000)*/ (drop=pmtsmade origterm); set kl2_Pool_Exercise; if LTV=. or LTV=0 then delete; ; optmodel; /* Declare the index set and parameters */ set pool; num custid {pool}; num prinbal {pool}; num currrate {pool}; /*num origterm {pool}; num pmtsmade {pool}; */num LTV {pool}; num LFS {pool}; num Term75 {pool}; num Pmt0 {pool}; /*num LTV_sq {pool}; */ num LFS350 {pool}; num LFS400 {pool};num LFS450 {pool};num LFS500 {pool};num LFS550 {pool};num LFS600 {pool};num LFS650 {pool};num LFS700 {pool};num LFS750 {pool};num LFS750plus {pool}; /* Read the index set and parameters from the input data set */ read data Pool_Exercise5 into pool=[custid] prinbal currrate /*origterm pmtsmade*/ LTV LFS Term75 Pmt0 /*LTV_sq*/ /* declare the variable*/ var Include{pool} BINARY; /* maximize objective function (bal) */ max Included_Bal = sum{k in pool} prinbal * Include ; /* subject to constraints */ con bal: 1588235200 <= Included_Bal <= 1588235300; /*Much faster with tolerance band for Included Bal. This is the amount necessary for $1B in SDART bond proceeds*/ con WA_LTV:sum{k in pool} (Include * prinbal * LTV ) >= 113 * Included_Bal; con WA_LFS:sum{k in pool} (Include * prinbal * LFS ) >= 555 * Included_Bal; con WA_APR:sum{k in pool} (Include * prinbal * currrate ) >= 16.20 * Included_Bal; con Min75: sum{k in pool} (Include * prinbal * Term75 ) >= 0.15 * Included_Bal; con Max75: sum{k in pool} (Include * prinbal * Term75 ) <= 0.20 * Included_Bal; con pymt0: sum{k in pool} (Include * prinbal * Pmt0 ) <= 0.42 * Included_Bal; con minLFS_350: sum{k in pool} (Include * prinbal * LFS350 ) >= 0 * Included_Bal; con maxLFS_350: sum{k in pool} (Include * prinbal * LFS350 ) <= 0.0058 * Included_Bal; con minLFS_400: 0.0165 * Included_Bal <= sum{k in pool} (Include * prinbal * LFS400 ); con maxLFS_400: sum{k in pool} (Include * prinbal * LFS400 ) <= 0.0265 * Included_Bal; con minLFS_450: 0.0274 * Included_Bal <= sum{k in pool} (Include * prinbal * LFS450 ); con maxLFS_450: sum{k in pool} (Include * prinbal * LFS450 ) <= .0374 * Included_Bal; con minLFS_500: 0.0909 * Included_Bal <= sum{k in pool} (Include * prinbal * LFS500 ); con maxLFS_500: sum{k in pool} (Include * prinbal * LFS500 ) <= .1009 * Included_Bal; con minLFS_550: 0.3629 * Included_Bal <= sum{k in pool} (Include * prinbal * LFS550 ); con maxLFS_550: sum{k in pool} (Include * prinbal * LFS550 ) <= 0.3729 * Included_Bal; con minLFS_600: 0.2594 * Included_Bal <= sum{k in pool} (Include * prinbal * LFS600 ) ; con maxLFS_600: sum{k in pool} (Include * prinbal * LFS600 ) <= 0.2694 * Included_Bal; con minLFS_650: 0.1518 * Included_Bal <= sum{k in pool} (Include * prinbal * LFS650 ) ; con maxLFS_650: sum{k in pool} (Include * prinbal * LFS650 ) <= .1618 * Included_Bal; con minLFS_700: .0423 * Included_Bal <= sum{k in pool} (Include * prinbal * LFS700 ) ; con maxLFS_700: sum{k in pool} (Include * prinbal * LFS700 ) <= .0523 * Included_Bal; con minLFS_750: .0026 * Included_Bal <= sum{k in pool} (Include * prinbal * LFS750 ) ; con LFS_750: sum{k in pool} (Include * prinbal * LFS750 ) <= .0126 * Included_Bal; con minLFS750plus: 0.0003 * Included_Bal <= sum{k in pool} (Include * prinbal * LFS750plus ) ; con maxLFS750plus: sum{k in pool} (Include * prinbal * LFS750plus ) <= .0103 * Included_Bal; /*Mixed Integer Linear Solver*/ solve with milp / presolver=aggressive /*probe=aggressive*/ emphasis=optimal absobjgap=5 /*/ presolver=BASIC with lp / solver = primal_spx with milp; /*relaxint relaxint with LP / solver=iterative*/ /*expand WA_LTV WA_LFS WA_APR maxLFS_650 / solve fix con; Expands aggregations so too large for this*/ print Included_Bal; create data Pool_Selected from [custid] Include; ; sql; create table outcome as count(e.custid) as loans, sum(e.prinbal) as bal, sum(e.prinbal*e.currrate)/sum(e.prinbal) as APR, sum(e.prinbal*e.LTV)/sum(e.prinbal) as LTV, sum(e.prinbal*e.LFS)/sum(e.prinbal) as LFS, case when e.origterm=75 then e.prinbal else 0 end)/sum(e.prinbal) as Term75_Perc, sum(case when e.pmtsmade=0 then e.prinbal else 0 end)/sum(e.prinbal) as Pmts0_Perc, case when LFS<=350 then e.prinbal else 0 end)/sum(e.prinbal) as LFS350, case when LFS between 351 and 400 then e.prinbal else 0 end)/sum(e.prinbal) as LFS400, case when LFS between 401 and 450 then e.prinbal else 0 end)/sum(e.prinbal) as LFS450, case when LFS between 451 and 500 then e.prinbal else 0 end)/sum(e.prinbal) as LFS500, case when LFS between 501 and 550 then e.prinbal else 0 end)/sum(e.prinbal) as LFS550, case when LFS between 551 and 600 then e.prinbal else 0 end)/sum(e.prinbal) as LFS600, case when LFS between 601 and 650 then e.prinbal else 0 end)/sum(e.prinbal) as LFS650, case when LFS between 651 and 700 then e.prinbal else 0 end)/sum(e.prinbal) as LFS700, case when LFS between 701 and 750 then e.prinbal else 0 end)/sum(e.prinbal) as 'LFS 750'n, case when LFS>750 then e.prinbal else 0 end)/sum(e.prinbal) as LFS750_Plus kl2.pool_exercise e join Pool_Selected p on e.custid=p.custid and p.Include=1 quit; transpose data=outcome; run; /*data outcome; set outcome; output; run;*/ print data=outcome; run; sql; create table kl2.mypool as E.custid, E.prinbal, E.LTV, E.LFS, E.origterm as Term, E.Pmtsmade, E.currrate as APR from kl2.pool_exercise e join Pool_Selected p on e.custid=p.custid and p.Include=1 quit;
... View more
