394.Which city’s billboards give high revenue but secretly deliver low ROI—and why? A Complete Sas Analytics Project

Which city’s billboards give high revenue but secretly deliver low ROI—and why?A Complete Sas Analytics Project

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

HERE IN THIS PROJECT WE USED THESE SAS STATEMENTS —
DATA STEP | PROC SQL |  PROC PRINT | PROC SGPLOT | MACROS | PROC CORR | PROC MEANS | PROC FREQ | PROC UNIVARIATE | APPEND | PROC SORT | MERGE | PROC TRANSPOSE | PROC DATASETS DELETE | DATA FUNCTIONS

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1. INTRODUCTION

Outdoor advertising through billboards is one of the oldest yet most powerful marketing channels. Companies invest millions of rupees every year to place advertisements on highways, cities, and high-traffic areas.

However, not all billboards perform equally.

Some generate:

·       High exposure

·       Strong brand recall

·       Excellent ROI

Others:

·       Sit in low-traffic areas

·       Are overpriced

·       Show suspicious revenue patterns (fraud risk)

This project simulates a real-world billboard analytics system using SAS, focusing on:

·       Revenue analysis

·       ROI computation

·       Utilization classification

·       Fraud detection

·       Time-based analytics

·       SQL + DATA step integration

·       Macro-driven automation

2. BUSINESS CONTEXT

Why companies analyze billboard data?

Business leaders want answers to:

·       Which city billboards generate maximum ROI?

·       Which ad types perform better (Digital vs Static)?

·       Are some billboards reporting fake revenue?

·       Should we renew contracts or terminate them?

·       How does traffic affect revenue?

·       How long should ads be displayed?

This analysis supports:

·       Marketing strategy

·       Budget optimization

·       Fraud prevention

·       Vendor performance review

3. TABLE OF CONTENTS

1.     Dataset Creation

2.     Variable Explanation

3.     Date Handling (MDY, INTCK, INTNX)

4.     Data Cleaning (Character + Numeric functions)

5.     PROC SQL Analytics

6.     PROC FREQ & PROC MEANS

7.     PROC UNIVARIATE (Distribution checks)

8.     Correlation Analysis (PROC CORR)

9.     Visualization (PROC SGPLOT)

10.  Utilization Classification Macro

11.  Fraud Detection Macro

12.  Dataset Appending & Transposing

13.  SET, MERGE operations

14.  PROC DATASETS Delete

15.  Business Insights

16.  Interview-oriented Explanation

17. Conclusion

4. DATASET CREATION

data billboard_raw;

    length Billboard_ID $6 City $15 Ad_Type $10 Vendor $15;

    input Billboard_ID $ City $ Traffic_Count Ad_Type $ Exposure_Time Revenue Cost

          Start_Date :date9. End_Date   :date9. Vendor $;

    format Start_Date End_Date date9.;

datalines;

BB001 Hyderabad 120000 Digital 15 850000 500000 01JAN2025 31JAN2025 MediaOne

BB002 Mumbai 180000 Digital 20 1500000 900000 01JAN2025 31JAN2025 AdVision

BB003 Delhi 95000 Static 10 420000 300000 01JAN2025 31JAN2025 BrandMax

BB004 Chennai 110000 Static 12 480000 350000 01JAN2025 31JAN2025 MediaOne

BB005 Bengaluru 160000 Digital 18 980000 600000 01JAN2025 31JAN2025 AdVision

BB006 Pune 70000 Static 8 210000 180000 01JAN2025 31JAN2025 LocalAds

BB007 Kolkata 90000 Digital 14 530000 400000 01JAN2025 31JAN2025 BrandMax

BB008 Ahmedabad 60000 Static 7 190000 170000 01JAN2025 31JAN2025 LocalAds

BB009 Jaipur 50000 Static 6 160000 140000 01JAN2025 31JAN2025 LocalAds

BB010 Kochi 75000 Digital 10 310000 250000 01JAN2025 31JAN2025 MediaOne

BB011 Vizag 68000 Static 7 200000 180000 01JAN2025 31JAN2025 BrandMax

BB012 Indore 82000 Digital 11 360000 260000 01JAN2025 31JAN2025 AdVision

BB013 Surat 72000 Static 8 220000 190000 01JAN2025 31JAN2025 LocalAds

BB014 Bhopal 65000 Static 7 180000 170000 01JAN2025 31JAN2025 LocalAds

BB015 Coimbatore 88000 Digital 12 410000 300000 01JAN2025 31JAN2025 MediaOne

BB016 Noida 140000 Digital 16 760000 500000 01JAN2025 31JAN2025 AdVision

;

run;

proc print data=billboard_raw;

run;

OUTPUT:

Obs	Billboard_ID	City	Ad_Type	Vendor	Traffic_Count	Exposure_Time	Revenue	Cost	Start_Date	End_Date
1	BB001	Hyderabad	Digital	MediaOne	120000	15	850000	500000	01JAN2025	31JAN2025
2	BB002	Mumbai	Digital	AdVision	180000	20	1500000	900000	01JAN2025	31JAN2025
3	BB003	Delhi	Static	BrandMax	95000	10	420000	300000	01JAN2025	31JAN2025
4	BB004	Chennai	Static	MediaOne	110000	12	480000	350000	01JAN2025	31JAN2025
5	BB005	Bengaluru	Digital	AdVision	160000	18	980000	600000	01JAN2025	31JAN2025
6	BB006	Pune	Static	LocalAds	70000	8	210000	180000	01JAN2025	31JAN2025
7	BB007	Kolkata	Digital	BrandMax	90000	14	530000	400000	01JAN2025	31JAN2025
8	BB008	Ahmedabad	Static	LocalAds	60000	7	190000	170000	01JAN2025	31JAN2025
9	BB009	Jaipur	Static	LocalAds	50000	6	160000	140000	01JAN2025	31JAN2025
10	BB010	Kochi	Digital	MediaOne	75000	10	310000	250000	01JAN2025	31JAN2025
11	BB011	Vizag	Static	BrandMax	68000	7	200000	180000	01JAN2025	31JAN2025
12	BB012	Indore	Digital	AdVision	82000	11	360000	260000	01JAN2025	31JAN2025
13	BB013	Surat	Static	LocalAds	72000	8	220000	190000	01JAN2025	31JAN2025
14	BB014	Bhopal	Static	LocalAds	65000	7	180000	170000	01JAN2025	31JAN2025
15	BB015	Coimbatore	Digital	MediaOne	88000	12	410000	300000	01JAN2025	31JAN2025
16	BB016	Noida	Digital	AdVision	140000	16	760000	500000	01JAN2025	31JAN2025

