Sunday, 4 January 2026

360.TRANSPORT ROUTES DATA ANALYSIS USING SAS DATA STEP | PROC SQL | PROC MEANS | PROC REG | PROC SGPLOT | MACROS | DATE FUNCTIONS (MDY-INTNX-INTCK) | APPEND | MERGE | SET | TRANSPOSE | PROC COPY

TRANSPORT ROUTES DATA ANALYSIS USING SAS DATA STEP | PROC SQL | PROC MEANS | PROC REG | PROC SGPLOT | MACROS | DATE FUNCTIONS (MDY-INTNX-INTCK) | APPEND | MERGE | SET | TRANSPOSE | PROC COPY

options nocenter;

1.TRANSPORT ROUTES DATASET CREATION

data transport_routes;

    length Route_Name $25 City $15;

    format Start_Date Analysis_Date date9.;

    Analysis_Date = '01JAN2026'd;

    input Route_Name $ City $ Stops Distance Travelers Revenue Start_Date :date9.;

    Route_Age_Years = intck('year', Start_Date, Analysis_Date);

    datalines;

BlueLine_Metro     Hyderabad 22 29.5 185000 750000 15MAR2015

RedLine_Metro      Delhi     30 34.2 240000 980000 10JAN2012

GreenBus_Corridor  Pune      18 21.0 95000  320000 01JUL2018

CityBus_Route12    Chennai   25 27.8 110000 410000 05MAY2016

Suburban_Train1    Mumbai    40 45.0 310000 1250000 12DEC2008

Airport_Express    Delhi     10 22.0 70000  520000 20FEB2011

Metro_Orange       Jaipur    14 16.5 52000  210000 01APR2019

BusRapid_A1        Ahmedabad 20 24.0 88000  360000 18AUG2017

RingRail_Outer     Kolkata   35 39.0 145000 670000 25NOV2014

CityLink_Bus9      Kochi     15 19.2 47000  180000 11JAN2020

Metro_Purple       Bengaluru 28 32.4 195000 820000 03SEP2013

HillRail_Shuttle   Shimla    8  9.5  18000  95000  01JAN2021

;

run;

proc print data=transport_routes;

run;

OUTPUT:

ObsRoute_NameCityStart_DateAnalysis_DateStopsDistanceTravelersRevenueRoute_Age_Years
1BlueLine_MetroHyderabad15MAR201501JAN20262229.518500075000011
2RedLine_MetroDelhi10JAN201201JAN20263034.224000098000014
3GreenBus_CorridorPune01JUL201801JAN20261821.0950003200008
4CityBus_Route12Chennai05MAY201601JAN20262527.811000041000010
5Suburban_Train1Mumbai12DEC200801JAN20264045.0310000125000018
6Airport_ExpressDelhi20FEB201101JAN20261022.07000052000015
7Metro_OrangeJaipur01APR201901JAN20261416.5520002100007
8BusRapid_A1Ahmedabad18AUG201701JAN20262024.0880003600009
9RingRail_OuterKolkata25NOV201401JAN20263539.014500067000012
10CityLink_Bus9Kochi11JAN202001JAN20261519.2470001800006
11Metro_PurpleBengaluru03SEP201301JAN20262832.419500082000013
12HillRail_ShuttleShimla01JAN202101JAN202689.518000950005


2.DATA VALIDATION – PROC CONTENTS & PRINT

proc contents data=transport_routes;

run;

OUTPUT:

The CONTENTS Procedure

Data Set NameWORK.TRANSPORT_ROUTESObservations12
Member TypeDATAVariables9
EngineV9Indexes0
Created01/05/2026 07:20:26Observation Length96
Last Modified01/05/2026 07:20:26Deleted Observations0
Protection CompressedNO
Data Set Type SortedNO
Label   
Data RepresentationSOLARIS_X86_64, LINUX_X86_64, ALPHA_TRU64, LINUX_IA64  
Encodingutf-8 Unicode (UTF-8)  
Engine/Host Dependent Information
Data Set Page Size131072
Number of Data Set Pages1
First Data Page1
Max Obs per Page1363
Obs in First Data Page12
Number of Data Set Repairs0
Filename/saswork/SAS_work7ECA00012603_odaws01-apse1-2.oda.sas.com/SAS_workA2B100012603_odaws01-apse1-2.oda.sas.com/transport_routes.sas7bdat
Release Created9.0401M8
Host CreatedLinux
Inode Number67108910
Access Permissionrw-r--r--
Owner Nameu63247146
File Size256KB
File Size (bytes)262144
Alphabetic List of Variables and Attributes
#VariableTypeLenFormat
4Analysis_DateNum8DATE9.
2CityChar15 
6DistanceNum8 
8RevenueNum8 
9Route_Age_YearsNum8 
1Route_NameChar25 
3Start_DateNum8DATE9.
5StopsNum8 
7TravelersNum8 

