380.How Do High-Cost, Low-Turnover Warehouses Get Detected Using SAS Analytics?

How Do High-Cost, Low-Turnover Warehouses Get Detected Using SAS Analytics?


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


Intro Section

This project demonstrates how SAS can be used to analyze warehouse operations, capacity utilization, and cost efficiency using real-world style data. By combining DATA step processing, PROC SQL, and advanced analytics procedures, this tutorial shows how logistics and supply-chain data can be converted into meaningful business intelligence.


Why This Analysis Matters

Modern warehouses handle millions of units of inventory and large operational budgets. Even small inefficiencies in utilization or staffing can result in major financial losses. This SAS-based warehouse analytics model helps identify underutilized facilities, cost anomalies, and potential fraud patterns.


1. DATA CREATION – RAW WAREHOUSE DATA

data warehouses_raw;

    format Start_Date date9.;

    length Warehouse_ID $6 Location $20;

    input Warehouse_ID $ Location $ Storage_Capacity Inventory_Turnover 

          Staff_Count Operating_Cost Utilization_Rate Start_Date : date9.;

    datalines;

WH001 Delhi 50000 4.5 120 1500000 78 01JAN2022

WH002 Mumbai 70000 6.2 150 2100000 85 05FEB2022

WH003 Chennai 45000 3.8 90 1200000 72 15MAR2022

WH004 Hyderabad 60000 5.1 130 1800000 82 01APR2022

WH005 Pune 48000 4.0 100 1400000 70 12MAY2022

WH006 Kolkata 55000 4.6 110 1600000 76 25JUN2022

WH007 Jaipur 40000 3.5 85 1100000 65 10JUL2022

WH008 Ahmedabad 52000 4.9 115 1550000 79 08AUG2022

WH009 Kochi 42000 3.7 80 1050000 68 02SEP2022

WH010 Bhopal 46000 4.1 95 1300000 73 01OCT2022

WH011 Indore 47000 4.3 98 1350000 75 15NOV2022

WH012 Surat 58000 5.2 125 1750000 83 01DEC2022

WH013 Nagpur 43000 3.9 90 1200000 71 10JAN2023

WH014 Noida 65000 5.8 140 2000000 88 01FEB2023

WH015 Faridabad 62000 5.4 135 1900000 86 15MAR2023

;

run;

proc print data=warehouses_raw;

run;

OUTPUT:

ObsStart_DateWarehouse_IDLocationStorage_CapacityInventory_TurnoverStaff_CountOperating_CostUtilization_Rate
101JAN2022WH001Delhi500004.5120150000078
205FEB2022WH002Mumbai700006.2150210000085
315MAR2022WH003Chennai450003.890120000072
401APR2022WH004Hyderabad600005.1130180000082
512MAY2022WH005Pune480004.0100140000070
625JUN2022WH006Kolkata550004.6110160000076
710JUL2022WH007Jaipur400003.585110000065
808AUG2022WH008Ahmedabad520004.9115155000079
902SEP2022WH009Kochi420003.780105000068
1001OCT2022WH010Bhopal460004.195130000073
1115NOV2022WH011Indore470004.398135000075
1201DEC2022WH012Surat580005.2125175000083
1310JAN2023WH013Nagpur430003.990120000071
1401FEB2023WH014Noida650005.8140200000088
1515MAR2023WH015Faridabad620005.4135190000086

·  Creates structured operational data.

·  Stores cost, staff, utilization metrics.

·  Provides date foundation for time analysis.

·  Supports business performance modeling.

·  Acts as raw SDTM-like source.


2. DATE ENGINEERING – MDY, INTNX, INTCK

data warehouses_dates;

    set warehouses_raw;

    Review_Date = intnx('month', Start_Date, 6);

    Operational_Months = intck('month', Start_Date, Review_Date);

run;

proc print data=warehouses_dates;

run;

OUTPUT:

