247.IN-DEPTH ANALYSIS OF GOVERNMENT BUS DEPOTS ACROSS VARIOUS INDIAN STATES USING PROC PRINT | PROC SORT | PROC FREQ | PROC MEANS | PROC SQL | ADVANCED MACROS | IN SAS FOR STRATEGIC TRANSPORTATION INSIGHTS AND INFRASTRUCTURE OPTIMIZATION

IN-DEPTH ANALYSIS OF GOVERNMENT BUS DEPOTS ACROSS VARIOUS INDIAN STATES USING PROC PRINT | PROC SORT | PROC FREQ | PROC MEANS | PROC SQL | ADVANCED MACROS | IN SAS FOR STRATEGIC TRANSPORTATION INSIGHTS AND INFRASTRUCTURE OPTIMIZATION

/*Creating a unique dataset about various Government Bus Depots across different Indian states*/

1. DATA CREATION: GOVT BUS DEPOTS DATASET

options nocenter;

data bus_depots;

    length Depot_Name $20 State $15 Region $12  Depot_Manager $15 Fuel_Type $10;

    input Depot_ID  Depot_Name $ State $ Region $ No_of_Buses Depot_Manager $                 Daily_Passenger_Count  Fuel_Type $  Depot_Area_sqkm Year_Of_Establishment;    

    datalines;

101 HyderabadDepot Telangana South 145 Ramesh 21000 Diesel 3.5 1990

102 SecunderabadDepot Telangana South 130 Suresh 19500 CNG 3.0 1985

103 ChennaiDepot TamilNadu South 180 Karthik 28000 Diesel 4.0 1980

104 MaduraiDepot TamilNadu South 90 Rajan 12500 Diesel 2.8 1995

105 BangaloreDepot Karnataka South 200 Ravi 32000 CNG 4.5 1988

106 MysoreDepot Karnataka South 120 Anand 16000 Diesel 3.2 1993

107 KochiDepot Kerala South 100 Manoj 14000 Diesel 3.0 1992

108 TrivandrumDepot Kerala South 85 Vinod 12000 CNG 2.7 1996

109 MumbaiDepot Maharashtra West 220 Aditya 33000 Diesel 5.0 1982

110 PuneDepot Maharashtra West 190 Nikhil 29000 Electric 4.8 1987

111 AhmedabadDepot Gujarat West 175 Bharat 27500 Diesel 4.0 1989

112 SuratDepot Gujarat West 160 Hemant 26000 Diesel 3.9 1991

113 JaipurDepot Rajasthan North 155 Harish 24000 Diesel 4.1 1990

114 UdaipurDepot Rajasthan North 100 Nilesh 14500 Diesel 3.5 1995

115 LucknowDepot UP North 210 Pankaj 35000 CNG 4.9 1986

116 KanpurDepot UP North 200 Amit 34000 CNG 4.8 1984

117 DelhiDepot Delhi North 300 Kapil 45000 CNG 5.5 1980

118 PatnaDepot Bihar East 140 Alok 20000 Diesel 3.4 1988

119 RanchiDepot Jharkhand East 135 Ritesh 19000 Diesel 3.2 1992

120 BhubaneswarDepot Odisha East 130 Sanjay 18500 Diesel 3.3 1990

121 KolkataDepot WB East 250 Arindam 40000 Diesel 5.1 1981

122 GuwahatiDepot Assam North-East 110 Prakash 17000 Diesel 3.7 1993

123 ShillongDepot Meghalaya North-East 90 Ranjit 15000 Diesel 3.2 1994

124 IndoreDepot MP Central 160 Rohit 26000 Diesel 4.0 1985

125 BhopalDepot MP Central 165 Deepak 26500 CNG 4.1 1986

;

run;

proc print;run;

Output:

Obs	Depot_Name	State	Region	Depot_Manager	Fuel_Type	Depot_ID	No_of_Buses	Daily_Passenger_Count	Depot_Area_sqkm	Year_Of_Establishment
1	HyderabadDepot	Telangana	South	Ramesh	Diesel	101	145	21000	3.5	1990
2	SecunderabadDepot	Telangana	South	Suresh	CNG	102	130	19500	3.0	1985
3	ChennaiDepot	TamilNadu	South	Karthik	Diesel	103	180	28000	4.0	1980
4	MaduraiDepot	TamilNadu	South	Rajan	Diesel	104	90	12500	2.8	1995
5	BangaloreDepot	Karnataka	South	Ravi	CNG	105	200	32000	4.5	1988
6	MysoreDepot	Karnataka	South	Anand	Diesel	106	120	16000	3.2	1993
7	KochiDepot	Kerala	South	Manoj	Diesel	107	100	14000	3.0	1992
8	TrivandrumDepot	Kerala	South	Vinod	CNG	108	85	12000	2.7	1996
9	MumbaiDepot	Maharashtra	West	Aditya	Diesel	109	220	33000	5.0	1982
10	PuneDepot	Maharashtra	West	Nikhil	Electric	110	190	29000	4.8	1987
11	AhmedabadDepot	Gujarat	West	Bharat	Diesel	111	175	27500	4.0	1989
12	SuratDepot	Gujarat	West	Hemant	Diesel	112	160	26000	3.9	1991
13	JaipurDepot	Rajasthan	North	Harish	Diesel	113	155	24000	4.1	1990
14	UdaipurDepot	Rajasthan	North	Nilesh	Diesel	114	100	14500	3.5	1995
15	LucknowDepot	UP	North	Pankaj	CNG	115	210	35000	4.9	1986
16	KanpurDepot	UP	North	Amit	CNG	116	200	34000	4.8	1984
17	DelhiDepot	Delhi	North	Kapil	CNG	117	300	45000	5.5	1980
18	PatnaDepot	Bihar	East	Alok	Diesel	118	140	20000	3.4	1988
19	RanchiDepot	Jharkhand	East	Ritesh	Diesel	119	135	19000	3.2	1992
20	BhubaneswarDepot	Odisha	East	Sanjay	Diesel	120	130	18500	3.3	1990
21	KolkataDepot	WB	East	Arindam	Diesel	121	250	40000	5.1	1981
22	GuwahatiDepot	Assam	North-East	Prakash	Diesel	122	110	17000	3.7	1993
23	ShillongDepot	Meghalaya	North-East	Ranjit	Diesel	123	90	15000	3.2	1994
24	IndoreDepot	MP	Central	Rohit	Diesel	124	160	26000	4.0	1985
25	BhopalDepot	MP	Central	Deepak	CNG	125	165	26500	4.1	1986

2. PROC PRINT – View 10 Obs Data

proc print data=bus_depots (obs=10) noobs label;

    title  "First 10 of Government Bus Depots in India";

run;

Output:

First 10 of Government Bus Depots in India

Depot_Name	State	Region	Depot_Manager	Fuel_Type	Depot_ID	No_of_Buses	Daily_Passenger_Count	Depot_Area_sqkm	Year_Of_Establishment
HyderabadDepot	Telangana	South	Ramesh	Diesel	101	145	21000	3.5	1990
SecunderabadDepot	Telangana	South	Suresh	CNG	102	130	19500	3.0	1985
ChennaiDepot	TamilNadu	South	Karthik	Diesel	103	180	28000	4.0	1980
MaduraiDepot	TamilNadu	South	Rajan	Diesel	104	90	12500	2.8	1995
BangaloreDepot	Karnataka	South	Ravi	CNG	105	200	32000	4.5	1988
MysoreDepot	Karnataka	South	Anand	Diesel	106	120	16000	3.2	1993
KochiDepot	Kerala	South	Manoj	Diesel	107	100	14000	3.0	1992
TrivandrumDepot	Kerala	South	Vinod	CNG	108	85	12000	2.7	1996
MumbaiDepot	Maharashtra	West	Aditya	Diesel	109	220	33000	5.0	1982
PuneDepot	Maharashtra	West	Nikhil	Electric	110	190	29000	4.8	1987

3. PROC SORT – Sorting by State and Buses

proc sort data=bus_depots out=sorted_depots;

    by descending No_of_Buses;

run;

proc print data=sorted_depots;

    title "Depots Sorted by Number of Buses (Descending)";

run;

Output:

Depots Sorted by Number of Buses (Descending)

