LIBNAME oriework '/..../..../....'; proc sql; connect to oracle (user=xxxx orapw=xxxx path=xxxx); create table oriework.PO_LOC as select * from connection to oracle ( SELECT LOC_ID, AUTO_PUBLISH_STRT_DT, AUTO_PUBLISH_EXPR_DT, AUTO_PUBLISH_EXPR_DY_QTY, AUTO_PUBLISH_EXPR_ALERT_DY_QTY, AUTO_PUBLISH_IND, AUTO_PUBLISH_EXEMPT_EVENTS, AUTO_BFR_DOW_IND_TXT FROM PO_LOC WHERE LOC_ID like 'D%' ); disconnect from oracle; quit; %macro mymac; OPTIONS NOTES SOURCE SOURCE2 MPRINT MLOGIC MERROR SYMBOLGEN; data _null_; set oriework.PO_LOC end=last; if last then call symput('nfiles',_n_); run; %do i=1 %to &nfiles; %put i = ; data _null_; set oriework.PO_LOC; if &i=_n_ then call symput('hotelcode',LOC_ID); run; %put &hotelcode; proc sql; connect to oracle (user=ORUSER orapw=sasuser1 path=HIROP1); create table oriework.pt as select * from connection to oracle ( SELECT B5.LOC_ID, B4.Period, B4.BFR_CONDITION_1, B4.avg_opt_pr, B4.avg_curr_pr, B4.avg_rec_pr, B5.avg_his_pr, abs ((B4.avg_curr_pr - B5.avg_his_pr)/B5.avg_his_pr) as curr_dif_rto, abs ((B4.avg_opt_pr - B5.avg_his_pr)/B5.avg_his_pr) as opt_dif_rto, abs ((B4.avg_rec_pr - B5.avg_his_pr)/B5.avg_his_pr) as rec_dif_rto FROM /*Calculate average price for historical, current, optimal and recommended*/ (SELECT LOC_ID, avg(MEAN_PRATIO_NBR) as avg_his_pr FROM elasticity where /* LOC_ID = 'ALAKZ' */ loc_id = %unquote(%str(%')&hotelcode%str(%')) and published_ind = 'Y' GROUP BY LOC_ID) B5 LEFT JOIN (SELECT LOC_ID, period, BFR_CONDITION_1, sum(in_period * opt_pr) / nullif(sum(in_period), 0) as avg_opt_pr, sum(in_period * curr_pr) / nullif(sum(in_period), 0) as avg_curr_pr, sum(in_period * rec_pr) / nullif(sum(in_period), 0) as avg_rec_pr FROM (SELECT LOC_ID, RATE_DT, period, BFR_CONDITION_1, case when RATE_DT - day_p < sysdate then 1 else 0 end in_period, opt_pr, curr_pr, rec_pr from (SELECT LOC_ID, RATE_DT, max(BFR_CONDITION) AS BFR_CONDITION_1, OPT_PR, CURR_PR, REC_PR FROM (SELECT LOC_ID, RATE_DT, EXCEPTION_START_DT, EXCEPTION_END_DT, OPT_PR, CURR_PR, REC_PR, case when RATE_DT between EXCEPTION_START_DT and EXCEPTION_END_DT then 'NON_AUTO_BFR' else 'AUTO_BFR' end BFR_CONDITION FROM (SELECT e.LOC_ID, e.exception_start_dt, e.exception_end_dt, f.RATE_DT, f.REC, f.OPT, f.CUR, f.COMP, f.OPT_PR, f.CURR_PR, f.REC_PR FROM (SELECT d.LOC_ID, c.EXCEPTION_START_DT, c.EXCEPTION_END_DT, d.AUTO_PUBLISH_STRT_DT, d.AUTO_PUBLISH_EXPR_DT, d.AUTO_PUBLISH_EXPR_DY_QTY, d.AUTO_PUBLISH_EXPR_ALERT_DY_QTY, d.AUTO_PUBLISH_IND, d.AUTO_PUBLISH_EXEMPT_EVENTS FROM PO_LOC d LEFT JOIN AUTO_PUBLISH_EXCEPTIONS c on c.LOC_ID = d.LOC_ID where d.loc_id = %unquote(%str(%')&hotelcode%str(%')) /* d.LOC_ID = 'ALAKZ' */ ORDER BY c.LOC_ID, c.EXCEPTION_ID) e /*select * from AUTO_PUBLISH_EXCEPTIONS where LOC_ID = 'BERCP'*/ LEFT JOIN (SELECT B1.LOC_ID, B1.RATE_DT, B1.DOW, /* B1. POSTING_TS, */ B1.OPT_BFR_UI as rec, B1.OPTML_BFR_RATE as opt, B1.SGL_AMOUNT as cur, B2.CMPBNCHMRK as comp, B1.OPTML_BFR_RATE / B2.CMPBNCHMRK AS opt_pr, B1.SGL_AMOUNT / B2.CMPBNCHMRK AS curr_pr, B1.OPT_BFR_UI / B2.CMPBNCHMRK AS rec_pr FROM (SELECT A1.LOC_ID, A1.RATE_DT, A1.DOW, A1.POSTING_TS, A1.OPT_BFR_UI, A1.OPTML_BFR_RATE, A2.SGL_AMOUNT FROM (SELECT A.LOC_ID, A.CONTROL_DATE AS RATE_DT, to_char(A.CONTROL_DATE, 'D') AS DOW, A.POSTING_TS AS POSTING_TS, B.RECMND_BFR_RATE AS OPT_BFR_UI, B.OPTML_BFR_RATE FROM (SELECT LOC_ID, CONTROL_DATE, max(POSTING_TS) AS POSTING_TS FROM OPTML_BFR WHERE CONTROL_DATE between SYSDATE and SYSDATE+365 AND /* LOC_ID = 'ALAKZ' */ loc_id = %unquote(%str(%')&hotelcode%str(%')) GROUP BY LOC_ID, CONTROL_DATE) A, OPTML_BFR B WHERE A.LOC_ID = B.LOC_ID AND A.CONTROL_DATE = B.CONTROL_DATE AND A.POSTING_TS = B.POSTING_TS ORDER BY A.LOC_ID, A.CONTROL_DATE) A1, (select A.LOC_ID, A.RATE_DATE AS RATE_DT, A.SGL_AMOUNT from rate_amount A, PO_LOC B where /* A.LOC_ID = 'ALAKZ' */ A.loc_id = %unquote(%str(%')&hotelcode%str(%')) and A.LOC_ID = B.LOC_ID and A.RATE_PROGRAM_ID = 'C1' AND A.rate_date between SYSDATE and SYSDATE+365 and A.room_type_id = B.ref_rm_typ ORDER BY A.RATE_DATE) A2 WHERE A1.RATE_DT = A2.RATE_DT AND A1.LOC_ID = A2.LOC_ID) B1, (WITH COMPSET_WTS AS ( SELECT LOC_ID, CONTROL_DATE, COMPSET_ID, MAX_RM_RATE, DECODE(TO_CHAR(CONTROL_DATE,'D'),1,WT_NBR_SUN, 2, WT_NBR_MON, 3, WT_NBR_TUE, 4, WT_NBR_WED, 5, WT_NBR_THU, 6, WT_NBR_FRI, WT_NBR_SAT) WT_NBR FROM COMPSET_LOC, CDOW WHERE /* LOC_ID = 'ALAKZ' */ loc_id = %unquote(%str(%')&hotelcode%str(%')) AND CONTROL_DATE BETWEEN SYSDATE AND SYSDATE+365 AND WT_NBR > 0 ORDER BY CONTROL_DATE) select a.loc_id, sum(WT_NBR * (case when RM_RATE_AMT_1 < 0 then nvl(MAX_RM_RATE, 0) else RM_RATE_AMT_1 end)) CMPBNCHMRK, ARR_DT , min(COMPET_DIFNTL_AMT)*100 COMPET_DIFNTL_AMT from COMPSET_WTS a, (select LOC_ID, COMPSET_ID, ARR_DT, nvL(RM_RATE_AMT, 0) RM_RATE_AMT_1, POSTING_TS FROM rubicon_expand where /* LOC_ID = 'ALAKZ' */ loc_id = %unquote(%str(%')&hotelcode%str(%')) AND LOS_QTY = 1 and ARR_DT BETWEEN SYSDATE AND SYSDATE+365 ) re1, (select LOC_ID, CONTROL_DATE , COMPET_DIFNTL_AMT FROM optml_bfr_cur WHERE loc_id = %unquote(%str(%')&hotelcode%str(%')) /* LOC_ID = 'ALAKZ' */ AND CONTROL_DATE BETWEEN SYSDATE AND SYSDATE+365 ) BFR where a.compset_id = re1.compset_id and a.control_date=re1.arr_dt and bfr.control_date = re1.arr_dt and a.LOC_ID = re1.LOC_ID and a.LOC_ID = bfr.LOC_ID group by a.loc_id, arr_dt ORDER BY loc_id, arr_dt) B2 WHERE B1.RATE_DT = B2.ARR_DT and B1.RATE_DT between SYSDATE and SYSDATE+365 and B1.LOC_ID = B2.LOC_ID) f on e.LOC_ID = f.LOC_ID order by RATE_DT) ) GROUP BY LOC_ID, RATE_DT, OPT_PR, CURR_PR, REC_PR ORDER BY RATE_DT ) localprice, (select '2 weeks' as period, 14 as day_p from dual union select '1 month' as period, 30 as day_p from dual union select '3 months' as period, 91 as day_p from dual union select '6 months' as period, 183 as day_p from dual union select '1 year', 365 as day_p from dual ) intervals ) detail GROUP BY LOC_ID, period, BFR_CONDITION_1 ) B4 on B4.LOC_ID = B5.LOC_ID order by LOC_ID, period ); disconnect from oracle; quit; %if %sysfunc(exist(oriework.HOTEL2)) %then %do; data oriework.HOTEL2; set oriework.HOTEL2 oriework.pt; run; %end; %else %do; data oriework.HOTEL2; set oriework.pt; run; %end; %end; %mend; %mymac;