294.HYD_MALL_FAMILY — A COMPLETE SAS PROJECT USING DATA STEP | PROC SQL | PROC SORT | PROC FREQ | PROC MEANS | PROC REPORT | MACRO FOR SUMMARY GENERATION

HYD_MALL_FAMILY — A COMPLETE SAS PROJECT USING DATA STEP | PROC SQL | PROC SORT | PROC FREQ | PROC MEANS | PROC REPORT | MACRO FOR SUMMARY GENERATION

options nodate nonumber linesize=120;

/* 1) Create the primary dataset with a DATA step */

data work.hyd_mall_family;

    infile datalines dsd dlm='|' truncover;

    length MallID $6 MallName $50 Type $20 Area $30 OpenDate $10

           NumStores 8 AnchorStores 8 Floors 8 ParkingSpots 8

           AvgMonthlyVisitors 8 MonthlyRevenue 8 IsOpen $1 Rating 8;

    input MallID $ MallName :$50. Type :$20. Area :$30.

          OpenDate :$10. NumStores AnchorStores Floors ParkingSpots

          AvgMonthlyVisitors MonthlyRevenue IsOpen $ Rating;

    /* Convert character OpenDate (YYYY-MM-DD) to SAS date */

    format OpenDate_d yymmdd10.;

    OpenDate_d = input(OpenDate, yymmdd10.);

    /* Create derived variables */

    Category = upcase(Type); /* standardized type field */

    /* Revenue per visitor and stores-per-floor example derivations */

    if AvgMonthlyVisitors > 0 then RevenuePerVisitor = MonthlyRevenue / AvgMonthlyVisitors;

    else RevenuePerVisitor = .;

    if Floors > 0 then StoresPerFloor = NumStores / Floors;

    else StoresPerFloor = .;

    /* Simple flag for large mall */

length MallSize $12;

    if NumStores >= 100 or ParkingSpots >= 2000 then MallSize="Large";

    else if NumStores >= 40 then MallSize="Medium";

    else MallSize="Small";

    drop OpenDate; /* keep the parsed OpenDate_d */

datalines;

M001|Galleria Hyderabad|Luxury|Banjara Hills|2015-11-10|120|4|5|2500|150000|22000000|Y|4

M002|NeighMart Kukatpally|Neighborhood|Kukatpally|2018-06-05|45|1|3|400|45000|3800000|Y|3

M003|OutletHub PharmaCity|Outlet|Kothur|2020-02-18|60|0|2|300|40000|1800000|Y|3

M004|CineMax Multiplex|Multiplex|Madhapur|2012-09-22|20|0|4|500|90000|5000000|Y|4

M005|DailyGrocers Begumpet|Grocery|Begumpet|2019-05-11|30|0|1|150|30000|1200000|Y|4

M006|ElectroBazaar LB Nagar|Electronics Bazaar|LB Nagar|2017-08-03|80|0|2|600|60000|3500000|Y|3

M007|LuxurySquare Jubilee|Luxury|Jubilee Hills|2016-12-01|95|3|6|2200|130000|18000000|Y|5

M008|TownCenter Ameerpet|Neighborhood|Ameerpet|2021-03-14|50|1|3|500|52000|4200000|Y|3

M009|FactoryOutlet Mehdipatnam|Outlet|Mehdipatnam|2014-10-07|70|0|2|450|48000|2100000|Y|3

M010|MoviePlex Kukatpally|Multiplex|Kukatpally|2010-04-30|18|0|5|350|80000|4200000|N|3

M011|GreenGrocers Secunderabad|Grocery|Secunderabad|2022-01-20|28|0|1|100|25000|900000|Y|4

M012|TechBazar HitechCity|Electronics Bazaar|Hitech City|2013-07-15|110|2|4|1800|140000|12500000|Y|5

M013|SilverArcade Kompally|Luxury|Kompally|2019-09-09|60|1|4|900|70000|6200000|Y|4

M014|NeighborhoodPlaza Chaderghat|Neighborhood|Chaderghat|2011-01-02|38|0|2|200|33000|1500000|Y|2

