267.Which Bus Routes In India Carry The Most Passengers? Let Sas Reveal The Data

Which Bus Routes In India Carry The Most Passengers? Let Sas Reveal The Data

/*CREATING A DATASET OF BUS PASSENGERS OF INDIA*/

1) MASTER CONTROL OPTIONS 

options nocenter nodate nonumber missing=' ' formchar="|----|+|---|+=|-/\<>*";

2) USER FORMATS FOR CLEANER REPORTING    

proc format;

  value $gender   'M'='Male' 'F'='Female' 'O'='Other';

  value $tkt      'REG'='Regular' 'STU'='Student' 'SNR'='Senior' 'WKP'='Weekly Pass';

  value $pay      'CASH'='Cash' 'UPI'='UPI' 'CARD'='Card' 'WAL'='Wallet';

  value $seat     'S'='Seater' 'SL'='Sleeper';

  value $ac       'A'='AC' 'N'='Non-AC';

  value $btype    'EXP'='Express' 'LOC'='Local' 'VOL'='Volvo';

  value ageseg    low-17='Child (<=17)'

                  18-24='Youth (18-24)'

                  25-44='Adult (25-44)'

                  45-59='Mid-age (45-59)'

                  60-high='Senior (60+)';

  value sats      1='Very Low' 2='Low' 3='Medium' 4='High' 5='Very High';

run;

Log:

NOTE: Format $GENDER has been output.

NOTE: Format $TKT has been output.

NOTE: Format $PAY has been output.

NOTE: Format $SEAT has been output.

NOTE: Format $AC has been output.

NOTE: Format $BTYPE has been output.

NOTE: Format AGESEG has been output.

NOTE: Format SATS has been output.

3) LOOKUP TABLES          |

3.1 Bus Operators 

data operators;

  length Operator_ID 8 Operator_Name $30 HQ_City $20 Rating 8;

  infile datalines dsd truncover;

  input Operator_ID Operator_Name :$30. HQ_City :$20. Rating;

  datalines;

1,TSRTC,Hyderabad,4.2

2,APSRTC,Vijayawada,4.0

3,MSRTC,Mumbai,3.9

4,KSRTC_Bengaluru,Bengaluru,4.4

5,UPSRTC,Delhi,3.7

;

run;

proc print;run;

Output:

Obs	Operator_ID	Operator_Name	HQ_City	Rating
1	1	TSRTC	Hyderabad	4.2
2	2	APSRTC	Vijayawada	4.0
3	3	MSRTC	Mumbai	3.9
4	4	KSRTC_Bengaluru	Bengaluru	4.4
5	5	UPSRTC	Delhi	3.7

3.2 Routes (origin-destination pairs) 

data routes;

  length Route_ID 8 Origin $20 Destination $20 Distance_km 8 Std_Fare 8;

  infile datalines dsd;

  input Route_ID Origin :$20. Destination :$20. Distance_km Std_Fare;

  datalines;

101,Hyderabad,Warangal,150,280

102,Hyderabad,Vijayawada,275,520

103,Mumbai,Pune,150,300

104,Bengaluru,Mysuru,150,320

105,Delhi,Agra,230,480

106,Chennai,Vellore,140,260

107,Hyderabad,Karimnagar,165,300

108,Pune,Nashik,210,360

109,Bengaluru,Tumakuru,75,150

110,Mumbai,Nashik,170,340

;

run;

proc print;run;

Output:

Obs	Route_ID	Origin	Destination	Distance_km	Std_Fare
1	101	Hyderabad	Warangal	150	280
2	102	Hyderabad	Vijayawada	275	520
3	103	Mumbai	Pune	150	300
4	104	Bengaluru	Mysuru	150	320
5	105	Delhi	Agra	230	480
6	106	Chennai	Vellore	140	260
7	107	Hyderabad	Karimnagar	165	300
8	108	Pune	Nashik	210	360
9	109	Bengaluru	Tumakuru	75	150
10	110	Mumbai	Nashik	170	340

4) PRIMARY FACT TABLE: PASSENGER TRIPS   

data passengers;

  length Trip_ID 8 Passenger_ID 8 Passenger_Name $28 Gender $1 Age 8

         City $20 Route_ID 8 Operator_ID 8 Ticket_Type $3 Fare_INR 8

         Distance_km 8 Boarding_Time 8 Alighting_Time 8

         Payment_Method $4 Seat_Type $2 AC_Flag $1 Bus_Type $3

         Trip_Date 8 Delay_Minutes 8 Satisfaction 8;

  format Gender $gender. Ticket_Type $tkt. Payment_Method $pay.

         Seat_Type $seat. AC_Flag $ac. Bus_Type $btype. Trip_Date date9.

         Boarding_Time datetime19. Alighting_Time datetime19.

         Age ageseg. Satisfaction sats.;

  infile datalines dsd truncover;

  input Trip_ID Passenger_ID Passenger_Name :$28. Gender $ Age City :$20.

        Route_ID Operator_ID Ticket_Type $ Fare_INR Distance_km

        Boarding_Time :datetime20. Alighting_Time :datetime20.

        Payment_Method $ Seat_Type $ AC_Flag $ Bus_Type $ Trip_Date :date9.

        Delay_Minutes Satisfaction;

  datalines;

1,1001,Anil Reddy,M,29,Hyderabad,101,1,REG,300,150,21JUL2025:07:10:00,21JUL2025:10:05:00,UPI,S,A,EXP,21JUL2025,5,4

2,1002,Neha Sharma,F,22,Hyderabad,102,2,STU,450,275,22JUL2025:06:40:00,22JUL2025:12:10:00,CARD,S,A,VOL,22JUL2025,10,5

3,1003,Rahul Patil,M,34,Mumbai,103,3,REG,320,150,19JUL2025:08:05:00,19JUL2025:11:05:00,UPI,S,N,LOC,19JUL2025,0,3