Obs	Depot_Name	State	Region	Depot_Manager	Fuel_Type	Depot_ID	No_of_Buses	Daily_Passenger_Count	Depot_Area_sqkm	Year_Of_Establishment
1	DelhiDepot	Delhi	North	Kapil	CNG	117	300	45000	5.5	1980
2	KolkataDepot	WB	East	Arindam	Diesel	121	250	40000	5.1	1981
3	MumbaiDepot	Maharashtra	West	Aditya	Diesel	109	220	33000	5.0	1982
4	LucknowDepot	UP	North	Pankaj	CNG	115	210	35000	4.9	1986
5	BangaloreDepot	Karnataka	South	Ravi	CNG	105	200	32000	4.5	1988
6	KanpurDepot	UP	North	Amit	CNG	116	200	34000	4.8	1984
7	PuneDepot	Maharashtra	West	Nikhil	Electric	110	190	29000	4.8	1987
8	ChennaiDepot	TamilNadu	South	Karthik	Diesel	103	180	28000	4.0	1980
9	AhmedabadDepot	Gujarat	West	Bharat	Diesel	111	175	27500	4.0	1989
10	BhopalDepot	MP	Central	Deepak	CNG	125	165	26500	4.1	1986
11	SuratDepot	Gujarat	West	Hemant	Diesel	112	160	26000	3.9	1991
12	IndoreDepot	MP	Central	Rohit	Diesel	124	160	26000	4.0	1985
13	JaipurDepot	Rajasthan	North	Harish	Diesel	113	155	24000	4.1	1990
14	HyderabadDepot	Telangana	South	Ramesh	Diesel	101	145	21000	3.5	1990
15	PatnaDepot	Bihar	East	Alok	Diesel	118	140	20000	3.4	1988
16	RanchiDepot	Jharkhand	East	Ritesh	Diesel	119	135	19000	3.2	1992
17	SecunderabadDepot	Telangana	South	Suresh	CNG	102	130	19500	3.0	1985
18	BhubaneswarDepot	Odisha	East	Sanjay	Diesel	120	130	18500	3.3	1990
19	MysoreDepot	Karnataka	South	Anand	Diesel	106	120	16000	3.2	1993
20	GuwahatiDepot	Assam	North-East	Prakash	Diesel	122	110	17000	3.7	1993
21	KochiDepot	Kerala	South	Manoj	Diesel	107	100	14000	3.0	1992
22	UdaipurDepot	Rajasthan	North	Nilesh	Diesel	114	100	14500	3.5	1995
23	MaduraiDepot	TamilNadu	South	Rajan	Diesel	104	90	12500	2.8	1995
24	ShillongDepot	Meghalaya	North-East	Ranjit	Diesel	123	90	15000	3.2	1994
25	TrivandrumDepot	Kerala	South	Vinod	CNG	108	85	12000	2.7	1996

4. PROC FREQ – Frequency of Regions and Fuel Types

proc freq data=bus_depots;

    tables Region Fuel_Type / nocum nopercent;

    title "Frequency Distribution of Depots by Region and Fuel Type";

run;

Output:

Frequency Distribution of Depots by Region and Fuel Type

The FREQ Procedure

Region	Frequency
Central	2
East	4
North	5
North-East	2
South	8
West	4

Fuel_Type	Frequency
CNG	7
Diesel	17
Electric	1

5. PROC MEANS – Summary Statistics

proc means data=bus_depots mean median min max maxdec=2;

    var No_of_Buses Daily_Passenger_Count Depot_Area_sqkm;

    title "Descriptive Statistics for Key Numerical Metrics";

run;

Output:

Descriptive Statistics for Key Numerical Metrics

The MEANS Procedure

Variable	Mean	Median	Minimum	Maximum
No_of_Buses Daily_Passenger_Count Depot_Area_sqkm	157.60 24200.00 3.89	155.00 24000.00 3.90	85.00 12000.00 2.70	300.00 45000.00 5.50

6. PROC SQL – Queries for Deeper Insights

a) Top 5 depots by passengers

proc sql outobs=5;

    title "Top 5 Bus Depots by Daily Passenger Count";

    select Depot_Name, State, Daily_Passenger_Count 

    from bus_depots 

    order by Daily_Passenger_Count desc;

quit;

Output:

Top 5 Bus Depots by Daily Passenger Count

Depot_Name	State	Daily_Passenger_Count
DelhiDepot	Delhi	45000
KolkataDepot	WB	40000
LucknowDepot	UP	35000
KanpurDepot	UP	34000
MumbaiDepot	Maharashtra	33000

b) Average buses per region

proc sql;

    title "Average Number of Buses by Region";

    select Region, avg(No_of_Buses) as Avg_Buses

    from bus_depots

    group by Region;

quit;

Output:

Average Number of Buses by Region

Region	Avg_Buses
Central	162.5
East	163.75
North	193
North-East	100
South	131.25
West	186.25