M015|OutletPoint Uppal|Outlet|Uppal|2018-11-11|55|0|2|250|36000|1700000|Y|3

M016|MegaMall Metro|Multiplex|Secunderabad|2008-06-18|200|6|8|5000|320000|45000000|Y|5

;

run;

proc print data=work.hyd_mall_family;

run;

Output:

Obs	MallID	MallName	Type	Area	NumStores	AnchorStores	Floors	ParkingSpots	AvgMonthlyVisitors	MonthlyRevenue	IsOpen	Rating	OpenDate_d	Category	RevenuePerVisitor	StoresPerFloor	MallSize
1	M001	Galleria Hyderabad	Luxury	Banjara Hills	120	4	5	2500	150000	22000000	Y	4	2015-11-10	LUXURY	146.667	24.0000	Large
2	M002	NeighMart Kukatpally	Neighborhood	Kukatpally	45	1	3	400	45000	3800000	Y	3	2018-06-05	NEIGHBORHOOD	84.444	15.0000	Medium
3	M003	OutletHub PharmaCity	Outlet	Kothur	60	0	2	300	40000	1800000	Y	3	2020-02-18	OUTLET	45.000	30.0000	Medium
4	M004	CineMax Multiplex	Multiplex	Madhapur	20	0	4	500	90000	5000000	Y	4	2012-09-22	MULTIPLEX	55.556	5.0000	Small
5	M005	DailyGrocers Begumpet	Grocery	Begumpet	30	0	1	150	30000	1200000	Y	4	2019-05-11	GROCERY	40.000	30.0000	Small
6	M006	ElectroBazaar LB Nagar	Electronics Bazaar	LB Nagar	80	0	2	600	60000	3500000	Y	3	2017-08-03	ELECTRONICS BAZAAR	58.333	40.0000	Medium
7	M007	LuxurySquare Jubilee	Luxury	Jubilee Hills	95	3	6	2200	130000	18000000	Y	5	2016-12-01	LUXURY	138.462	15.8333	Large
8	M008	TownCenter Ameerpet	Neighborhood	Ameerpet	50	1	3	500	52000	4200000	Y	3	2021-03-14	NEIGHBORHOOD	80.769	16.6667	Medium
9	M009	FactoryOutlet Mehdipatnam	Outlet	Mehdipatnam	70	0	2	450	48000	2100000	Y	3	2014-10-07	OUTLET	43.750	35.0000	Medium
10	M010	MoviePlex Kukatpally	Multiplex	Kukatpally	18	0	5	350	80000	4200000	N	3	2010-04-30	MULTIPLEX	52.500	3.6000	Small
11	M011	GreenGrocers Secunderabad	Grocery	Secunderabad	28	0	1	100	25000	900000	Y	4	2022-01-20	GROCERY	36.000	28.0000	Small
12	M012	TechBazar HitechCity	Electronics Bazaar	Hitech City	110	2	4	1800	140000	12500000	Y	5	2013-07-15	ELECTRONICS BAZAAR	89.286	27.5000	Large
13	M013	SilverArcade Kompally	Luxury	Kompally	60	1	4	900	70000	6200000	Y	4	2019-09-09	LUXURY	88.571	15.0000	Medium
14	M014	NeighborhoodPlaza Chaderghat	Neighborhood	Chaderghat	38	0	2	200	33000	1500000	Y	2	2011-01-02	NEIGHBORHOOD	45.455	19.0000	Small
15	M015	OutletPoint Uppal	Outlet	Uppal	55	0	2	250	36000	1700000	Y	3	2018-11-11	OUTLET	47.222	27.5000	Medium
16	M016	MegaMall Metro	Multiplex	Secunderabad	200	6	8	5000	320000	45000000	Y	5	2008-06-18	MULTIPLEX	140.625	25.0000	Large

/* 2) Create a small second dataset for reviews/contacts to demonstrate PROC SQL joins */

data work.mall_reviews;

    length MallID $6 Reviewer $30 ReviewDate $10 Stars 8 Comment $100;

    infile datalines dsd dlm='|' truncover;

    input MallID $ Reviewer :$30. ReviewDate :$10. Stars Comment :$100.;

    ReviewDate_d = input(ReviewDate, yymmdd10.);

    format ReviewDate_d yymmdd10.;

    drop ReviewDate;

