Analysis Of Transactions For ABC Retail Company Using SAS Enterprise Guide

Total amount of money spent per weekend on each shopping week

Business intelligence is being readily internalized by the organizations via various cutting-edge statistical softwares to unearth patterns hidden within raw data of the organizations and derive meaningful insights that act as strategic assets for decision making. Proper decision making minimizes operational cost and gives a strategic dimension to the organization. In today’s world of Big Data, business intelligence is crucial and it forays basically into the following arenas:

• Data mining & Data warehousing
• Sophisticated reporting of complex and huge datasets
• Business automation
• Predictive modeling
• Dashboard & data visualization
• Trend analysis

Among various business intelligence platforms in use by businesses, SAS (Statistical Analysis System) is a leading business intelligence platform that is the selected choice of large corporates. SAS is proficient in delivering mainly the following:

• Advanced analytics
• Multivariate analyses
• Data analysis of huge datasets
• Predictive analytics
• Data management
• Advanced reporting

Using SAS Enterprise edition, here our objective is to analyze the transactions of a retail organization i.e. ABC Retail Company based out of United States of America. We will highlight the central findings along with the SAS codes necessary to generate the findings.

1.

 Total amount of money spent per weekend on each shopping week

 The SAS System

SHOP_WEEK	TOTAL SPEND
200607	$13,797.83
200609	$14,785.95
200612	$14,341.26
200619	$14,652.97

SAS Code:

ODS HTML FILE = “C:UsersNTDesktopSAS FilesmyZip_7036713.html”;

PROC SQL;

SELECT SHOP_WEEK, SUM(SPEND) “TOTAL SPEND” FORMAT DOLLAR16.2

FROM mylib.transactions

GROUP BY SHOP_WEEK;

QUIT;

ODS HTML Close;

It can be seen that the highest spend has been for 9^th week year 2006 and the least is for 7^th week year 2006. The spend reduced from $ 14,785.95 on 9^th week to $ 14,341.26 on 12^th week 2006. Thus, a significant increase is found between 7^th and 9^th week but after that there is a little drop in 12^th week followed by increase in 19^th week.

2.

Money spent by each age group

 The SAS System

CUST_LIFESTAGE	TOTAL SPEND
XX	$17,194.16
OA	$5,805.45
OF	$3,097.99
OT	$12,821.90
PE	$3,123.07
YA	$5,987.48
YF	$9,547.96

SAS Code:

ODS HTML FILE = “C:UsersNTDesktopSAS FilesmyZip_7036714.html”;

PROC SQL;

SELECT CUST_LIFESTAGE, SUM(SPEND) “TOTAL SPEND” FORMAT DOLLAR16.2

FROM mylib.transactions

GROUP BY CUST_LIFESTAGE;

ORDER BY CUST_LIFESTAGE;

QUIT;

ODS HTML Close;

The above finding is very crucial. We can see that XX i.e. unclassified has the highest spend. This is a drawback as the company has not been able to classify this segment. Then we see that the spend of OT i.e. others are second highest i.e. $ 12,821.90. This area has to be also classified properly. The young adults i.e. YA spends $ 5,987.48, OA i.e. older adults spend $5,805.45, the young families i.e. YF spend $ 9,547.96, pensioners spend $ 3,123.07 and older families i.e. OF spend $ 3,097.99.

3.

Total quantity sold and total amount of sales of each product in each state

The SAS System