proc print data=transport_routes(obs=5);

run;

OUTPUT:

ObsRoute_NameCityStart_DateAnalysis_DateStopsDistanceTravelersRevenueRoute_Age_Years
1BlueLine_MetroHyderabad15MAR201501JAN20262229.518500075000011
2RedLine_MetroDelhi10JAN201201JAN20263034.224000098000014
3GreenBus_CorridorPune01JUL201801JAN20261821.0950003200008
4CityBus_Route12Chennai05MAY201601JAN20262527.811000041000010
5Suburban_Train1Mumbai12DEC200801JAN20264045.0310000125000018

3.DESCRIPTIVE STATISTICS – PROC MEANS

proc means data=transport_routes mean min max sum;

    var Stops Distance Travelers Revenue Route_Age_Years;

run;

OUTPUT:

The MEANS Procedure

VariableMeanMinimumMaximumSum
Stops
Distance
Travelers
Revenue
Route_Age_Years
22.0833333
26.6750000
129583.33
547083.33
10.6666667
8.0000000
9.5000000
18000.00
95000.00
5.0000000
40.0000000
45.0000000
310000.00
1250000.00
18.0000000
265.0000000
320.1000000
1555000.00
6565000.00
128.0000000

4.SQL-BASED ANALYSIS – PROC SQL

proc sql;

    create table city_summary as

    select City,

           count(*) as Total_Routes,

           sum(Travelers) as Total_Travelers,

           sum(Revenue) as Total_Revenue

    from transport_routes

    group by City;

quit;

proc print data=city_summary;

run;

OUTPUT:

ObsCityTotal_RoutesTotal_TravelersTotal_Revenue
1Ahmedabad188000360000
2Bengaluru1195000820000
3Chennai1110000410000
4Delhi23100001500000
5Hyderabad1185000750000
6Jaipur152000210000
7Kochi147000180000
8Kolkata1145000670000
9Mumbai13100001250000
10Pune195000320000
11Shimla11800095000


5.MACRO FOR ROUTE PERFORMANCE RATING

%macro route_rating(inset=, outset=);

data &outset;

    set &inset;

    if Revenue >= 800000 then Performance = "Excellent";

    else if Revenue >= 400000 then Performance = "Good";

    else if Revenue >= 200000 then Performance = "Average";

    else Performance = "Low";

run;

proc print data=&outset;

run;

%mend;


%route_rating(inset=transport_routes, outset=routes_rated);

OUTPUT:

ObsRoute_NameCityStart_DateAnalysis_DateStopsDistanceTravelersRevenueRoute_Age_YearsPerformance
1BlueLine_MetroHyderabad15MAR201501JAN20262229.518500075000011Good
2RedLine_MetroDelhi10JAN201201JAN20263034.224000098000014Excellent
3GreenBus_CorridorPune01JUL201801JAN20261821.0950003200008Average
4CityBus_Route12Chennai05MAY201601JAN20262527.811000041000010Good
5Suburban_Train1Mumbai12DEC200801JAN20264045.0310000125000018Excellent
6Airport_ExpressDelhi20FEB201101JAN20261022.07000052000015Good
7Metro_OrangeJaipur01APR201901JAN20261416.5520002100007Average
8BusRapid_A1Ahmedabad18AUG201701JAN20262024.0880003600009Average
9RingRail_OuterKolkata25NOV201401JAN20263539.014500067000012Good
10CityLink_Bus9Kochi11JAN202001JAN20261519.2470001800006Low
11Metro_PurpleBengaluru03SEP201301JAN20262832.419500082000013Excellent
12HillRail_ShuttleShimla01JAN202101JAN202689.518000950005Low


6.TIME-BASED ANALYSIS – INTNX AND MDY

data route_dates;

    set transport_routes;

    Next_Review_Date = intnx('month', Analysis_Date, 6, 'same');

    Financial_Year_Start = mdy(4,1,year(Analysis_Date));

    format Next_Review_Date Financial_Year_Start date9.;

