So this is the code for the whole program: options fullstimer;
%let INN = 0000000000;
%let BeginDate = 01jan2019;
%let EndDate = 01feb2019;
%let Months = %sysfunc(intck(month,"&BeginDate."d,"&EndDate."d));
data dates;
do m = 0 to &months.;
first_of_month = intnx('month',"&BeginDate."d,m,'s');
last_of_month = intnx('month', "&BeginDate."d,m,'e');
output;
end;
format first_of_month last_of_month date9.;
run;
PROC SQL;
%Macro pos;
data _null_;
set dates end=eof;
call symputx(cats('obs_date',_N_),last_of_month);
if eof then call symputx('numdates',_n_);
run;
data summary;
length sum 8;
format date datetime20.;
format sum nlnum32.;
retain _all_;
run;
%do i=1 %to &numdates.;
%let date_= %sysfunc(dhms(&&obs_date&i,0,0,0));
proc sql;
CREATE TABLE WORK.OPEN_POS_&&obs_date&i AS
SELECT
t9.BOARD_ID,
t9.NAME,
t1.BUY_SELL,
t1.ST_ACTUAL,
t1.TRADE_DATE,
t1.TYP,
t1.STATUS,
t1.CONFIRMED,
t3.SHORT_NAME AS SHORT_NAME3 length=30 format=$30. informat=$30.,
t3.ISIN length=12 format=$12. informat=$12.,
t2.SHORT_NAME length=30 format=$30. informat=$30.,
t2.INN length=10 format=$10. informat=$10.,
t6.SHORT_NAME AS SHORT_NAME1 length=30 format=$30. informat=$30.,
t6.INN AS INN1 length=10 format=$10. informat=$10.,
t1.PRICE,
t1.VAL,
t1.AMOUNT,
t1.DUE_DATE,
t1.CLIENT_CODE_ID,
t4.DETAILS ,
t7.CodeCurSimple length=4 format=$4. informat=$4.,
t8.RATE,
t8.UNIT
FROM SV_EQ_TRADES t1
LEFT JOIN SV_EQ_FIRMS t2 ON (t1.FIRM_ID = t2.FIRM_ID)
LEFT JOIN SV_EQ_SECS t3 ON (t1.SECURITY_ID = t3.SECURITY_ID)
LEFT JOIN SV_EQ_CLIENTCODES t4 ON (t1.CLIENT_CODE_ID = t4.CLIENT_CODE_ID)
LEFT JOIN SV_EQ_FIRMS t6 ON (t1.CP_FIRM_ID = t6.FIRM_ID)
LEFT JOIN WORK.CODESEQTOCURRENCY t7 ON (t1.CURRENCY_ID = t7.CodeEqCurr)
LEFT JOIN WORK.FILTER_FOR_SV_CURR_XRATES t8 ON t7.CodeCurSimple = t8.CURRENCY1_ID and t1.TRADE_DATE = t8.X_DATE
LEFT JOIN SV_EQ_BOARDS t9 ON (t1.BOARD_ID = t9.BOARD_ID)
WHERE (t1.TRADE_DATE<=&date_ and t1.DUE_DATE>=&date_ and t1.TRADE_DATE<t1.DUE_DATE) AND t2.INN = "&INN"
AND t1.TYP in ('r','h','j')
and t1.STATUS = 'M'
and t1.CONFIRMED = 'C'
and t1.ST_ACTUAL = 'A'
ORDER BY t1.TRADE_DATE;
quit;
data full_trades_&&obs_date&i;
set work.OPEN_POS_&&obs_date&i;
length Deal_Type $7;
length Details_Type $7;
length repo_type $14;
if TYP in ("N" "T") then
Deal_Type= "Market";
else if TYP in ("R" "r" "H" "h" "J" "j") then
Deal_Type= "Repo";
if DETAILS = '' then Details_Type="Self";
else if DETAILS ne '' then Details_Type="Client";
if typ in ('R' 'H' 'J') and buy_sell = 'S' then
repo_type='straight_repo';
else if typ in ('r' 'h' 'j') and buy_sell = 'B' then
repo_type='straight_repo';
else if typ in ('R' 'H' 'J') and buy_sell = 'B' then
repo_type='reverse_repo';
else if typ in ('r' 'h' 'j') and buy_sell = 'S' then
repo_type='reverse_repo';
Month=trade_date;
format month dtmonyy.;
run;
title1 "Данные на %sysfunc(putn(%sysfunc(dhms(&&obs_date&i,0,0,0)),datetime20.))" ;
proc tabulate data=full_trades_&&obs_date&i;
class Buy_Sell CodeCurSimple name repo_type/ order=freq ;
var VALrur VAL;
table CodeCurSimple all={s=[background=graydf ]},
(repo_type)*(VALrur*(N*f=nlnum32. sum*f=nlnum32.2 colpctsum)VAL*(sum*f=nlnum32.2)) all*VALrur*(N*f=nlnum32. sum*f=nlnum32.2 colpctsum*f=nlnum32.2);
run;
title1;
proc means data=full_trades_&&obs_date&i sum noprint;
var VALrur;
output out=summary_&&obs_date&i sum=sum;
format sum nlnum32.;
run;
proc append base=summary
data=summary_&&obs_date&i force;
where _type_=0;
run;
data summary;
set summary;
date = &date_;
retain date;
run;
%_eg_conditional_dropds(WORK.OPEN_POS_&&obs_date&i);
%end;
%mend pos;
%pos
... View more
