Recency, frequency, monetary – interaction (RFM-I) are guiding principles for extracting customer data in the process of generating segments and prediction models. Managing and processing of RFM-I data are often the most time consuming part compared to the analysis.
We often see various kinds of tables gathered from different relational databases and a need to scan those tables record-by-record. SAS is an appropriate choice for even huge data tables due to the principles governing the implementation of the the SAS engine.
Below a pseudo code macro extracted from an existing code base combining several data sources. The programming language is SAS, since SAS offers great transparency and robustness. I considered both proc sql, hash look-up tables and combinations of data steps and application of proc means, implemented those alternatives and concluded division of data into presorted historic data tables and present data tables lead to both transparency and speed-up of execution of code using proc sort and a single data step with a clever application of retain statements and small helpful macro scripts. The actual application included gathering of population data and restriction of those data through a set of inclusion and exclusion criteria, then a rerun with an augmented set of tables. A lookup-table thus seemed appropriate … I learned a simple macro generating a format identifying persons from the previously generated population list was the fastest and most elegant approach. Formatting using proc format and SAS macro language is also the preferred choice in the generation of complex indicators based on continuous or factor coded variables, although PRXMATCH and regular expressions should be considered.
The macro generates data suited for analysis using least-squares or logistic regression models. A similar macro scheme may be constructed for application of recurrent event Cox regression models. Link to SAS-program file.