datalines;

M001|Anita R|2024-06-10|5|Excellent luxury experience and helpful staff

M002|Suresh K|2023-11-02|3|Convenient location but limited parking

M007|Priya L|2022-12-15|5|Great variety of brands

M012|Ramesh P|2024-01-08|4|Huge electronics selection

M016|Deepa S|2020-07-20|5|Very large and well managed

M010|Vikram M|2021-02-11|2|Closed on weekdays at times

;

run;

proc print data=work.mall_reviews;

run;

Output:

Obs	MallID	Reviewer	Stars	Comment	ReviewDate_d
1	M001	Anita R	5	Excellent luxury experience and helpful staff	2024-06-10
2	M002	Suresh K	3	Convenient location but limited parking	2023-11-02
3	M007	Priya L	5	Great variety of brands	2022-12-15
4	M012	Ramesh P	4	Huge electronics selection	2024-01-08
5	M016	Deepa S	5	Very large and well managed	2020-07-20
6	M010	Vikram M	2	Closed on weekdays at times	2021-02-11

/* 3) Example PROC SQL join to merge average review stars into the main dataset */

proc sql;

    create table work.hyd_mall_with_reviews as

    select a.*,

           b_avg.AvgStars, b_cnt.ReviewCount

    from work.hyd_mall_family as a

    left join (

        select MallID, mean(Stars) as AvgStars

        from work.mall_reviews

        group by MallID

    ) as b_avg

    on a.MallID = b_avg.MallID

    left join (

        select MallID, count(*) as ReviewCount

        from work.mall_reviews

        group by MallID

    ) as b_cnt

    on a.MallID = b_cnt.MallID;

quit;

proc print data=work.hyd_mall_with_reviews;

run;

Output:

Obs	MallID	MallName	Type	Area	NumStores	AnchorStores	Floors	ParkingSpots	AvgMonthlyVisitors	MonthlyRevenue	IsOpen	Rating	OpenDate_d	Category	RevenuePerVisitor	StoresPerFloor	MallSize	AvgStars	ReviewCount
1	M001	Galleria Hyderabad	Luxury	Banjara Hills	120	4	5	2500	150000	22000000	Y	4	2015-11-10	LUXURY	146.667	24.0000	Large	5	1
2	M002	NeighMart Kukatpally	Neighborhood	Kukatpally	45	1	3	400	45000	3800000	Y	3	2018-06-05	NEIGHBORHOOD	84.444	15.0000	Medium	3	1
3	M003	OutletHub PharmaCity	Outlet	Kothur	60	0	2	300	40000	1800000	Y	3	2020-02-18	OUTLET	45.000	30.0000	Medium	.	.
4	M004	CineMax Multiplex	Multiplex	Madhapur	20	0	4	500	90000	5000000	Y	4	2012-09-22	MULTIPLEX	55.556	5.0000	Small	.	.
5	M005	DailyGrocers Begumpet	Grocery	Begumpet	30	0	1	150	30000	1200000	Y	4	2019-05-11	GROCERY	40.000	30.0000	Small	.	.
6	M006	ElectroBazaar LB Nagar	Electronics Bazaar	LB Nagar	80	0	2	600	60000	3500000	Y	3	2017-08-03	ELECTRONICS BAZAAR	58.333	40.0000	Medium	.	.
7	M007	LuxurySquare Jubilee	Luxury	Jubilee Hills	95	3	6	2200	130000	18000000	Y	5	2016-12-01	LUXURY	138.462	15.8333	Large	5	1
8	M008	TownCenter Ameerpet	Neighborhood	Ameerpet	50	1	3	500	52000	4200000	Y	3	2021-03-14	NEIGHBORHOOD	80.769	16.6667	Medium	.	.
9	M009	FactoryOutlet Mehdipatnam	Outlet	Mehdipatnam	70	0	2	450	48000	2100000	Y	3	2014-10-07	OUTLET	43.750	35.0000	Medium	.	.
10	M010	MoviePlex Kukatpally	Multiplex	Kukatpally	18	0	5	350	80000	4200000	N	3	2010-04-30	MULTIPLEX	52.500	3.6000	Small	2	1
11	M011	GreenGrocers Secunderabad	Grocery	Secunderabad	28	0	1	100	25000	900000	Y	4	2022-01-20	GROCERY	36.000	28.0000	Small	.	.
12	M012	TechBazar HitechCity	Electronics Bazaar	Hitech City	110	2	4	1800	140000	12500000	Y	5	2013-07-15	ELECTRONICS BAZAAR	89.286	27.5000	Large	4	1
13	M013	SilverArcade Kompally	Luxury	Kompally	60	1	4	900	70000	6200000	Y	4	2019-09-09	LUXURY	88.571	15.0000	Medium	.	.
14	M014	NeighborhoodPlaza Chaderghat	Neighborhood	Chaderghat	38	0	2	200	33000	1500000	Y	2	2011-01-02	NEIGHBORHOOD	45.455	19.0000	Small	.	.
15	M015	OutletPoint Uppal	Outlet	Uppal	55	0	2	250	36000	1700000	Y	3	2018-11-11	OUTLET	47.222	27.5000	Medium	.	.
16	M016	MegaMall Metro	Multiplex	Secunderabad	200	6	8	5000	320000	45000000	Y	5	2008-06-18	MULTIPLEX	140.625	25.0000	Large	5	1