4,1004,Priyanka Rao,F,27,Bengaluru,104,4,REG,340,150,18JUL2025:09:00:00,18JUL2025:12:00:00,WAL,S,A,EXP,18JUL2025,7,4

5,1005,Amit Singh,M,41,Delhi,105,5,REG,500,230,17JUL2025:13:20:00,17JUL2025:17:50:00,CASH,SL,A,VOL,17JUL2025,20,2

6,1006,Sunita Iyer,F,63,Chennai,106,2,SNR,220,140,16JUL2025:06:30:00,16JUL2025:09:15:00,UPI,S,N,LOC,16JUL2025,3,4

7,1007,Farhan Shaikh,M,19,Mumbai,103,3,STU,250,150,21JUL2025:18:30:00,21JUL2025:21:30:00,WAL,S,A,EXP,21JUL2025,2,5

8,1008,Sruthi Nair,F,31,Bengaluru,109,4,WKP,140,75,21JUL2025:07:50:00,21JUL2025:09:10:00,UPI,S,N,LOC,21JUL2025,1,4

9,1009,Rakesh Kumar,M,47,Hyderabad,107,1,REG,310,165,20JUL2025:05:55:00,20JUL2025:09:05:00,CARD,SL,A,VOL,20JUL2025,0,3

10,1010,Kavya Joshi,F,25,Pune,108,3,REG,360,210,22JUL2025:10:30:00,22JUL2025:14:15:00,UPI,S,N,EXP,22JUL2025,4,4

11,1011,Abhinav Jain,M,33,Delhi,105,5,REG,480,230,21JUL2025:08:00:00,21JUL2025:12:30:00,UPI,S,A,EXP,21JUL2025,12,3

12,1012,Meena Kumari,F,58,Chennai,106,2,SNR,240,140,21JUL2025:15:45:00,21JUL2025:18:30:00,CASH,S,N,LOC,21JUL2025,0,5

13,1013,Arun Gupta,M,28,Hyderabad,102,2,REG,520,275,20JUL2025:20:00:00,21JUL2025:01:30:00,UPI,S,A,VOL,20JUL2025,15,4

14,1014,Sahana R,F,24,Bengaluru,104,4,STU,260,150,22JUL2025:16:40:00,22JUL2025:19:40:00,UPI,S,N,LOC,22JUL2025,0,5

15,1015,Vikram Mehta,M,39,Mumbai,110,3,REG,340,170,18JUL2025:06:20:00,18JUL2025:09:10:00,CARD,SL,A,EXP,18JUL2025,6,4

;

run;

proc print;run;

Output:

Obs	Trip_ID	Passenger_ID	Passenger_Name	Gender	Age	City	Route_ID	Operator_ID	Ticket_Type	Fare_INR	Distance_km	Boarding_Time	Alighting_Time	Payment_Method	Seat_Type	AC_Flag	Bus_Type	Trip_Date	Delay_Minutes	Satisfaction
1	1	1001	Anil Reddy	Male	Adult (25-44)	Hyderabad	101	1	Regular	300	150	21JUL2025:07:10:00	21JUL2025:10:05:00	UPI	Seater	AC	Express	21JUL2025	5	High
2	2	1002	Neha Sharma	Female	Youth (18-24)	Hyderabad	102	2	Student	450	275	22JUL2025:06:40:00	22JUL2025:12:10:00	Card	Seater	AC	Volvo	22JUL2025	10	Very High
3	3	1003	Rahul Patil	Male	Adult (25-44)	Mumbai	103	3	Regular	320	150	19JUL2025:08:05:00	19JUL2025:11:05:00	UPI	Seater	Non-AC	Local	19JUL2025	0	Medium
4	4	1004	Priyanka Rao	Female	Adult (25-44)	Bengaluru	104	4	Regular	340	150	18JUL2025:09:00:00	18JUL2025:12:00:00	Wallet	Seater	AC	Express	18JUL2025	7	High
5	5	1005	Amit Singh	Male	Adult (25-44)	Delhi	105	5	Regular	500	230	17JUL2025:13:20:00	17JUL2025:17:50:00	Cash	Sleeper	AC	Volvo	17JUL2025	20	Low
6	6	1006	Sunita Iyer	Female	Senior (60+)	Chennai	106	2	Senior	220	140	16JUL2025:06:30:00	16JUL2025:09:15:00	UPI	Seater	Non-AC	Local	16JUL2025	3	High
7	7	1007	Farhan Shaikh	Male	Youth (18-24)	Mumbai	103	3	Student	250	150	21JUL2025:18:30:00	21JUL2025:21:30:00	Wallet	Seater	AC	Express	21JUL2025	2	Very High
8	8	1008	Sruthi Nair	Female	Adult (25-44)	Bengaluru	109	4	Weekly Pass	140	75	21JUL2025:07:50:00	21JUL2025:09:10:00	UPI	Seater	Non-AC	Local	21JUL2025	1	High
9	9	1009	Rakesh Kumar	Male	Mid-age (45-59)	Hyderabad	107	1	Regular	310	165	20JUL2025:05:55:00	20JUL2025:09:05:00	Card	Sleeper	AC	Volvo	20JUL2025	0	Medium
10	10	1010	Kavya Joshi	Female	Adult (25-44)	Pune	108	3	Regular	360	210	22JUL2025:10:30:00	22JUL2025:14:15:00	UPI	Seater	Non-AC	Express	22JUL2025	4	High
11	11	1011	Abhinav Jain	Male	Adult (25-44)	Delhi	105	5	Regular	480	230	21JUL2025:08:00:00	21JUL2025:12:30:00	UPI	Seater	AC	Express	21JUL2025	12	Medium
12	12	1012	Meena Kumari	Female	Mid-age (45-59)	Chennai	106	2	Senior	240	140	21JUL2025:15:45:00	21JUL2025:18:30:00	Cash	Seater	Non-AC	Local	21JUL2025	0	Very High
13	13	1013	Arun Gupta	Male	Adult (25-44)	Hyderabad	102	2	Regular	520	275	20JUL2025:20:00:00	21JUL2025:01:30:00	UPI	Seater	AC	Volvo	20JUL2025	15	High
14	14	1014	Sahana R	Female	Youth (18-24)	Bengaluru	104	4	Student	260	150	22JUL2025:16:40:00	22JUL2025:19:40:00	UPI	Seater	Non-AC	Local	22JUL2025	0	Very High
15	15	1015	Vikram Mehta	Male	Adult (25-44)	Mumbai	110	3	Regular	340	170	18JUL2025:06:20:00	18JUL2025:09:10:00	Card	Sleeper	AC	Express	18JUL2025	6	High

