0

LOGISTIC REGRESSION

logistic reg concept 250x175 - LOGISTIC REGRESSION

0

MARKET BASKET ANALYSIS

Market basket analysis - MARKET BASKET ANALYSIS

Market basket analysis 

Market basket analysis and Sequential Analysis are methods of Pattern Discovery.

Market basket analysis:

It is also known as Association rules discovery or affinity analysis.  The Algorithm worked on Conditional probability.

Item set or transaction dataset: The data grid comprising of the transactions to be analyzed. The transaction id variable should be unique and should have the list of items bought in the basket. The rules for the transaction are based on the Antecedents and Consequents in the sequence of the products bought.

Market basket analysis 

Market basket analysis

Market basket analysis 

Support = Transactions that contain every item in U and V/ All transaction.

Confidence = Transactions that contain every item in U and V/ Transaction that contain the item in U.

Expected Confidence = transaction that contains the items in V/ all transactions

Lift = Confidence of U=> V/ Expected confidence of U=> V

Sequential Analysis Concept:   In what sequence people are purchasing product or services.

Market basket analysis 

For sequence Analysis below concept need to be known:

Chain Count: Maximum number of items that can be included in a sequence. Maximum value can be 10.

Consolidated Time: allow you to specify whether repeated visits to a location or repeated purchase over a given interval can be considered into a single visit for analysis purpose. Like two product purchase less than a particular time might be considered to be a single transaction.

Maximum transaction Duration: allow you to specify the maximum length of time for a series of transactions to be considered a sequence.  An example, you might want to specify that the purchase of two products more than particular time does not constitute a sequence.

Support Type: Specifies whether the sequence analysis should use the support count or Support percentage property.

Support Count: Specifies the minimum frequency required to include a sequence in the sequence analysis when the sequence support type property is set to count. If a sequence has a count less than the specific value, that sequence is excluded from the output.

Support Percentage: specifies the minimum level of support to include the sequence in the analysis when the support type property is set to Percent. If a sequence has a frequency that is less than the specified percentage of the total number of the transaction. Then that sequence is excluded from the output. Permissible values are a real number between 0 to 100.

Market basket analysis 

Base sas code for MARKET BASKET ANALYSIS

libname datapath “C:\Users\KOUSTAV\MBA”;

PROC IMPORT OUT= datapath.SASMBC
DATAFILE= “C:\Users\KOUSTAV\MBA\Retail_data.csv”
DBMS=CSV REPLACE; GETNAMES=YES; DATAROW=2;
RUN;

Market basket analysis 

data datapath.txn_data;
set datapath.SASMBC;
run;

Market basket analysis 

proc sql;
Create table datapath.txn_data11 as select transaction_id, Prod1 as item1,
Prod2 as item2, Prod3 as item3 from datapath.txn_data;
quit;

Market basket analysis 

proc sql;
Create table dat2 as select transaction_id as tid, Prod1 as item from datapath.txn_data;
quit;

Market basket analysis 

proc sql;
insert into dat2 select transaction_id, Prod2 from datapath.txn_data;
quit;

Market basket analysis 

proc sql;
insert into dat2 select transaction_id, Prod3 from datapath.txn_data;
quit;

Market basket analysis 

data datapath.txn_data1;
set dat2;
run;

Market basket analysis 

*macro to select the distinct items from the basket
%macro select_distinct_items(CANDIDATE_LIST=, ITEMS_EACH_ROW=);
%global ITEM_LIST ITEM_COUNT; * Will be used in subsequent macros ;
%local i;
proc sql noprint;
%if (&GLBL_ITEM_ID_IS_STRING = “Y”) %then %do;
select distinct “””” || trim(item) || “”””, count(distinct item)
%end;
%else %do;
select distinct item, count(distinct item)
%end;
into :ITEM_LIST separated by ” “, :ITEM_COUNT
from
(
%do i = 1 %to &ITEMS_EACH_ROW;
%if (&i > 1) %then %do;
union
%end;
select item&i as item from &CANDIDATE_LIST
%end;
);
run;
%put ITEM_LIST = &ITEM_LIST;
%put ITEM_COUNT = &ITEM_COUNT;
%mend select_distinct_items;

Market basket analysis 