ObsStart_DateWarehouse_IDLocationStorage_CapacityInventory_TurnoverStaff_CountOperating_CostUtilization_RateReview_DateOperational_Months
101JAN2022WH001Delhi500004.5120150000078228276
205FEB2022WH002Mumbai700006.2150210000085228586
315MAR2022WH003Chennai450003.890120000072228896
401APR2022WH004Hyderabad600005.1130180000082229196
512MAY2022WH005Pune480004.0100140000070229506
625JUN2022WH006Kolkata550004.6110160000076229806
710JUL2022WH007Jaipur400003.585110000065230116
808AUG2022WH008Ahmedabad520004.9115155000079230426
902SEP2022WH009Kochi420003.780105000068230706
1001OCT2022WH010Bhopal460004.195130000073231016
1115NOV2022WH011Indore470004.398135000075231316
1201DEC2022WH012Surat580005.2125175000083231626
1310JAN2023WH013Nagpur430003.990120000071231926
1401FEB2023WH014Noida650005.8140200000088232236
1515MAR2023WH015Faridabad620005.4135190000086232546

·  INTNX creates review cycle.

·  INTCK calculates duration.

·  Tracks warehouse aging.

·  Supports efficiency trend.

·  Used for cost scaling.


3. CHARACTER FUNCTION CLEANSING

data warehouses_clean;

    set warehouses_dates;

    Location_Clean = propcase(strip(Location));

    Warehouse_Code = upcase(catx('-', 'WH', Warehouse_ID));

run;

proc print data=warehouses_clean;

run;

OUTPUT:

ObsStart_DateWarehouse_IDLocationStorage_CapacityInventory_TurnoverStaff_CountOperating_CostUtilization_RateReview_DateOperational_MonthsLocation_CleanWarehouse_Code
101JAN2022WH001Delhi500004.5120150000078228276DelhiWH-WH001
205FEB2022WH002Mumbai700006.2150210000085228586MumbaiWH-WH002
315MAR2022WH003Chennai450003.890120000072228896ChennaiWH-WH003
401APR2022WH004Hyderabad600005.1130180000082229196HyderabadWH-WH004
512MAY2022WH005Pune480004.0100140000070229506PuneWH-WH005
625JUN2022WH006Kolkata550004.6110160000076229806KolkataWH-WH006
710JUL2022WH007Jaipur400003.585110000065230116JaipurWH-WH007
808AUG2022WH008Ahmedabad520004.9115155000079230426AhmedabadWH-WH008
902SEP2022WH009Kochi420003.780105000068230706KochiWH-WH009
1001OCT2022WH010Bhopal460004.195130000073231016BhopalWH-WH010
1115NOV2022WH011Indore470004.398135000075231316IndoreWH-WH011
1201DEC2022WH012Surat580005.2125175000083231626SuratWH-WH012
1310JAN2023WH013Nagpur430003.990120000071231926NagpurWH-WH013
1401FEB2023WH014Noida650005.8140200000088232236NoidaWH-WH014
1515MAR2023WH015Faridabad620005.4135190000086232546FaridabadWH-WH015

·  STRIP removes blanks.

·  PROPCSE standardizes text.

·  CATX builds codes.

·  UPCASE enforces IDs.

·  Prevents merge errors.


4. UTILIZATION CLASSIFICATION MACRO

%macro utilization_classify(input=, output=);

data &output;

    set &input;

    length Utilization_Level $12;

    if Utilization_Rate >= 85 then Utilization_Level = "High";

    else if Utilization_Rate >= 70 then Utilization_Level = "Medium";

    else Utilization_Level = "Low";

run;

proc print data=&output;

run;

%mend;


%utilization_classify(input=warehouses_clean, output=warehouses_util);

OUTPUT:

ObsStart_DateWarehouse_IDLocationStorage_CapacityInventory_TurnoverStaff_CountOperating_CostUtilization_RateReview_DateOperational_MonthsLocation_CleanWarehouse_CodeUtilization_Level
101JAN2022WH001Delhi500004.5120150000078228276DelhiWH-WH001Medium
205FEB2022WH002Mumbai700006.2150210000085228586MumbaiWH-WH002High
315MAR2022WH003Chennai450003.890120000072228896ChennaiWH-WH003Medium
401APR2022WH004Hyderabad600005.1130180000082229196HyderabadWH-WH004Medium
512MAY2022WH005Pune480004.0100140000070229506PuneWH-WH005Medium
625JUN2022WH006Kolkata550004.6110160000076229806KolkataWH-WH006Medium
710JUL2022WH007Jaipur400003.585110000065230116JaipurWH-WH007Low
808AUG2022WH008Ahmedabad520004.9115155000079230426AhmedabadWH-WH008Medium
902SEP2022WH009Kochi420003.780105000068230706KochiWH-WH009Low
1001OCT2022WH010Bhopal460004.195130000073231016BhopalWH-WH010Medium
1115NOV2022WH011Indore470004.398135000075231316IndoreWH-WH011Medium
1201DEC2022WH012Surat580005.2125175000083231626SuratWH-WH012Medium
1310JAN2023WH013Nagpur430003.990120000071231926NagpurWH-WH013Medium
1401FEB2023WH014Noida650005.8140200000088232236NoidaWH-WH014High
1515MAR2023WH015Faridabad620005.4135190000086232546FaridabadWH-WH015High

·  Automates logic.

·  Creates business flags.

·  Reusable macro.

·  Reduces coding errors.

·  Enables dashboarding.


5. FRAUD RISK MACRO

%macro fraud_flag(input=, output=);

data &output;

    set &input;

    if Operating_Cost > 1900000 and Inventory_Turnover < 4 then Fraud_Risk="High";

    else Fraud_Risk="Normal";

run;

proc print data=&output;

run;

%mend;


%fraud_flag(input=warehouses_util, output=warehouses_risk);

OUTPUT:

ObsFraud_riskStart_DateWarehouse_IDLocationStorage_CapacityInventory_TurnoverStaff_CountOperating_CostUtilization_RateReview_DateOperational_MonthsLocation_CleanWarehouse_CodeUtilization_Level
1Normal01JAN2022WH001Delhi500004.5120150000078228276DelhiWH-WH001Medium
2Normal05FEB2022WH002Mumbai700006.2150210000085228586MumbaiWH-WH002High
3Normal15MAR2022WH003Chennai450003.890120000072228896ChennaiWH-WH003Medium
4Normal01APR2022WH004Hyderabad600005.1130180000082229196HyderabadWH-WH004Medium
5Normal12MAY2022WH005Pune480004.0100140000070229506PuneWH-WH005Medium
6Normal25JUN2022WH006Kolkata550004.6110160000076229806KolkataWH-WH006Medium
7Normal10JUL2022WH007Jaipur400003.585110000065230116JaipurWH-WH007Low
8Normal08AUG2022WH008Ahmedabad520004.9115155000079230426AhmedabadWH-WH008Medium
9Normal02SEP2022WH009Kochi420003.780105000068230706KochiWH-WH009Low
10Normal01OCT2022WH010Bhopal460004.195130000073231016BhopalWH-WH010Medium
11Normal15NOV2022WH011Indore470004.398135000075231316IndoreWH-WH011Medium
12Normal01DEC2022WH012Surat580005.2125175000083231626SuratWH-WH012Medium
13Normal10JAN2023WH013Nagpur430003.990120000071231926NagpurWH-WH013Medium
14Normal01FEB2023WH014Noida650005.8140200000088232236NoidaWH-WH014High
15Normal15MAR2023WH015Faridabad620005.4135190000086232546FaridabadWH-WH015High

·  Detects inefficiency.

·  Flags audit cases.

·  Uses numeric logic.

·  Supports compliance.

·  Prevents revenue loss.


6. PROC SQL – BUSINESS QUERIES

proc sql;

    create table warehouse_summary as

    select Location_Clean,

           avg(Utilization_Rate) as Avg_Utilization,

           sum(Operating_Cost) as Total_Cost

    from warehouses_risk

    group by Location_Clean;

quit;

proc print data=warehouse_summary;

run;

OUTPUT:

ObsLocation_CleanAvg_UtilizationTotal_Cost
1Ahmedabad791550000
2Bhopal731300000
3Chennai721200000
4Delhi781500000
5Faridabad861900000
6Hyderabad821800000
7Indore751350000
8Jaipur651100000
9Kochi681050000
10Kolkata761600000
11Mumbai852100000
12Nagpur711200000
13Noida882000000
14Pune701400000
15Surat831750000

·  Aggregates KPIs.

·  Groups by city.

·  Produces management reports.

·  SQL-based analytics.

·  Faster querying.


7. PROC MEANS

proc means data=warehouses_risk mean min max;

    var Storage_Capacity Operating_Cost Utilization_Rate;

run;

OUTPUT:

The MEANS Procedure

VariableMeanMinimumMaximum
Storage_Capacity
Operating_Cost
Utilization_Rate
52200.00
1520000.00
76.7333333
40000.00
1050000.00
65.0000000
70000.00
2100000.00
88.0000000

·  Shows central tendency.

·  Finds extreme values.

·  Validates data.

·  Supports planning.

·  Baseline metrics.


8. PROC CORR

proc corr data=warehouses_risk;

     var Storage_Capacity Utilization_Rate Operating_Cost;

run;

OUTPUT:

The CORR Procedure

3 Variables:Storage_Capacity Utilization_Rate Operating_Cost
Simple Statistics
VariableNMeanStd DevSumMinimumMaximum
Storage_Capacity155220090887830004000070000
Utilization_Rate1576.733337.00476115165.0000088.00000
Operating_Cost1515200003320932280000010500002100000
Pearson Correlation Coefficients, N = 15
Prob > |r| under H0: Rho=0
 Storage_CapacityUtilization_RateOperating_Cost
Storage_Capacity
1.00000
 
0.93551
<.0001
0.99136
<.0001
Utilization_Rate
0.93551
<.0001
1.00000
 
0.94973
<.0001
Operating_Cost
0.99136
<.0001
0.94973
<.0001
1.00000
 

·  Checks dependencies.

·  Cost vs usage link.

·  Helps optimization.

·  Identifies inefficiency.

·  Supports forecasting.

9. PROC FREQ

proc freq data=warehouses_risk;

    tables Utilization_Level Fraud_Risk;

run;

OUTPUT:

The FREQ Procedure

Utilization_LevelFrequencyPercentCumulative
Frequency
Cumulative
Percent
High320.00320.00
Low213.33533.33
Medium1066.6715100.00
Fraud_riskFrequencyPercentCumulative
Frequency
Cumulative
Percent
Normal15100.0015100.00

·  Counts categories.

·  Identifies risky warehouses.

·  Quality control.

·  Compliance reports.

·  Audit support.

10. PROC UNIVARIATE

proc univariate data=warehouses_risk;

    var Operating_Cost Utilization_Rate;

run;

OUTPUT:

The UNIVARIATE Procedure

Variable: Operating_Cost

Moments
N15Sum Weights15
Mean1520000Sum Observations22800000
Std Deviation332092.93Variance1.10286E11
Skewness0.30264324Kurtosis-1.0555485
Uncorrected SS3.62E13Corrected SS1.544E12
Coeff Variation21.8482191Std Error Mean85746.0259
Basic Statistical Measures
LocationVariability
Mean1520000Std Deviation332093
Median1500000Variance1.10286E11
Mode1200000Range1050000
  Interquartile Range600000
Tests for Location: Mu0=0
TestStatisticp Value
Student's tt17.72677Pr > |t|<.0001
SignM7.5Pr >= |M|<.0001
Signed RankS60Pr >= |S|<.0001
Quantiles (Definition 5)
LevelQuantile
100% Max2100000
99%2100000
95%2100000
90%2000000
75% Q31800000
50% Median1500000
25% Q11200000
10%1100000
5%1050000
1%1050000
0% Min1050000
Extreme Observations
LowestHighest
ValueObsValueObs
10500009175000012
1100000718000004
120000013190000015
12000003200000014
13000001021000002

The UNIVARIATE Procedure

Variable: Utilization_Rate