5) METADATA INSPECTION                   

proc contents data=passengers varnum; 

run;

Output:

The CONTENTS Procedure

Data Set Name	WORK.PASSENGERS	Observations	15
Member Type	DATA	Variables	20
Engine	V9	Indexes	0
Created	08/21/2025 19:16:53	Observation Length	160
Last Modified	08/21/2025 19:16:53	Deleted Observations	0
Protection		Compressed	NO
Data Set Type		Sorted	NO
Label
Data Representation	SOLARIS_X86_64, LINUX_X86_64, ALPHA_TRU64, LINUX_IA64
Encoding	utf-8 Unicode (UTF-8)

Engine/Host Dependent Information
Data Set Page Size	131072
Number of Data Set Pages	1
First Data Page	1
Max Obs per Page	818
Obs in First Data Page	15
Number of Data Set Repairs	0
Filename	/saswork/SAS_work4EA100014E17_odaws01-apse1-2.oda.sas.com/SAS_work819100014E17_odaws01-apse1-2.oda.sas.com/passengers.sas7bdat
Release Created	9.0401M8
Host Created	Linux
Inode Number	201326817
Access Permission	rw-r--r--
Owner Name	u63247146
File Size	256KB
File Size (bytes)	262144

Variables in Creation Order
#	Variable	Type	Len	Format
1	Trip_ID	Num	8
2	Passenger_ID	Num	8
3	Passenger_Name	Char	28
4	Gender	Char	1	$GENDER.
5	Age	Num	8	AGESEG.
6	City	Char	20
7	Route_ID	Num	8
8	Operator_ID	Num	8
9	Ticket_Type	Char	3	$TKT.
10	Fare_INR	Num	8
11	Distance_km	Num	8
12	Boarding_Time	Num	8	DATETIME19.
13	Alighting_Time	Num	8	DATETIME19.
14	Payment_Method	Char	4	$PAY.
15	Seat_Type	Char	2	$SEAT.
16	AC_Flag	Char	1	$AC.
17	Bus_Type	Char	3	$BTYPE.
18	Trip_Date	Num	8	DATE9.
19	Delay_Minutes	Num	8
20	Satisfaction	Num	8	SATS.

6) ENRICH FACTS WITH LOOKUPS (SQL JOIN)  

proc sql outobs=5;

  create table trips as

  select p.*,

         o.Operator_Name, o.HQ_City, o.Rating as Operator_Rating,

         r.Origin, r.Destination, r.Distance_km as Route_Distance_km,

         r.Std_Fare as Route_Std_Fare

  from passengers p

  left join operators o on p.Operator_ID = o.Operator_ID

  left join routes r    on p.Route_ID    = r.Route_ID;

quit;

proc print;run;

Output:

Obs	Trip_ID	Passenger_ID	Passenger_Name	Gender	Age	City	Route_ID	Operator_ID	Ticket_Type	Fare_INR	Distance_km	Boarding_Time	Alighting_Time	Payment_Method	Seat_Type	AC_Flag	Bus_Type	Trip_Date	Delay_Minutes	Satisfaction	Operator_Name	HQ_City	Operator_Rating	Origin	Destination	Route_Distance_km	Route_Std_Fare
1	1	1001	Anil Reddy	Male	Adult (25-44)	Hyderabad	101	1	Regular	300	150	21JUL2025:07:10:00	21JUL2025:10:05:00	UPI	Seater	AC	Express	21JUL2025	5	High	TSRTC	Hyderabad	4.2	Hyderabad	Warangal	150	280
2	13	1013	Arun Gupta	Male	Adult (25-44)	Hyderabad	102	2	Regular	520	275	20JUL2025:20:00:00	21JUL2025:01:30:00	UPI	Seater	AC	Volvo	20JUL2025	15	High	APSRTC	Vijayawada	4.0	Hyderabad	Vijayawada	275	520
3	2	1002	Neha Sharma	Female	Youth (18-24)	Hyderabad	102	2	Student	450	275	22JUL2025:06:40:00	22JUL2025:12:10:00	Card	Seater	AC	Volvo	22JUL2025	10	Very High	APSRTC	Vijayawada	4.0	Hyderabad	Vijayawada	275	520
4	7	1007	Farhan Shaikh	Male	Youth (18-24)	Mumbai	103	3	Student	250	150	21JUL2025:18:30:00	21JUL2025:21:30:00	Wallet	Seater	AC	Express	21JUL2025	2	Very High	MSRTC	Mumbai	3.9	Mumbai	Pune	150	300
5	3	1003	Rahul Patil	Male	Adult (25-44)	Mumbai	103	3	Regular	320	150	19JUL2025:08:05:00	19JUL2025:11:05:00	UPI	Seater	Non-AC	Local	19JUL2025	0	Medium	MSRTC	Mumbai	3.9	Mumbai	Pune	150	300

7) DERIVATIONS & QUALITY CHECKS          