* —————————————————————————- ** macro to create list of unique combinations*- *
*-*Update this marco items based on the unique products in the basket-*
%macro create_candidate_set(ITEM_LIST=, ITEM_COUNT=, ITEMSET_SIZE=, OUTPUT_CANDIDATE_FILE=);
data &OUTPUT_CANDIDATE_FILE;
set datapath.txn_data11(drop=transaction_id);
item4=”;
item5=”;
item6=”;
item7=”;
item8=”;
item9=”;
run;
%put OUTPUT_CANDIDATE_FILE = &OUTPUT_CANDIDATE_FILE;
%mend create_candidate_set;

Market basket analysis 

/* get the support count for each individual item present in the dataset*/
%macro first_list_of_support(OUTPUT_LIST_FILE=);
proc sql noprint;
create table &OUTPUT_LIST_FILE as
select item as item1
, count(*) as Support_Count
, count(*) / &GLBL_MBA_TRANSACTION_COUNT as support
from &GLBL_MBA_TRANS_FILE
group by item
having support >= &GLBL_MBA_MIN_SUPPORT_PCT
;
quit;
run;

data &GLBL_MBA_RESULTS_FILE;
set &GLBL_MBA_RESULTS_FILE &OUTPUT_LIST_FILE (in=new);
Iteration = 1;
run;
%mend first_list_of_support;

Market basket analysis 

/*support count for each combination*/

%macro calculate_support(ITEMSET_SIZE=, INPUT_CANDIDATE_FILE=, OUTPUT_LIST_FILE=);
%local i;
%do I = 1 %to &ITEMSET_SIZE;
proc sql noprint;
create table work.x&i as
select * from &GLBL_MBA_TRANS_FILE
where item in (select distinct item&i from &INPUT_CANDIDATE_FILE);
quit; run;
%end;
proc sql noprint;
create table &OUTPUT_LIST_FILE as
select %do I = 1 %to &ITEMSET_SIZE;
%if (&I > 1) %then %do;
,
%end;
x&I..item as item&I
%end;
, count(*) as Support_Count
, count(*) / &GLBL_MBA_TRANSACTION_COUNT as support
, &ITEMSET_SIZE as Iteration
from %do I = 1 %to &ITEMSET_SIZE;
%if (&I > 1) %then %do;
,
%end;
work.x&I %end;
where %do I = 1 %to &ITEMSET_SIZE – 1;
%if (&I > 1) %then %do;
and
%end;
%put testval = x%sysevalf(&I+1).tid;
%put testval1 = x%sysevalf(&I+1).item;
x&I..tid = x%trim(%sysevalf(&I+1)).tid and x&I..item < x%trim(%sysevalf(&I+1)).item
%end;
group by
%do I = 1 %to &ITEMSET_SIZE;
%if (&I > 1) %then %do;
,
%end;
x&I..item
%end;
, Iteration
having support >= &GLBL_MBA_MIN_SUPPORT_PCT;
quit; run;

Market basket analysis 

data &GLBL_MBA_RESULTS_FILE;
set &GLBL_MBA_RESULTS_FILE &OUTPUT_LIST_FILE;
run;
proc datasets lib=work nolist;
%do I = 1 %to %trim(&ITEMSET_SIZE);
delete x&i;
%end;
run;
%put INPUT_CANDIDATE_FILE =&INPUT_CANDIDATE_FILE;
%put OUTPUT_LIST_FILE = &OUTPUT_LIST_FILE;
%mend calculate_support;

Market basket analysis 

/*assign specific formats to all variables*/

%macro initialize_result_set();
%local I;
data &GLBL_MBA_RESULTS_FILE;
attrib Iteration length=8;
%do I = 1 %to &GLBL_MBA_MAX_ITERATIONS;
%if (&GLBL_ITEM_ID_IS_STRING = “Y”) %then %do;
attrib Item&I length=$32;
%end; %else %do;
attrib Item&I length=8;
%end; %end;
attrib Support_Count length=8;
attrib Support length=8 format=6.2;
* Remaining columns filled after apriori. ;
%do I = 1 %to &GLBL_MBA_MAX_ITERATIONS-1;
%if (&GLBL_ITEM_ID_IS_STRING = “Y”) %then %do;
attrib LHS&I length=$32;
%end; %else %do;
attrib LHS&I length=8;
%end; %end;
%if (&GLBL_ITEM_ID_IS_STRING = “Y”) %then %do;
attrib RHS length=$32;
%end; %else %do;
attrib RHS length=8;
%end;
attrib Confidence length=8 format=6.2;
attrib Lift length=8 format=6.2;
delete; * Without this you get an empty row ;
run;
%mend initialize_result_set;