/* 4) Sorting the dataset by Type and MonthlyRevenue (descending) */

proc sort data=work.hyd_mall_with_reviews out=work.hyd_mall_sorted;

    by Type descending MonthlyRevenue;

run;

proc print data=work.hyd_mall_sorted;

run;

Output:

Obs	MallID	MallName	Type	Area	NumStores	AnchorStores	Floors	ParkingSpots	AvgMonthlyVisitors	MonthlyRevenue	IsOpen	Rating	OpenDate_d	Category	RevenuePerVisitor	StoresPerFloor	MallSize	AvgStars	ReviewCount
1	M012	TechBazar HitechCity	Electronics Bazaar	Hitech City	110	2	4	1800	140000	12500000	Y	5	2013-07-15	ELECTRONICS BAZAAR	89.286	27.5000	Large	4	1
2	M006	ElectroBazaar LB Nagar	Electronics Bazaar	LB Nagar	80	0	2	600	60000	3500000	Y	3	2017-08-03	ELECTRONICS BAZAAR	58.333	40.0000	Medium	.	.
3	M005	DailyGrocers Begumpet	Grocery	Begumpet	30	0	1	150	30000	1200000	Y	4	2019-05-11	GROCERY	40.000	30.0000	Small	.	.
4	M011	GreenGrocers Secunderabad	Grocery	Secunderabad	28	0	1	100	25000	900000	Y	4	2022-01-20	GROCERY	36.000	28.0000	Small	.	.
5	M001	Galleria Hyderabad	Luxury	Banjara Hills	120	4	5	2500	150000	22000000	Y	4	2015-11-10	LUXURY	146.667	24.0000	Large	5	1
6	M007	LuxurySquare Jubilee	Luxury	Jubilee Hills	95	3	6	2200	130000	18000000	Y	5	2016-12-01	LUXURY	138.462	15.8333	Large	5	1
7	M013	SilverArcade Kompally	Luxury	Kompally	60	1	4	900	70000	6200000	Y	4	2019-09-09	LUXURY	88.571	15.0000	Medium	.	.
8	M016	MegaMall Metro	Multiplex	Secunderabad	200	6	8	5000	320000	45000000	Y	5	2008-06-18	MULTIPLEX	140.625	25.0000	Large	5	1
9	M004	CineMax Multiplex	Multiplex	Madhapur	20	0	4	500	90000	5000000	Y	4	2012-09-22	MULTIPLEX	55.556	5.0000	Small	.	.
10	M010	MoviePlex Kukatpally	Multiplex	Kukatpally	18	0	5	350	80000	4200000	N	3	2010-04-30	MULTIPLEX	52.500	3.6000	Small	2	1
11	M008	TownCenter Ameerpet	Neighborhood	Ameerpet	50	1	3	500	52000	4200000	Y	3	2021-03-14	NEIGHBORHOOD	80.769	16.6667	Medium	.	.
12	M002	NeighMart Kukatpally	Neighborhood	Kukatpally	45	1	3	400	45000	3800000	Y	3	2018-06-05	NEIGHBORHOOD	84.444	15.0000	Medium	3	1
13	M014	NeighborhoodPlaza Chaderghat	Neighborhood	Chaderghat	38	0	2	200	33000	1500000	Y	2	2011-01-02	NEIGHBORHOOD	45.455	19.0000	Small	.	.
14	M009	FactoryOutlet Mehdipatnam	Outlet	Mehdipatnam	70	0	2	450	48000	2100000	Y	3	2014-10-07	OUTLET	43.750	35.0000	Medium	.	.
15	M003	OutletHub PharmaCity	Outlet	Kothur	60	0	2	300	40000	1800000	Y	3	2020-02-18	OUTLET	45.000	30.0000	Medium	.	.
16	M015	OutletPoint Uppal	Outlet	Uppal	55	0	2	250	36000	1700000	Y	3	2018-11-11	OUTLET	47.222	27.5000	Medium	.	.