data trips_enriched;

  set trips;

  /* Travel time in minutes */

  Travel_Minutes = intck('minute', Boarding_Time, Alighting_Time);

  /* On-time flag (<=5 min delay) */

  On_Time = (Delay_Minutes <= 5);

  /* Fare per km (observed) and deviation from standard */

  Fare_per_km = round(Fare_INR / Distance_km, .01);

  Std_Fare_per_km = round(Route_Std_Fare / Route_Distance_km, .01);

  Fare_Deviation = Fare_per_km - Std_Fare_per_km;

  /* Day part for operational analysis */

  length Day_Part $10;

  hour_b = hour(Boarding_Time);

  if 5 <= hour_b < 12 then Day_Part='Morning';

  else if 12 <= hour_b < 17 then Day_Part='Afternoon';

  else if 17 <= hour_b <= 23 then Day_Part='Evening';

  else Day_Part='Night';

  drop hour_b;

  label Travel_Minutes = "Trip Duration (mins)"

        On_Time        = "On-time (<=5 min delay)"

        Fare_per_km    = "Fare per km (INR)"

        Fare_Deviation = "Fare-per-km minus standard";

run;

proc print;run;

Output:

Obs	Trip_ID	Passenger_ID	Passenger_Name	Gender	Age	City	Route_ID	Operator_ID	Ticket_Type	Fare_INR	Distance_km	Boarding_Time	Alighting_Time	Payment_Method	Seat_Type	AC_Flag	Bus_Type	Trip_Date	Delay_Minutes	Satisfaction	Operator_Name	HQ_City	Operator_Rating	Origin	Destination	Route_Distance_km	Route_Std_Fare	Travel_Minutes	On_Time	Fare_per_km	Std_Fare_per_km	Fare_Deviation	Day_Part
1	1	1001	Anil Reddy	Male	Adult (25-44)	Hyderabad	101	1	Regular	300	150	21JUL2025:07:10:00	21JUL2025:10:05:00	UPI	Seater	AC	Express	21JUL2025	5	High	TSRTC	Hyderabad	4.2	Hyderabad	Warangal	150	280	175	1	2.00	1.87	0.13	Morning
2	13	1013	Arun Gupta	Male	Adult (25-44)	Hyderabad	102	2	Regular	520	275	20JUL2025:20:00:00	21JUL2025:01:30:00	UPI	Seater	AC	Volvo	20JUL2025	15	High	APSRTC	Vijayawada	4.0	Hyderabad	Vijayawada	275	520	330	0	1.89	1.89	0.00	Evening
3	2	1002	Neha Sharma	Female	Youth (18-24)	Hyderabad	102	2	Student	450	275	22JUL2025:06:40:00	22JUL2025:12:10:00	Card	Seater	AC	Volvo	22JUL2025	10	Very High	APSRTC	Vijayawada	4.0	Hyderabad	Vijayawada	275	520	330	0	1.64	1.89	-0.25	Morning
4	7	1007	Farhan Shaikh	Male	Youth (18-24)	Mumbai	103	3	Student	250	150	21JUL2025:18:30:00	21JUL2025:21:30:00	Wallet	Seater	AC	Express	21JUL2025	2	Very High	MSRTC	Mumbai	3.9	Mumbai	Pune	150	300	180	1	1.67	2.00	-0.33	Evening
5	3	1003	Rahul Patil	Male	Adult (25-44)	Mumbai	103	3	Regular	320	150	19JUL2025:08:05:00	19JUL2025:11:05:00	UPI	Seater	Non-AC	Local	19JUL2025	0	Medium	MSRTC	Mumbai	3.9	Mumbai	Pune	150	300	180	1	2.13	2.00	0.13	Morning

/* Basic sort to prepare for reports */

proc sort data=trips_enriched out=trips_sorted;

  by City Trip_Date descending Fare_INR;

run;

proc print data=trips_sorted (obs=5);

run;

Obs	Trip_ID	Passenger_ID	Passenger_Name	Gender	Age	City	Route_ID	Operator_ID	Ticket_Type	Fare_INR	Distance_km	Boarding_Time	Alighting_Time	Payment_Method	Seat_Type	AC_Flag	Bus_Type	Trip_Date	Delay_Minutes	Satisfaction	Operator_Name	HQ_City	Operator_Rating	Origin	Destination	Route_Distance_km	Route_Std_Fare	Travel_Minutes	On_Time	Fare_per_km	Std_Fare_per_km	Fare_Deviation	Day_Part
1	13	1013	Arun Gupta	Male	Adult (25-44)	Hyderabad	102	2	Regular	520	275	20JUL2025:20:00:00	21JUL2025:01:30:00	UPI	Seater	AC	Volvo	20JUL2025	15	High	APSRTC	Vijayawada	4.0	Hyderabad	Vijayawada	275	520	330	0	1.89	1.89	0.00	Evening
2	1	1001	Anil Reddy	Male	Adult (25-44)	Hyderabad	101	1	Regular	300	150	21JUL2025:07:10:00	21JUL2025:10:05:00	UPI	Seater	AC	Express	21JUL2025	5	High	TSRTC	Hyderabad	4.2	Hyderabad	Warangal	150	280	175	1	2.00	1.87	0.13	Morning
3	2	1002	Neha Sharma	Female	Youth (18-24)	Hyderabad	102	2	Student	450	275	22JUL2025:06:40:00	22JUL2025:12:10:00	Card	Seater	AC	Volvo	22JUL2025	10	Very High	APSRTC	Vijayawada	4.0	Hyderabad	Vijayawada	275	520	330	0	1.64	1.89	-0.25	Morning
4	3	1003	Rahul Patil	Male	Adult (25-44)	Mumbai	103	3	Regular	320	150	19JUL2025:08:05:00	19JUL2025:11:05:00	UPI	Seater	Non-AC	Local	19JUL2025	0	Medium	MSRTC	Mumbai	3.9	Mumbai	Pune	150	300	180	1	2.13	2.00	0.13	Morning
5	7	1007	Farhan Shaikh	Male	Youth (18-24)	Mumbai	103	3	Student	250	150	21JUL2025:18:30:00	21JUL2025:21:30:00	Wallet	Seater	AC	Express	21JUL2025	2	Very High	MSRTC	Mumbai	3.9	Mumbai	Pune	150	300	180	1	1.67	2.00	-0.33	Evening

