SAS Learning Hub: 305.FAMILY MALL PROMOTION ANALYSIS USING | PROC SQL | PROC MEANS | PROC UNIVARIATE | PROC SGPLOT | PROC SGPANEL | AND MACRO ROI EVALUATION FOR SEASONAL FOOTFALL UPLIFT AND RETURN ON INVESTMENT CALCULATION

FAMILY MALL PROMOTION ANALYSIS USING | PROC SQL | PROC MEANS | PROC UNIVARIATE | PROC SGPLOT | PROC SGPANEL | AND MACRO ROI EVALUATION FOR SEASONAL FOOTFALL UPLIFT AND RETURN ON INVESTMENT CALCULATION

options nodate nonumber nocenter;

1. Create the main dataset:

data work.mall_events;

infile datalines dlm=',' dsd truncover;

length MallID $4 MallType $12 Season $8 Event $30;

input MallID $ MallType $ Season $ Event :$30. PreFootfall PostFootfall;

datalines;

M001,Regional,Peak,WinterFête,12000,15000

M002,Community,OffPeak,BookFair,4200,5200

M003,Outlet,Peak,ClearanceBash,7000,9800

M004,Regional,OffPeak,FoodCarnival,9400,10100

M005,Community,Peak,MusicWeekend,5300,6100

M006,Outlet,OffPeak,NightMarket,4100,4500

M007,Regional,Peak,DiwaliFest,15000,19700

M008,Community,OffPeak,HandicraftExpo,3600,4300

M009,Outlet,Peak,SummerPromo,8200,8900

M010,Regional,OffPeak,KidsFestival,6800,7400

;

run;

proc print data=work.mall_events noobs;

title "MALL_EVENTS (10 obs x 6 vars)";

run;

OUTPUT:

MALL_EVENTS (10 obs x 6 vars)


MallID	MallType	Season	Event	PreFootfall	PostFootfall
M001	Regional	Peak	WinterFête	12000	15000
M002	Community	OffPeak	BookFair	4200	5200
M003	Outlet	Peak	ClearanceBash	7000	9800
M004	Regional	OffPeak	FoodCarnival	9400	10100
M005	Community	Peak	MusicWeekend	5300	6100
M006	Outlet	OffPeak	NightMarket	4100	4500
M007	Regional	Peak	DiwaliFest	15000	19700
M008	Community	OffPeak	HandicraftExpo	3600	4300
M009	Outlet	Peak	SummerPromo	8200	8900
M010	Regional	OffPeak	KidsFestival	6800	7400

2. Create a separate promo cost table (one row per Mall)

data work.promo_costs;

infile datalines dlm=',' dsd truncover;

length MallID $4 PromoCostUSD 8;

input MallID $ PromoCostUSD;

datalines;

M001,25000

M002,4800

M003,12000

M004,9000

M005,7200

M006,3500

M007,39000

M008,3200

M009,6800

M010,5600

;

run;

proc print data=work.promo_costs noobs;

title "PROMO_COSTS (promo spend per mall)";

run;

OUTPUT:

PROMO_COSTS (promo spend per mall)


MallID	PromoCostUSD
M001	25000
M002	4800
M003	12000
M004	9000
M005	7200
M006	3500
M007	39000
M008	3200
M009	6800
M010	5600

3. PROC SQL: join events and costs, compute uplift metrics

proc sql;

create table work.mall_joined as

select a.MallID, a.MallType, a.Season, a.Event,

a.PreFootfall, a.PostFootfall,

b.PromoCostUSD,

/* Absolute uplift */

(a.PostFootfall - a.PreFootfall) as UpliftAbs format=8.,

/* Percent uplift: careful for zero baseline */

case

when a.PreFootfall > 0 then

(a.PostFootfall - a.PreFootfall)/a.PreFootfall * 100

else .

end as UpliftPct format=8.2

from work.mall_events as a

left join work.promo_costs as b

on a.MallID = b.MallID

;

quit;

proc print data=work.mall_joined noobs;

title "Joined table with uplift calculations";

run;

OUTPUT:

Joined table with uplift calculations