Moments
N15Sum Weights15
Mean76.7333333Sum Observations1151
Std Deviation7.00476029Variance49.0666667
Skewness0.0618266Kurtosis-1.0712095
Uncorrected SS89007Corrected SS686.933333
Coeff Variation9.12870585Std Error Mean1.80862133
Basic Statistical Measures
LocationVariability
Mean76.73333Std Deviation7.00476
Median76.00000Variance49.06667
Mode.Range23.00000
  Interquartile Range12.00000
Tests for Location: Mu0=0
TestStatisticp Value
Student's tt42.42642Pr > |t|<.0001
SignM7.5Pr >= |M|<.0001
Signed RankS60Pr >= |S|<.0001
Quantiles (Definition 5)
LevelQuantile
100% Max88
99%88
95%88
90%86
75% Q383
50% Median76
25% Q171
10%68
5%65
1%65
0% Min65
Extreme Observations
LowestHighest
ValueObsValueObs
657824
6898312
705852
71138615
7238814

·  Finds abnormal costs.

·  Measures skewness.

·  Supports fraud analysis.

·  Statistical profiling.

·  Risk analytics.

11. PROC SGPLOT

proc sgplot data=warehouses_risk;

    scatter x=Storage_Capacity y=Utilization_Rate;

run;

OUTPUT:

The SGPlot Procedure

·  Capacity vs usage.

·  Pattern detection.

·  Outlier spotting.

·  Business storytelling.

·  Dashboard support.


12. PROC TRANSPOSE

proc transpose data=warehouse_summary out=warehouse_wide;

    by Location_Clean NotSorted;

    id Location_Clean;

    var Avg_Utilization;

run;

proc print data=warehouse_wide;

run;

OUTPUT:

ObsLocation_Clean_NAME_AhmedabadBhopalChennaiDelhiFaridabadHyderabadIndoreJaipurKochiKolkataMumbaiNagpurNoidaPuneSurat
1AhmedabadAvg_Utilization79..............
2BhopalAvg_Utilization.73.............
3ChennaiAvg_Utilization..72............
4DelhiAvg_Utilization...78...........
5FaridabadAvg_Utilization....86..........
6HyderabadAvg_Utilization.....82.........
7IndoreAvg_Utilization......75........
8JaipurAvg_Utilization.......65.......
9KochiAvg_Utilization........68......
10KolkataAvg_Utilization.........76.....
11MumbaiAvg_Utilization..........85....
12NagpurAvg_Utilization...........71...
13NoidaAvg_Utilization............88..
14PuneAvg_Utilization.............70.
15SuratAvg_Utilization..............83

·  Converts rows to columns.

·  Dashboard layout.

·  Excel-style view.

·  Comparison easier.

·  Presentation ready.


13. PROC DATASETS DELETE

proc datasets library=work;

    delete warehouses_raw;

quit;

LOG:

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

·  Removes junk data.

·  Frees memory.

·  Controls versions.

·  Prevents confusion.

·  Validation compliance.


What You Will Learn

In this tutorial, you will learn how to create structured warehouse datasets, apply date functions like INTCK and INTNX, clean text using character functions, and build automated macros for utilization classification and fraud risk detection.

CONCLUSION:

This warehouse analytics system demonstrates how SAS is used in logistics, supply chain, and financial audit teams to monitor utilization, detect cost fraud, and optimize operational efficiency using SQL, statistics, macros, and data engineering.

 

INTERVIEW QUESTIONS FOR YOU

1.What is a SAS library?
2.What is the difference between SET and MERGE?
3.What is PROC FREQ used for?


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 Warehouse data.


Our Mission:

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


This project is suitable for:

SAS Programmer Interviews

SAS Programmer Job Seekers

SAS Analysts


Follow Us On : 


 


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

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






Comments

Popular posts from this blog

409.Can We Build a Reliable Emergency Services Analytics & Fraud Detection System in SAS While Identifying and Fixing Intentional Errors?

397.If a satellite has excellent signal strength but very high latency, can it still deliver good quality communication? Why or why not?A Sas Study

401.How Efficient Are Global Data Centers? A Complete SAS Analytics Study