run;

proc print data=route_dates;

run;

OUTPUT:

ObsRoute_NameCityStart_DateAnalysis_DateStopsDistanceTravelersRevenueRoute_Age_YearsNext_Review_DateFinancial_Year_Start
1BlueLine_MetroHyderabad15MAR201501JAN20262229.51850007500001101JUL202601APR2026
2RedLine_MetroDelhi10JAN201201JAN20263034.22400009800001401JUL202601APR2026
3GreenBus_CorridorPune01JUL201801JAN20261821.095000320000801JUL202601APR2026
4CityBus_Route12Chennai05MAY201601JAN20262527.81100004100001001JUL202601APR2026
5Suburban_Train1Mumbai12DEC200801JAN20264045.031000012500001801JUL202601APR2026
6Airport_ExpressDelhi20FEB201101JAN20261022.0700005200001501JUL202601APR2026
7Metro_OrangeJaipur01APR201901JAN20261416.552000210000701JUL202601APR2026
8BusRapid_A1Ahmedabad18AUG201701JAN20262024.088000360000901JUL202601APR2026
9RingRail_OuterKolkata25NOV201401JAN20263539.01450006700001201JUL202601APR2026
10CityLink_Bus9Kochi11JAN202001JAN20261519.247000180000601JUL202601APR2026
11Metro_PurpleBengaluru03SEP201301JAN20262832.41950008200001301JUL202601APR2026
12HillRail_ShuttleShimla01JAN202101JAN202689.51800095000501JUL202601APR2026


7.REGRESSION ANALYSIS – PROC REG

proc reg data=transport_routes;

    model Revenue = Travelers Distance Stops;

run;

quit;

OUTPUT:

The REG Procedure

Model: MODEL1

Dependent Variable: Revenue

Number of Observations Read12
Number of Observations Used12
Analysis of Variance
SourceDFSum of
Squares
Mean
Square
F ValuePr > F
Model31.352004E124.506682E11188.58<.0001
Error8191184209602389802620  
Corrected Total111.371123E12   
Root MSE48886R-Square0.9861
Dependent Mean547083Adj R-Sq0.9808
Coeff Var8.93568  
Parameter Estimates
VariableDFParameter
Estimate
Standard
Error
t ValuePr > |t|
Intercept1-9837655344-1.780.1134
Travelers13.454380.398718.66<.0001
Distance1252826082.003924.160.0032
Stops1-215815425.32108-3.980.0041

The REG Procedure

Model: MODEL1

Dependent Variable: Revenue

Panel of fit diagnostics for Revenue.
Panel of scatterplots of residuals by regressors for Revenue.

8.DATA VISUALIZATION – PROC SGPLOT

proc sgplot data=transport_routes;

    scatter x=Travelers y=Revenue;

    reg x=Travelers y=Revenue;

    title "Revenue vs Travelers Across Transport Routes";

run;

OUTPUT:

The SGPlot Procedure


9.APPEND OPERATION – ADDING NEW ROUTES

data new_routes;

    length Route_Name $25 City $15;

    format Start_Date date9.;

    input Route_Name $ City $ Stops Distance Travelers Revenue Start_Date :date9.;

    datalines;

Metro_Silver     Noida 18 21.5 65000 280000 01JAN2022

Bus_CrossTown    Indore 14 17.2 42000 160000 10FEB2023

;

run;

proc print data=new_routes;

run;

OUTPUT:

ObsRoute_NameCityStart_DateStopsDistanceTravelersRevenue
1Metro_SilverNoida01JAN20221821.565000280000
2Bus_CrossTownIndore10FEB20231417.242000160000


proc append base=transport_routes

            data=new_routes force;

run;

proc print data=transport_routes;

run;

OUTPUT:

ObsRoute_NameCityStart_DateAnalysis_DateStopsDistanceTravelersRevenueRoute_Age_Years
1BlueLine_MetroHyderabad15MAR201501JAN20262229.518500075000011
2RedLine_MetroDelhi10JAN201201JAN20263034.224000098000014
3GreenBus_CorridorPune01JUL201801JAN20261821.0950003200008
4CityBus_Route12Chennai05MAY201601JAN20262527.811000041000010
5Suburban_Train1Mumbai12DEC200801JAN20264045.0310000125000018
6Airport_ExpressDelhi20FEB201101JAN20261022.07000052000015
7Metro_OrangeJaipur01APR201901JAN20261416.5520002100007
8BusRapid_A1Ahmedabad18AUG201701JAN20262024.0880003600009
9RingRail_OuterKolkata25NOV201401JAN20263539.014500067000012
10CityLink_Bus9Kochi11JAN202001JAN20261519.2470001800006
11Metro_PurpleBengaluru03SEP201301JAN20262832.419500082000013
12HillRail_ShuttleShimla01JAN202101JAN202689.518000950005
13Metro_SilverNoida01JAN2022.1821.565000280000.
14Bus_CrossTownIndore10FEB2023.1417.242000160000.


10.MERGE OPERATION – ADDING CITY ZONES

data city_zones;

    input City $ Zone $;

    datalines;

Hyderabad South

Delhi North

Mumbai West

Bengaluru South

;

run;

proc print data=city_zones;

run;

OUTPUT:

ObsCityZone
1HyderabaSouth
2DelhiNorth
3MumbaiWest
4BengalurSouth

proc sort data=transport_routes; by City; run;

proc print data=transport_routes;

run;

OUTPUT:

ObsRoute_NameCityStart_DateAnalysis_DateStopsDistanceTravelersRevenueRoute_Age_Years
1BusRapid_A1Ahmedabad18AUG201701JAN20262024.0880003600009
2Metro_PurpleBengaluru03SEP201301JAN20262832.419500082000013
3CityBus_Route12Chennai05MAY201601JAN20262527.811000041000010
4RedLine_MetroDelhi10JAN201201JAN20263034.224000098000014
5Airport_ExpressDelhi20FEB201101JAN20261022.07000052000015
6BlueLine_MetroHyderabad15MAR201501JAN20262229.518500075000011
7Bus_CrossTownIndore10FEB2023.1417.242000160000.
8Metro_OrangeJaipur01APR201901JAN20261416.5520002100007
9CityLink_Bus9Kochi11JAN202001JAN20261519.2470001800006
10RingRail_OuterKolkata25NOV201401JAN20263539.014500067000012
11Suburban_Train1Mumbai12DEC200801JAN20264045.0310000125000018
12Metro_SilverNoida01JAN2022.1821.565000280000.
13GreenBus_CorridorPune01JUL201801JAN20261821.0950003200008
14HillRail_ShuttleShimla01JAN202101JAN202689.518000950005


proc sort data=city_zones; by City; run;

proc print data=city_zones;

run;

OUTPUT:

ObsCityZone
1BengalurSouth
2DelhiNorth
3HyderabaSouth
4MumbaiWest


data routes_merged;

    merge transport_routes(in=a) city_zones(in=b);

    by City;

    if a;

run;

proc print data=routes_merged;

run;

OUTPUT:

ObsRoute_NameCityStart_DateAnalysis_DateStopsDistanceTravelersRevenueRoute_Age_YearsZone
1BusRapid_A1Ahmedabad18AUG201701JAN20262024.0880003600009 
2Metro_PurpleBengaluru03SEP201301JAN20262832.419500082000013 
3CityBus_Route12Chennai05MAY201601JAN20262527.811000041000010 
4RedLine_MetroDelhi10JAN201201JAN20263034.224000098000014North
5Airport_ExpressDelhi20FEB201101JAN20261022.07000052000015North
6BlueLine_MetroHyderabad15MAR201501JAN20262229.518500075000011 
7Bus_CrossTownIndore10FEB2023.1417.242000160000. 
8Metro_OrangeJaipur01APR201901JAN20261416.5520002100007 
9CityLink_Bus9Kochi11JAN202001JAN20261519.2470001800006 
10RingRail_OuterKolkata25NOV201401JAN20263539.014500067000012 
11Suburban_Train1Mumbai12DEC200801JAN20264045.0310000125000018West
12Metro_SilverNoida01JAN2022.1821.565000280000. 
13GreenBus_CorridorPune01JUL201801JAN20261821.0950003200008 
14HillRail_ShuttleShimla01JAN202101JAN202689.518000950005 


11.SET STATEMENT – STACKING DATA

data all_routes;

    set transport_routes 

        routes_rated;

run;

proc print data=all_routes;

run;

OUTPUT:

ObsRoute_NameCityStart_DateAnalysis_DateStopsDistanceTravelersRevenueRoute_Age_YearsPerformance
1BusRapid_A1Ahmedabad18AUG201701JAN20262024.0880003600009 
2Metro_PurpleBengaluru03SEP201301JAN20262832.419500082000013 
3CityBus_Route12Chennai05MAY201601JAN20262527.811000041000010 
4RedLine_MetroDelhi10JAN201201JAN20263034.224000098000014 
5Airport_ExpressDelhi20FEB201101JAN20261022.07000052000015 
6BlueLine_MetroHyderabad15MAR201501JAN20262229.518500075000011 
7Bus_CrossTownIndore10FEB2023.1417.242000160000. 
8Metro_OrangeJaipur01APR201901JAN20261416.5520002100007 
9CityLink_Bus9Kochi11JAN202001JAN20261519.2470001800006 
10RingRail_OuterKolkata25NOV201401JAN20263539.014500067000012 
11Suburban_Train1Mumbai12DEC200801JAN20264045.0310000125000018 
12Metro_SilverNoida01JAN2022.1821.565000280000. 
13GreenBus_CorridorPune01JUL201801JAN20261821.0950003200008 
14HillRail_ShuttleShimla01JAN202101JAN202689.518000950005 
15BlueLine_MetroHyderabad15MAR201501JAN20262229.518500075000011Good
16RedLine_MetroDelhi10JAN201201JAN20263034.224000098000014Excellent
17GreenBus_CorridorPune01JUL201801JAN20261821.0950003200008Average
18CityBus_Route12Chennai05MAY201601JAN20262527.811000041000010Good
19Suburban_Train1Mumbai12DEC200801JAN20264045.0310000125000018Excellent
20Airport_ExpressDelhi20FEB201101JAN20261022.07000052000015Good
21Metro_OrangeJaipur01APR201901JAN20261416.5520002100007Average
22BusRapid_A1Ahmedabad18AUG201701JAN20262024.0880003600009Average
23RingRail_OuterKolkata25NOV201401JAN20263539.014500067000012Good
24CityLink_Bus9Kochi11JAN202001JAN20261519.2470001800006Low
25Metro_PurpleBengaluru03SEP201301JAN20262832.419500082000013Excellent
26HillRail_ShuttleShimla01JAN202101JAN202689.518000950005Low


12.TRANSPOSE – ROUTE METRICS FORMAT CHANGE

proc transpose data=transport_routes out=routes_transposed;

    var Travelers Revenue;

    by Route_Name NotSorted;

run;

proc print data=routes_transposed;

run;

OUTPUT:

ObsRoute_Name_NAME_COL1
1BusRapid_A1Travelers88000
2BusRapid_A1Revenue360000
3Metro_PurpleTravelers195000
4Metro_PurpleRevenue820000
5CityBus_Route12Travelers110000
6CityBus_Route12Revenue410000
7RedLine_MetroTravelers240000
8RedLine_MetroRevenue980000
9Airport_ExpressTravelers70000
10Airport_ExpressRevenue520000
11BlueLine_MetroTravelers185000
12BlueLine_MetroRevenue750000
13Bus_CrossTownTravelers42000
14Bus_CrossTownRevenue160000
15Metro_OrangeTravelers52000
16Metro_OrangeRevenue210000
17CityLink_Bus9Travelers47000
18CityLink_Bus9Revenue180000
19RingRail_OuterTravelers145000
20RingRail_OuterRevenue670000
21Suburban_Train1Travelers310000
22Suburban_Train1Revenue1250000
23Metro_SilverTravelers65000
24Metro_SilverRevenue280000
25GreenBus_CorridorTravelers95000
26GreenBus_CorridorRevenue320000
27HillRail_ShuttleTravelers18000
28HillRail_ShuttleRevenue95000


13.PROC COPY – DATA MIGRATION

libname backup "/folders/myfolders/backup";

proc copy in=work out=backup;

    select transport_routes;

run;

**Try The Above Code And Comment It




To Visit My Previous Intermediate Students Performance Dataset:Click Here
To Visit My Previous Namkeen Data Creation:Click Here
To Visit My Previous Hyd_Mall_Family Dataset:Click Here
To Visit My Previous Home Energy Consumption Dataset:Click Here



Follow Us On : 


 


--->FOLLOW OUR BLOG FOR MORE INFORMATION.

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

No comments:

Post a Comment