Total QTY	TOTAL SALE AMOUNT	PRODUCT CATEGORY	STORE_STATE
764	$971.85	Fresh	ACT
7154	$9,288.67	Fresh	NSW
1488	$1,904.00	Fresh	NT
2095	$2,915.86	Fresh	QLD
2513	$3,199.16	Fresh	SA
892	$1,127.97	Fresh	TAS
4436	$5,882.56	Fresh	VIC
1984	$2,783.51	Fresh	WA
232	$360.06	Grocery	ACT
1792	$2,469.06	Grocery	NSW
335	$355.43	Grocery	NT
572	$716.94	Grocery	QLD
641	$961.37	Grocery	SA
212	$305.16	Grocery	TAS
1033	$1,440.92	Grocery	VIC
522	$582.11	Grocery	WA
655	$910.14	Mixed	ACT
5206	$6,797.76	Mixed	NSW
1097	$1,558.58	Mixed	NT
1620	$2,125.31	Mixed	QLD
1916	$2,379.70	Mixed	SA
633	$797.37	Mixed	TAS
3391	$4,438.74	Mixed	VIC
1495	$1,954.23	Mixed	WA
24	$66.14	Nonfood	ACT
173	$296.81	Nonfood	NSW
26	$47.50	Nonfood	NT
60	$90.87	Nonfood	QLD
76	$192.23	Nonfood	SA
19	$74.14	Nonfood	TAS
110	$201.09	Nonfood	VIC
48	$79.11	Nonfood	WA
1	$9.27	XX	ACT
27	$178.37	XX	NSW
1	$4.72	XX	QLD
7	$34.81	XX	SA
1	$0.96	XX	TAS
5	$31.06	XX	VIC
8	$44.47	XX	WA

SAS Code:

ODS HTML FILE = “C:UsersNTDesktopSAS FilesmyZip_7036715.html”;

PROC SQL;

SELECT SUM(QUANTITY) “Total QTY”, SUM(SPEND) “TOTAL SALE AMOUNT” FORMAT DOLLAR16.2, BASKET_DOMINANT_MISSION “PRODUCT CATEGORY”, STORE_STATE

FROM mylib.transactions

GROUP BY BASKET_DOMINANT_MISSION, STORE_STATE;

QUIT;

ODS HTML Close;

It can be seen that the highest selling product is Fresh and it is highest in the state of Victoria followed by mixed product category which is also highest in the state of Victoria i.e. VIC. The least selling states are Tasmania i.e. TAS, Australian Capital Territory and West Australia. Thus, the retailer has to vehemently focus in these areas.

4.

First 20 products that have the highest sales in value where more than 1 item was sold

Row	TOTAL SALE AMOUNT	PROD_CODE	QUANTITY
1	$13.14	PRD0901637	9
2	$11.13	PRD0902765	3
3	$9.63	PRD0900508	3
4	$8.64	PRD0902163	3
5	$6.60	PRD0904911	3
6	$6.39	PRD0904806	3
7	$5.79	PRD0901512	3
8	$5.46	PRD0904461	3
9	$5.46	PRD0903269	6
10	$4.89	PRD0902907	3
11	$4.05	PRD0901488	3
12	$3.84	PRD0902232	3
13	$3.78	PRD0900940	3
14	$3.57	PRD0900947	3
15	$2.73	PRD0903269	3
16	$2.12	PRD0901672	4
17	$1.64	PRD0900407	4
18	$1.62	PRD0902742	3
19	$0.96	PRD0903471	3
20	$0.84	PRD0900684	3

SAS Code:

ODS HTML FILE = “C:UsersNTDesktopSAS FilesmyZip_7036716.html”;

PROC SQL number;

Select SPEND “TOTAL SALE AMOUNT” FORMAT DOLLAR16.2, PROD_CODE, QUANTITY

FROM mylib.transactions (firstobs = 1 obs = 20)

where QUANTITY>1

order BY SPEND desc;

quit;

ODS HTML Close;

The highest selling product is PRD0901637 and the least selling product is PRD0900684.

5.

Row	Unique Customers	STORE_REGION
1	1857	W01

SAS Code:

ODS HTML FILE = “C:UsersNTDesktopSAS FilesmyZip_7036717.html”;

PROC SQL number;

Select COUNT (DISTINCT CUST_CODE) ” Unique Customers”, STORE_REGION

FROM mylib.transactions

where STORE_REGION=’W01′;

quit;

ODS HTML Close;

There are 1857 unique customers in the store region W01.

The five areas where the organization has to focus:

• Classify the sales properly in the customer life stage segment as most of the sales are categorized as others or unclassified
• Target sales within 7^thand 9^th week
• Properly market and target the states of Tasmania i.e. TAS, Australian Capital Territory and West Australia
• Focus marketing strategies more on Non-food segment
• Target the young families with more innovative product basket

Conclusion

The study helped in comprehending various aspects of transactions of the retailer and simultaneously devises strategies to be more productive and earn more revenues. The study helped to focus on the areas of strength as well as the areas of weaknesses so that prescriptive measures can be taken wisely.