Market basket analysis 

/*Define the LHS and RHS variables*/

%macro find_confidence_and_lift();
%local I;
data Work.With_LHS_RHS (drop = i j k);
set &GLBL_MBA_RESULTS_FILE;
array items[&GLBL_MBA_MAX_ITERATIONS]
%if (&GLBL_ITEM_ID_IS_STRING = “Y”) %then %do; $32 %end;
item1-item&GLBL_MBA_MAX_ITERATIONS;
* LHS has one less item than itemset size (iteration) ;
array lhs[%sysevalf(&GLBL_MBA_MAX_ITERATIONS – 1)]
%if (&GLBL_ITEM_ID_IS_STRING = “Y”) %then %do; $32 %end;
LHS1-LHS%trim(%sysevalf(&GLBL_MBA_MAX_ITERATIONS – 1));
if (Iteration = 1) then do;
LHS1 = item1; RHS = item1; output; end;
else do;
do i = 1 to Iteration; k = 0;
do j = 1 to Iteration;
* put Iteration= i= j= k=;
if (i = j) then do; RHS = items[j]; end;
else do;
k = k + 1; LHS[k] = items[j]; end; end;
output; * write one row for each LHS->RHS ;
end; end;
run;
* Having populated LHS and RHS, now do confidence and lift ;

Market basket analysis 

proc sql noprint;
create table Work.Almost_Confidence_And_Lift as
select a.Iteration
%do i = 1 %to %sysevalf(&GLBL_MBA_MAX_ITERATIONS-1);
, a.LHS&I %end;
, a.RHS
, a.Support
, a.support / b.support as Confidence format=6.2
, (a.support / b.support) / c.support as Lift format=6.2
from Work.with_lhs_rhs as a
left join &GLBL_MBA_RESULTS_FILE as b
on (a.iteration – 1) = b.iteration
%do i = 1 %to %sysevalf(&GLBL_MBA_MAX_ITERATIONS-1);
and a.LHS&i = b.item&i
%end;
left join &GLBL_MBA_RESULTS_FILE as c
on c.Iteration = 1
and a.RHS = c.item1
order by a.RHS
, a.ITERATION descending
%do i = 1 %to %sysevalf(&GLBL_MBA_MAX_ITERATIONS-1);
, a.LHS&i
%end; ; quit; run;
* Now merge individual lifts back in ;
proc sql noprint;
create table Work.Lifts_Only as
select rhs , lhs1, lift
from Work.Almost_Confidence_And_Lift
where iteration = 2; quit; run;
proc sql noprint;
create table Work.With_Confidence_And_Lift as
select a.* %do i = 1 %to %sysevalf(&GLBL_MBA_MAX_ITERATIONS-1);
, t&i..Lift as LHS&i._lift %end;
from Work.Almost_Confidence_And_Lift as a
%do i = 1 %to %sysevalf(&GLBL_MBA_MAX_ITERATIONS-1);
left join Work.Lifts_Only as t&i
on a.rhs = t&i..rhs and a.LHS&i = t&i..LHS1 %end;
order by a.RHS, a.ITERATION descending
%do i = 1 %to %sysevalf(&GLBL_MBA_MAX_ITERATIONS-1);
, a.LHS&I %end;;
quit; run;
data &GLBL_MBA_RESULTS_FILE;
set Work.With_Confidence_And_Lift;
run;
proc datasets lib=Work nolist;
delete With_LHS_RHS;
delete Lifts_Only;
delete Almost_Confidence_and_Lift;
delete With_Confidence_and_Lift;
run;
%mend find_confidence_and_lift;

Market basket analysis 

/*Format the result*/

