Thanks @PaigeMiller Code is like following: %macro noshow_fcst;
OPTIONS NOTES SOURCE SOURCE2 MPRINT MLOGIC MERROR SYMBOLGEN;
data _null_;
set PO_LOC end=last;
if last then call symput('nfiles',_n_);
run;
%do i=1 %to &nfiles;
%put i =;
data _null_;
set PO_LOC;
if &i=_n_ then
call symput('LOC_ID',LOC_ID);
run;
%Let Lead_Time = 0;
/* HIST5_&LOC_ID IS obtained from Teradata database */
/* Transfer pure Noshow and Cancellation from 'arrival date' & 'length of stay' level to 'stay date' */
DATA HIST5_1_&LOC_ID;
SET HIST5_&LOC_ID;
Cancellation_Lead = Arrival_Date - Cancel_Date;
if Cancellation_Lead > 30 then Cancellation_Lead = 30;
If Cancellation_Lead < 0 then Cancellation_Lead = 0;
FORMAT Stay_Date yymmdd10.;
do k=1 to length_of_stay;
Stay_Date = Arrival_Date - 1 + k;
output;
end;
drop k length_of_stay;
run;
/* Filter the data as we count 'No-show' for 1 night and Cancellation for Lead Time N */
Data HIST5_2_&LOC_ID;
Set HIST5_1_&LOC_ID;
If Cancellations > 0 and Cancellation_Lead > &Lead_Time then delete;
If Stay_Date > Arrival_Date and Noshows > 0 then delete;
Run;
/* Actual Noshow and Cancellation data by Stay Date (Month and Day of Week) level */
Proc Sql;
Create Table HIST5_3_&LOC_ID as select distinct
Operating_Div_Cd,
Chain_Cd,
Loc_Id,
Stay_Date,
Month(Stay_Date) as Mon,
Weekday(Stay_Date) as Dow,
Prod_Class_Id,
sum(Cancellations+Noshows) as Cancel_Noshow
from HIST5_2_&LOC_ID
group by 1, 2, 3, 4, 5, 6, 7
order by 1, 2, 3, 7, 4;
Quit;
/* Forecasting Approach: Poisson Regression, omitting impact of Special Event */
/* This part could obatin data for Noshow + Cancellation on some Lead Time */
proc genmod data=HIST5_3_&LOC_ID;
class Mon Dow;
by Operating_Div_cd chain_cd loc_id prod_class_id;
model Cancel_Noshow = Mon Dow/ dist=poisson
link=log scale=pearson;
output out=HIST_5_6_Pos_&LOC_ID p=Noshow_Cancel_Pred_Pos;
where Stay_Date between &START1 and &END1;
run;
ods exclude all;
/* Check if HIST_5_6_Pos_&LOC_ID has any columns or not */
Proc Sql noprint;
Select count(distinct name) into: does_column_exist
from sashelp.vcolumn where lowcase(name) = "noshow_cancel_pred_pos" and lowcase(memname) = "hist_5_6_pos_&loc_id";
Quit;
%if &does_column_exist > 0 %then %do;
/* Gather output data by Month and day of week level for Noshow + Cancellation */
Proc Sql;
Create Table Noshowfcst_&LOC_ID as select distinct
Operating_Div_Cd,
Chain_Cd,
Loc_Id,
Prod_Class_Id,
Mon,
Dow,
Case
When Weekday(Stay_Date) in (1,2,3,4,5) then 'WD'
Else 'WE'
End as DY_TYP,
avg(Noshow_Cancel_Pred_Pos) as Noshow_Fcst
from HIST_5_6_Pos_&LOC_ID
group by 1, 2, 3, 4, 5, 6, 7;
Quit;
%end;
%else %do;
%end;
%end;
%mend;
/* The first macro ends for No-show Forecasting */
%noshow_fcst; Part of Log Information for HIST_5_6_POS_&LOC_ID: NOTE: The data set WORK.HIST_5_6_POS_AADAL has 912 observations and 9 variables. SYMBOLGEN: Macro variable LOC_ID resolves to AADAL MPRINT(NOSHOW_FCST): Select count(distinct name) into: does_column_exist from sashelp.vcolumn where lowcase(name) = "noshow_cancel_pred_pos" and lowcase(memname) = "hist_5_6_pos_AADAL"; MPRINT(NOSHOW_FCST): Quit; SYMBOLGEN: Macro variable DOES_COLUMN_EXIST resolves to 0 MLOGIC(NOSHOW_FCST): %IF condition &does_column_exist > 0 is FALSE MLOGIC(NOSHOW_FCST): %DO loop index variable I is now 2; loop will iterate again. Part of the data for 'hist_5_6_pos_&loc_id' : Thank you! And for this example, 'Noshowfcst_&LOC_ID' cannot be obtained even if 'hist_5_6_pos_&LOC_ID' exists.
... View more