5. VARIABLE EXPLANATION

Variable	Meaning
Billboard_ID	Unique billboard identifier
City	Location of billboard
Traffic_Count	Avg daily vehicles
Ad_Type	Digital / Static
Exposure_Time	Display duration per hour
Revenue	Revenue generated
Cost	Cost of billboard
Start_Date	Campaign start
End_Date	Campaign end
Vendor	Advertising vendor

6. DERIVED VARIABLES (ROI, DAYS, CLEANING)

data billboard_clean;

    set billboard_raw;

    /* Character Cleaning */

    City = propcase(strip(City));

    Vendor = upcase(trim(Vendor));

    Ad_Type = upcase(strip(Ad_Type));

    /* Date Calculations */

    Campaign_Days = intck('day', Start_Date, End_Date) + 1;

    Next_Renewal = intnx('month', End_Date, 1);

    /* ROI Calculation */

    ROI_Percentage = ((Revenue - Cost) / Cost) * 100;

    /* Exposure Efficiency */

    Exposure_Efficiency = Traffic_Count * Exposure_Time;

    format Next_Renewal date9.;

run;

proc print data=billboard_clean;

run;

OUTPUT:

Obs	Billboard_ID	City	Ad_Type	Vendor	Traffic_Count	Exposure_Time	Revenue	Cost	Start_Date	End_Date	Campaign_Days	Next_Renewal	ROI_Percentage	Exposure_Efficiency
1	BB001	Hyderabad	DIGITAL	MEDIAONE	120000	15	850000	500000	01JAN2025	31JAN2025	31	01FEB2025	70.0000	1800000
2	BB002	Mumbai	DIGITAL	ADVISION	180000	20	1500000	900000	01JAN2025	31JAN2025	31	01FEB2025	66.6667	3600000
3	BB003	Delhi	STATIC	BRANDMAX	95000	10	420000	300000	01JAN2025	31JAN2025	31	01FEB2025	40.0000	950000
4	BB004	Chennai	STATIC	MEDIAONE	110000	12	480000	350000	01JAN2025	31JAN2025	31	01FEB2025	37.1429	1320000
5	BB005	Bengaluru	DIGITAL	ADVISION	160000	18	980000	600000	01JAN2025	31JAN2025	31	01FEB2025	63.3333	2880000
6	BB006	Pune	STATIC	LOCALADS	70000	8	210000	180000	01JAN2025	31JAN2025	31	01FEB2025	16.6667	560000
7	BB007	Kolkata	DIGITAL	BRANDMAX	90000	14	530000	400000	01JAN2025	31JAN2025	31	01FEB2025	32.5000	1260000
8	BB008	Ahmedabad	STATIC	LOCALADS	60000	7	190000	170000	01JAN2025	31JAN2025	31	01FEB2025	11.7647	420000
9	BB009	Jaipur	STATIC	LOCALADS	50000	6	160000	140000	01JAN2025	31JAN2025	31	01FEB2025	14.2857	300000
10	BB010	Kochi	DIGITAL	MEDIAONE	75000	10	310000	250000	01JAN2025	31JAN2025	31	01FEB2025	24.0000	750000
11	BB011	Vizag	STATIC	BRANDMAX	68000	7	200000	180000	01JAN2025	31JAN2025	31	01FEB2025	11.1111	476000
12	BB012	Indore	DIGITAL	ADVISION	82000	11	360000	260000	01JAN2025	31JAN2025	31	01FEB2025	38.4615	902000
13	BB013	Surat	STATIC	LOCALADS	72000	8	220000	190000	01JAN2025	31JAN2025	31	01FEB2025	15.7895	576000
14	BB014	Bhopal	STATIC	LOCALADS	65000	7	180000	170000	01JAN2025	31JAN2025	31	01FEB2025	5.8824	455000
15	BB015	Coimbatore	DIGITAL	MEDIAONE	88000	12	410000	300000	01JAN2025	31JAN2025	31	01FEB2025	36.6667	1056000
16	BB016	Noida	DIGITAL	ADVISION	140000	16	760000	500000	01JAN2025	31JAN2025	31	01FEB2025	52.0000	2240000

·  STRIP / TRIM → remove unwanted spaces

·  PROPERCASE / UPCASE → reporting consistency

·  INTCK → campaign duration

·  INTNX → renewal planning

·  ROI → key business KPI

 7. PROC SQL ANALYTICS

proc sql;

    create table city_summary as

    select City,

           count(*) as Total_Billboards,

           sum(Revenue) as Total_Revenue format=comma12.,

           avg(ROI_Percentage) as Avg_ROI format=8.2

    from billboard_clean

    group by City;

quit;

proc print data=city_summary;

run;

OUTPUT:

Obs	City	Total_Billboards	Total_Revenue	Avg_ROI
1	Ahmedabad	1	190,000	11.76
2	Bengaluru	1	980,000	63.33
3	Bhopal	1	180,000	5.88
4	Chennai	1	480,000	37.14
5	Coimbatore	1	410,000	36.67
6	Delhi	1	420,000	40.00
7	Hyderabad	1	850,000	70.00
8	Indore	1	360,000	38.46
9	Jaipur	1	160,000	14.29
10	Kochi	1	310,000	24.00
11	Kolkata	1	530,000	32.50
12	Mumbai	1	1,500,000	66.67
13	Noida	1	760,000	52.00
14	Pune	1	210,000	16.67
15	Surat	1	220,000	15.79
16	Vizag	1	200,000	11.11