8) QUICK VIEWS                           

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

  title "Sample of Enriched Trips — First 10 Rows";

run;

title;

Output:

Sample of Enriched Trips — First 15 Rows

Trip_ID	Passenger_ID	Passenger_Name	Gender	Age	City	Route_ID	Operator_ID	Ticket_Type	Fare_INR	Distance_km	Boarding_Time	Alighting_Time	Payment_Method	Seat_Type	AC_Flag	Bus_Type	Trip_Date	Delay_Minutes	Satisfaction	Operator_Name	HQ_City	Operator_Rating	Origin	Destination	Route_Distance_km	Route_Std_Fare	Trip Duration (mins)	On-time (<=5 min delay)	Fare per km (INR)	Std_Fare_per_km	Fare-per-km minus standard	Day_Part
13	1013	Arun Gupta	Male	Adult (25-44)	Hyderabad	102	2	Regular	520	275	20JUL2025:20:00:00	21JUL2025:01:30:00	UPI	Seater	AC	Volvo	20JUL2025	15	High	APSRTC	Vijayawada	4.0	Hyderabad	Vijayawada	275	520	330	0	1.89	1.89	0.00	Evening
1	1001	Anil Reddy	Male	Adult (25-44)	Hyderabad	101	1	Regular	300	150	21JUL2025:07:10:00	21JUL2025:10:05:00	UPI	Seater	AC	Express	21JUL2025	5	High	TSRTC	Hyderabad	4.2	Hyderabad	Warangal	150	280	175	1	2.00	1.87	0.13	Morning
2	1002	Neha Sharma	Female	Youth (18-24)	Hyderabad	102	2	Student	450	275	22JUL2025:06:40:00	22JUL2025:12:10:00	Card	Seater	AC	Volvo	22JUL2025	10	Very High	APSRTC	Vijayawada	4.0	Hyderabad	Vijayawada	275	520	330	0	1.64	1.89	-0.25	Morning
3	1003	Rahul Patil	Male	Adult (25-44)	Mumbai	103	3	Regular	320	150	19JUL2025:08:05:00	19JUL2025:11:05:00	UPI	Seater	Non-AC	Local	19JUL2025	0	Medium	MSRTC	Mumbai	3.9	Mumbai	Pune	150	300	180	1	2.13	2.00	0.13	Morning
7	1007	Farhan Shaikh	Male	Youth (18-24)	Mumbai	103	3	Student	250	150	21JUL2025:18:30:00	21JUL2025:21:30:00	Wallet	Seater	AC	Express	21JUL2025	2	Very High	MSRTC	Mumbai	3.9	Mumbai	Pune	150	300	180	1	1.67	2.00	-0.33	Evening

9) FREQUENCY & DISTRIBUTION ANALYSIS     

9.1 Ticket type by city 

proc freq data=trips_enriched;

  tables City*Ticket_Type / nocol nopercent norow;

  title "Ticket Type Mix by City";

run;

Output:

Ticket Type Mix by City

The FREQ Procedure

Frequency

Table of City by Ticket_Type City Ticket_Type Regular Student Total Hyderabad 2 1 3 Mumbai 1 1 2 Total 3 2 5

9.2 Payment method and AC preference 

proc freq data=trips_enriched;

  tables Payment_Method*AC_Flag / chisq;

  title "Payment Method vs AC/Non-AC";

run;

Output:

Payment Method vs AC/Non-AC

The FREQ Procedure

Frequency Percent Row Pct Col Pct

Table of Payment_Method by AC_Flag Payment_Method AC_Flag AC Non-AC Total Card 1 20.00 100.00 25.00 0 0.00 0.00 0.00 1 20.00     UPI 2 40.00 66.67 50.00 1 20.00 33.33 100.00 3 60.00     Wallet 1 20.00 100.00 25.00 0 0.00 0.00 0.00 1 20.00     Total 4 80.00 1 20.00 5 100.00

Statistics for Table of Payment_Method by AC_Flag

Statistic	DF	Value	Prob
WARNING: 100% of the cells have expected counts less than 5. Chi-Square may not be a valid test.
Chi-Square	2	0.8333	0.6592
Likelihood Ratio Chi-Square	2	1.1849	0.5530
Mantel-Haenszel Chi-Square	1	0.0000	1.0000
Phi Coefficient		0.4082
Contingency Coefficient		0.3780
Cramer's V		0.4082

Sample Size = 5

 9.3 Age distribution and satisfaction 

proc univariate data=trips_enriched noprint;

  var Fare_INR Travel_Minutes Delay_Minutes;

  histogram Fare_INR Travel_Minutes / normal;

  inset n mean std min max / position=ne;

run;

Output:

The UNIVARIATE Procedure

The UNIVARIATE Procedure

Fitted Normal Distribution for Fare_INR

Parameters for Normal Distribution
Parameter	Symbol	Estimate
Mean	Mu	368
Std Dev	Sigma	112.5611

Goodness-of-Fit Tests for Normal Distribution
Test	Statistic		p Value
Kolmogorov-Smirnov	D	0.26510458	Pr > D	>0.150
Cramer-von Mises	W-Sq	0.05149527	Pr > W-Sq	>0.250
Anderson-Darling	A-Sq	0.29400112	Pr > A-Sq	>0.250

Quantiles for Normal Distribution
Percent	Quantile
	Observed	Estimated
1.0	250.000	106.144
5.0	250.000	182.853
10.0	250.000	223.747
25.0	300.000	292.079
50.0	320.000	368.000
75.0	450.000	443.921
90.0	520.000	512.253
95.0	520.000	553.147
99.0	520.000	629.856

The UNIVARIATE Procedure

The UNIVARIATE Procedure

Fitted Normal Distribution for Travel_Minutes (Trip Duration (mins))