/* 5) Frequency counts for mall types and open status */

proc freq data=work.hyd_mall_sorted;

    tables Type IsOpen / nocum nopercent;

    title 'FREQUENCY: MALL TYPE AND OPEN STATUS';

run;

Output:

FREQUENCY: MALL TYPE AND OPEN STATUS

The FREQ Procedure

Type	Frequency
Electronics Bazaar	2
Grocery	2
Luxury	3
Multiplex	3
Neighborhood	3
Outlet	3

IsOpen	Frequency
N	1
Y	15

/* 6) Descriptive statistics for numeric variables */

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

    var NumStores AnchorStores Floors ParkingSpots AvgMonthlyVisitors MonthlyRevenue Rating RevenuePerVisitor StoresPerFloor;

    class Type;

    title 'DESCRIPTIVE STATISTICS BY MALL TYPE';

run;

Output:

DESCRIPTIVE STATISTICS BY MALL TYPE

The MEANS Procedure

Type	N Obs	Variable	N	Mean	Median	Minimum	Maximum	Std Dev	Sum
Electronics Bazaar	2	NumStores AnchorStores Floors ParkingSpots AvgMonthlyVisitors MonthlyRevenue Rating RevenuePerVisitor StoresPerFloor	2 2 2 2 2 2 2 2 2	95.0000000 1.0000000 3.0000000 1200.00 100000.00 8000000.00 4.0000000 73.8095238 33.7500000	95.0000000 1.0000000 3.0000000 1200.00 100000.00 8000000.00 4.0000000 73.8095238 33.7500000	80.0000000 0 2.0000000 600.0000000 60000.00 3500000.00 3.0000000 58.3333333 27.5000000	110.0000000 2.0000000 4.0000000 1800.00 140000.00 12500000.00 5.0000000 89.2857143 40.0000000	21.2132034 1.4142136 1.4142136 848.5281374 56568.54 6363961.03 1.4142136 21.8866385 8.8388348	190.0000000 2.0000000 6.0000000 2400.00 200000.00 16000000.00 8.0000000 147.6190476 67.5000000
Grocery	2	NumStores AnchorStores Floors ParkingSpots AvgMonthlyVisitors MonthlyRevenue Rating RevenuePerVisitor StoresPerFloor	2 2 2 2 2 2 2 2 2	29.0000000 0 1.0000000 125.0000000 27500.00 1050000.00 4.0000000 38.0000000 29.0000000	29.0000000 0 1.0000000 125.0000000 27500.00 1050000.00 4.0000000 38.0000000 29.0000000	28.0000000 0 1.0000000 100.0000000 25000.00 900000.00 4.0000000 36.0000000 28.0000000	30.0000000 0 1.0000000 150.0000000 30000.00 1200000.00 4.0000000 40.0000000 30.0000000	1.4142136 0 0 35.3553391 3535.53 212132.03 0 2.8284271 1.4142136	58.0000000 0 2.0000000 250.0000000 55000.00 2100000.00 8.0000000 76.0000000 58.0000000
Luxury	3	NumStores AnchorStores Floors ParkingSpots AvgMonthlyVisitors MonthlyRevenue Rating RevenuePerVisitor StoresPerFloor	3 3 3 3 3 3 3 3 3	91.6666667 2.6666667 5.0000000 1866.67 116666.67 15400000.00 4.3333333 124.5665446 18.2777778	95.0000000 3.0000000 5.0000000 2200.00 130000.00 18000000.00 4.0000000 138.4615385 15.8333333	60.0000000 1.0000000 4.0000000 900.0000000 70000.00 6200000.00 4.0000000 88.5714286 15.0000000	120.0000000 4.0000000 6.0000000 2500.00 150000.00 22000000.00 5.0000000 146.6666667 24.0000000	30.1385689 1.5275252 1.0000000 850.4900548 41633.32 8214621.11 0.5773503 31.4414903 4.9730757	275.0000000 8.0000000 15.0000000 5600.00 350000.00 46200000.00 13.0000000 373.6996337 54.8333333
Multiplex	3	NumStores AnchorStores Floors ParkingSpots AvgMonthlyVisitors MonthlyRevenue Rating RevenuePerVisitor StoresPerFloor	3 3 3 3 3 3 3 3 3	79.3333333 2.0000000 5.6666667 1950.00 163333.33 18066666.67 4.0000000 82.8935185 11.2000000	20.0000000 0 5.0000000 500.0000000 90000.00 5000000.00 4.0000000 55.5555556 5.0000000	18.0000000 0 4.0000000 350.0000000 80000.00 4200000.00 3.0000000 52.5000000 3.6000000	200.0000000 6.0000000 8.0000000 5000.00 320000.00 45000000.00 5.0000000 140.6250000 25.0000000	104.5051833 3.4641016 2.0816660 2642.44 135769.41 23328380.43 1.0000000 50.0202666 11.9716331	238.0000000 6.0000000 17.0000000 5850.00 490000.00 54200000.00 12.0000000 248.6805556 33.6000000
Neighborhood	3	NumStores AnchorStores Floors ParkingSpots AvgMonthlyVisitors MonthlyRevenue Rating RevenuePerVisitor StoresPerFloor	3 3 3 3 3 3 3 3 3	44.3333333 0.6666667 2.6666667 366.6666667 43333.33 3166666.67 2.6666667 70.2227402 16.8888889	45.0000000 1.0000000 3.0000000 400.0000000 45000.00 3800000.00 3.0000000 80.7692308 16.6666667	38.0000000 0 2.0000000 200.0000000 33000.00 1500000.00 2.0000000 45.4545455 15.0000000	50.0000000 1.0000000 3.0000000 500.0000000 52000.00 4200000.00 3.0000000 84.4444444 19.0000000	6.0277138 0.5773503 0.5773503 152.7525232 9609.02 1457166.20 0.5773503 21.5284557 2.0092379	133.0000000 2.0000000 8.0000000 1100.00 130000.00 9500000.00 8.0000000 210.6682207 50.6666667
Outlet	3	NumStores AnchorStores Floors ParkingSpots AvgMonthlyVisitors MonthlyRevenue Rating RevenuePerVisitor StoresPerFloor	3 3 3 3 3 3 3 3 3	61.6666667 0 2.0000000 333.3333333 41333.33 1866666.67 3.0000000 45.3240741 30.8333333	60.0000000 0 2.0000000 300.0000000 40000.00 1800000.00 3.0000000 45.0000000 30.0000000	55.0000000 0 2.0000000 250.0000000 36000.00 1700000.00 3.0000000 43.7500000 27.5000000	70.0000000 0 2.0000000 450.0000000 48000.00 2100000.00 3.0000000 47.2222222 35.0000000	7.6376262 0 0 104.0833000 6110.10 208166.60 0 1.7586500 3.8188131	185.0000000 0 6.0000000 1000.00 124000.00 5600000.00 9.0000000 135.9722222 92.5000000

