### 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 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.

## No comments:

## Post a Comment

Do provide us your feedback, it would help us serve your better.