Business Use:

• Identify top revenue cities
• City-wise ROI benchmarking

 8. PROC FREQ – AD TYPE DISTRIBUTION

proc freq data=billboard_clean;

    tables Ad_Type*City / nocol norow;

run;

OUTPUT:

The FREQ Procedure

Frequency Percent

Table of Ad_Type by City Ad_Type City Ahmedabad Bengaluru Bhopal Chennai Coimbatore Delhi Hyderabad Indore Jaipur Kochi Kolkata Mumbai Noida Pune Surat Vizag Total DIGITAL 0 0.00 1 6.25 0 0.00 0 0.00 1 6.25 0 0.00 1 6.25 1 6.25 0 0.00 1 6.25 1 6.25 1 6.25 1 6.25 0 0.00 0 0.00 0 0.00 8 50.00 STATIC 1 6.25 0 0.00 1 6.25 1 6.25 0 0.00 1 6.25 0 0.00 0 0.00 1 6.25 0 0.00 0 0.00 0 0.00 0 0.00 1 6.25 1 6.25 1 6.25 8 50.00 Total 1 6.25 1 6.25 1 6.25 1 6.25 1 6.25 1 6.25 1 6.25 1 6.25 1 6.25 1 6.25 1 6.25 1 6.25 1 6.25 1 6.25 1 6.25 1 6.25 16 100.00 ·  Understand Digital vs Static adoption ·  City-level ad strategy

9. PROC MEANS – STATISTICAL SUMMARY

proc means data=billboard_clean n mean min max sum;

    var Revenue Cost ROI_Percentage Traffic_Count;

run;

OUTPUT:

The MEANS Procedure

Variable	N	Mean	Minimum	Maximum	Sum
Revenue Cost ROI_Percentage Traffic_Count	16 16 16 16	485000.00 336875.00 33.5169429 95312.50	160000.00 140000.00 5.8823529 50000.00	1500000.00 900000.00 70.0000000 180000.00	7760000.00 5390000.00 536.2710868 1525000.00

·  High-level KPI summary

·  Management dashboards

10. PROC UNIVARIATE – DISTRIBUTION & OUTLIERS

proc univariate data=billboard_clean;

    var ROI_Percentage Revenue;

    histogram ROI_Percentage;

run;

OUTPUT:

The UNIVARIATE Procedure

Variable: ROI_Percentage

Moments
N	16	Sum Weights	16
Mean	33.5169429	Sum Observations	536.271087
Std Deviation	20.8943348	Variance	436.573228
Skewness	0.47865089	Kurtosis	-0.9526861
Uncorrected SS	24522.7658	Corrected SS	6548.59843
Coeff Variation	62.33962	Std Error Mean	5.22358371

Basic Statistical Measures
Location		Variability
Mean	33.51694	Std Deviation	20.89433
Median	34.58333	Variance	436.57323
Mode	.	Range	64.11765
		Interquartile Range	30.96241

Tests for Location: Mu0=0
Test	Statistic		p Value
Student's t	t	6.416465	Pr > |t|	<.0001
Sign	M	8	Pr >= |M|	<.0001
Signed Rank	S	68	Pr >= |S|	<.0001

Quantiles (Definition 5)
Level	Quantile
100% Max	70.00000
99%	70.00000
95%	70.00000
90%	66.66667
75% Q3	46.00000
50% Median	34.58333
25% Q1	15.03759
10%	11.11111
5%	5.88235
1%	5.88235
0% Min	5.88235

Extreme Observations
Lowest		Highest
Value	Obs	Value	Obs
5.88235	14	40.0000	3
11.11111	11	52.0000	16
11.76471	8	63.3333	5
14.28571	9	66.6667	2
15.78947	13	70.0000	1

The UNIVARIATE Procedure

The UNIVARIATE Procedure

Variable: Revenue

Moments
N	16	Sum Weights	16
Mean	485000	Sum Observations	7760000
Std Deviation	370135.11	Variance	1.37E11
Skewness	1.61902833	Kurtosis	2.61213494
Uncorrected SS	5.8186E12	Corrected SS	2.055E12
Coeff Variation	76.3165176	Std Error Mean	92533.7776

Basic Statistical Measures
Location		Variability
Mean	485000.0	Std Deviation	370135
Median	385000.0	Variance	1.37E11
Mode	.	Range	1340000
		Interquartile Range	440000

Tests for Location: Mu0=0
Test	Statistic		p Value
Student's t	t	5.241329	Pr > |t|	<.0001
Sign	M	8	Pr >= |M|	<.0001
Signed Rank	S	68	Pr >= |S|	<.0001

Quantiles (Definition 5)
Level	Quantile
100% Max	1500000
99%	1500000
95%	1500000
90%	980000
75% Q3	645000
50% Median	385000
25% Q1	205000
10%	180000
5%	160000
1%	160000
0% Min	160000

Extreme Observations
Lowest		Highest
Value	Obs	Value	Obs
160000	9	530000	7
180000	14	760000	16
190000	8	850000	1
200000	11	980000	5
210000	6	1500000	2

·  Detect skewness

·  Identify abnormal ROI values

 

11. PROC CORR – RELATIONSHIP ANALYSIS

proc corr data=billboard_clean;

    var Traffic_Count Exposure_Efficiency Revenue ROI_Percentage;

run;

OUTPUT:

The CORR Procedure

4 Variables:	Traffic_Count Exposure_Efficiency Revenue ROI_Percentage

Simple Statistics
Variable	N	Mean	Std Dev	Sum	Minimum	Maximum
Traffic_Count	16	95313	37416	1525000	50000	180000
Exposure_Efficiency	16	1221563	955883	19545000	300000	3600000
Revenue	16	485000	370135	7760000	160000	1500000
ROI_Percentage	16	33.51694	20.89433	536.27109	5.88235	70.00000