c) Electric or CNG-based depots

proc sql;

    title "List of Eco-friendly Depots (Electric or CNG)";

    select Depot_Name, Fuel_Type, State 

    from bus_depots

    where Fuel_Type in ('CNG', 'Electric');

quit;

Output:

List of Eco-friendly Depots (Electric or CNG)

Depot_Name	Fuel_Type	State
SecunderabadDepot	CNG	Telangana
BangaloreDepot	CNG	Karnataka
TrivandrumDepot	CNG	Kerala
PuneDepot	Electric	Maharashtra
LucknowDepot	CNG	UP
KanpurDepot	CNG	UP
DelhiDepot	CNG	Delhi
BhopalDepot	CNG	MP

7. SAS MACRO – Automate State-Wise Report Generation

%macro StateReport(state);

    proc print data=bus_depots;

        where State = "&state";

        title "Bus Depot Report for &state";

    run;

    

    proc means data=bus_depots;

        where State = "&state";

        var No_of_Buses Daily_Passenger_Count;

        title "Statistical Summary for &state Depots";

    run;

%mend;

%StateReport(Karnataka)

Output:

Bus Depot Report for Karnataka

Obs	Depot_Name	State	Region	Depot_Manager	Fuel_Type	Depot_ID	No_of_Buses	Daily_Passenger_Count	Depot_Area_sqkm	Year_Of_Establishment
5	BangaloreDepot	Karnataka	South	Ravi	CNG	105	200	32000	4.5	1988
6	MysoreDepot	Karnataka	South	Anand	Diesel	106	120	16000	3.2	1993

Statistical Summary for Karnataka Depots

The MEANS Procedure

Variable	N	Mean	Std Dev	Minimum	Maximum
No_of_Buses Daily_Passenger_Count	2 2	160.0000000 24000.00	56.5685425 11313.71	120.0000000 16000.00	200.0000000 32000.00

%StateReport(Maharashtra)

Output:

Bus Depot Report for Maharashtra

Obs	Depot_Name	State	Region	Depot_Manager	Fuel_Type	Depot_ID	No_of_Buses	Daily_Passenger_Count	Depot_Area_sqkm	Year_Of_Establishment
9	MumbaiDepot	Maharashtra	West	Aditya	Diesel	109	220	33000	5.0	1982
10	PuneDepot	Maharashtra	West	Nikhil	Electric	110	190	29000	4.8	1987

Statistical Summary for Maharashtra Depots

The MEANS Procedure

Variable	N	Mean	Std Dev	Minimum	Maximum
No_of_Buses Daily_Passenger_Count	2 2	205.0000000 31000.00	21.2132034 2828.43	190.0000000 29000.00	220.0000000 33000.00

8. Advanced Use Case: Depots Established Before 1990

data pre90;

    set bus_depots;

    if Year_Of_Establishment < 1990;

run;

proc print data=pre90;

    title "Depots Established Before 1990";

run;

Output:

Depots Established Before 1990

Obs	Depot_Name	State	Region	Depot_Manager	Fuel_Type	Depot_ID	No_of_Buses	Daily_Passenger_Count	Depot_Area_sqkm	Year_Of_Establishment
1	SecunderabadDepot	Telangana	South	Suresh	CNG	102	130	19500	3.0	1985
2	ChennaiDepot	TamilNadu	South	Karthik	Diesel	103	180	28000	4.0	1980
3	BangaloreDepot	Karnataka	South	Ravi	CNG	105	200	32000	4.5	1988
4	MumbaiDepot	Maharashtra	West	Aditya	Diesel	109	220	33000	5.0	1982
5	PuneDepot	Maharashtra	West	Nikhil	Electric	110	190	29000	4.8	1987
6	AhmedabadDepot	Gujarat	West	Bharat	Diesel	111	175	27500	4.0	1989
7	LucknowDepot	UP	North	Pankaj	CNG	115	210	35000	4.9	1986
8	KanpurDepot	UP	North	Amit	CNG	116	200	34000	4.8	1984
9	DelhiDepot	Delhi	North	Kapil	CNG	117	300	45000	5.5	1980
10	PatnaDepot	Bihar	East	Alok	Diesel	118	140	20000	3.4	1988
11	KolkataDepot	WB	East	Arindam	Diesel	121	250	40000	5.1	1981
12	IndoreDepot	MP	Central	Rohit	Diesel	124	160	26000	4.0	1985
13	BhopalDepot	MP	Central	Deepak	CNG	125	165	26500	4.1	198