/* 7) Generate a readable report using PROC REPORT */

proc report data=work.hyd_mall_sorted nowd;

    columns Type MallID MallName Area MallSize NumStores Floors ParkingSpots AvgMonthlyVisitors MonthlyRevenue Rating AvgStars ReviewCount;

    define Type / group 'Mall Type';

    define MallID / display 'ID';

    define MallName / display 'Mall Name' width=30;

    define Area / display 'Area';

    define MallSize / display 'Size';

    define NumStores / analysis 'Stores' format=8.;

    define Floors / analysis 'Floors';

    define ParkingSpots / analysis 'Parking';

    define AvgMonthlyVisitors / analysis 'Avg Visitors' format=comma12.;

    define MonthlyRevenue / analysis 'Monthly Revenue' format=dollar12.;

    define Rating / analysis 'Rating';

    define AvgStars / analysis 'Avg Review Stars' format=5.2;

    define ReviewCount / analysis 'Review Count';

    title 'MALL SUMMARY REPORT';

run;

Output:

MALL SUMMARY REPORT

Mall Type	ID	Mall Name	Area	Size	Stores	Floors	Parking	Avg Visitors	Monthly Revenue	Rating	Avg Review Stars	Review Count
Electronics Bazaar	M012	TechBazar HitechCity	Hitech City	Large	110	4	1800	140,000	$12,500,000	5	4.00	1
	M006	ElectroBazaar LB Nagar	LB Nagar	Medium	80	2	600	60,000	$3,500,000	3	.	.
