Mini Market Basket Analysis

An interesting interview question

Recently I appeared in several interviews and faced a variety of  SAS related questions and puzzles. One of those SAS puzzle / question was quite interesting, which I would like to share with you guys with its answer and SAS code !

Hope you get to learn something from the same.

The question was  :

Suppose, there are various customers associated with a bank and these customers use various services and products such as Insurance, Saving Account, Credit Card, Home Loan etc.
The bank is interested in having a matrix, which can tell them the tendency of customer for taking another product, if they are having one already. Basically if there are N products, we need to create an N by N matrix.

Data was something like  :

Data Base_data;
Input Name  $ Product $ ;
cards;

 Base_data

Rajat Card
Rajat Saving
Rajat LIC
Vinod Card
Vinod LIC
Vinod Loan
Arya Card
Arya Saving
Arya LIC
Vertika Card
Vertika Saving
Vetika Loan
;
Run;

Target Matrix

We required to have a matrix as shown in Right side >>>

How did we make the matrix ?

1. First a self join -  Cartesian product was made to list out all the combination of Prodcuts each customer is having:

Proc sql;
create table new_data as 
select a.Name, a.product as Product_1, b.product as Product_2 
from Base_data as a,Base_data as b
where a.Name = b.Name
having product_1 <> product_2;
Quit;

2. Then for each combination of Products, number of unique customers were counted.

Proc Sql;
create table Customer_count as 
select product_1 , product_2 , count(distinct Name) as Count
from New_data 
group by Product_1 , product_2 ;
Quit;

3. Now Finally Data was tranposed to transform it into a matrix shape.

Proc transpose Data = Customer_count out =The_Matrix ;
by Product_1 ;
ID product_2 ;
var Count ;
Run;

4.  And finally, it was customized for the presentation perspective.

Data The_matrix;
retain Product_1 Card LIC Loan saving;
set The_matrix;
drop _name_;
proc Print;
Run;

And we got the matrix in desired format. Please do try running code and see how it works.

It is quite interesting and useful analysis. It is also useful while you perform a Market Basket Analysis.

Enjoy reading our other articles and stay tuned with us.

Kindly do provide your feedback in the 'Comments' Section and share as much as possible.