9. Additional Logic: High Density Depots

data high_density;

    set bus_depots;

    Passenger_Density = Daily_Passenger_Count / Depot_Area_sqkm;

    if Passenger_Density > 6000;

run;

proc print data=high_density;

    title "High-Density Depots Based on Area and Passenger Footfall";

run;

Output:

High-Density Depots Based on Area and Passenger Footfall

Obs	Depot_Name	State	Region	Depot_Manager	Fuel_Type	Depot_ID	No_of_Buses	Daily_Passenger_Count	Depot_Area_sqkm	Year_Of_Establishment	Passenger_Density
1	SecunderabadDepot	Telangana	South	Suresh	CNG	102	130	19500	3.0	1985	6500.00
2	ChennaiDepot	TamilNadu	South	Karthik	Diesel	103	180	28000	4.0	1980	7000.00
3	BangaloreDepot	Karnataka	South	Ravi	CNG	105	200	32000	4.5	1988	7111.11
4	MumbaiDepot	Maharashtra	West	Aditya	Diesel	109	220	33000	5.0	1982	6600.00
5	PuneDepot	Maharashtra	West	Nikhil	Electric	110	190	29000	4.8	1987	6041.67
6	AhmedabadDepot	Gujarat	West	Bharat	Diesel	111	175	27500	4.0	1989	6875.00
7	SuratDepot	Gujarat	West	Hemant	Diesel	112	160	26000	3.9	1991	6666.67
8	LucknowDepot	UP	North	Pankaj	CNG	115	210	35000	4.9	1986	7142.86
9	KanpurDepot	UP	North	Amit	CNG	116	200	34000	4.8	1984	7083.33
10	DelhiDepot	Delhi	North	Kapil	CNG	117	300	45000	5.5	1980	8181.82
11	KolkataDepot	WB	East	Arindam	Diesel	121	250	40000	5.1	1981	7843.14
12	IndoreDepot	MP	Central	Rohit	Diesel	124	160	26000	4.0	1985	6500.00
13	BhopalDepot	MP	Central	Deepak	CNG	125	165	26500	4.1	1986	6463.41

10. Macro with SQL: Generate Top Depot by Each Region

%macro topDepotByRegion;

    proc sql noprint;

        select distinct Region into :regions separated by ',' 

        from bus_depots;

    quit;

    %let count = %sysfunc(countw(%bquote(&regions), %str(,)));

    %do i = 1 %to &count;

        %let reg = %scan(%bquote(&regions), &i, %str(,));

        proc sql;

            title "Top Depot in &reg Region by Daily Passenger Count";

            select Depot_Name, Region, Daily_Passenger_Count 

            from bus_depots

            where Region = "&reg"

              and Daily_Passenger_Count = 

                  (select max(Daily_Passenger_Count)

                   from bus_depots 

                   where Region = "&reg");

        quit;

    %end;

%mend;

%topDepotByRegion;

Output:

Top Depot in Central Region by Daily Passenger Count

Depot_Name	Region	Daily_Passenger_Count
BhopalDepot	Central	26500

Top Depot in East Region by Daily Passenger Count

Depot_Name	Region	Daily_Passenger_Count
KolkataDepot	East	40000

Top Depot in North Region by Daily Passenger Count

Depot_Name	Region	Daily_Passenger_Count
DelhiDepot	North	45000

Top Depot in North-East Region by Daily Passenger Count

Depot_Name	Region	Daily_Passenger_Count
GuwahatiDepot	North-East	17000

Top Depot in South Region by Daily Passenger Count

Depot_Name	Region	Daily_Passenger_Count
BangaloreDepot	South	32000

Top Depot in West Region by Daily Passenger Count

Depot_Name	Region	Daily_Passenger_Count
MumbaiDepot	West	33000

To Visit My Previous First. Last. Statements:Click Here

To Visit My Previous Set Where Statements:Click Here

To Visit My Previous Online Gaming Dataset:Click Here

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

--->PLEASE FOLLOW OUR BLOG FOR MORE INFORMATION.

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

PLEASE FOLLOW OUR TELEGRAM CHANNEL CLICK HERE

PLEASE FOLLOW OUR FACEBOOK PAGE  CLICK HERE

PLEASE FOLLOW OUR INSTAGRAM PAGE CLICK HERE