Hi Rob, thanks for your time again. It seems that I have a problem with the other of the parameters. Anyway, I did as you said, with a slight modification. The first change sort of worked, and the second did not. The read data for the Santander_hist_3 worked, but the table was like in the figure I attached. Instead of having several columns for the periods it sort of transposed them for lines. Also , there were warnings of duplicate regarding the dates, which I believe are harmless because the compound keys (date, asset) are unique. I attached a printout together with the lim_metals, which worked ok. I don't mind if this still works to identify the data. Fot the IMPVAR id did not work, I guess. the expand command does not show it and when I uncomment the solver statement it throws an error (attached it too). Any lights on these? Thank you again. =======here is the code====== /* we kept some words in Portuguese as in the original data Portuguese vs English ativo = asset data = date */ DATA max_limits; input ativo $ val_max; datalines; Nickel 5.5 Tin 2.2 ; %let max_metals=6.0; /* the following are not used yet since we are validating the model with only two metal commodities*/ %let max_agri=12; %let max_total=20; PROC OPTMODEL; /* Read historic and constraints table from WORKLIB */ /* LIM_METALS contains the max values by asset/period and the max of total assets by type (in this case METALS) per period */ set <str> PERIODS=/_1M _3M _6M _9M _1Y _2Y _3Y/; set <str> COMMS; num lim_metals {COMMS, PERIODS}; read data WORK.LIM_METAL into COMMS=[commodity] {j in PERIODS} <lim_metals[commodity, j]=col(j)>; print lim_metals; /* test number 1 set DATES; read data WORK.santander_hist_3 into DATES=[date]; num hist_data {DATES, COMMS, PERIODS}; read data WORK.SANTANDER_HIST into [date ativo] {j in PERIODS} <hist_data[date,ativo,j]=col(j)>; print hist_data; */ /*this one works, but not how I intended to. The periods were moved as a single column set <num> DATES; set <str, num> COMMS_DATES; num hist_data{COMMS_DATES, PERIODS}; read data WORK.santander_hist_3 INTO COMMS_DATES=[ativo date] {period in PERIODS} <hist_data[ativo, date, period]=col(period)>; print hist_data; */ /* test number 3*/ set <num> DATES; num hist_data{DATES, COMMS, PERIODS}; read data WORK.Santander_hist_3 into DATES=[date]; read data WORK.Santander_hist_3 into COMMS=[ativo]; read data WORK.SANTANDER_HIST into [date ativo] {j in PERIODS} <hist_data[date,ativo,j]=col(j)>; print hist_data; num val_max{COMMS}; read data max_limits INTO COMMS=[ativo] val_max; print val_max; /* variables to optimise and constraints */ var amount{COMMS, PERIODS} >= 0; /* not quite sure the following IMPVAR statement has worked it does not show on expand and when I call the solver it throws an error.*/ impvar amount_date_comm_period {date in DATES}=sum{comm in COMMS,period in PERIODS} amount[comm,period]*hist_data[date,comm,period]; /* the following IMPVAR statement seems to work fine*/ impvar total_comm {comm in COMMS}=sum{period in PERIODS} amount[comm, period]; /* BEFORE impvars. Kept it for test purposes. con lim_comm {comm in COMMS}: sum{period in PERIODS} amount[comm, period]<= val_max[comm]; Replaced this line with the IMPVAR defined above*/ con lim_comm {comm in COMMS}: total_comm[comm] <= val_max[comm]; con lim_total: sum{comm in COMMS} total_comm[comm] <= &max_metals; expand; /* Objective Function */ min value_at_risk= smallest(6, of amount_date_comm_period[*]); solve with nlp /algorithm=as ms; =======the table remain the same =======
... View more