Grocery	M005	DailyGrocers Begumpet	Begumpet	Small	30	1	150	30,000	$1,200,000	4	.	.
	M011	GreenGrocers Secunderabad	Secunderabad	Small	28	1	100	25,000	$900,000	4	.	.
Luxury	M001	Galleria Hyderabad	Banjara Hills	Large	120	5	2500	150,000	$22,000,000	4	5.00	1
	M007	LuxurySquare Jubilee	Jubilee Hills	Large	95	6	2200	130,000	$18,000,000	5	5.00	1
	M013	SilverArcade Kompally	Kompally	Medium	60	4	900	70,000	$6,200,000	4	.	.
Multiplex	M016	MegaMall Metro	Secunderabad	Large	200	8	5000	320,000	$45,000,000	5	5.00	1
	M004	CineMax Multiplex	Madhapur	Small	20	4	500	90,000	$5,000,000	4	.	.
	M010	MoviePlex Kukatpally	Kukatpally	Small	18	5	350	80,000	$4,200,000	3	2.00	1
Neighborhood	M008	TownCenter Ameerpet	Ameerpet	Medium	50	3	500	52,000	$4,200,000	3	.	.
	M002	NeighMart Kukatpally	Kukatpally	Medium	45	3	400	45,000	$3,800,000	3	3.00	1
	M014	NeighborhoodPlaza Chaderghat	Chaderghat	Small	38	2	200	33,000	$1,500,000	2	.	.
Outlet	M009	FactoryOutlet Mehdipatnam	Mehdipatnam	Medium	70	2	450	48,000	$2,100,000	3	.	.
	M003	OutletHub PharmaCity	Kothur	Medium	60	2	300	40,000	$1,800,000	3	.	.
	M015	OutletPoint Uppal	Uppal	Medium	55	2	250	36,000	$1,700,000	3	.	.

/* 8) Macro that generates summary tables for each mall type */