Parameters for Normal Distribution
Parameter	Symbol	Estimate
Mean	Mu	239
Std Dev	Sigma	83.09633

Goodness-of-Fit Tests for Normal Distribution
Test	Statistic		p Value
Kolmogorov-Smirnov	D	0.36115392	Pr > D	0.031
Cramer-von Mises	W-Sq	0.13115599	Pr > W-Sq	0.029
Anderson-Darling	A-Sq	0.75927167	Pr > A-Sq	0.019

Quantiles for Normal Distribution
Percent	Quantile
	Observed	Estimated
1.0	175.000	45.6890
5.0	175.000	102.3187
10.0	175.000	132.5078
25.0	180.000	182.9524
50.0	180.000	239.0000
75.0	330.000	295.0476
90.0	330.000	345.4922
95.0	330.000	375.6813
99.0	330.000	432.3110

10) NUMERIC SUMMARIES                    

proc means data=trips_enriched n mean std min p25 median p75 max maxdec=1;

  class City Bus_Type;

  var Fare_INR Distance_km Travel_Minutes Delay_Minutes Satisfaction;

  title "Key Measures by City and Bus Type";

run;

Output:

Key Measures by City and Bus Type

The MEANS Procedure

City	Bus_Type	N Obs	Variable	Label	N	Mean	Std Dev	Minimum	25th Pctl	Median	75th Pctl	Maximum
Hyderabad	Express	1	Fare_INR Distance_km Travel_Minutes Delay_Minutes Satisfaction	Trip Duration (mins)	1 1 1 1 1	300.0 150.0 175.0 5.0 4.0		300.0 150.0 175.0 5.0 4.0	300.0 150.0 175.0 5.0 4.0	300.0 150.0 175.0 5.0 4.0	300.0 150.0 175.0 5.0 4.0	300.0 150.0 175.0 5.0 4.0
	Volvo	2	Fare_INR Distance_km Travel_Minutes Delay_Minutes Satisfaction	Trip Duration (mins)	2 2 2 2 2	485.0 275.0 330.0 12.5 4.5	49.5 0.0 0.0 3.5 0.7	450.0 275.0 330.0 10.0 4.0	450.0 275.0 330.0 10.0 4.0	485.0 275.0 330.0 12.5 4.5	520.0 275.0 330.0 15.0 5.0	520.0 275.0 330.0 15.0 5.0
Mumbai	Express	1	Fare_INR Distance_km Travel_Minutes Delay_Minutes Satisfaction	Trip Duration (mins)	1 1 1 1 1	250.0 150.0 180.0 2.0 5.0		250.0 150.0 180.0 2.0 5.0	250.0 150.0 180.0 2.0 5.0	250.0 150.0 180.0 2.0 5.0	250.0 150.0 180.0 2.0 5.0	250.0 150.0 180.0 2.0 5.0
	Local	1	Fare_INR Distance_km Travel_Minutes Delay_Minutes Satisfaction	Trip Duration (mins)	1 1 1 1 1	320.0 150.0 180.0 0.0 3.0		320.0 150.0 180.0 0.0 3.0	320.0 150.0 180.0 0.0 3.0	320.0 150.0 180.0 0.0 3.0	320.0 150.0 180.0 0.0 3.0	320.0 150.0 180.0 0.0 3.0

proc summary data=trips_enriched nway;

  class Operator_Name City;

  var Fare_INR Delay_Minutes;

  output out=op_city_summary(drop=_type_ _freq_) 

         n=Trips sum(Fare_INR)=Revenue_INR mean(Delay_Minutes)=Avg_Delay_Min;

run;

proc print;run;

Output:

Obs	Operator_Name	City	Trips	Revenue_INR	Avg_Delay_Min
1	APSRTC	Hyderabad	2	970	12.5
2	MSRTC	Mumbai	2	570	1.0
3	TSRTC	Hyderabad	1	300	5.0

10.1 Rank operators by revenue within city 

proc rank data=op_city_summary out=op_city_rank ties=low descending;

  by City;

  var Revenue_INR;

  ranks Rev_Rank;

run;

Output:

Obs	Operator_Name	City	Trips	Revenue_INR	Avg_Delay_Min	Rev_Rank
1	APSRTC	Hyderabad	2	970	12.5	1
2	MSRTC	Mumbai	2	570	1.0	1
3	TSRTC	Hyderabad	1	300	5.0	1

11) TABULATE & REPORT 

proc tabulate data=trips_enriched format=8.1;

  class City Ticket_Type Bus_Type;

  var Fare_INR Travel_Minutes;

  table City,

        (Ticket_Type all)*(Fare_INR*(mean) Travel_Minutes*(mean))

        Bus_Type*(Fare_INR*(mean)) / misstext='0';

  title "Average Fare and Duration by Ticket Type and Bus Type (City-wise)";

run;

Output:

Average Fare and Duration by Ticket Type and Bus Type (City-wise)

	Ticket_Type				All		Bus_Type
	Regular		Student				Express	Local	Volvo
	Fare_INR	Trip Duration (mins)	Fare_INR	Trip Duration (mins)	Fare_INR	Trip Duration (mins)	Fare_INR	Fare_INR	Fare_INR
	Mean	Mean	Mean	Mean	Mean	Mean	Mean	Mean	Mean
City	410.0	252.5	450.0	330.0	423.3	278.3	300.0	0	485.0
Hyderabad
Mumbai	320.0	180.0	250.0	180.0	285.0	180.0	250.0	320.0	0

proc report data=op_city_rank nowd headline headskip;

  columns City Operator_Name Trips Revenue_INR Rev_Rank Avg_Delay_Min;

  define City / group "City";

  define Operator_Name / display "Operator";

  define Trips / analysis sum "Trips";

  define Revenue_INR / analysis sum "Revenue (INR)" format=comma10.;

  define Rev_Rank / display "Revenue Rank (Within City)";

  define Avg_Delay_Min / analysis mean format=6.1 "Avg Delay (min)";

  title "Operator Performance by City — Ranked by Revenue";