MallID	MallType	Season	Event	PreFootfall	PostFootfall	PromoCostUSD	UpliftAbs	UpliftPct
M001	Regional	Peak	WinterFête	12000	15000	25000	3000	25.00
M002	Community	OffPeak	BookFair	4200	5200	4800	1000	23.81
M003	Outlet	Peak	ClearanceBash	7000	9800	12000	2800	40.00
M004	Regional	OffPeak	FoodCarnival	9400	10100	9000	700	7.45
M005	Community	Peak	MusicWeekend	5300	6100	7200	800	15.09
M006	Outlet	OffPeak	NightMarket	4100	4500	3500	400	9.76
M007	Regional	Peak	DiwaliFest	15000	19700	39000	4700	31.33
M008	Community	OffPeak	HandicraftExpo	3600	4300	3200	700	19.44
M009	Outlet	Peak	SummerPromo	8200	8900	6800	700	8.54
M010	Regional	OffPeak	KidsFestival	6800	7400	5600	600	8.82

4. Summarize uplift by MallType and Season

proc means data=work.mall_joined n mean median std min max;

class MallType Season;

var PreFootfall PostFootfall UpliftAbs UpliftPct PromoCostUSD;

title "Descriptive stats of footfall and uplift by MallType and Season";

run;

OUTPUT:

Descriptive stats of footfall and uplift by MallType and Season

The MEANS Procedure


MallType	Season	N Obs	Variable	N	Mean	Median	Std Dev	Minimum	Maximum
Community	OffPeak	2	PreFootfall PostFootfall UpliftAbs UpliftPct PromoCostUSD	2 2 2 2 2	3900.00 4750.00 850.0000000 21.6269841 4000.00	3900.00 4750.00 850.0000000 21.6269841 4000.00	424.2640687 636.3961031 212.1320344 3.0865772 1131.37	3600.00 4300.00 700.0000000 19.4444444 3200.00	4200.00 5200.00 1000.00 23.8095238 4800.00
	Peak	1	PreFootfall PostFootfall UpliftAbs UpliftPct PromoCostUSD	1 1 1 1 1	5300.00 6100.00 800.0000000 15.0943396 7200.00	5300.00 6100.00 800.0000000 15.0943396 7200.00	. . . . .	5300.00 6100.00 800.0000000 15.0943396 7200.00	5300.00 6100.00 800.0000000 15.0943396 7200.00
Outlet	OffPeak	1	PreFootfall PostFootfall UpliftAbs UpliftPct PromoCostUSD	1 1 1 1 1	4100.00 4500.00 400.0000000 9.7560976 3500.00	4100.00 4500.00 400.0000000 9.7560976 3500.00	. . . . .	4100.00 4500.00 400.0000000 9.7560976 3500.00	4100.00 4500.00 400.0000000 9.7560976 3500.00
	Peak	2	PreFootfall PostFootfall UpliftAbs UpliftPct PromoCostUSD	2 2 2 2 2	7600.00 9350.00 1750.00 24.2682927 9400.00	7600.00 9350.00 1750.00 24.2682927 9400.00	848.5281374 636.3961031 1484.92 22.2479938 3676.96	7000.00 8900.00 700.0000000 8.5365854 6800.00	8200.00 9800.00 2800.00 40.0000000 12000.00
Regional	OffPeak	2	PreFootfall PostFootfall UpliftAbs UpliftPct PromoCostUSD	2 2 2 2 2	8100.00 8750.00 650.0000000 8.1351690 7300.00	8100.00 8750.00 650.0000000 8.1351690 7300.00	1838.48 1909.19 70.7106781 0.9734887 2404.16	6800.00 7400.00 600.0000000 7.4468085 5600.00	9400.00 10100.00 700.0000000 8.8235294 9000.00
	Peak	2	PreFootfall PostFootfall UpliftAbs UpliftPct PromoCostUSD	2 2 2 2 2	13500.00 17350.00 3850.00 28.1666667 32000.00	13500.00 17350.00 3850.00 28.1666667 32000.00	2121.32 3323.40 1202.08 4.4783429 9899.49	12000.00 15000.00 3000.00 25.0000000 25000.00	15000.00 19700.00 4700.00 31.3333333 39000.00

5. Nonparametric tests and effect size

Strategy:

- For within-mall pre/post comparison (paired), produce difference = Post - Pre

- Test if median difference differs from 0 using Wilcoxon signed-rank via PROC UNIVARIATE (SIGN test & signrank)

- Compute a common nonparametric effect size r = Z / sqrt(N) when Z is available from Wilcoxon.

/* Create differences and a long-format dataset for tests */

data work.diff;

set work.mall_joined;

Diff = PostFootfall - PreFootfall;

UpliftPct_num = UpliftPct; /* explicit numeric var */

run;

proc print data=work.diff;

run;

OUTPUT:


Obs	MallID	MallType	Season	Event	PreFootfall	PostFootfall	PromoCostUSD	UpliftAbs	UpliftPct	Diff	UpliftPct_num
1	M001	Regional	Peak	WinterFête	12000	15000	25000	3000	25.00	3000	25.0000
2	M002	Community	OffPeak	BookFair	4200	5200	4800	1000	23.81	1000	23.8095
3	M003	Outlet	Peak	ClearanceBash	7000	9800	12000	2800	40.00	2800	40.0000
4	M004	Regional	OffPeak	FoodCarnival	9400	10100	9000	700	7.45	700	7.4468
5	M005	Community	Peak	MusicWeekend	5300	6100	7200	800	15.09	800	15.0943
6	M006	Outlet	OffPeak	NightMarket	4100	4500	3500	400	9.76	400	9.7561
7	M007	Regional	Peak	DiwaliFest	15000	19700	39000	4700	31.33	4700	31.3333
8	M008	Community	OffPeak	HandicraftExpo	3600	4300	3200	700	19.44	700	19.4444
9	M009	Outlet	Peak	SummerPromo	8200	8900	6800	700	8.54	700	8.5366
10	M010	Regional	OffPeak	KidsFestival	6800	7400	5600	600	8.82	600	8.8235

/* Paired test across all malls: Wilcoxon signed-rank (paired) */

proc univariate data=work.diff;

var Diff;

ods select TestsForLocation;

title "Paired (pre vs post) — Signed Rank & Sign Test for median difference";

run;

OUTPUT:

Paired (pre vs post) — Signed Rank & Sign Test for median difference

The UNIVARIATE Procedure

Variable: Diff


Tests for Location: Mu0=0
Test	Statistic		p Value
Student's t	t	3.365545	Pr > \|t\|	0.0083
Sign	M	5	Pr >= \|M\|	0.0020
Signed Rank	S	27.5	Pr >= \|S\|	0.0020

6. Prepare data for plotting (long format: 'Period'=Pre/Post)

data work.plotdata;

set work.mall_joined;

Period = 'Pre'; Footfall = PreFootfall; output;

Period = 'Post'; Footfall = PostFootfall; output;

keep MallID MallType Season Event Period Footfall;

run;

proc print data=work.plotdata;

run;

OUTPUT:


Obs	MallID	MallType	Season	Event	Period	Footfall
1	M001	Regional	Peak	WinterFête	Pre	12000
2	M001	Regional	Peak	WinterFête	Pos	15000
3	M002	Community	OffPeak	BookFair	Pre	4200
4	M002	Community	OffPeak	BookFair	Pos	5200
5	M003	Outlet	Peak	ClearanceBash	Pre	7000
6	M003	Outlet	Peak	ClearanceBash	Pos	9800
7	M004	Regional	OffPeak	FoodCarnival	Pre	9400
8	M004	Regional	OffPeak	FoodCarnival	Pos	10100
9	M005	Community	Peak	MusicWeekend	Pre	5300
10	M005	Community	Peak	MusicWeekend	Pos	6100
11	M006	Outlet	OffPeak	NightMarket	Pre	4100
12	M006	Outlet	OffPeak	NightMarket	Pos	4500
13	M007	Regional	Peak	DiwaliFest	Pre	15000
14	M007	Regional	Peak	DiwaliFest	Pos	19700
15	M008	Community	OffPeak	HandicraftExpo	Pre	3600
16	M008	Community	OffPeak	HandicraftExpo	Pos	4300
17	M009	Outlet	Peak	SummerPromo	Pre	8200
18	M009	Outlet	Peak	SummerPromo	Pos	8900
19	M010	Regional	OffPeak	KidsFestival	Pre	6800
20	M010	Regional	OffPeak	KidsFestival	Pos	7400

7. Plot pre/post trends

proc sgpanel data=work.plotdata;

panelby MallType / columns=1 spacing=10 novarname;

series x=Period y=Footfall / group=MallID markers lineattrs=(thickness=2);

scatter x=Period y=Footfall / group=MallID;

rowaxis label="Footfall (visitors)";

colaxis discreteorder=data;

title "Pre vs Post footfall for each mall, panelled by MallType";

run;

OUTPUT:

The SGPanel Procedure

proc sql;

create table work.mean_by_type as

select MallType, Period, mean(Footfall) as MeanFootfall

from work.plotdata

group by MallType, Period;

quit;

proc print data=work.mean_by_type;

run;

OUTPUT:


Obs	MallType	Period	MeanFootfall
1	Community	Pos	5200.00
2	Community	Pre	4366.67
3	Outlet	Pos	7733.33
4	Outlet	Pre	6433.33
5	Regional	Pos	13050.00
6	Regional	Pre	10800.00