%macro write_webpage(WEBPAGE=, RHS=);
%local I;
proc sql noprint;
create table work.I as
select distinct rhs as item from &GLBL_MBA_RESULTS_FILE
%if (&RHS ne ) %then %do; where rhs in &RHS %end; ;
quit; run;
data _NULL_;
set &GLBL_MBA_RESULTS_FILE end=eof;
%if (&RHS ne ) %then %do;
where rhs in &RHS; %end;
file &WEBPAGE lrecl=500;
if (_N_ = 1) then do;
put “<html>”; put “<head>”;
put “<title>Market Basket Analysis</title>”;
put “<body>”;
put “<h3 align=’center’>Market Basket Analysis<br/>”;
put “Source File: &GLBL_MBA_TRANS_FILE (Obs = &GLBL_MBA_TRANSACTION_COUNT)<br/>”;
percent = put(&GLBL_MBA_MIN_SUPPORT_PCT, percent6.1);
put “Minimum support: ” percent ” (n = &GLBL_MBA_MIN_SUPPORT_COUNT)</h3>”;
put “</br>”; put “<table align=’center’ width=’40%’>”;
put “<tr><td><p><b>Quicklink to Right Hand Side (RHS) variables</b><ul>”;
do while (i_eof = 0); set Work.I end=i_eof;
put “<li><a href=’#” item +(-1) “‘>” item +(-1) “</a>”; end;
put “</ul></p></td></tr>”;
put “<tr bgcolor=’yellow’><td align=’center’><p>”;
put ‘Possible interaction effects shown in yellow.</br>’;
put “Example: Lift(AB%str(&)rarr%str(;)C) %str(&)gt%str(;)
max(Lift(A%str(&)rarr%str(;)C), Lift(B%str(&)rarr%str(;)C))</td></tr>”;

put ‘</p></td></tr>’; put “</table>”; end;
lag_rhs = lag(rhs);
if (rhs ne lag_rhs) then do; if (_n_ > 1) then do;
put “</td></tr>”; put “</table>”; end;
put “<a id='” rhs +(-1) “‘</a>”;
put “<br/><br/>”;
put “<table align=’center’ width=’90%’ border=’1′ cellpadding=’2′
cellspacing=’2′>”;
put “<caption><font color=’blue’><b>RHS: ” rhs “<b></font></caption>”;
put “<tr>”;
%do i = 1 %to %sysevalf(&GLBL_MBA_MAX_ITERATIONS – 1);
put “<th>LHS &i</th>”; %end;
put “<th width=’100′>Support</th>”;
put “<th width=’100′>Confidence</th>”;
put “<th width=’100′>Lift</th>”;
put “</tr>”; end;
if (Lift > max(0 %do i = 1 %to %sysevalf(&GLBL_MBA_MAX_ITERATIONS – 1);
, LHS&i._Lift %end; )) then do;
bigLift = 1; end;
else do; bigLift = 0; end;
if (bigLift = 1) then do;
put “<tr bgcolor=’yellow’>”; end;
else do;
put “<tr>”; end;
%do i = 1 %to %sysevalf(&GLBL_MBA_MAX_ITERATIONS – 1);
put “<td>”; %if (&GLBL_ITEM_ID_IS_STRING = “Y”) %then %do;
if (LHS&i = “”) then do; put ‘&nbsp’; end;
else do;
put LHS&i; if (bigLift = 1) then do;
put ” (” LHS&i._Lift +(-1) “)”; end; end; %end; %else %do;
if (LHS&i = .) then o; put ‘&nbsp’; end; else do;
put LHS&i; if (bigLift = 1) then do;
put ” (” LHS&i._Lift +(-1) “)”; end; end; %end;
* put “</td>”; * </td> optional so omitting it to get a smaller file ;
%end;
* </td> optional so omitting it to get a smaller file ;
put “<td align=’center’>” Support; put “<td align=’center’>” Confidence;
put “<td align=’center’>” Lift; put “</tr>”; if (eof) then do;
put “</td></tr>”; put “</table>”; dt = put(today(), weekdate29.);
tm = put(time(), tod5.); put “<p align=’center’>” dt ” at ” tm “</p>”;
put “</body>”; put “</html>”; end;
run;
%mend write_webpage;

Market basket analysis 