run;

Output:

Operator Performance by City — Ranked by Revenue

City	Operator	Trips	Revenue (INR)	Revenue Rank (Within City)	Avg Delay (min)
Hyderabad	APSRTC	2	970	1	12.5
	TSRTC	1	300	1	5.0
Mumbai	MSRTC	2	570	1	1.0

12) TRANSPOSE EXAMPLE (WIDE PIVOT) 

12.1 Payment method revenue by city — then pivot to wide 

proc sql ;

  create table pay_city as

  select City, Payment_Method, sum(Fare_INR) as Revenue_INR

  from trips_enriched

  group by City, Payment_Method;

quit;

proc print;run;

Output:

Obs	City	Payment_Method	Revenue_INR
1	Hyderabad	Card	450
2	Hyderabad	UPI	820
3	Mumbai	UPI	320
4	Mumbai	Wallet	250

proc sort data=pay_city; 

    by City Payment_Method;

 run;

proc transpose data=pay_city out=pay_city_wide prefix=Rev_;

  by City;

  id Payment_Method;

  var Revenue_INR;

run;

proc print data=pay_city_wide;

run;

Output:

Obs	City	_NAME_	Rev_Card	Rev_UPI	Rev_Wallet
1	Hyderabad	Revenue_INR	450	820
2	Mumbai	Revenue_INR		320	250

13) PROC SQL: KPIs, MONTH BUCKET, VIEWS  

proc sql;

/*   Month bucket (using Trip_Date) */

  create table kpi_month as

  select put(intnx('month', Trip_Date, 0, 'b'), monyy7.) as Mon label='Month',

         City,

         count(*) as Trips,

         mean(Fare_INR) as Avg_Fare,

         sum(Fare_INR)  as Revenue_INR,

         mean(Delay_Minutes) as Avg_Delay

  from trips_enriched

  group by calculated Mon, City;

/*   Route profitability metric (fare vs route standard) */

  create table route_perf as

  select Route_ID, Origin, Destination,

         count(*) as Trips,

         mean(Fare_per_km) as Avg_Fare_per_km,

         mean(Std_Fare_per_km) as Std_per_km,

         mean(Fare_Deviation)  as Avg_Deviation

  from trips_enriched

  group by Route_ID, Origin, Destination

  order by Avg_Deviation desc;

proc print data=kpi_month;

run;

Output:

Obs	Mon	City	Trips	Avg_Fare	Revenue_INR	Avg_Delay
1	JUL2025	Hyderabad	3	423.333	1270	10
2	JUL2025	Mumbai	2	285.000	570	1

proc print data=route_perf ;

run;

Output:

Obs	Route_ID	Origin	Destination	Trips	Avg_Fare_per_km	Std_per_km	Avg_Deviation
1	101	Hyderabad	Warangal	1	2.000	1.87	0.130
2	103	Mumbai	Pune	2	1.900	2.00	-0.100
3	102	Hyderabad	Vijayawada	2	1.765	1.89	-0.125

/*   A view for frequent student riders (ticket=STU or WKP) */

  create view v_student_frequent as

  select Passenger_ID, Passenger_Name, City, Ticket_Type, count(*) as Trips

  from trips_enriched

  where Ticket_Type in ('STU', 'WKP')

  group by Passenger_ID, Passenger_Name, City, Ticket_Type

  having calculated Trips >= 2;

quit;

proc print data=v_student_frequent ;

run;

Log:

NOTE: No observations in data set WORK.V_STUDENT_FREQUENT.

NOTE: There were 2 observations read from the data set WORK.TRIPS_ENRICHED.

WHERE Ticket_Type in ('STU', 'WKP');

NOTE: There were 0 observations read from the data set WORK.V_STUDENT_FREQUENT.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.00 seconds

user cpu time 0.01 seconds

system cpu time 0.00 seconds

memory 5528.34k

OS Memory 29608.00k

Timestamp 08/21/2025 02:04:04 PM

Step Count 227 Switch Count 0

Page Faults 0

Page Reclaims 205

Page Swaps 0

Voluntary Context Switches 10

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 0

14) VISUAL: SGPLOT                       

ods graphics on;

proc sgplot data=trips_enriched;

  vbox Fare_per_km / category=City;

  title "Fare per km — Distribution by City";

run;

proc sgplot data=trips_enriched;

  vbar City / response=Satisfaction stat=mean datalabel;

  title "Average Satisfaction by City";

run;

ods graphics off;

Output:

---

15) MACROS FOR AUTOMATION                

15.1 Macro to print a city-specific report with parameterized filters 

%macro city_report(city=, bus=, mindate=, maxdate=);

  %put NOTE: Running city_report for &city. bus=&bus. date=&mindate - &maxdate;

  title "City Report: &city (Bus=&bus, Dates=&mindate to &maxdate)";

  proc sql;

    create table _city as

    select *

    from trips_enriched

    where City="&city"

      %if %length(&bus) %then and Bus_Type="&bus";

      %if %length(&mindate) %then and Trip_Date >= "&mindate"d;

      %if %length(&maxdate) %then and Trip_Date <= "&maxdate"d;

    ;

  quit;

  proc means data=_city n mean std min p25 median p75 max;

    class Ticket_Type;

    var Fare_INR Travel_Minutes Delay_Minutes Satisfaction;

    title2 "Descriptive Stats by Ticket Type";

  run;

  proc freq data=_city;

    tables Payment_Method*AC_Flag / chisq;

    title2 "Payment vs AC Flag — &city";

  run;

  proc report data=_city nowd;

    columns Route_ID Origin Destination Bus_Type Ticket_Type Fare_INR Travel_Minutes Delay_Minutes Satisfaction;

    define Route_ID / group;

    define Origin / display;

    define Destination / display;

    define Bus_Type / display;

    define Ticket_Type / display;

    define Fare_INR / analysis mean format=8.1 "Avg Fare";

    define Travel_Minutes / analysis mean format=6.1 "Avg Minutes";

    define Delay_Minutes / analysis mean format=5.1 "Avg Delay";

    define Satisfaction / analysis mean format=4.1 "Avg Sat";

  run;

  title;