Pearson Correlation Coefficients, N = 16 Prob > |r| under H0: Rho=0
	Traffic_Count	Exposure_Efficiency	Revenue	ROI_Percentage
Traffic_Count	1.00000	0.98852 <.0001	0.96308 <.0001	0.89989 <.0001
Exposure_Efficiency	0.98852 <.0001	1.00000	0.98270 <.0001	0.88389 <.0001
Revenue	0.96308 <.0001	0.98270 <.0001	1.00000	0.89602 <.0001
ROI_Percentage	0.89989 <.0001	0.88389 <.0001	0.89602 <.0001	1.00000

·  Does traffic truly drive revenue?

·  Data-driven ad placement

 

12. PROC SGPLOT – VISUALIZATION

proc sgplot data=billboard_clean;

    scatter x=Traffic_Count y=Revenue;

    reg x=Traffic_Count y=Revenue;

run;

OUTPUT:

->Visual justification for budget allocation

13. UTILIZATION CLASSIFICATION MACRO

%macro utilization;

data billboard_util;

    set billboard_clean;

length Utilization $8.;

    if Exposure_Efficiency > 2000000 then Utilization='HIGH';

    else if Exposure_Efficiency > 1000000 then Utilization='MEDIUM';

    else Utilization='LOW';

run;

proc print data=billboard_util;

run;

%mend;

%utilization;

OUTPUT:

Obs	Billboard_ID	City	Ad_Type	Vendor	Traffic_Count	Exposure_Time	Revenue	Cost	Start_Date	End_Date	Campaign_Days	Next_Renewal	ROI_Percentage	Exposure_Efficiency	Utilization
1	BB001	Hyderabad	DIGITAL	MEDIAONE	120000	15	850000	500000	01JAN2025	31JAN2025	31	01FEB2025	70.0000	1800000	MEDIUM
2	BB002	Mumbai	DIGITAL	ADVISION	180000	20	1500000	900000	01JAN2025	31JAN2025	31	01FEB2025	66.6667	3600000	HIGH
3	BB003	Delhi	STATIC	BRANDMAX	95000	10	420000	300000	01JAN2025	31JAN2025	31	01FEB2025	40.0000	950000	LOW
4	BB004	Chennai	STATIC	MEDIAONE	110000	12	480000	350000	01JAN2025	31JAN2025	31	01FEB2025	37.1429	1320000	MEDIUM
5	BB005	Bengaluru	DIGITAL	ADVISION	160000	18	980000	600000	01JAN2025	31JAN2025	31	01FEB2025	63.3333	2880000	HIGH
6	BB006	Pune	STATIC	LOCALADS	70000	8	210000	180000	01JAN2025	31JAN2025	31	01FEB2025	16.6667	560000	LOW
7	BB007	Kolkata	DIGITAL	BRANDMAX	90000	14	530000	400000	01JAN2025	31JAN2025	31	01FEB2025	32.5000	1260000	MEDIUM
8	BB008	Ahmedabad	STATIC	LOCALADS	60000	7	190000	170000	01JAN2025	31JAN2025	31	01FEB2025	11.7647	420000	LOW
9	BB009	Jaipur	STATIC	LOCALADS	50000	6	160000	140000	01JAN2025	31JAN2025	31	01FEB2025	14.2857	300000	LOW
10	BB010	Kochi	DIGITAL	MEDIAONE	75000	10	310000	250000	01JAN2025	31JAN2025	31	01FEB2025	24.0000	750000	LOW
11	BB011	Vizag	STATIC	BRANDMAX	68000	7	200000	180000	01JAN2025	31JAN2025	31	01FEB2025	11.1111	476000	LOW
12	BB012	Indore	DIGITAL	ADVISION	82000	11	360000	260000	01JAN2025	31JAN2025	31	01FEB2025	38.4615	902000	LOW
13	BB013	Surat	STATIC	LOCALADS	72000	8	220000	190000	01JAN2025	31JAN2025	31	01FEB2025	15.7895	576000	LOW
14	BB014	Bhopal	STATIC	LOCALADS	65000	7	180000	170000	01JAN2025	31JAN2025	31	01FEB2025	5.8824	455000	LOW
15	BB015	Coimbatore	DIGITAL	MEDIAONE	88000	12	410000	300000	01JAN2025	31JAN2025	31	01FEB2025	36.6667	1056000	MEDIUM
16	BB016	Noida	DIGITAL	ADVISION	140000	16	760000	500000	01JAN2025	31JAN2025	31	01FEB2025	52.0000	2240000	HIGH

·  Automates performance tagging

·  Reusable logic

14. FRAUD DETECTION MACRO

%macro fraud_check;

data fraud_flags;

    set billboard_util;

    if ROI_Percentage > 60 or Revenue > 2000000 then Fraud_Flag='YES';

    else Fraud_Flag='NO';

run;

proc print data=fraud_flags;

run;

%mend;

%fraud_check;

OUTPUT:

Obs	Billboard_ID	City	Ad_Type	Vendor	Traffic_Count	Exposure_Time	Revenue	Cost	Start_Date	End_Date	Campaign_Days	Next_Renewal	ROI_Percentage	Exposure_Efficiency	Utilization	Fraud_Flag
1	BB001	Hyderabad	DIGITAL	MEDIAONE	120000	15	850000	500000	01JAN2025	31JAN2025	31	01FEB2025	70.0000	1800000	MEDIUM	YES
2	BB002	Mumbai	DIGITAL	ADVISION	180000	20	1500000	900000	01JAN2025	31JAN2025	31	01FEB2025	66.6667	3600000	HIGH	YES
3	BB003	Delhi	STATIC	BRANDMAX	95000	10	420000	300000	01JAN2025	31JAN2025	31	01FEB2025	40.0000	950000	LOW	NO
4	BB004	Chennai	STATIC	MEDIAONE	110000	12	480000	350000	01JAN2025	31JAN2025	31	01FEB2025	37.1429	1320000	MEDIUM	NO
5	BB005	Bengaluru	DIGITAL	ADVISION	160000	18	980000	600000	01JAN2025	31JAN2025	31	01FEB2025	63.3333	2880000	HIGH	YES
6	BB006	Pune	STATIC	LOCALADS	70000	8	210000	180000	01JAN2025	31JAN2025	31	01FEB2025	16.6667	560000	LOW	NO
7	BB007	Kolkata	DIGITAL	BRANDMAX	90000	14	530000	400000	01JAN2025	31JAN2025	31	01FEB2025	32.5000	1260000	MEDIUM	NO
8	BB008	Ahmedabad	STATIC	LOCALADS	60000	7	190000	170000	01JAN2025	31JAN2025	31	01FEB2025	11.7647	420000	LOW	NO
9	BB009	Jaipur	STATIC	LOCALADS	50000	6	160000	140000	01JAN2025	31JAN2025	31	01FEB2025	14.2857	300000	LOW	NO
10	BB010	Kochi	DIGITAL	MEDIAONE	75000	10	310000	250000	01JAN2025	31JAN2025	31	01FEB2025	24.0000	750000	LOW	NO
11	BB011	Vizag	STATIC	BRANDMAX	68000	7	200000	180000	01JAN2025	31JAN2025	31	01FEB2025	11.1111	476000	LOW	NO
12	BB012	Indore	DIGITAL	ADVISION	82000	11	360000	260000	01JAN2025	31JAN2025	31	01FEB2025	38.4615	902000	LOW	NO
13	BB013	Surat	STATIC	LOCALADS	72000	8	220000	190000	01JAN2025	31JAN2025	31	01FEB2025	15.7895	576000	LOW	NO
14	BB014	Bhopal	STATIC	LOCALADS	65000	7	180000	170000	01JAN2025	31JAN2025	31	01FEB2025	5.8824	455000	LOW	NO
15	BB015	Coimbatore	DIGITAL	MEDIAONE	88000	12	410000	300000	01JAN2025	31JAN2025	31	01FEB2025	36.6667	1056000	MEDIUM	NO
16	BB016	Noida	DIGITAL	ADVISION	140000	16	760000	500000	01JAN2025	31JAN2025	31	01FEB2025	52.0000	2240000	HIGH	NO

·  Detect suspicious revenue inflation

·  Vendor audit support

15. APPEND DATA 

Create A New Data 

data billboard_feb;

    length Billboard_ID $6 City $15 Ad_Type $10 Vendor $15;

    input Billboard_ID $ City $ Traffic_Count Ad_Type $ Exposure_Time Revenue Cost

          Start_Date :date9. End_Date   :date9. Vendor $;

    format Start_Date End_Date date9.;

datalines;

BB017 Hyderabad 125000 Digital 15 880000 520000 01FEB2025 28FEB2025 MediaOne

BB018 Mumbai 185000 Digital 20 1550000 920000 01FEB2025 28FEB2025 AdVision

BB019 Delhi 98000 Static 11 450000 310000 01FEB2025 28FEB2025 BrandMax

BB020 Chennai 115000 Static 13 500000 360000 01FEB2025 28FEB2025 MediaOne

;

run;

proc print data=billboard_feb;

run;

OUTPUT:

Obs	Billboard_ID	City	Ad_Type	Vendor	Traffic_Count	Exposure_Time	Revenue	Cost	Start_Date	End_Date
1	BB017	Hyderabad	Digital	MediaOne	125000	15	880000	520000	01FEB2025	28FEB2025
2	BB018	Mumbai	Digital	AdVision	185000	20	1550000	920000	01FEB2025	28FEB2025
3	BB019	Delhi	Static	BrandMax	98000	11	450000	310000	01FEB2025	28FEB2025
4	BB020	Chennai	Static	MediaOne	115000	13	500000	360000	01FEB2025	28FEB2025

data billboard_feb_clean;

    set billboard_feb;

    City    = propcase(strip(City));

    Vendor  = upcase(strip(Vendor));

    Ad_Type = upcase(strip(Ad_Type));

    Campaign_Days = intck('day', Start_Date, End_Date) + 1;

    Next_Renewal  = intnx('month', End_Date, 1);

    ROI_Percentage = ((Revenue - Cost) / Cost) * 100;

    Exposure_Efficiency = Traffic_Count * Exposure_Time;

    format Next_Renewal date9.;

run;

proc print data=billboard_feb_clean;

run;

OUTPUT:

Obs	Billboard_ID	City	Ad_Type	Vendor	Traffic_Count	Exposure_Time	Revenue	Cost	Start_Date	End_Date	Campaign_Days	Next_Renewal	ROI_Percentage	Exposure_Efficiency
1	BB017	Hyderabad	DIGITAL	MEDIAONE	125000	15	880000	520000	01FEB2025	28FEB2025	28	01MAR2025	69.2308	1875000
2	BB018	Mumbai	DIGITAL	ADVISION	185000	20	1550000	920000	01FEB2025	28FEB2025	28	01MAR2025	68.4783	3700000
3	BB019	Delhi	STATIC	BRANDMAX	98000	11	450000	310000	01FEB2025	28FEB2025	28	01MAR2025	45.1613	1078000
4	BB020	Chennai	STATIC	MEDIAONE	115000	13	500000	360000	01FEB2025	28FEB2025	28	01MAR2025	38.8889	1495000

proc append base=billboard_clean

            data=billboard_feb_clean

            force;

run;

proc print data=billboard_clean;

run;

OUTPUT:

Obs	Billboard_ID	City	Ad_Type	Vendor	Traffic_Count	Exposure_Time	Revenue	Cost	Start_Date	End_Date	Campaign_Days	Next_Renewal	ROI_Percentage	Exposure_Efficiency
1	BB001	Hyderabad	DIGITAL	MEDIAONE	120000	15	850000	500000	01JAN2025	31JAN2025	31	01FEB2025	70.0000	1800000
2	BB002	Mumbai	DIGITAL	ADVISION	180000	20	1500000	900000	01JAN2025	31JAN2025	31	01FEB2025	66.6667	3600000
3	BB003	Delhi	STATIC	BRANDMAX	95000	10	420000	300000	01JAN2025	31JAN2025	31	01FEB2025	40.0000	950000
4	BB004	Chennai	STATIC	MEDIAONE	110000	12	480000	350000	01JAN2025	31JAN2025	31	01FEB2025	37.1429	1320000
5	BB005	Bengaluru	DIGITAL	ADVISION	160000	18	980000	600000	01JAN2025	31JAN2025	31	01FEB2025	63.3333	2880000
6	BB006	Pune	STATIC	LOCALADS	70000	8	210000	180000	01JAN2025	31JAN2025	31	01FEB2025	16.6667	560000
7	BB007	Kolkata	DIGITAL	BRANDMAX	90000	14	530000	400000	01JAN2025	31JAN2025	31	01FEB2025	32.5000	1260000
8	BB008	Ahmedabad	STATIC	LOCALADS	60000	7	190000	170000	01JAN2025	31JAN2025	31	01FEB2025	11.7647	420000
9	BB009	Jaipur	STATIC	LOCALADS	50000	6	160000	140000	01JAN2025	31JAN2025	31	01FEB2025	14.2857	300000
10	BB010	Kochi	DIGITAL	MEDIAONE	75000	10	310000	250000	01JAN2025	31JAN2025	31	01FEB2025	24.0000	750000
11	BB011	Vizag	STATIC	BRANDMAX	68000	7	200000	180000	01JAN2025	31JAN2025	31	01FEB2025	11.1111	476000
12	BB012	Indore	DIGITAL	ADVISION	82000	11	360000	260000	01JAN2025	31JAN2025	31	01FEB2025	38.4615	902000
13	BB013	Surat	STATIC	LOCALADS	72000	8	220000	190000	01JAN2025	31JAN2025	31	01FEB2025	15.7895	576000
14	BB014	Bhopal	STATIC	LOCALADS	65000	7	180000	170000	01JAN2025	31JAN2025	31	01FEB2025	5.8824	455000
15	BB015	Coimbatore	DIGITAL	MEDIAONE	88000	12	410000	300000	01JAN2025	31JAN2025	31	01FEB2025	36.6667	1056000
16	BB016	Noida	DIGITAL	ADVISION	140000	16	760000	500000	01JAN2025	31JAN2025	31	01FEB2025	52.0000	2240000
17	BB017	Hyderabad	DIGITAL	MEDIAONE	125000	15	880000	520000	01FEB2025	28FEB2025	28	01MAR2025	69.2308	1875000
18	BB018	Mumbai	DIGITAL	ADVISION	185000	20	1550000	920000	01FEB2025	28FEB2025	28	01MAR2025	68.4783	3700000
19	BB019	Delhi	STATIC	BRANDMAX	98000	11	450000	310000	01FEB2025	28FEB2025	28	01MAR2025	45.1613	1078000
20	BB020	Chennai	STATIC	MEDIAONE	115000	13	500000	360000	01FEB2025	28FEB2025	28	01MAR2025	38.8889	1495000

• Monthly campaign extension

16. TRANSPOSE FOR REPORTING

proc transpose data=city_summary out=city_transposed;

    by City NotSorted;

    var Total_Revenue Avg_ROI;

run;

proc print data=city_transposed;

run;

OUTPUT:

Obs	City	_NAME_	COL1
1	Ahmedabad	Total_Revenue	190000.00
2	Ahmedabad	Avg_ROI	11.76
3	Bengaluru	Total_Revenue	980000.00
4	Bengaluru	Avg_ROI	63.33
5	Bhopal	Total_Revenue	180000.00
6	Bhopal	Avg_ROI	5.88
7	Chennai	Total_Revenue	480000.00
8	Chennai	Avg_ROI	37.14
9	Coimbatore	Total_Revenue	410000.00
10	Coimbatore	Avg_ROI	36.67
11	Delhi	Total_Revenue	420000.00
12	Delhi	Avg_ROI	40.00
13	Hyderabad	Total_Revenue	850000.00
14	Hyderabad	Avg_ROI	70.00
15	Indore	Total_Revenue	360000.00
16	Indore	Avg_ROI	38.46
17	Jaipur	Total_Revenue	160000.00
18	Jaipur	Avg_ROI	14.29
19	Kochi	Total_Revenue	310000.00
20	Kochi	Avg_ROI	24.00
21	Kolkata	Total_Revenue	530000.00
22	Kolkata	Avg_ROI	32.50
23	Mumbai	Total_Revenue	1500000.00
24	Mumbai	Avg_ROI	66.67
25	Noida	Total_Revenue	760000.00
26	Noida	Avg_ROI	52.00
27	Pune	Total_Revenue	210000.00
28	Pune	Avg_ROI	16.67
29	Surat	Total_Revenue	220000.00
30	Surat	Avg_ROI	15.79
31	Vizag	Total_Revenue	200000.00
32	Vizag	Avg_ROI	11.11

• Report-friendly format

17. SORT & MERGE EXAMPLE

proc sort data=billboard_util;by Billboard_ID;run;

proc print data=billboard_util;

run;

OUTPUT:

Obs	Billboard_ID	City	Ad_Type	Vendor	Traffic_Count	Exposure_Time	Revenue	Cost	Start_Date	End_Date	Campaign_Days	Next_Renewal	ROI_Percentage	Exposure_Efficiency	Utilization
1	BB001	Hyderabad	DIGITAL	MEDIAONE	120000	15	850000	500000	01JAN2025	31JAN2025	31	01FEB2025	70.0000	1800000	MEDIUM
2	BB002	Mumbai	DIGITAL	ADVISION	180000	20	1500000	900000	01JAN2025	31JAN2025	31	01FEB2025	66.6667	3600000	HIGH
3	BB003	Delhi	STATIC	BRANDMAX	95000	10	420000	300000	01JAN2025	31JAN2025	31	01FEB2025	40.0000	950000	LOW
4	BB004	Chennai	STATIC	MEDIAONE	110000	12	480000	350000	01JAN2025	31JAN2025	31	01FEB2025	37.1429	1320000	MEDIUM
5	BB005	Bengaluru	DIGITAL	ADVISION	160000	18	980000	600000	01JAN2025	31JAN2025	31	01FEB2025	63.3333	2880000	HIGH
6	BB006	Pune	STATIC	LOCALADS	70000	8	210000	180000	01JAN2025	31JAN2025	31	01FEB2025	16.6667	560000	LOW
7	BB007	Kolkata	DIGITAL	BRANDMAX	90000	14	530000	400000	01JAN2025	31JAN2025	31	01FEB2025	32.5000	1260000	MEDIUM
8	BB008	Ahmedabad	STATIC	LOCALADS	60000	7	190000	170000	01JAN2025	31JAN2025	31	01FEB2025	11.7647	420000	LOW
9	BB009	Jaipur	STATIC	LOCALADS	50000	6	160000	140000	01JAN2025	31JAN2025	31	01FEB2025	14.2857	300000	LOW
10	BB010	Kochi	DIGITAL	MEDIAONE	75000	10	310000	250000	01JAN2025	31JAN2025	31	01FEB2025	24.0000	750000	LOW
11	BB011	Vizag	STATIC	BRANDMAX	68000	7	200000	180000	01JAN2025	31JAN2025	31	01FEB2025	11.1111	476000	LOW
12	BB012	Indore	DIGITAL	ADVISION	82000	11	360000	260000	01JAN2025	31JAN2025	31	01FEB2025	38.4615	902000	LOW
13	BB013	Surat	STATIC	LOCALADS	72000	8	220000	190000	01JAN2025	31JAN2025	31	01FEB2025	15.7895	576000	LOW
14	BB014	Bhopal	STATIC	LOCALADS	65000	7	180000	170000	01JAN2025	31JAN2025	31	01FEB2025	5.8824	455000	LOW
15	BB015	Coimbatore	DIGITAL	MEDIAONE	88000	12	410000	300000	01JAN2025	31JAN2025	31	01FEB2025	36.6667	1056000	MEDIUM
16	BB016	Noida	DIGITAL	ADVISION	140000	16	760000	500000	01JAN2025	31JAN2025	31	01FEB2025	52.0000	2240000	HIGH

proc sort data=fraud_flags;by Billboard_ID;run;

proc print data=fraud_flags;

run;

OUTPUT:

Obs	Billboard_ID	City	Ad_Type	Vendor	Traffic_Count	Exposure_Time	Revenue	Cost	Start_Date	End_Date	Campaign_Days	Next_Renewal	ROI_Percentage	Exposure_Efficiency	Utilization	Fraud_Flag
1	BB001	Hyderabad	DIGITAL	MEDIAONE	120000	15	850000	500000	01JAN2025	31JAN2025	31	01FEB2025	70.0000	1800000	MEDIUM	YES
2	BB002	Mumbai	DIGITAL	ADVISION	180000	20	1500000	900000	01JAN2025	31JAN2025	31	01FEB2025	66.6667	3600000	HIGH	YES
3	BB003	Delhi	STATIC	BRANDMAX	95000	10	420000	300000	01JAN2025	31JAN2025	31	01FEB2025	40.0000	950000	LOW	NO
4	BB004	Chennai	STATIC	MEDIAONE	110000	12	480000	350000	01JAN2025	31JAN2025	31	01FEB2025	37.1429	1320000	MEDIUM	NO
5	BB005	Bengaluru	DIGITAL	ADVISION	160000	18	980000	600000	01JAN2025	31JAN2025	31	01FEB2025	63.3333	2880000	HIGH	YES
6	BB006	Pune	STATIC	LOCALADS	70000	8	210000	180000	01JAN2025	31JAN2025	31	01FEB2025	16.6667	560000	LOW	NO
7	BB007	Kolkata	DIGITAL	BRANDMAX	90000	14	530000	400000	01JAN2025	31JAN2025	31	01FEB2025	32.5000	1260000	MEDIUM	NO
8	BB008	Ahmedabad	STATIC	LOCALADS	60000	7	190000	170000	01JAN2025	31JAN2025	31	01FEB2025	11.7647	420000	LOW	NO
9	BB009	Jaipur	STATIC	LOCALADS	50000	6	160000	140000	01JAN2025	31JAN2025	31	01FEB2025	14.2857	300000	LOW	NO
10	BB010	Kochi	DIGITAL	MEDIAONE	75000	10	310000	250000	01JAN2025	31JAN2025	31	01FEB2025	24.0000	750000	LOW	NO
11	BB011	Vizag	STATIC	BRANDMAX	68000	7	200000	180000	01JAN2025	31JAN2025	31	01FEB2025	11.1111	476000	LOW	NO
12	BB012	Indore	DIGITAL	ADVISION	82000	11	360000	260000	01JAN2025	31JAN2025	31	01FEB2025	38.4615	902000	LOW	NO
13	BB013	Surat	STATIC	LOCALADS	72000	8	220000	190000	01JAN2025	31JAN2025	31	01FEB2025	15.7895	576000	LOW	NO
14	BB014	Bhopal	STATIC	LOCALADS	65000	7	180000	170000	01JAN2025	31JAN2025	31	01FEB2025	5.8824	455000	LOW	NO
15	BB015	Coimbatore	DIGITAL	MEDIAONE	88000	12	410000	300000	01JAN2025	31JAN2025	31	01FEB2025	36.6667	1056000	MEDIUM	NO
16	BB016	Noida	DIGITAL	ADVISION	140000	16	760000	500000	01JAN2025	31JAN2025	31	01FEB2025	52.0000	2240000	HIGH	NO

data final_report;

    merge billboard_util 

          fraud_flags;

    by Billboard_ID;

run;

proc print data=final_report;

run;

OUTPUT:

Obs	Billboard_ID	City	Ad_Type	Vendor	Traffic_Count	Exposure_Time	Revenue	Cost	Start_Date	End_Date	Campaign_Days	Next_Renewal	ROI_Percentage	Exposure_Efficiency	Utilization	Fraud_Flag
1	BB001	Hyderabad	DIGITAL	MEDIAONE	120000	15	850000	500000	01JAN2025	31JAN2025	31	01FEB2025	70.0000	1800000	MEDIUM	YES
2	BB002	Mumbai	DIGITAL	ADVISION	180000	20	1500000	900000	01JAN2025	31JAN2025	31	01FEB2025	66.6667	3600000	HIGH	YES
3	BB003	Delhi	STATIC	BRANDMAX	95000	10	420000	300000	01JAN2025	31JAN2025	31	01FEB2025	40.0000	950000	LOW	NO
4	BB004	Chennai	STATIC	MEDIAONE	110000	12	480000	350000	01JAN2025	31JAN2025	31	01FEB2025	37.1429	1320000	MEDIUM	NO
5	BB005	Bengaluru	DIGITAL	ADVISION	160000	18	980000	600000	01JAN2025	31JAN2025	31	01FEB2025	63.3333	2880000	HIGH	YES
6	BB006	Pune	STATIC	LOCALADS	70000	8	210000	180000	01JAN2025	31JAN2025	31	01FEB2025	16.6667	560000	LOW	NO
7	BB007	Kolkata	DIGITAL	BRANDMAX	90000	14	530000	400000	01JAN2025	31JAN2025	31	01FEB2025	32.5000	1260000	MEDIUM	NO
8	BB008	Ahmedabad	STATIC	LOCALADS	60000	7	190000	170000	01JAN2025	31JAN2025	31	01FEB2025	11.7647	420000	LOW	NO
9	BB009	Jaipur	STATIC	LOCALADS	50000	6	160000	140000	01JAN2025	31JAN2025	31	01FEB2025	14.2857	300000	LOW	NO
10	BB010	Kochi	DIGITAL	MEDIAONE	75000	10	310000	250000	01JAN2025	31JAN2025	31	01FEB2025	24.0000	750000	LOW	NO
11	BB011	Vizag	STATIC	BRANDMAX	68000	7	200000	180000	01JAN2025	31JAN2025	31	01FEB2025	11.1111	476000	LOW	NO
12	BB012	Indore	DIGITAL	ADVISION	82000	11	360000	260000	01JAN2025	31JAN2025	31	01FEB2025	38.4615	902000	LOW	NO
13	BB013	Surat	STATIC	LOCALADS	72000	8	220000	190000	01JAN2025	31JAN2025	31	01FEB2025	15.7895	576000	LOW	NO
14	BB014	Bhopal	STATIC	LOCALADS	65000	7	180000	170000	01JAN2025	31JAN2025	31	01FEB2025	5.8824	455000	LOW	NO
15	BB015	Coimbatore	DIGITAL	MEDIAONE	88000	12	410000	300000	01JAN2025	31JAN2025	31	01FEB2025	36.6667	1056000	MEDIUM	NO
16	BB016	Noida	DIGITAL	ADVISION	140000	16	760000	500000	01JAN2025	31JAN2025	31	01FEB2025	52.0000	2240000	HIGH	NO

• Consolidated analytics dataset

18. PROC DATASETS DELETE

proc datasets library=work nolist;

    delete billboard_raw;

quit;

LOG:

NOTE: Deleting WORK.BILLBOARD_RAW (memtype=DATA).

·  Memory optimization

·  Production hygiene

19. BUSINESS INSIGHTS

·       Digital billboards show higher ROI

·       Traffic strongly correlates with revenue

·       Some vendors show suspicious ROI spikes

·       Low-traffic cities require cost renegotiation

·       Utilization classification simplifies decision-making

20. WHY THIS PROJECT IS STRONG

1. Uses SQL + DATA step
2. Macros for automation
3. Date functions (MDY, INTCK, INTNX)
4. Fraud logic
5. Business interpretation
6. End-to-end lifecycle

21. CONCLUSION

This project shows how advertising billboard data can be transformed into meaningful business insights using SAS. By combining clean data preparation, SQL analysis, statistical procedures, visualizations, macros, and fraud checks, we can clearly understand which billboards perform well, which waste money, and which may be risky.
Overall, the analysis helps businesses maximize ROI, optimize ad placement, control costs, and make confident, data-driven decisions instead of relying on guesswork.

This project mirrors a real advertising analytics system used by:

·       Media agencies

·       Marketing analytics teams

·       Audit departments

INTERVIEW QUESTIONS FOR YOU

·  What is a macro parameter?

·  What is %DO and %END?

·  What is %PUT?

 

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

About the Author:

SAS Learning Hub is a data analytics and SAS programming platform focused on clinical, financial, and real-world data analysis. The content is created by professionals with academic training in Pharmaceutics and hands-on experience in Base SAS, PROC SQL, Macros, SDTM, and ADaM, providing practical and industry-relevant SAS learning resources.

Disclaimer:

The datasets and analysis in this article are created for educational and demonstration purposes only. They do not represent BillBoard data.

Our Mission:

This blog provides industry-focused SAS programming tutorials and analytics projects covering finance, healthcare, and technology.

This project is suitable for:

·  Students learning SAS

·  Data analysts building portfolios

·  Professionals preparing for SAS interviews

·  Bloggers writing about analytics and smart cities

·  EV and energy industry professionals

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Follow Us On : 

 

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--->Follow our blog for more SAS-based analytics projects and industry data models.

---> Support Us By Following Our Blog..

To deepen your understanding of SAS analytics, please refer to our other data science and industry-focused projects listed below:

1.Do high-budget movies really guarantee high profits, or is it just a myth?A Complete Sas Study

2.Are players with higher match consistency always the ones winning major tournaments?A Complete Sas Study

3.Is higher income really leading to better savings, or just higher spending?A Complete Sas Study

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

About Us | Contact | Privacy Policy