%macro MallTypeSummary(in=work.hyd_mall_with_reviews, outdir=work);

    /* Step 1: get distinct mall types into a macro variable list (no quotes) */

    proc sql noprint;

        select distinct strip(Type)

            into :type_list separated by '|'

        from &in.;

    quit;

    %put NOTE: Mall types found = &type_list.;

    /* Step 2: loop over each type */

    %let i = 1;

    %let this_type = %scan(&type_list, &i, |);

    %do %while (%length(&this_type) > 0);

        proc sql;

            create table &outdir..summary_%sysfunc(tranwrd(%lowcase(&this_type),%str( ),_)) as

            select Type,

                   count(*) as N_Malls,

                   mean(NumStores) as Mean_Stores format=8.2,

                   mean(ParkingSpots) as Mean_Parking format=8.2,

                   mean(AvgMonthlyVisitors) as Mean_Visitors format=comma12.,

                   mean(MonthlyRevenue) as Mean_Revenue format=dollar12.,

                   mean(Rating) as Mean_Rating format=5.2

            from &in.

            where Type = "&this_type"

            group by Type;

        quit;

        title "Summary for Mall Type: &this_type";

        proc print data=&outdir..summary_%sysfunc(tranwrd(%lowcase(&this_type),%str( ),_)) noobs;

            var Type N_Malls Mean_Stores Mean_Parking Mean_Visitors Mean_Revenue Mean_Rating;

        run;

        %let i = %eval(&i + 1);

        %let this_type = %scan(&type_list, &i, |);

    %end;

    title;

%mend MallTypeSummary;

%MallTypeSummary(in=work.hyd_mall_with_reviews, outdir=work);

Output:

Summary for Mall Type: Electronics Bazaar

Type	N_Malls	Mean_Stores	Mean_Parking	Mean_Visitors	Mean_Revenue	Mean_Rating
Electronics Bazaar	2	95.00	1200.00	100,000	$8,000,000	4.00

Summary for Mall Type: Grocery

Type	N_Malls	Mean_Stores	Mean_Parking	Mean_Visitors	Mean_Revenue	Mean_Rating
Grocery	2	29.00	125.00	27,500	$1,050,000	4.00

Summary for Mall Type: Luxury

Type	N_Malls	Mean_Stores	Mean_Parking	Mean_Visitors	Mean_Revenue	Mean_Rating
Luxury	3	91.67	1866.67	116,667	$15,400,000	4.33

Summary for Mall Type: Multiplex

Type	N_Malls	Mean_Stores	Mean_Parking	Mean_Visitors	Mean_Revenue	Mean_Rating
Multiplex	3	79.33	1950.00	163,333	$18,066,667	4.00

Summary for Mall Type: Neighborhood

Type	N_Malls	Mean_Stores	Mean_Parking	Mean_Visitors	Mean_Revenue	Mean_Rating
Neighborhood	3	44.33	366.67	43,333	$3,166,667	2.67

Summary for Mall Type: Outlet

Type	N_Malls	Mean_Stores	Mean_Parking	Mean_Visitors	Mean_Revenue	Mean_Rating
Outlet	3	61.67	333.33	41,333	$1,866,667	3.00

/* 9) Example of using PROC SQL to create a cross-tab style table: average revenue by Type and MallSize */

proc sql;

    create table work.revenue_crosstab as

    select Type, MallSize,

           mean(MonthlyRevenue) as AvgRevenue format=dollar14.

    from work.hyd_mall_with_reviews

    group by Type, MallSize

    order by Type, MallSize;

quit;

proc print data=work.revenue_crosstab noobs;

    title 'AVERAGE MONTHLY REVENUE BY TYPE AND SIZE';

run;

Output:

AVERAGE MONTHLY REVENUE BY TYPE AND SIZE

Type	MallSize	AvgRevenue
Electronics Bazaar	Large	$12,500,000
Electronics Bazaar	Medium	$3,500,000
Grocery	Small	$1,050,000
Luxury	Large	$20,000,000
Luxury	Medium	$6,200,000
Multiplex	Large	$45,000,000
Multiplex	Small	$4,600,000
Neighborhood	Medium	$4,000,000
Neighborhood	Small	$1,500,000
Outlet	Medium	$1,866,667

To Visit My Previous Online Streaming Flatform Dataset:Click Here

To Visit My Previous Statistical Evaluation Of Clinical Trials:Click Here

To Visit My Previous Unlocking Retail Insights Dataset:Click Here

To Visit My Previous Sas Interview Questions-1:Click Here

Follow Us On : 

 

--- FOLLOW OUR BLOG FOR MORE INFORMATION.

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