/*assign values to all the macro variables initiated above*/
%macro mba(TRANS_FILE=, ITEM_ID_IS_STRING=, MAXIMUM_ITERATIONS=,
MINIMUM_SUPPORT=, RHS=, RESULTS_FILE=, WEBPAGE=);
%local I;
%global GLBL_MBA_MAX_ITERATIONS;
%let GLBL_MBA_MAX_ITERATIONS = &MAXIMUM_ITERATIONS;
%global GLBL_ITEM_ID_IS_STRING;
%let GLBL_ITEM_ID_IS_STRING = &ITEM_ID_IS_STRING;
%global GLBL_MBA_TRANS_FILE;
%let GLBL_MBA_TRANS_FILE = &TRANS_FILE;
%global GLBL_MBA_RESULTS_FILE;
%let GLBL_MBA_RESULTS_FILE = &RESULTS_FILE;
%global GLBL_MBA_TRANSACTION_COUNT;
proc sql noprint;
select count(distinct tid) into :GLBL_MBA_TRANSACTION_COUNT
from &GLBL_MBA_TRANS_FILE; quit; run;
%put GLBL_MBA_TRANSACTION_COUNT = &GLBL_MBA_TRANSACTION_COUNT;
%global GLBL_MBA_MIN_SUPPORT_PCT;
%global GLBL_MBA_MIN_SUPPORT_COUNT;
%if (&MINIMUM_SUPPORT >= 1) %then %do;
%let GLBL_MBA_MIN_SUPPORT_COUNT = %sysevalf(&MINIMUM_SUPPORT);
%let GLBL_MBA_MIN_SUPPORT_PCT = %sysevalf(&MINIMUM_SUPPORT /
&GLBL_MBA_TRANSACTION_COUNT);
%end;
%else %do;
%let GLBL_MBA_MIN_SUPPORT_COUNT =
%sysfunc(round(%sysevalf(&GLBL_MBA_TRANSACTION_COUNT * &MINIMUM_SUPPORT)));
%let GLBL_MBA_MIN_SUPPORT_PCT = %sysevalf(&MINIMUM_SUPPORT);
%end;
%put GLBL_MBA_MIN_SUPPORT_COUNT = &GLBL_MBA_MIN_SUPPORT_COUNT;
%put GLBL_MBA_MAX_SUPPORT_PCT = &GLBL_MBA_MIN_SUPPORT_PCT;

%initialize_result_set();
%first_list_of_support(OUTPUT_LIST_FILE=Work.L1);
%let DONE = N;
%let MAX_ITERS = %sysevalf(&GLBL_MBA_MAX_ITERATIONS – 1);
%let i = 0; %do %while (&i < &MAX_ITERS and &DONE = N);
%let i = %sysevalf(&i + 1);
%select_distinct_items(CANDIDATE_LIST=work.L&I, ITEMS_EACH_ROW=&I);
%if (&ITEM_COUNT <= &i) %then %do;
proc datasets lib=Work nolist;
delete L&I; run;
%let DONE = Y; %end;
%else %do; %local OIS; %let OIS = %trim(%sysevalf(&I+1));
%local OCF; %let OCF = WORK.C%TRIM(%sysevalf(&I+1)); %put OCF = &OCF;

%create_candidate_set(ITEM_LIST=&ITEM_LIST, ITEM_COUNT=&ITEM_COUNT, ITEMSET_SIZE=&OIS, OUTPUT_CANDIDATE_FILE=&OCF);
%local OLF; %let OLF = WORK.L%trim(%sysevalf(&I+1));
%put OLF = &OLF;
%calculate_support(ITEMSET_SIZE=&OIS, INPUT_CANDIDATE_FILE=&OCF, OUTPUT_LIST_FILE=&OLF);

proc datasets lib=WORK nolist;
delete L&I; delete C&OIS; run; %end;
%end;
%find_confidence_and_lift();
%write_webpage(WEBPAGE=&WEBPAGE, RHS=&RHS);
%mend mba;

Market basket analysis 

/*Set the path to save the results page
%let MYLIB = C:\Users\Koustav\Downloads\MBA;
libname Perm “&MYLIB”;

/*Set the values in the macro and call the macro

%mba(TRANS_FILE=datapath.txn_data1
, ITEM_ID_IS_STRING=”Y”
, MAXIMUM_ITERATIONS=3
, MINIMUM_SUPPORT=0.01
, RHS=(‘H’,’I’)
, RESULTS_FILE=Perm.Results_Groc
, WEBPAGE=”C:\Users\KOUSTAV\Downloads\MBA\mba_groc.html”
);
run;