proc sgplot data=work.mean_by_type;

series x=Period y=MeanFootfall / group=MallType markers;

yaxis label="Mean Footfall";

title "Average Pre/Post Footfall by MallType";

run;

OUTPUT:

The SGPlot Procedure

8. Macro: Evaluate ROI of promotions across MallType

- Inputs: revenue_per_visitor (USD), conversion_rate (fraction of extra visitors who translate to spend),

optionally list of MallType (defaults to all)

- ROI formula (simple marketing ROI): ROI = (IncrementalRevenue - PromoCost) / PromoCost

where IncrementalRevenue = IncrementalVisitors * conversion_rate * revenue_per_visitor

- We'll loop by MallType and print ROI and related metrics.

%macro ROI_by_type(rev_per_visitor=2.50, conv_rate=0.05);

/* rev_per_visitor: expected average revenue per visiting customer (USD)

conv_rate: proportion of additional visitors who make a purchase (0-1) */

proc sql;

create table work.roi as

select MallType,

sum(PreFootfall) as SumPre format=8.,

sum(PostFootfall) as SumPost format=8.,

sum(PostFootfall - PreFootfall) as SumUplift format=8.,

sum(PromoCostUSD) as TotalPromoCost format=dollar12.2

from work.mall_joined

group by MallType;

quit;

data work.roi_calc;

set work.roi;

RevenuePerVisitor = &rev_per_visitor;

ConversionRate = &conv_rate;

IncrementalVisitors = SumUplift;

IncrementalRevenue = IncrementalVisitors * ConversionRate * RevenuePerVisitor;

NetGainUSD = IncrementalRevenue - TotalPromoCost;

/* Avoid divide-by-zero */

if TotalPromoCost > 0 then ROI = NetGainUSD / TotalPromoCost;

else ROI = .;

/* ROI percent for readability */

ROI_pct = ROI * 100;

run;

title "ROI Summary by MallType (rev_per_visitor=&rev_per_visitor, conv_rate=&conv_rate)";

proc print data=work.roi_calc noobs label;

var MallType SumPre SumPost IncrementalVisitors TotalPromoCost IncrementalRevenue NetGainUSD ROI_pct;

label SumPre = "Sum PreFootfall"

SumPost = "Sum PostFootfall"

IncrementalVisitors = "Incremental Visitors"

TotalPromoCost = "Total Promo Cost (USD)"

IncrementalRevenue = "Incremental Revenue (USD)"

NetGainUSD = "Net Gain (USD)"

ROI_pct = "ROI (%)";

run;

%mend ROI_by_type;

%ROI_by_type(rev_per_visitor=3.00, conv_rate=0.07);

OUTPUT:

ROI Summary by MallType (rev_per_visitor=3.00, conv_rate=0.07)


MallType	Sum PreFootfall	Sum PostFootfall	Incremental Visitors	Total Promo Cost (USD)	Incremental Revenue (USD)	Net Gain (USD)	ROI (%)
Community	13100	15600	2500	$15,200.00	525	-14675	-96.5461
Outlet	19300	23200	3900	$22,300.00	819	-21481	-96.3274
Regional	43200	52200	9000	$78,600.00	1890	-76710	-97.5954

To Visit My Previous Proc Means And Nway Option:Click Here

To Visit My Previous Proc Means And CharType Option:Click Here

To Visit My Previous SAS Functions:Click Here

To Visit My Previous Length Statement Using In Many Ways:Click Here

Follow Us On :

--- FOLLOW OUR BLOG FOR MORE INFORMATION.

--->PLEASE DO COMMENTS AND SHARE OUR BLOG.

SAS Learning Hub

Monday, 10 November 2025

305.FAMILY MALL PROMOTION ANALYSIS USING | PROC SQL | PROC MEANS | PROC UNIVARIATE | PROC SGPLOT | PROC SGPANEL | AND MACRO ROI EVALUATION FOR SEASONAL FOOTFALL UPLIFT AND RETURN ON INVESTMENT CALCULATION

FAMILY MALL PROMOTION ANALYSIS USING | PROC SQL | PROC MEANS | PROC UNIVARIATE | PROC SGPLOT | PROC SGPANEL | AND MACRO ROI EVALUATION FOR SEASONAL FOOTFALL UPLIFT AND RETURN ON INVESTMENT CALCULATION

Follow Us On :

No comments:

Post a Comment

Popular Posts