%mend;

15.2 Macro to generate operator leaderboard by KPI 

%macro operator_leaderboard(metric=Revenue_INR);

  proc sql;

    create table _op as

    select Operator_Name,

           sum(Fare_INR) as Revenue_INR,

           count(*) as Trips,

           mean(Satisfaction) as Avg_Sat,

           mean(Delay_Minutes) as Avg_Delay

    from trips_enriched

    group by Operator_Name;

  quit;

  %if %upcase(&metric)=REVENUE_INR %then %do;

    proc sort data=_op; by descending Revenue_INR; run;

  %end;

  %else %if %upcase(&metric)=TRIPS %then %do;

    proc sort data=_op; by descending Trips; run;

  %end;

  %else %if %upcase(&metric)=AVG_SAT %then %do;

    proc sort data=_op; by descending Avg_Sat; run;

  %end;

  %else %if %upcase(&metric)=AVG_DELAY %then %do;

    proc sort data=_op; by Avg_Delay; run;

  %end;

  proc print data=_op label noobs;

    label Revenue_INR='Revenue (INR)' Trips='Trips' Avg_Sat='Avg Satisfaction' Avg_Delay='Avg Delay (min)';

    title "Operator Leaderboard — Sorted by &metric";

  run; title;

%mend;

15.3 Macro loop to print common city snapshots 

%macro common_cities;

  %city_report(city=Hyderabad, bus=EXP, mindate=16JUL2025, maxdate=24JUL2025);

  %city_report(city=Mumbai,    bus=EXP, mindate=16JUL2025, maxdate=24JUL2025);

  %city_report(city=Bengaluru, bus=,    mindate=16JUL2025, maxdate=24JUL2025);

%mend;

%common_cities;

Output:

City Report: Hyderabad (Bus=EXP, Dates=16JUL2025 to 24JUL2025)

Descriptive Stats by Ticket Type

The MEANS Procedure

Ticket_Type	N Obs	Variable	Label	N	Mean	Std Dev	Minimum	25th Pctl	Median	75th Pctl	Maximum
Regular	1	Fare_INR Travel_Minutes Delay_Minutes Satisfaction	Trip Duration (mins)	1 1 1 1	300.0000000 175.0000000 5.0000000 4.0000000		300.0000000 175.0000000 5.0000000 4.0000000	300.0000000 175.0000000 5.0000000 4.0000000	300.0000000 175.0000000 5.0000000 4.0000000	300.0000000 175.0000000 5.0000000 4.0000000	300.0000000 175.0000000 5.0000000 4.0000000

City Report: Hyderabad (Bus=EXP, Dates=16JUL2025 to 24JUL2025)

Payment vs AC Flag — Hyderabad

The FREQ Procedure

Frequency Percent Row Pct Col Pct

Table of Payment_Method by AC_Flag Payment_Method AC_Flag AC Total UPI 1 100.00 100.00 100.00 1 100.00     Total 1 100.00 1 100.00

City Report: Hyderabad (Bus=EXP, Dates=16JUL2025 to 24JUL2025)

Payment vs AC Flag — Hyderabad

Route_ID	Origin	Destination	Bus_Type	Ticket_Type	Avg Fare	Avg Minutes	Avg Delay	Avg Sat
101	Hyderabad	Warangal	Express	Regular	300.0	175.0	5.0	4.0

City Report: Mumbai (Bus=EXP, Dates=16JUL2025 to 24JUL2025)

Descriptive Stats by Ticket Type

The MEANS Procedure

Ticket_Type	N Obs	Variable	Label	N	Mean	Std Dev	Minimum	25th Pctl	Median	75th Pctl	Maximum
Student	1	Fare_INR Travel_Minutes Delay_Minutes Satisfaction	Trip Duration (mins)	1 1 1 1	250.0000000 180.0000000 2.0000000 5.0000000		250.0000000 180.0000000 2.0000000 5.0000000	250.0000000 180.0000000 2.0000000 5.0000000	250.0000000 180.0000000 2.0000000 5.0000000	250.0000000 180.0000000 2.0000000 5.0000000	250.0000000 180.0000000 2.0000000 5.0000000

City Report: Mumbai (Bus=EXP, Dates=16JUL2025 to 24JUL2025)

Payment vs AC Flag — Mumbai

The FREQ Procedure

Frequency Percent Row Pct Col Pct

Table of Payment_Method by AC_Flag Payment_Method AC_Flag AC Total Wallet 1 100.00 100.00 100.00 1 100.00     Total 1 100.00 1 100.00

City Report: Mumbai (Bus=EXP, Dates=16JUL2025 to 24JUL2025)

Payment vs AC Flag — Mumbai

Route_ID	Origin	Destination	Bus_Type	Ticket_Type	Avg Fare	Avg Minutes	Avg Delay	Avg Sat
103	Mumbai	Pune	Express	Student	250.0	180.0	2.0	5.0

%operator_leaderboard(metric=Revenue_INR);

Output:

Operator Leaderboard — Sorted by Revenue_INR

Operator_Name	Revenue (INR)	Trips	Avg Satisfaction	Avg Delay (min)
APSRTC	970	2	4.5	12.5
MSRTC	570	2	4.0	1.0
TSRTC	300	1	4.0	5.0

%operator_leaderboard(metric=AVG_Delay);

Output:

Operator Leaderboard — Sorted by AVG_Delay

Operator_Name	Revenue (INR)	Trips	Avg Satisfaction	Avg Delay (min)
MSRTC	570	2	4.0	1.0
TSRTC	300	1	4.0	5.0
APSRTC	970	2	4.5	12.5

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

--->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