topic Re: Addind Data to existing excel file - in Mathematical Optimization, Discrete-Event Simulation, and OR

Addind Data to existing excel file -

Kh_lak — Fri, 26 Jun 2015 18:27:45 GMT

proc, oHi All,

I imported excel into SAS and ran the query and it ran fine. Now I want to add the data (from row 142K to 219K), i save the files and when i run the query it shows me the data old data.

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*/;

KL2_Pool_Exercise;

'SDART_Total_Eligible_103114.xlsx';

print data=KL2_Pool_Exercise;

This is the error message I am getting -

ERROR: Physical file does not exist, D:\SAS94\Config\Lev1\SASApp\SDART_Total_Eligible_103114.xlsx.

Any help would be appreciated - thanks

Re: Addind Data to existing excel file -

Reeza — Fri, 26 Jun 2015 19:52:13 GMT

What does this have to do with optimization, model, constraints, etc?

Your code is truncated so hard to see, but does the file actually exist and can your application see the location from the server it's running from?

Re: Addind Data to existing excel file -

Tom — Fri, 26 Jun 2015 20:02:48 GMT

The code you posted is all garbled, but your error message makes it look like you tried to run:

proc import datafile='SDART_Total_Eligible_103114.xlsx'

....

and SAS is not finding that file in the current directory.

Re: Addind Data to existing excel file -

Kh_lak — Fri, 26 Jun 2015 20:11:01 GMT

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;

Re: Addind Data to existing excel file -

Kh_lak — Fri, 26 Jun 2015 20:21:02 GMT

Below is the full code, I am trying to update the data in excel.."..eligibility file" tExcel part is fine but when i call the excel in sas, it shows the old data -

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: 1176470500 <= Included_Bal <= 1176470600; /*Much faster with tolerance band for Included Bal. This is the amount necessary for $1B in SDART bond proceeds*/