Thanks Kurt, Is this closer to what it should look like?
%macro aaRFM;
/* Simply call the secure store macro aaRFM_exec */
%aaRFM_exec;
%macro aaRFM_exec ;
/* Keep this global macro variable: RFM_MsgFile defined, because the EM_RFM_CONTROL macro may
be invoked multiple times */
%global RFM_MsgFile;
%let RFM_MsgFile = SASHELP.AARFM;
%put %sysfunc(sasmsg(&RFM_MsgFile., macro_has_been_processed, NOQUOTE, &sysmacroname.));
%mend aaRFM_exec;
/* The macro EM_RFM_CONTROL */
%macro EM_RFM_CONTROL
(
Mode =C, /* Mode of analysis: T = Transactional Data, C = Customer Data */
InData =WORK.QUERY_FOR_MEANSUMMARYSTATS, /* Input data */
CustomerID =customer_num, /* Customer ID */
/* RFM Analysis Specifications */
N_R_Grp = 5, /* Number of Most_Recent_Purchase_Date groups */
N_F_Grp = 5, /* Number of Frequency groups */
N_M_Grp = 5, /* Number of Monetary groups */
BinMethod = N, /* Method for binning: N = Nested and I = Independent */
/* Input Variables and Specifications Only for Transactional Data Mode
(will be ignored if Mode is Customer Data) */
FreqVar =, /* Optional replication frequency variable */
PurchaseDate =, /* Purchase date */
PurchaseAmt =, /* Purchase amount */
SetMiss = Y, /* Y = If a customer ID has missing purchase date or missing purchase amount,
then set all results to missing regardless of any complete records,
N = Otherwise */
SummaryFunc = SUM, /* Summary function: SUM, MEAN, MEDIAN, or MAX */
/* Input Variables for Customer Data Mode, but Output Variables for Transactional Data Model */
MostRecentDate =MAX_of_Order_date, /* Most recent purchase date (Output for Transactional) */
NPurchase =_FREQ_, /* Number of purchases (Output for Transactional) */
TotPurchaseAmt =Ext_Price_Sum, /* Total amount of purchases (Output for Transactional) */
/* Display options for both Modes */
MonetizationMap = Y, /* Show Monetization map (Y/N)? */
BinChart = Y, /* Chart of bin counts (Y/N)? */
BinTable = N, /* Table of bin counts (Y/N)? */
/* Output options for both Modes */
OutData =WORK.RFM_TABLE, /* (Out) Output customer level data with scores */
RightBinBoundData =, /* (Out) Optional output bin right boundary data */
Recency_Score =Recency_Score, /* (Out) Recency score (1=least recent, N_R_Grp=most recent) */
Frequency_Score =Frequency_Score, /* (Out) Frequency score (1=least frequent, N_F_Grp=most frequent) */
Monetary_Score =Monetary_Score, /* (Out) Monetary score (1=least amount, N_M_Grp=most amount) */
RFM_Score =RFM_Score /* (Out) RFM Score (= 100 * Recency_score + 10 * Frequency_Score + Monetary_Score) */
) ;
/* Save the starting timestamp */
%local TimeStart;
%let TimeStart = %sysfunc(datetime());
/* Upper case the input switch */
%let SummaryFunc = %upcase(&SummaryFunc.);
%let Mode = %upcase(&Mode.);
%let BinMethod = %upcase(&BinMethod.);
%let BinTable = %upcase(&BinTable.);
%let BinChart = %upcase(&BinChart.);
%let MonetizationMap = %upcase(&MonetizationMap.);
%let SetMiss = %upcase(&SetMiss.);
/* Turn on the IN macro operator */
options minoperator;
/* Initialize */
%global RFM_ErrCode; /* Local error code */
%global RFM_WorkLibrary; /* Library name for storing temporary datasets */
%let RFM_ErrCode = 0;
%let RFM_WorkLibrary = WORK;
%local actual_N_F_Grp; /* Actual number of Frequency groups found */
%local actual_N_M_Grp; /* Actual number of Monetary groups found */
%local actual_N_R_Grp; /* Actual number of Recency groups found */
%local CustomerIDLabel; /* Variable label of the CustomerID */
%local InDataCopy; /* A copy of input data excluding records where Customer ID is missing */
%local InDataConents; /* Output dataset containing the contents of input data */
%local min_F_Score; /* Minimum Frequency score */
%local min_M_Score; /* Minimum Monetary score */
%local min_R_Score; /* Minimum Recency score */
%local MostRecentDate_fmt; /* Format for displaying the MostRecentDate variable */
%local MostRecentDateLabel; /* Variable label of the MostRecentDate variable */
%local MyName; /* Name of this macro */
%local NPurchase_fmt; /* Format for displaying the NPurchase variable */
%local NPurchaseLabel; /* Variable label of the NPurchase variable */
%local NReqVar; /* Number of required variables */
%local OutDataCopy; /* A copy of output data */
%local OutputScore; /* A dataset in the WORK library to store score */
%local qmatch; /* A binary (0/1) indicator to determine if a match (1) is found */
%local RBBData; /* Local copy of the right bin bound data */
%local RFMvarV7OK; /* Equal 1 if RFM variables and scores are V7 compatible */
%local RFM_Min_Score; /* Minimum RFM score */
%local RFM_Score_vname; /* Name of the RFM_Score variable */
%local StoreFN1; /* Stored footnote 1 */
%local StoreFN2; /* Stored footnote 2 */
%local StoreTitle; /* Stored title */
%local TotPurchaseAmt_fmt; /* Format for displaying the TotPurchaseAmt variable */
%local TotPurchaseAmtLabel; /* Variable label of the TotPurchaseAmt variable */
%let actual_N_F_Grp = &N_F_Grp.;
%let actual_N_M_Grp = &N_M_Grp.;
%let actual_N_R_Grp = &N_R_Grp.;
%let CustomerIDLabel = %str();
%let InDataCopy = _RFM_INDATA_COPY_;
%let InDataConents = _RFM_INDATA_CONTENTS_;
%let min_F_Score = 1;
%let min_M_Score = 1;
%let min_R_Score = 1;
%let MostRecentDate_fmt = nldate.;
%let MostRecentDateLabel = %str();
%let MyName = (&SYSMACRONAME.);
%let NPurchase_fmt = nlbest32.;
%let NPurchaseLabel = %str();
%let NReqVar = 0;
%let OutDataCopy = _RFM_OUTDATA_COPY_;
%let OutputScore = _RFM_SCORE_;
%let qmatch = 0;
%let RBBData = &RightBinBoundData.;
%let RFMvarV7OK = 0;
%let RFM_Min_Score = 111;
%let RFM_Score_vname = &RFM_Score.;
%let StoreFN1 = %str();
%let StoreFN2 = %str();
%let StoreTitle = %str();
%let TotPurchaseAmt_fmt = nlmny32.;
%let TotPurchaseAmtLabel = %str();
/* Check for valid arguments */
%if (&Mode. ne T and &Mode. ne C) %then %let RFM_ErrCode = 1;
%else %if (%length(&InData.) eq 0) %then %let RFM_ErrCode = 2;
%else %if (%sysfunc(exist(&InData.)) eq 0 and %sysfunc(exist(&InData., VIEW)) eq 0) %then %let RFM_ErrCode = 3;
%else %if (%length(&CustomerID.) eq 0) %then %let RFM_ErrCode = 4;
%else %if (&BinMethod. ne N and &BinMethod. ne I) %then %let RFM_ErrCode = 8;
%else %if (%length(&OutData.) eq 0) %then %let RFM_ErrCode = 9;
/* Check for required input variables for each mode of operation */
%if (&RFM_ErrCode. eq 0) %then %do;
%if (&Mode. eq T) %then %do;
%if (%length(&PurchaseDate.) eq 0 or %length(&PurchaseAmt.) eq 0) %then %let RFM_ErrCode = 11;
%end;
%else %do;
%if (%length(&MostRecentDate.) eq 0 or %length(&NPurchase.) eq 0 or %length(&TotPurchaseAmt.) eq 0) %then %let RFM_ErrCode = 12;
%end;
%end;
/* Number of recency groups must be integer between 1 and 9 inclusively */
%if (&RFM_ErrCode. eq 0) %then %do;
%if (&N_R_Grp. in (1 2 3 4 5 6 7 8 9)) %then %let RFM_ErrCode = 0;
%else %let RFM_ErrCode = 5;
%end;
/* Number of frequency groups must be integer between 1 and 9 inclusively */
%if (&RFM_ErrCode. eq 0) %then %do;
%if (&N_F_Grp. in (1 2 3 4 5 6 7 8 9)) %then %let RFM_ErrCode = 0;
%else %let RFM_ErrCode = 6;
%end;
/* Number of monetary groups must be integer between 1 and 9 inclusively */
%if (&RFM_ErrCode. eq 0) %then %do;
%if (&N_M_Grp. in (1 2 3 4 5 6 7 8 9)) %then %let RFM_ErrCode = 0;
%else %let RFM_ErrCode = 7;
%end;
%if (&RFM_ErrCode. eq 0) %then %do;
proc contents data = &InData.
out = &RFM_WorkLibrary..&InDataConents. (keep = NAME TYPE LABEL)
noprint;
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
/* Check if the required input variables are numeric for both modes of operation */
%if (&RFM_ErrCode. eq 0) %then %do;
%let NReqVar = 1; /* Customer ID */
%if (%length(&FreqVar.) gt 0) %then %let NReqVar = %eval(&NReqVar. + 1);
%if (&Mode. eq T) %then %let NReqVar = %eval(&NReqVar. + 2);
%else %let NReqVar = %eval(&NReqVar. + 3);
data _NULL_;
set &RFM_WorkLibrary..&InDataConents. end = eof;
retain nOKVar 0;
select (upcase(NAME));
when ("%upcase(%trim(&CustomerID.))") nOKVar = nOKVar + 1;
%if (%length(&FreqVar.) gt 0) %then %do;
when ("%upcase(%trim(&FreqVar.))") nOKVar = nOKVar + 1;
%end;
%if (&Mode. eq T) %then %do;
when ("%upcase(%trim(&PurchaseDate.))")
do;
if (TYPE eq 1) then nOKVar = nOKVar + 1;
end;
when ("%upcase(%trim(&PurchaseAmt.))")
do;
if (TYPE eq 1) then nOKVar = nOKVar + 1;
end;
%end;
%else %do;
when ("%upcase(%trim(&MostRecentDate.))")
do;
if (TYPE eq 1) then nOKVar = nOKVar + 1;
end;
when ("%upcase(%trim(&NPurchase.))")
do;
if (TYPE eq 1) then nOKVar = nOKVar + 1;
end;
when ("%upcase(%trim(&TotPurchaseAmt.))")
do;
if (TYPE eq 1) then nOKVar = nOKVar + 1;
end;
%end;
otherwise;
end;
if (eof eq 1 and nOKVar ne &NReqVar.) then
do;
call symput('RFM_ErrCode',
%if (&Mode. eq T) %then '11';
%else '12';
);
end;
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
/* Translatable strings */
%if (&RFM_ErrCode. eq 0) %then %do;
%let Recency_Label = %sysfunc(sasmsg(&RFM_MsgFile., Recency_Label, NOQUOTE));
%let Frequency_Label = %sysfunc(sasmsg(&RFM_MsgFile., Frequency_Label, NOQUOTE));
%let Monetary_Label = %sysfunc(sasmsg(&RFM_MsgFile., Monetary_Label, NOQUOTE));
%let RecCount_Label = %sysfunc(sasmsg(&RFM_MsgFile., RecCount_Label, NOQUOTE));
%let CustomerIDLabel = %sysfunc(sasmsg(&RFM_MsgFile., CustomerIDLabel, NOQUOTE));
%let MostRecentDateLabel = %sysfunc(sasmsg(&RFM_MsgFile., MostRecentDateLabel, NOQUOTE));
%let NPurchaseLabel = %sysfunc(sasmsg(&RFM_MsgFile., NPurchaseLabel, NOQUOTE));
%let Title_Recency = %sysfunc(sasmsg(&RFM_MsgFile., Title_Recency, NOQUOTE));
%let Title_Most_Recent_Date = %sysfunc(sasmsg(&RFM_MsgFile., Title_Most_Recent_Date, NOQUOTE));
%let Title_Frequency_by_R = %sysfunc(sasmsg(&RFM_MsgFile., Title_Frequency_by_R, NOQUOTE));
%let Title_NPurchase_by_R = %sysfunc(sasmsg(&RFM_MsgFile., Title_NPurchase_by_R, NOQUOTE));
%let Title_Monetary_by_RF = %sysfunc(sasmsg(&RFM_MsgFile., Title_Monetary_by_RF, NOQUOTE));
%local singleStatLabel;
%local pluralStatLabel;
%if (&SummaryFunc. eq SUM) %then %do;
%let singleStatLabel = %sysfunc(sasmsg(&RFM_MsgFile., Sum_Singular, NOQUOTE));
%let pluralStatLabel = %sysfunc(sasmsg(&RFM_MsgFile., Sum_Plural, NOQUOTE));
%end;
%else %if (&SummaryFunc. eq MEAN) %then %do;
%let singleStatLabel = %sysfunc(sasmsg(&RFM_MsgFile., Average_Singular, NOQUOTE));
%let pluralStatLabel = %sysfunc(sasmsg(&RFM_MsgFile., Average_Plural, NOQUOTE));
%end;
%else %if (&SummaryFunc. eq MEDIAN) %then %do;
%let singleStatLabel = %sysfunc(sasmsg(&RFM_MsgFile., Median_Singular, NOQUOTE));
%let pluralStatLabel = %sysfunc(sasmsg(&RFM_MsgFile., Median_Plural, NOQUOTE));
%end;
%else %if (&SummaryFunc. eq MAX) %then %do;
%let singleStatLabel = %sysfunc(sasmsg(&RFM_MsgFile., Maximum_Singular, NOQUOTE));
%let pluralStatLabel = %sysfunc(sasmsg(&RFM_MsgFile., Maximum_Plural, NOQUOTE));
%end;
%if (&Mode. eq T) %then %do;
%let TotPurchaseAmtLabel = %sysfunc(sasmsg(&RFM_MsgFile., TotPurchaseAmtLabel, NOQUOTE, &singleStatLabel.));
%let Title_TotPurchaseAmt_by_RF = %sysfunc(sasmsg(&RFM_MsgFile., Title_TotPurchaseAmt_by_RF, NOQUOTE, &pluralStatLabel.));
%let Title_Monetization = %sysfunc(sasmsg(&RFM_MsgFile., Title_Monetization, NOQUOTE, &pluralStatLabel.));
%end;
%else %do;
%let TotPurchaseAmtLabel = %sysfunc(sasmsg(&RFM_MsgFile., RFMSummaryAll, NOQUOTE));
data _NULL_;
set &RFM_WorkLibrary..&InDataConents.;
where (lengthn(LABEL) gt 0);
select (upcase(NAME));
when ("%upcase(%trim(&CustomerID.))") call symput('CustomerIDLabel', strip(LABEL));
when ("%upcase(%trim(&MostRecentDate.))") call symput('MostRecentDateLabel', strip(LABEL));
when ("%upcase(%trim(&NPurchase.))") call symput('NPurchaseLabel', strip(LABEL));
when ("%upcase(%trim(&TotPurchaseAmt.))") call symput('TotPurchaseAmtLabel', strip(LABEL));
otherwise;
end;
run;
%let Title_TotPurchaseAmt_by_RF = %sysfunc(sasmsg(&RFM_MsgFile., Dist_Title_TotPurchaseAmt_by_RF, NOQUOTE, &singleStatLabel.)) ;
%let Title_Monetization = %sysfunc(sasmsg(&RFM_MsgFile., Title_MonetizationMap, NOQUOTE, &singleStatLabel.)) ;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%end;
/* Remove records where the Customer ID is missing, and the frequency weight, if specified, is missing;
and save resulting data into WORK.InputData */
%if (&RFM_ErrCode. eq 0) %then %do;
data &RFM_WorkLibrary..&InDataCopy.;
set &InData.;
if (missing(&CustomerID.) eq 0
%if (%length(&FreqVar.) gt 0) %then and missing(&FreqVar.) eq 0;
) then output;
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&RFM_ErrCode. eq 0) %then %do;
%let RFM_Max_Score = %eval(100 * &N_R_Grp. + 10 * &N_F_Grp. + &N_M_Grp.);
%let minRS = 1;
%let minFS = 1;
%let minMS = 1;
%let minRFM = 111;
%let fn1 = %sysfunc(sasmsg(&RFM_MsgFile., RFM_Common_Footnote1, NOQUOTE, &N_R_Grp., &N_F_Grp., &N_M_Grp.));
%if (&BinMethod. eq N) %then %let fn2 = %sysfunc(sasmsg(&RFM_MsgFile., RFM_Common_Footnote2N, NOQUOTE));
%else %let fn2 = %sysfunc(sasmsg(&RFM_MsgFile., RFM_Common_Footnote2I, NOQUOTE));
/* If the new variables are not specified, then use the default variable names */
%if (%length(&Recency_Score.) eq 0) %then %let Recency_Score = Recency_Score;
%if (%length(&Frequency_Score.) eq 0) %then %let Frequency_Score = Frequency_Score;
%if (%length(&Monetary_Score.) eq 0) %then %let Monetary_Score = Monetary_Score;
%if (%length(&RFM_Score.) eq 0) %then %let RFM_Score = RFM_Score;
/* If input is a transactional data, then aggregate the input data to create the customer data.
Otherwise, simply make a copy of the input customer data */
%if (&Mode. eq T) %then %do;
%if (%length(&MostRecentDate.) eq 0) %then %let MostRecentDate = MostRecentDate;
%if (%length(&NPurchase.) eq 0) %then %let NPurchase = NPurchase;
%if (%length(&TotPurchaseAmt.) eq 0) %then %let TotPurchaseAmt = TotPurchaseAmt;
%EM_RFM_TRAN_2_CUST (InData = &RFM_WorkLibrary..&InDataCopy.,
CustomerID = &CustomerID.,
FreqVar = &FreqVar.,
PurchaseDate = &PurchaseDate,
PurchaseAmt = &PurchaseAmt.,
SetMiss = &SetMiss.,
SummaryFunc = &SummaryFunc.,
OutData = &RFM_WorkLibrary..&OutDataCopy.,
MostRecentDate = &MostRecentDate.,
NPurchase = &NPurchase.,
TotPurchaseAmt = &TotPurchaseAmt.);
%end;
%end;
%if (&RFM_ErrCode. eq 0) %then %do;
/* Only use the complete records in customer data in subsequent calculations */
data &RFM_WorkLibrary..&OutDataCopy.;
set
%if (&Mode. eq T) %then &RFM_WorkLibrary..&OutDataCopy.;
%else &RFM_WorkLibrary..&InDataCopy.;
end = eof;
retain nComplete 0;
if (missing(&MostRecentDate.) eq 0 and
missing(&NPurchase.) eq 0 and
missing(&TotPurchaseAmt.) eq 0) then
do;
nComplete + 1;
output;
end;
if (eof eq 1) then
do;
if (nComplete gt 0) then
do;
call symput('MostRecentDate_fmt', strip(vformat(&MostRecentDate.)));
call symput('NPurchase_fmt', strip(vformat(&NPurchase.)));
call symput('TotPurchaseAmt_fmt', strip(vformat(&TotPurchaseAmt.)));
end;
else call symput('RFM_ErrCode', '10');
end;
keep &CustomerID. &MostRecentDate. &NPurchase. &TotPurchaseAmt.;
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&RFM_ErrCode. eq 0) %then %do;
proc sort data = &RFM_WorkLibrary..&OutDataCopy.;
by &CustomerID.;
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&RFM_ErrCode. eq 0) %then %do;
/* Create Right Bin Bound data only if the required variables are V7 compatible */
%let RFMvarV7OK = 0;
%let RBBData =;
%if (%length(&RightBinBoundData.) gt 0) %then %do;
%if ((%sysfunc(nvalid(&MostRecentDate., V7)) eq 1) and
(%sysfunc(nvalid(&NPurchase., V7)) eq 1) and
(%sysfunc(nvalid(&TotPurchaseAmt., V7)) eq 1) and
(%sysfunc(nvalid(&Recency_Score., V7)) eq 1) and
(%sysfunc(nvalid(&Frequency_Score., V7)) eq 1) and
(%sysfunc(nvalid(&Monetary_Score., V7)) eq 1) and
(%sysfunc(nvalid(&RFM_Score., V7)) eq 1)) %then %do;
%let RFMvarV7OK = 1;
%let RBBData = &RightBinBoundData.;
%end;
%end;
/* Calculate scores based on the binning method */
%if (&BinMethod. eq N) %then %do;
%EM_RFM_NESTED_RANK (InData = &RFM_WorkLibrary..&OutDataCopy.,
CustomerID = &CustomerID.,
MostRecentDate = &MostRecentDate.,
NPurchase = &NPurchase.,
TotPurchaseAmt = &TotPurchaseAmt.,
N_R_Grp = &N_R_Grp.,
N_F_Grp = &N_F_Grp.,
N_M_Grp = &N_M_Grp.,
OutData = &RFM_WorkLibrary..&OutputScore.,
RightBinBoundData = &RBBData.,
Recency_Score = &Recency_Score.,
Frequency_Score = &Frequency_Score.,
Monetary_Score = &Monetary_Score.);
%end;
%else %if (&BinMethod. eq I) %then %do;
%EM_RFM_INDEPENDENT_RANK (InData = &RFM_WorkLibrary..&OutDataCopy.,
CustomerID = &CustomerID.,
MostRecentDate = &MostRecentDate.,
NPurchase = &NPurchase.,
TotPurchaseAmt = &TotPurchaseAmt.,
N_R_Grp = &N_R_Grp.,
N_F_Grp = &N_F_Grp.,
N_M_Grp = &N_M_Grp.,
OutData = &RFM_WorkLibrary..&OutputScore.,
RightBinBoundData = &RBBData.,
Recency_Score = &Recency_Score.,
Frequency_Score = &Frequency_Score.,
Monetary_Score = &Monetary_Score.);
%end;
%end;
%if (&RFM_ErrCode. eq 0) %then %do;
/* Find out the actual number of groups formed */
data &RFM_WorkLibrary..&OutputScore.;
set &RFM_WorkLibrary..&OutputScore. end = eof;
retain min_R_Score &N_R_Grp.;
retain min_F_Score &N_F_Grp.;
retain min_M_Score &N_M_Grp.;
if (&Recency_Score. lt min_R_Score) then min_R_Score = &Recency_Score;
if (&Frequency_Score. lt min_F_Score) then min_F_Score = &Frequency_Score;
if (&Monetary_Score. lt min_M_Score) then min_M_Score = &Monetary_Score;
if (eof eq 1) then
do;
call symput('min_R_Score', strip(put(min_R_Score, best32.)));
call symput('min_F_Score', strip(put(min_F_Score, best32.)));
call symput('min_M_Score', strip(put(min_R_Score, best32.)));
call symput('actual_N_R_Grp', strip(put((&N_R_Grp. - min_R_Score + 1), best32.)));
call symput('actual_N_F_Grp', strip(put((&N_F_Grp. - min_F_Score + 1), best32.)));
call symput('actual_N_M_Grp', strip(put((&N_M_Grp. - min_M_Score + 1), best32.)));
end;
keep &CustomerID. &Recency_Score. &Frequency_Score. &Monetary_Score.;
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&RFM_ErrCode. eq 0) %then %do;
proc sort data = &RFM_WorkLibrary..&OutDataCopy.;
by &CustomerID.;
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&RFM_ErrCode. eq 0) %then %do;
proc sort data = &RFM_WorkLibrary..&OutputScore.;
by &CustomerID.;
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&RFM_ErrCode. eq 0) %then %do;
/* Merge the calculated scores back to the customer data for output */
data &RFM_WorkLibrary..&OutDataCopy.;
merge &RFM_WorkLibrary..&OutDataCopy. (in = in1)
&OutputScore (in = in2) end = eof;
by &CustomerID.;
retain RFM_Min_Score &RFM_Max_Score.;
%if (&Mode. eq T) %then %do;
label &CustomerID. = "&CustomerIDLabel.";
label &MostRecentDate. = "&MostRecentDateLabel.";
label &NPurchase. = "&NPurchaseLabel.";
label &TotPurchaseAmt. = "&TotPurchaseAmtLabel.";
%end;
label &Recency_score. = %sysfunc(sasmsg(&RFM_MsgFile., Recency_Score_Label, NOQUOTE, &min_R_Score., &N_R_Grp.)) ;
label &Frequency_Score. = %sysfunc(sasmsg(&RFM_MsgFile., Frequency_Score_Label, NOQUOTE, &min_F_Score., &N_F_Grp.)) ;
label &Monetary_Score. = %sysfunc(sasmsg(&RFM_MsgFile., Monetary_Score_Label, NOQUOTE, &min_M_Score., &N_M_Grp.)) ;
label &RFM_Score. = %sysfunc(sasmsg(&RFM_MsgFile., RFM_Score_Label, NOQUOTE)) ;
if (in1) then do;
if (in2) then do;
&RFM_Score. = 100 * &Recency_score. + 10 * &Frequency_Score. + &Monetary_Score.;
if (&RFM_Score. lt RFM_Min_Score) then RFM_Min_Score = &RFM_Score.;
end;
output;
end;
if (eof eq 1) then
do;
call symput('RFM_Min_Score', strip(put(RFM_Min_Score, best32.)));
%if (%length(&RBBData.) gt 0) %then %do;
call symput('RFM_Score_vname', strip(vname(&RFM_Score.)));
%end;
end;
drop RFM_Min_Score;
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&RFM_ErrCode. eq 0 and %length(&RBBData.) gt 0) %then %do;
/* Store the RFM Score variable name to the dataset label */
data &RightBinBoundData. (label = "&RFM_Score_vname.");
set &RBBData.;
label SCORE_TYPE = %sysfunc(sasmsg(&RFM_MsgFile., SCORE_TYPE_Label, NOQUOTE)) ;
label &Recency_score. = %sysfunc(sasmsg(&RFM_MsgFile., Recency_Score_Label, NOQUOTE, &min_R_Score., &N_R_Grp.)) ;
label &Frequency_Score. = %sysfunc(sasmsg(&RFM_MsgFile., Frequency_Score_Label, NOQUOTE, &min_F_Score., &N_F_Grp.)) ;
label &Monetary_Score. = %sysfunc(sasmsg(&RFM_MsgFile., Monetary_Score_Label, NOQUOTE, &min_M_Score., &N_M_Grp.)) ;
%if (&Mode. eq T) %then %do;
label &MostRecentDate. = "&MostRecentDateLabel.";
label &NPurchase. = "&NPurchaseLabel.";
label &TotPurchaseAmt. = "&TotPurchaseAmtLabel.";
%end;
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&RFM_ErrCode. eq 0) %then %do;
%let min_r_fmt = %sysfunc(sasmsg(&RFM_MsgFile., Recency_fmt_LR, NOQUOTE, &min_R_Score.));
%let min_f_fmt = %sysfunc(sasmsg(&RFM_MsgFile., Frequency_fmt_LF, NOQUOTE, &min_F_Score.));
%let min_m_fmt = %sysfunc(sasmsg(&RFM_MsgFile., Monetary_fmt_LA, NOQUOTE, &min_M_Score.));
%let min_rfm_fmt = %sysfunc(sasmsg(&RFM_MsgFile., RFM_fmt_min_score, NOQUOTE, &RFM_Min_Score.));
%let max_r_fmt = %sysfunc(sasmsg(&RFM_MsgFile., Recency_fmt_MR, NOQUOTE, &N_R_Grp.));
%let max_f_fmt = %sysfunc(sasmsg(&RFM_MsgFile., Frequency_fmt_MF, NOQUOTE, &N_F_Grp.));
%let max_m_fmt = %sysfunc(sasmsg(&RFM_MsgFile., Monetary_fmt_HA, NOQUOTE, &N_M_Grp.));
%let max_rfm_fmt = %sysfunc(sasmsg(&RFM_MsgFile., RFM_fmt_max_score, NOQUOTE, &RFM_Max_Score.));
proc format maxlablen = 60;
value Recency_fmt
%if (&min_R_Score. lt &N_R_Grp.) %then &min_R_Score. = "&min_r_fmt.";
&N_R_Grp. = "&max_r_fmt.";
value Frequency_fmt
%if (&min_F_Score. lt &N_F_Grp.) %then &min_F_Score. = "&min_f_fmt.";
&N_F_Grp. = "&max_f_fmt.";
value Monetary_fmt
%if (&min_M_Score. lt &N_M_Grp.) %then &min_M_Score. = "&min_m_fmt.";
&N_M_Grp. = "&max_M_fmt.";
value RFM_fmt
%if (&RFM_Min_Score. lt &RFM_Max_Score.) %then &RFM_Min_Score. = "&min_rfm_fmt.";
&RFM_Max_Score. = "&max_rfm_fmt.";
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&RFM_ErrCode. eq 0) %then %do;
/* Capture the existing title1, footnote1, and footnote2 */
%if (&BinTable. eq Y or &BinChart. eq Y or &MonetizationMap. eq Y) %then %do;
data _NULL_;
set sashelp.vtitle;
if (type eq 'T' and number eq 1) then call symput('StoreTitle', trim(text));
else if (type eq 'F' and number eq 1) then call symput('StoreFN1', trim(text));
else if (type eq 'F' and number eq 2) then call symput('StoreFN2', trim(text));
run;
%end;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&RFM_ErrCode. eq 0 and &BinTable. eq Y) %then %do;
/* Display a table of bin counts and medians of analysis variables */
%let allTxt = %sysfunc(sasmsg(&RFM_MsgFile., RFMSummaryAll, NOQUOTE));
%let nTxt = %sysfunc(sasmsg(&RFM_MsgFile., RFMSummaryN, NOQUOTE));
%let pctnTxt = %sysfunc(sasmsg(&RFM_MsgFile., RFMSummaryPctn, NOQUOTE));
%let medianTxt = %sysfunc(sasmsg(&RFM_MsgFile., RFMSummaryMedian, NOQUOTE));
%let titleTxt = %sysfunc(sasmsg(&RFM_MsgFile., RFMSummaryTitle, NOQUOTE));
proc tabulate data = &RFM_WorkLibrary..&OutDataCopy.;
class &RFM_Score. / descending;
var &MostRecentDate. &NPurchase. &TotPurchaseAmt.;
table ALL = "&allTxt" &RFM_Score.,
n = "&nTxt."*f=nlnum32.
pctn = "&pctnTxt."*f=nlnum32.2
(median = "&medianTxt.") * (&MostRecentDate.*f=&MostRecentDate_fmt. &NPurchase.*f=&NPurchase_fmt. &TotPurchaseAmt.*f=&TotPurchaseAmt_fmt.)
/ nocellmerge;
format &RFM_Score. RFM_fmt.;
title "&titleTxt";
footnote1 "&fn1.";
footnote2 "&fn2.";
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&BinChart. eq Y) %then %do;
/* Display a series of graphs of bin counts and box-plots of analysis variables */
ods graphics on / width = 8in height = 8in;
%let PanelSpacing = 4;
%if (&RFM_ErrCode. eq 0) %then %do;
proc sgplot data = &RFM_WorkLibrary..&OutDataCopy.;
vbar &Recency_score. / stat = freq;
xaxis label = "&Recency_Label.";
yaxis label = "&RecCount_Label." grid offsetmin = 0;
format &Recency_score. Recency_fmt.;
title "&Title_Recency.";
footnote1 "&fn1.";
footnote2 "&fn2.";
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&RFM_ErrCode. eq 0) %then %do;
proc sgplot data = &RFM_WorkLibrary..&OutDataCopy.;
vbox &MostRecentDate. / category = &Recency_score.;
xaxis label = "&Recency_Label.";
yaxis grid;
format &MostRecentDate. &MostRecentDate_fmt.;
format &Recency_score. Recency_fmt.;
title "&Title_Most_Recent_Date.";
footnote1 "&fn1.";
footnote2 "&fn2.";
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&RFM_ErrCode. eq 0) %then %do;
proc sgpanel data = &RFM_WorkLibrary..&OutDataCopy.;
panelby &Recency_score.
/ rows = &actual_N_R_Grp.
start = bottomleft rowheaderpos = right layout = rowlattice novarname spacing = &PanelSpacing.;
vbar &Frequency_Score. / stat = freq;
rowaxis label = "&RecCount_Label." grid type = linear offsetmin = 0;
colaxis label = "&Frequency_Label.";
format &Recency_score. Recency_fmt.;
format &Frequency_Score. Frequency_fmt.;
title "&Title_Frequency_by_R.";
footnote1 "&fn1.";
footnote2 "&fn2.";
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&RFM_ErrCode. eq 0) %then %do;
proc sgpanel data = &RFM_WorkLibrary..&OutDataCopy.;
panelby &Recency_score.
/ rows = &actual_N_R_Grp.
start = bottomleft rowheaderpos = right layout = rowlattice novarname spacing = &PanelSpacing.;
hbox &NPurchase. / category = &Frequency_Score.;
colaxis grid type = log;
rowaxis reverse;
format &Recency_score. Recency_fmt.;
format &Frequency_Score. Frequency_fmt.;
format &NPurchase. &NPurchase_fmt.;
title "&Title_NPurchase_by_R.";
footnote1 "&fn1.";
footnote2 "&fn2.";
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&RFM_ErrCode. eq 0) %then %do;
proc sgpanel data = &RFM_WorkLibrary..&OutDataCopy.;
panelby &Frequency_Score. &Recency_score.
/ rows = &actual_N_R_Grp. columns = &actual_N_F_Grp.
start = bottomleft rowheaderpos = right colheaderpos = top layout = lattice novarname spacing = &PanelSpacing.;
vbar &Monetary_Score. / stat = freq;
rowaxis label = "&RecCount_Label." grid offsetmin = 0;
format &Recency_score. Recency_fmt.;
format &Frequency_Score. Frequency_fmt.;
title "&Title_Monetary_by_RF.";
footnote1 "&fn1.";
footnote2 "&fn2.";
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&RFM_ErrCode. eq 0) %then %do;
proc sgpanel data = &RFM_WorkLibrary..&OutDataCopy.;
panelby &Frequency_Score. &Recency_score.
/ rows = &actual_N_R_Grp. columns = &actual_N_F_Grp.
start = bottomleft rowheaderpos = right colheaderpos = top layout = lattice novarname spacing = &PanelSpacing.;
hbox &TotPurchaseAmt. / category = &Monetary_Score.;
colaxis grid type = log;
rowaxis reverse;
format &Recency_score. recency_fmt.;
format &Frequency_Score. frequency_fmt.;
format &TotPurchaseAmt. &TotPurchaseAmt_fmt.;
title "&Title_TotPurchaseAmt_by_RF.";
footnote1 "&fn1.";
footnote2 "&fn2.";
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%end;
%if (&MonetizationMap. eq Y) %then %do;
ods graphics on / width = 8in height = 8in;
%if (&RFM_ErrCode. eq 0) %then %do;
%let fn0 = %sysfunc(sasmsg(&RFM_MsgFile., monetizationMapFootNote, NOQUOTE, &TotPurchaseAmtLabel., &Recency_Label., &Frequency_Label.));
title "&Title_Monetization.";
proc template;
define statgraph MonetizationMap;
begingraph / designwidth=%eval(120 * &actual_N_R_Grp.) designheight=%eval(120 * &actual_N_F_Grp.);
entrytitle "&Title_Monetization.";
layout overlay / aspectratio = 1 border = false
xaxisopts = (type = linear linearopts = (integer=true tickvaluesequence = (start = &min_R_Score. end = &N_R_Grp. increment = 1))
label = "&Recency_Label.")
yaxisopts = (type = linear linearopts = (integer=true tickvaluesequence = (start = &min_F_Score. end = &N_F_Grp. increment = 1))
label = "&Frequency_Label.");
scatterplot x = &Recency_score. y = &Frequency_Score.
/ markercolorgradient = &TotPurchaseAmt.
markerattrs = (symbol = squarefilled size = 80)
colormodel = threecolorramp
name = 's2';
continuouslegend 's2' / orient = vertical location = outside valign = center halign = right valuecounthint = 10;
endlayout;
entryfootnote "&fn0.";
entryfootnote "&fn1.";
entryfootnote "&fn2.";
endgraph;
end;
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&RFM_ErrCode. eq 0) %then %do;
proc summary data= &RFM_WorkLibrary..&OutDataCopy. nway;
class &Recency_score. &Frequency_Score.;
var &TotPurchaseAmt.;
output out = &RFM_WorkLibrary..&OutputScore. (drop = _TYPE_ _FREQ_)
median(&TotPurchaseAmt.) = &TotPurchaseAmt.;
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&RFM_ErrCode. eq 0) %then %do;
proc sgrender data = &RFM_WorkLibrary..&OutputScore. template = MonetizationMap;
format &Recency_score. recency_fmt.;
format &Frequency_Score. frequency_fmt.;
format &TotPurchaseAmt. &TotPurchaseAmt_fmt.;
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
/* Restore stored title, footnotes 1 and 2 */
%if (&RFM_ErrCode. eq 0 and (&BinTable. eq Y or &BinChart. eq Y or &MonetizationMap. eq Y)) %then %do;
title
%if (%length(&StoreTitle.) gt 0) %then "&StoreTitle.";
;
footnote1
%if (%length(&StoreFN1.) gt 0) %then "&StoreFN1.";
;
footnote2
%if (%length(&StoreFN2.) gt 0) %then "&StoreFN2.";
;
%end;
/* Display warnings */
%if (&actual_N_R_Grp. lt &N_R_Grp.) %then %put %sysfunc(sasmsg(&RFM_MsgFile., recency_groups_formed_warning, NOQUOTE, &sysmacroname., &actual_N_R_Grp., &N_R_Grp.));
%if (&actual_N_F_Grp. lt &N_F_Grp.) %then %put %sysfunc(sasmsg(&RFM_MsgFile., frequency_groups_formed_warning, NOQUOTE, &sysmacroname., &actual_N_F_Grp., &N_F_Grp.));
%if (&actual_N_M_Grp. lt &N_M_Grp.) %then %put %sysfunc(sasmsg(&RFM_MsgFile., monetary_groups_formed_warning, NOQUOTE, &sysmacroname., &actual_N_M_Grp., &N_M_Grp.));
%end;
/* Save output data */
%if (&RFM_ErrCode. eq 0) %then %do;
data &OutData.;
set &RFM_WorkLibrary..&OutDataCopy.;
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
/* Clean up temporary datasets */
proc datasets lib = &RFM_WorkLibrary. nolist noprint nowarn;
delete &InDataConents. &InDataCopy. &OutDataCopy. &OutputScore.;
quit;
/* Display errors and warnings */
%if (&RFM_ErrCode. eq 0) %then %do;
%if (%length(&RightBinBoundData.) gt 0 and &RFMvarV7OK. eq 0) %then %put %sysfunc(sasmsg(&RFM_MsgFile., RFM_variable_V7_incompatible, NOQUOTE, &sysmacroname.));
%end;
%else %if (&RFM_ErrCode. eq 1) %then %put %sysfunc(sasmsg(&RFM_MsgFile., unexpected_analysis_mode, NOQUOTE, &sysmacroname.));
%else %if (&RFM_ErrCode. eq 2) %then %put %sysfunc(sasmsg(&RFM_MsgFile., input_dataset_not_specified, NOQUOTE, &sysmacroname.));
%else %if (&RFM_ErrCode. eq 3) %then %put %sysfunc(sasmsg(&RFM_MsgFile., input_dataset_not_found, NOQUOTE, &sysmacroname.));
%else %if (&RFM_ErrCode. eq 4) %then %put %sysfunc(sasmsg(&RFM_MsgFile., customer_id_variable_not_found, NOQUOTE, &sysmacroname.));
%else %if (&RFM_ErrCode. eq 5) %then %put %sysfunc(sasmsg(&RFM_MsgFile., recency_group_int_not_between_1_and_10, NOQUOTE, &sysmacroname.));
%else %if (&RFM_ErrCode. eq 6) %then %put %sysfunc(sasmsg(&RFM_MsgFile., frequency_group_int_not_between_1_and_10, NOQUOTE, &sysmacroname.));
%else %if (&RFM_ErrCode. eq 7) %then %put %sysfunc(sasmsg(&RFM_MsgFile., monetary_group_int_not_between_1_and_10, NOQUOTE, &sysmacroname.));
%else %if (&RFM_ErrCode. eq 😎 %then %put %sysfunc(sasmsg(&RFM_MsgFile., unrecognized_binning, NOQUOTE, &sysmacroname.));
%else %if (&RFM_ErrCode. eq 9) %then %put %sysfunc(sasmsg(&RFM_MsgFile., outdsn_not_specified, NOQUOTE, &sysmacroname.));
%else %if (&RFM_ErrCode. eq 10) %then %put %sysfunc(sasmsg(&RFM_MsgFile., no_complete_nonmissing_rec, NOQUOTE, &sysmacroname., %quote(&MostRecentDateLabel.), %quote(&NPurchaseLabel.), %quote(&TotPurchaseAmtLabel.)));
%else %if (&RFM_ErrCode. eq 11) %then %put %sysfunc(sasmsg(&RFM_MsgFile., transactional_input_not_specified, NOQUOTE, &sysmacroname.));
%else %if (&RFM_ErrCode. eq 12) %then %put %sysfunc(sasmsg(&RFM_MsgFile., customer_input_not_specified, NOQUOTE, &sysmacroname.));
%else %if (&RFM_ErrCode. eq 13) %then %put %sysfunc(sasmsg(&RFM_MsgFile., common_errmsg_proc_error, NOQUOTE, &sysmacroname.));
/* Remove the global macro variables that are defined in this macro */
%symdel RFM_ErrCode RFM_WorkLibrary;
/* Report the elapsed time of this macro */
%local TimeElapsed;
%let TimeElapsed = %sysevalf(%sysfunc(datetime()) - &TimeStart.);
%let TimeElapsed = %sysfunc(putn(&TimeElapsed., time12.2-l));
%put ;
%put NOTE: &MyName. Elapsed Time = &TimeElapsed.;
%mend EM_RFM_CONTROL;
/* The macro EM_RFM_TRAN_2_CUST creates the customer data from the input transactional data.
Recency is the maximum of PurchaseDate assuming it is a date variable.
Frequency is the number of complete records.
Monetary is the summarized function value of complete records. Available summary
function are: (1) SUM, (2) MEAN, (3) MEDIAN, and (4) MAX. */
%macro EM_RFM_TRAN_2_CUST
(
InData =WORK.QUERY_FOR_MEANSUMMARYSTATS, /* Input transactional data */
CustomerID =customer_num, /* Customer ID */
FreqVar =, /* Optional replication frequency variable */
PurchaseDate =, /* Purchase date */
PurchaseAmt =, /* Purchase amount */
SetMiss =, /* Y = If a customer ID has missing purchase date or missing purchase amount,
then set all results to missing regardless of any complete records */
SummaryFunc =, /* Summary function: SUM, MEAN, MEDIAN, or MAX */
OutData =, /* (Out) Output customer level data */
MostRecentDate =, /* (Out) Most recent purchase date */
NPurchase =, /* (Out) Number of purchases */
TotPurchaseAmt = /* (Out) Total amount of purchases */
) ;
%local TEMPFILE1; /* Name of temporary dataset 1 */
%local TEMPFILE2; /* Name of temporary dataset 2 */
%local TEMPFILE3; /* Name of temporary dataset 3 */
%let TEMPFILE1 = _RFM_TEMP1_;
%let TEMPFILE2 = _RFM_TEMP2_;
%let TEMPFILE3 = _RFM_TEMP3_;
/* Separate input transactional data */
%if (&RFM_ErrCode. eq 0) %then %do;
data &RFM_WorkLibrary..&TEMPFILE2.
&RFM_WorkLibrary..&TEMPFILE3.;
set &InData.;
if (missing(&PurchaseDate.) eq 0 and missing(&PurchaseAmt.) eq 0) then output &RFM_WorkLibrary..&TEMPFILE2.;
else output &RFM_WorkLibrary..&TEMPFILE3.;
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
/* Calculations based on complete records within a customer ID */
%if (&RFM_ErrCode. eq 0) %then %do;
proc summary data = &RFM_WorkLibrary..&TEMPFILE2. nway;
class &CustomerID.;
var &PurchaseDate. &PurchaseAmt.;
%if (%length(&FreqVar.) gt 0) %then %do;
freq &FreqVar.;
%end;
output out = &OutData. (drop = _TYPE_ rename = (_FREQ_ = &NPurchase.))
max( &PurchaseDate. ) = &MostRecentDate.
&SummaryFunc.( &PurchaseAmt. ) = &TotPurchaseAmt.;
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&SetMiss. eq Y) %then %do;
/* Finds customer ID that has missing purchase date or missing purchase amount.
The unique customer IDs are written to a data set in ascending order. */
%if (&RFM_ErrCode. eq 0) %then %do;
proc sql;
create table &RFM_WorkLibrary..&TEMPFILE1. as
select distinct S0.&CustomerID.
from &RFM_WorkLibrary..&TEMPFILE3. as S0
order by S0.&CustomerID.;
;
quit;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&RFM_ErrCode. eq 0) %then %do;
proc sort data = &OutData.;
by &CustomerID.;
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
/* Override the MostRecentDate, the NPurchase, and the TotPurchaseAmt if the customer ID
has missing values. */
%if (&RFM_ErrCode. eq 0) %then %do;
data &OutData.;
merge &OutData. (IN = In0)
&RFM_WorkLibrary..&TEMPFILE1. (IN = In1);
by &CustomerID.;
if (In0 and In1) then
do;
call missing(&MostRecentDate., &NPurchase., &TotPurchaseAmt.);
end;
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%end;
proc datasets lib = &RFM_WorkLibrary. nolist noprint nowarn;
delete &TEMPFILE1. &TEMPFILE2. &TEMPFILE3.;
quit;
%mend EM_RFM_TRAN_2_CUST;
/* The macro EM_RFM_SET_SCORE divides the key variable within each distinct values of the BY variables
into groups such that the accumulated replication weights within each group is as largest as
possible without exceeding the threshold value. */
/* (S0928934) Rename the BY variables on entry to avoid problems in using FIRST. operator on name literals.
The BY variables are restored to their original names on exit */
%macro EM_RFM_SET_SCORE
(
InData =, /* Input data */
ByVar1 =, /* First BY variable (data is sorted in ASCENDING order first by this variable)*/
ByVar2 =, /* Second BY variable (data is sorted in ASCENDING order second by this variable) */
KeyVar =, /* The key variable (the data is sorted in DESCENDING order last by this key) */
nGroup =, /* Number of groups to be formed by binning the key variable */
Threshold =, /* The threshold used in deciding whether to increase score by 1 */
nCount =, /* The replication weight variable (number of records represented by this row) */
OutData =, /* (Out) Output data */
RightBinBoundData =, /* (Out) Optional output bin boundary data */
ScoreVar = /* (Out) The calculated score of the key variable */
) ;
%if (&RFM_ErrCode. eq 0) %then %do;
data &OutData.
%if (%length(&RightBinBoundData.) gt 0) %then &RightBinBoundData.;
;
set &InData.
%if (&ByVar1. ne ) %then %do;
(rename = (&ByVar1. = ByVar1
%if &ByVar2. ne %then %do;
&ByVar2. = ByVar2
%end;
)
)
%end;
;
%if (&ByVar1. ne ) %then %do;
by ByVar1
%if (&ByVar2. ne ) %then %do;
ByVar2
%end;
;
%end;
retain CumCount 0; /* Cumulative count */
retain iGroup 0; /* Index of formed group */
retain &ScoreVar. %eval(&nGroup. + 1); /* The score starts from highest value */
%if (&ByVar2. ne ) %then %do;
if (first.ByVar2) then
do;
%end;
%else %if (&ByVar1. ne ) %then %do;
if (first.ByVar1) then
do;
%end;
%if (&ByVar1. ne or &ByVar2. ne ) %then %do;
CumCount = 0;
iGroup = 0;
&ScoreVar. = %eval(&nGroup. + 1);
end;
%end;
/* Move to another tile group if the following criterion is satisfied.
If the threshold falls on a particular variable value, then that value goes to the lower
tile if majority frequency weight is less than the threshold, otherwise go to the upper tile */
CumCount + &nCount.;
if (CumCount GT (iGroup * &Threshold. + 0.5 * &nCount.)) then
do;
iGroup + 1;
&ScoreVar. = &ScoreVar. - 1;
%if (%length(&RightBinBoundData.) gt 0) %then %do;
output &RightBinBoundData.;
%end;
end;
output &OutData.;
keep &KeyVar. &ScoreVar. &nCount.;
%if (&ByVar1. ne ) %then %do;
keep ByVar1;
rename ByVar1 = &ByVar1.;
%end;
%if (&ByVar2. ne ) %then %do;
keep ByVar2;
rename ByVar2 = &ByVar2.;
%end;
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&RFM_ErrCode. eq 0 and %length(&RightBinBoundData.) gt 0) %then %do;
/* Override the right boundary of the highest index group with missing because the right boundary should be positive infinity */
data &RightBinBoundData.;
set &RightBinBoundData. (drop = &nCount.);
if (&ScoreVar. eq &nGroup.) then &KeyVar. = .;
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%mend EM_RFM_SET_SCORE;
/* The macro EM_RFM_NESTED_RANK calculates the scores in the following sequence:
1. Rank most recent purchase date into desired number of groups,
2. Rank number of purchases into desired number of groups within each Recency score,
3. Rank total amount of purchases into desired number of groups within each Recency by
Frequency scores. */
%macro EM_RFM_NESTED_RANK
(
InData =WORK.QUERY_FOR_MEANSUMMARYSTATS, /* Input customer data */
CustomerID =customer_num, /* Customer ID */
MostRecentDate =MAX_of_Order_date, /* Most recent purchase date */
NPurchase =_FREQ_, /* Number of purchases */
TotPurchaseAmt =Ext_Price_Sum, /* Total amount of purchases */
N_R_Grp =, /* Number of Most_Recent_Purchase_Date groups */
N_F_Grp =, /* Number of Frequency groups */
N_M_Grp =, /* Number of Monetary groups */
OutData =work.rfm_data, /* (Out) Output customer level data */
RightBinBoundData =, /* (Out) Optional output bin boundary data */
Recency_Score =recency_score, /* (Out) Recency score (1=least recent, N_R_Grp=most recent) */
Frequency_Score =frequency_score, /* (Out) Frequency score (1=least frequent, N_F_Grp=most frequent) */
Monetary_Score = monetary_score /* (Out) Monetary score (1=least amount, N_M_Grp=most amount) */
) ;
%local BIN_F; /* Name of temporary dataset for storing Frequency bin boundaries */
%local BIN_M; /* Name of temporary dataset for storing Monetary bin boundaries */
%local BIN_R; /* Name of temporary dataset for storing Recency bin boundaries */
%local TEMPFILE1; /* Name of temporary dataset 1 */
%local TEMPFILE2; /* Name of temporary dataset 2 */
%local TEMPFILE3; /* Name of temporary dataset 3 */
%let TEMPFILE1 = _RFM_TEMP1_;
%let TEMPFILE2 = _RFM_TEMP2_;
%let TEMPFILE3 = _RFM_TEMP3_;
%if (%length(&RightBinBoundData.) gt 0) %then %do;
%let BIN_R = _RFM_BIN_R;
%let BIN_F = _RFM_BIN_F;
%let BIN_M = _RFM_BIN_M;
%end;
%else %do;
%let BIN_R =;
%let BIN_F =;
%let BIN_M =;
%end;
/* Step1: Rank the Recency */
/* 1. Obtain record count for each Most Recent Date so that identical dates have identical recency scores
2. Calculate the threshold number of cases that should be in each recency group */
%if (&RFM_ErrCode. eq 0) %then %do;
proc sql;
create table &RFM_WorkLibrary..&TEMPFILE2. as
select distinct S1.&MostRecentDate.,
count(*) as nCount_R,
(select count(*) / &N_R_Grp.
from &InData.
) as Threshold
from &InData. as S1
group by S1.&MostRecentDate.
order by S1.&MostRecentDate. desc
;
quit;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
/* 3. Determine recency score */
%if (&RFM_ErrCode. eq 0) %then %do;
%EM_RFM_SET_SCORE (InData = &RFM_WorkLibrary..&TEMPFILE2.,
ByVar1 =,
ByVar2 =,
KeyVar = &MostRecentDate.,
nGroup = &N_R_Grp.,
Threshold = Threshold,
nCount = nCount_R,
OutData = &RFM_WorkLibrary..&TEMPFILE3.,
RightBinBoundData = &BIN_R.,
ScoreVar = &Recency_Score.);
%end;
/* 4. Merge recency score back to the customer data */
%if (&RFM_ErrCode. eq 0) %then %do;
proc sql;
create table &OutData. as
select S1.*,
S3.&Recency_Score.
from &InData. as S1 left join
&RFM_WorkLibrary..&TEMPFILE3. as S3
on S1.&MostRecentDate. = S3.&MostRecentDate.
;
quit;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
/* Step2: Rank the Frequency */
/* 1. Obtain record count for each value combination of recency score and number of purchase so that
identical numbers of purchase have identical frequency scores
2. Calculate the threshold number of cases that should be in each recency by frequency group */
%if (&RFM_ErrCode. eq 0) %then %do;
proc sql;
create table &RFM_WorkLibrary..&TEMPFILE2. as
select distinct S0.&Recency_Score., S0.&NPurchase.,
count(*) as nCount_RF,
(select sum(S3.nCount_R) / &N_F_Grp.
from &RFM_WorkLibrary..&TEMPFILE3. as S3
where S3.&Recency_Score. = S0.&Recency_Score.
) as Threshold
from &OutData. as S0
group by S0.&Recency_Score., S0.&NPurchase.
order by S0.&Recency_Score., S0.&NPurchase. desc
;
quit;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
/* 3. Determine frequency score within each recency score */
%if (&RFM_ErrCode. eq 0) %then %do;
%EM_RFM_SET_SCORE (InData = &RFM_WorkLibrary..&TEMPFILE2.,
ByVar1 = &Recency_Score.,
ByVar2 =,
KeyVar = &NPurchase.,
nGroup = &N_F_Grp.,
Threshold = Threshold,
nCount = nCount_RF,
OutData = &RFM_WorkLibrary..&TEMPFILE3.,
RightBinBoundData = &BIN_F.,
ScoreVar = &Frequency_Score.);
%end;
/* 4. Merge frequency score back to the customer data */
%if (&RFM_ErrCode. eq 0) %then %do;
proc sql;
create table &RFM_WorkLibrary..&TEMPFILE1. as
select S0.*,
S3.&Frequency_Score.
from &OutData. as S0 left join
&RFM_WorkLibrary..&TEMPFILE3. as S3
on S0.&Recency_Score. = S3.&Recency_Score. and
S0.&NPurchase. = S3.&NPurchase.
;
quit;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
/* Step3: Rank the Monetary */
/* 1. Obtain record count for each value combination of recency score, frequency score and amount of
purchase so that identical amount of purchase have identical monetary scores
2. Calculate the threshold number of cases that should be in each recency by frequency by monetary group */
%if (&RFM_ErrCode. eq 0) %then %do;
proc sql;
create table &RFM_WorkLibrary..&TEMPFILE2. as
select distinct S1.&Recency_Score., S1.&Frequency_Score., S1.&TotPurchaseAmt.,
count(*) as nCount_RFM,
(select sum(S3.nCount_RF) / &N_M_Grp.
from &RFM_WorkLibrary..&TEMPFILE3. as S3
where S3.&Recency_Score. = S1.&Recency_Score. and
S3.&Frequency_Score. = S1.&Frequency_Score.
) as Threshold
from &RFM_WorkLibrary..&TEMPFILE1. as S1
group by S1.&Recency_Score., S1.&Frequency_Score., S1.&TotPurchaseAmt.
order by S1.&Recency_Score., S1.&Frequency_Score., S1.&TotPurchaseAmt. desc
;
quit;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
/* 3. Determine monetary score within each value combination of recency score and frequency score */
%if (&RFM_ErrCode. eq 0) %then %do;
%EM_RFM_SET_SCORE (InData = &RFM_WorkLibrary..&TEMPFILE2.,
ByVar1 = &Recency_Score.,
ByVar2 = &Frequency_Score.,
KeyVar = &TotPurchaseAmt.,
nGroup = &N_M_Grp.,
Threshold = Threshold,
nCount = nCount_RFM,
OutData = &RFM_WorkLibrary..&TEMPFILE3.,
RightBinBoundData = &BIN_M.,
ScoreVar = &Monetary_Score.);
%end;
/* 4. Merge monetary score back to the output data */
%if (&RFM_ErrCode. eq 0) %then %do;
proc sql;
create table &OutData. as
select S1.*,
S3.&Monetary_Score.
from &RFM_WorkLibrary..&TEMPFILE1. as S1 left join
&RFM_WorkLibrary..&TEMPFILE3. as S3
on S1.&Recency_Score. = S3.&Recency_Score. and
S1.&Frequency_Score. = S3.&Frequency_Score. and
S1.&TotPurchaseAmt. = S3.&TotPurchaseAmt.
;
quit;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
/* Save right boundaries of bins into a data */
%if (%length(&RightBinBoundData.) gt 0) %then %do;
%if (&RFM_ErrCode. eq 0) %then %do;
proc sort data = &BIN_R.;
by &Recency_Score.;
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&RFM_ErrCode. eq 0) %then %do;
proc sort data = &BIN_F.;
by &Recency_Score. &Frequency_Score.;
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&RFM_ErrCode. eq 0) %then %do;
proc sort data = &BIN_M.;
by &Recency_Score. &Frequency_Score. &Monetary_Score.;
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&RFM_ErrCode. eq 0) %then %do;
data &RightBinBoundData.;
retain SCORE_TYPE &Recency_Score. &Frequency_Score. &Monetary_Score.
&MostRecentDate. &NPurchase. &TotPurchaseAmt.;
set &BIN_R. (in = inR)
&BIN_F. (in = inF)
&BIN_M. (in = inM);
/* Set the SCORE_TYPE values that do not need to be translated */
length SCORE_TYPE $ 9;
if (inR) then SCORE_TYPE = 'RECENCY ';
else if (inF) then SCORE_TYPE = 'FREQUENCY';
else if (inM) then SCORE_TYPE = 'MONETARY ';
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%end;
/* Remove the temporary files */
proc datasets lib = &RFM_WorkLibrary. nolist noprint nowarn;
delete &TEMPFILE1. &TEMPFILE2. &TEMPFILE3. &BIN_R. &BIN_F. &BIN_M.;
quit;
%mend EM_RFM_NESTED_RANK;
/* The macro EM_RFM_INDEPENDENT_RANK calculates the scores in the following sequence:
1. Rank most recent purchase date into desired number of groups,
2. Rank number of purchases into desired number of groups,
3. Rank total amount of purchases into desired number of groups. */
%macro EM_RFM_INDEPENDENT_RANK
(
InData =WORK.QUERY_FOR_MEANSUMMARYSTATS, /* Input customer data */
CustomerID =Customer_num, /* Customer ID */
MostRecentDate =MAX_of_Order_date, /* Most recent purchase date */
NPurchase =_FREQ_, /* Number of purchases */
TotPurchaseAmt =Ext_Price_Sum, /* Total amount of purchases */
N_R_Grp =, /* Number of Most_Recent_Purchase_Date groups */
N_F_Grp =, /* Number of Frequency groups */
N_M_Grp =, /* Number of Monetary groups */
OutData =work.rfm_data, /* (Out) Output customer level data */
RightBinBoundData =, /* (Out) Optional output bin boundary data */
Recency_Score =Recency_score, /* (Out) Recency score (1=least recent, N_R_Grp=most recent) */
Frequency_Score =frequency_score, /* (Out) Frequency score (1=least frequent, N_F_Grp=most frequent) */
Monetary_Score =monetary_score /* (Out) Monetary score (1=least amount, N_M_Grp=most amount) */
) ;
%local BIN_F; /* Name of temporary dataset for storing Frequency bin boundaries */
%local BIN_M; /* Name of temporary dataset for storing Monetary bin boundaries */
%local BIN_R; /* Name of temporary dataset for storing Recency bin boundaries */
%local TEMPFILE1; /* Name of temporary dataset 1 */
%local TEMPFILE2; /* Name of temporary dataset 2 */
%local TEMPFILE3; /* Name of temporary dataset 3 */
%let TEMPFILE1 = _RFM_TEMP1_;
%let TEMPFILE2 = _RFM_TEMP2_;
%let TEMPFILE3 = _RFM_TEMP3_;
%if (%length(&RightBinBoundData.) gt 0) %then %do;
%let BIN_R = _RFM_BIN_R;
%let BIN_F = _RFM_BIN_F;
%let BIN_M = _RFM_BIN_M;
%end;
%else %do;
%let BIN_R =;
%let BIN_F =;
%let BIN_M =;
%end;
/* Step1: Rank the Recency */
/* 1. Obtain record count for each Most Recent Date so that identical dates have identical recency scores
2. Calculate the threshold number of cases that should be in each recency group */
%if (&RFM_ErrCode. eq 0) %then %do;
proc sql;
create table &RFM_WorkLibrary..&TEMPFILE2. as
select distinct S1.&MostRecentDate.,
count(*) as nCount_R,
(select count(*) / &N_R_Grp.
from &InData.
) as Threshold
from &InData. as S1
group by S1.&MostRecentDate.
order by S1.&MostRecentDate. desc
;
quit;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
/* 3. Determine recency score */
%if (&RFM_ErrCode. eq 0) %then %do;
%EM_RFM_SET_SCORE (InData = &RFM_WorkLibrary..&TEMPFILE2.,
ByVar1 =,
ByVar2 =,
KeyVar = &MostRecentDate.,
nGroup = &N_R_Grp.,
Threshold = Threshold,
nCount = nCount_R,
OutData = &RFM_WorkLibrary..&TEMPFILE3.,
RightBinBoundData = &BIN_R.,
ScoreVar = &Recency_Score.);
%end;
/* 4. Merge recency score back to the customer data */
%if (&RFM_ErrCode. eq 0) %then %do;
proc sql;
create table &OutData. as
select S1.*,
S3.&Recency_Score.
from &InData. as S1 left join
&RFM_WorkLibrary..&TEMPFILE3. as S3
on S1.&MostRecentDate. = S3.&MostRecentDate.
;
quit;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
/* Step2: Rank the Frequency */
/* 1. Obtain record count for each number of purchase so that identical numbers of purchase have
identical frequency scores
2. Calculate the threshold number of cases that should be in each frequency group */
%if (&RFM_ErrCode. eq 0) %then %do;
proc sql;
create table &RFM_WorkLibrary..&TEMPFILE2. as
select distinct S0.&NPurchase.,
count(*) as nCount_F,
(select count(*) / &N_F_Grp.
from &OutData.
) as Threshold
from &OutData. as S0
group by S0.&NPurchase.
order by S0.&NPurchase. desc
;
quit;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
/* 3. Determine frequency score */
%if (&RFM_ErrCode. eq 0) %then %do;
%EM_RFM_SET_SCORE (InData = &RFM_WorkLibrary..&TEMPFILE2.,
ByVar1 =,
ByVar2 =,
KeyVar = &NPurchase.,
nGroup = &N_F_Grp.,
Threshold = Threshold,
nCount = nCount_F,
OutData = &RFM_WorkLibrary..&TEMPFILE3.,
RightBinBoundData = &BIN_F.,
ScoreVar = &Frequency_Score.);
%end;
/* 4. Merge frequency score back to the customer data */
%if (&RFM_ErrCode. eq 0) %then %do;
proc sql;
create table &RFM_WorkLibrary..&TEMPFILE1. as
select S0.*,
S3.&Frequency_Score.
from &OutData. as S0 left join
&RFM_WorkLibrary..&TEMPFILE3. as S3
on S0.&NPurchase. = S3.&NPurchase.
;
quit;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
/* Step3: Rank the Monetary */
/* 1. Obtain record count for each amount of purchase so that identical amount of purchase have
identical monetary scores
2. Calculate the threshold number of cases that should be in each monetary group */
%if (&RFM_ErrCode. eq 0) %then %do;
proc sql;
create table &RFM_WorkLibrary..&TEMPFILE2. as
select distinct S1.&TotPurchaseAmt.,
count(*) as nCount_M,
(select count(*) / &N_M_Grp.
from &RFM_WorkLibrary..&TEMPFILE1.
) as Threshold
from &RFM_WorkLibrary..&TEMPFILE1. as S1
group by S1.&TotPurchaseAmt.
order by S1.&TotPurchaseAmt. desc
;
quit;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
/* 3. Determine monetary score */
%if (&RFM_ErrCode. eq 0) %then %do;
%EM_RFM_SET_SCORE (InData = &RFM_WorkLibrary..&TEMPFILE2.,
ByVar1 =,
ByVar2 =,
KeyVar = &TotPurchaseAmt.,
nGroup = &N_M_Grp.,
Threshold = Threshold,
nCount = nCount_M,
OutData = &RFM_WorkLibrary..&TEMPFILE3.,
RightBinBoundData = &BIN_M.,
ScoreVar = &Monetary_Score.);
%end;
/* 4. Merge monetary score back to the output data */
%if (&RFM_ErrCode. eq 0) %then %do;
proc sql;
create table &OutData. as
select S1.*,
S3.&Monetary_Score.
from &RFM_WorkLibrary..&TEMPFILE1. as S1 left join
&RFM_WorkLibrary..&TEMPFILE3. as S3
on S1.&TotPurchaseAmt. = S3.&TotPurchaseAmt.
;
quit;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
/* Save right boundaries of bins into a data */
%if (%length(&RightBinBoundData.) gt 0) %then %do;
%if (&RFM_ErrCode. eq 0) %then %do;
proc sort data = &BIN_R.;
by &Recency_Score.;
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&RFM_ErrCode. eq 0) %then %do;
proc sort data = &BIN_F.;
by &Frequency_Score.;
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&RFM_ErrCode. eq 0) %then %do;
proc sort data = &BIN_M.;
by &Monetary_Score.;
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%if (&RFM_ErrCode. eq 0) %then %do;
data &RightBinBoundData.;
retain SCORE_TYPE &Recency_Score. &Frequency_Score. &Monetary_Score.
&MostRecentDate. &NPurchase. &TotPurchaseAmt.;
set &BIN_R. (in = inR)
&BIN_F. (in = inF)
&BIN_M. (in = inM);
/* Set the SCORE_TYPE values that do not need to be translated */
length SCORE_TYPE $ 9;
if (inR) then SCORE_TYPE = 'RECENCY ';
else if (inF) then SCORE_TYPE = 'FREQUENCY';
else if (inM) then SCORE_TYPE = 'MONETARY ';
run;
%if (&syserr. gt 4) %then %let RFM_ErrCode = 13;
%end;
%end;
/* Remove the temporary files */
proc datasets lib = &RFM_WorkLibrary. nolist nowarn;
delete &TEMPFILE1. &TEMPFILE2. &TEMPFILE3. &BIN_R. &BIN_F. &BIN_M.;
quit;
%mend EM_RFM_INDEPENDENT_RANK;
%mend aaRFM_exec;
%mend aaRFM;
Click image to register for webinar
