320.OCEAN SHIP FUEL EFFICIENCY AND VOYAGE ANALYSIS USING PROC SQL | PROC MEANS | PROC CORR | PROC UNIVARIATE | PROC SGPLOT WITH INTCK | INTNX AND MACRO-BASED GROUPING

OCEAN SHIP FUEL EFFICIENCY AND VOYAGE ANALYSIS USING PROC SQL | PROC MEANS | PROC CORR | PROC UNIVARIATE | PROC SGPLOT WITH INTCK | INTNX AND MACRO-BASED GROUPING

STEP 1: CREATE BASE DATASET WITH SHIP DETAILS + DATES

options nocenter;

data work.ships_raw;

    length Ship_Name $30 Type $20;

    infile datalines dlm='|' dsd;

    input Ship_ID Ship_Name :$30. Type :$20. Cargo_Capacity /* in metric tons */

          Crew_Size Distance_Travelled  /* in nautical miles for this voyage */

          Fuel_Consumption    /* in metric tons for this voyage */ Departure_Date :date9.

          Arrival_Date   :date9.;    

    format Departure_Date Arrival_Date date9.;         

    datalines;

1|Arctic Voyager|Container|18000|24|12000|950|01JAN2025|25JAN2025

2|Pacific Titan|Oil Tanker|300000|28|9000|1100|15FEB2025|05MAR2025

3|Indian Star|Bulk Carrier|85000|22|7000|650|20JAN2025|10FEB2025

4|Atlantic Breeze|Container|15000|20|5000|420|10MAR2025|22MAR2025

5|Southern Cross|LNG Carrier|140000|30|11000|980|05APR2025|28APR2025

6|Northern Light|Research Vessel|8000|40|3500|260|18JAN2025|05FEB2025

7|Ocean Spirit|Car Carrier|20000|26|8000|700|25FEB2025|18MAR2025

8|Cargo King|Bulk Carrier|95000|24|9500|900|02MAR2025|27MAR2025

9|Eastern Pearl|Container|13000|18|4300|380|12APR2025|24APR2025

10|Western Horizon|Oil Tanker|280000|27|10000|1250|01MAY2025|20MAY2025

11|Blue Dolphin|LNG Carrier|135000|29|8200|770|15MAR2025|05APR2025

12|Red Mariner|Bulk Carrier|90000|23|7600|820|08MAY2025|30MAY2025

13|Silver Wave|Research Vessel|7500|38|4200|310|28FEB2025|20MAR2025

14|Golden Anchor|Car Carrier|22000|25|6700|560|10APR2025|30APR2025

;

run;

proc print data=work.ships_raw;

run;

OUTPUT:

Obs	Ship_Name	Type	Ship_ID	Cargo_Capacity	Crew_Size	Distance_Travelled	Fuel_Consumption	Departure_Date	Arrival_Date
1	Arctic Voyager	Container	1	18000	24	12000	950	01JAN2025	25JAN2025
2	Pacific Titan	Oil Tanker	2	300000	28	9000	1100	15FEB2025	05MAR2025
3	Indian Star	Bulk Carrier	3	85000	22	7000	650	20JAN2025	10FEB2025
4	Atlantic Breeze	Container	4	15000	20	5000	420	10MAR2025	22MAR2025
5	Southern Cross	LNG Carrier	5	140000	30	11000	980	05APR2025	28APR2025
6	Northern Light	Research Vessel	6	8000	40	3500	260	18JAN2025	05FEB2025
7	Ocean Spirit	Car Carrier	7	20000	26	8000	700	25FEB2025	18MAR2025
8	Cargo King	Bulk Carrier	8	95000	24	9500	900	02MAR2025	27MAR2025
9	Eastern Pearl	Container	9	13000	18	4300	380	12APR2025	24APR2025
10	Western Horizon	Oil Tanker	10	280000	27	10000	1250	01MAY2025	20MAY2025
11	Blue Dolphin	LNG Carrier	11	135000	29	8200	770	15MAR2025	05APR2025
12	Red Mariner	Bulk Carrier	12	90000	23	7600	820	08MAY2025	30MAY2025
13	Silver Wave	Research Vessel	13	7500	38	4200	310	28FEB2025	20MAR2025
14	Golden Anchor	Car Carrier	14	22000	25	6700	560	10APR2025	30APR2025

STEP 2: ADD DATE-DERIVED VARIABLES USING INTCK & INTNX

data work.ships_dates;

    set work.ships_raw;

    /* Voyage duration in days using INTCK */

    Voyage_Days = intck('day', Departure_Date, Arrival_Date);

    /* Schedule next maintenance 6 months after departure using INTNX */

    Next_Maintenance = intnx('month', Departure_Date, 6, 'same');

    /* Create a year-month bucket for reporting (beginning of month) */

    Year_Month = intnx('month', Departure_Date, 0, 'beginning');

    /* Different display formats for date variables */

    format Next_Maintenance yymmdd10.

                 Year_Month       monyy7.

                 Departure_Date   date9.

                 Arrival_Date     ddmmyy10.;

run;

proc print data=work.ships_dates noobs;

    title "Ships with Date-Derived Variables (INTCK and INTNX)";

run;

OUTPUT:

Ships with Date-Derived Variables (INTCK and INTNX)

Ship_Name	Type	Ship_ID	Cargo_Capacity	Crew_Size	Distance_Travelled	Fuel_Consumption	Departure_Date	Arrival_Date	Voyage_Days	Next_Maintenance	Year_Month
Arctic Voyager	Container	1	18000	24	12000	950	01JAN2025	25/01/2025	24	2025-07-01	JAN2025
Pacific Titan	Oil Tanker	2	300000	28	9000	1100	15FEB2025	05/03/2025	18	2025-08-15	FEB2025
Indian Star	Bulk Carrier	3	85000	22	7000	650	20JAN2025	10/02/2025	21	2025-07-20	JAN2025
Atlantic Breeze	Container	4	15000	20	5000	420	10MAR2025	22/03/2025	12	2025-09-10	MAR2025
Southern Cross	LNG Carrier	5	140000	30	11000	980	05APR2025	28/04/2025	23	2025-10-05	APR2025
Northern Light	Research Vessel	6	8000	40	3500	260	18JAN2025	05/02/2025	18	2025-07-18	JAN2025
Ocean Spirit	Car Carrier	7	20000	26	8000	700	25FEB2025	18/03/2025	21	2025-08-25	FEB2025
Cargo King	Bulk Carrier	8	95000	24	9500	900	02MAR2025	27/03/2025	25	2025-09-02	MAR2025
Eastern Pearl	Container	9	13000	18	4300	380	12APR2025	24/04/2025	12	2025-10-12	APR2025
Western Horizon	Oil Tanker	10	280000	27	10000	1250	01MAY2025	20/05/2025	19	2025-11-01	MAY2025
Blue Dolphin	LNG Carrier	11	135000	29	8200	770	15MAR2025	05/04/2025	21	2025-09-15	MAR2025
Red Mariner	Bulk Carrier	12	90000	23	7600	820	08MAY2025	30/05/2025	22	2025-11-08	MAY2025
Silver Wave	Research Vessel	13	7500	38	4200	310	28FEB2025	20/03/2025	20	2025-08-28	FEB2025
Golden Anchor	Car Carrier	14	22000	25	6700	560	10APR2025	30/04/2025	20	2025-10-10	APR2025

STEP 3: USE PROC SQL TO BUILD ANALYSIS DATASET

proc sql;

    create table work.ships_analysis as

    select Ship_ID, Ship_Name, Type, Cargo_Capacity, Crew_Size, Distance_Travelled,

           Fuel_Consumption, Departure_Date, Arrival_Date, Voyage_Days,

           Next_Maintenance, Year_Month,

           /* Fuel efficiency: nautical miles per ton of fuel */

           (Distance_Travelled / Fuel_Consumption) as Fuel_Efficiency

                label = "Nm per Ton of Fuel",

           /* Another ratio: distance per ton of capacity */

           (Distance_Travelled / Cargo_Capacity) as Dist_per_Capacity

                label = "Nm per Ton of Capacity"

    from work.ships_dates;

quit;

proc print data=work.ships_analysis (obs=10);

    title "Sample of Ships Analysis Dataset";

run;

OUTPUT:

Sample of Ships Analysis Dataset

Obs	Ship_ID	Ship_Name	Type	Cargo_Capacity	Crew_Size	Distance_Travelled	Fuel_Consumption	Departure_Date	Arrival_Date	Voyage_Days	Next_Maintenance	Year_Month	Fuel_Efficiency	Dist_per_Capacity
1	1	Arctic Voyager	Container	18000	24	12000	950	01JAN2025	25/01/2025	24	2025-07-01	JAN2025	12.6316	0.66667
2	2	Pacific Titan	Oil Tanker	300000	28	9000	1100	15FEB2025	05/03/2025	18	2025-08-15	FEB2025	8.1818	0.03000
3	3	Indian Star	Bulk Carrier	85000	22	7000	650	20JAN2025	10/02/2025	21	2025-07-20	JAN2025	10.7692	0.08235
4	4	Atlantic Breeze	Container	15000	20	5000	420	10MAR2025	22/03/2025	12	2025-09-10	MAR2025	11.9048	0.33333
5	5	Southern Cross	LNG Carrier	140000	30	11000	980	05APR2025	28/04/2025	23	2025-10-05	APR2025	11.2245	0.07857
6	6	Northern Light	Research Vessel	8000	40	3500	260	18JAN2025	05/02/2025	18	2025-07-18	JAN2025	13.4615	0.43750
7	7	Ocean Spirit	Car Carrier	20000	26	8000	700	25FEB2025	18/03/2025	21	2025-08-25	FEB2025	11.4286	0.40000
8	8	Cargo King	Bulk Carrier	95000	24	9500	900	02MAR2025	27/03/2025	25	2025-09-02	MAR2025	10.5556	0.10000
9	9	Eastern Pearl	Container	13000	18	4300	380	12APR2025	24/04/2025	12	2025-10-12	APR2025	11.3158	0.33077
10	10	Western Horizon	Oil Tanker	280000	27	10000	1250	01MAY2025	20/05/2025	19	2025-11-01	MAY2025	8.0000	0.03571

STEP 4: MACRO FOR FUEL-EFFICIENCY GROUPING

%macro fuel_group(in=work.ships_analysis, out=work.ships_fe_grouped);

    data &out.;

        set &in.;

        length Fuel_Group $20;

        if Fuel_Efficiency >= 18 then Fuel_Group = "Highly_Efficient";

        else if Fuel_Efficiency >= 15 then Fuel_Group = "Moderate";

        else Fuel_Group = "Low_Efficiency";

    run;

    proc print data= &out.;

    run;

%mend fuel_group;

%fuel_group();

OUTPUT:

Obs	Ship_ID	Ship_Name	Type	Cargo_Capacity	Crew_Size	Distance_Travelled	Fuel_Consumption	Departure_Date	Arrival_Date	Voyage_Days	Next_Maintenance	Year_Month	Fuel_Efficiency	Dist_per_Capacity	Fuel_Group
1	1	Arctic Voyager	Container	18000	24	12000	950	01JAN2025	25/01/2025	24	2025-07-01	JAN2025	12.6316	0.66667	Low_Efficiency
2	2	Pacific Titan	Oil Tanker	300000	28	9000	1100	15FEB2025	05/03/2025	18	2025-08-15	FEB2025	8.1818	0.03000	Low_Efficiency
3	3	Indian Star	Bulk Carrier	85000	22	7000	650	20JAN2025	10/02/2025	21	2025-07-20	JAN2025	10.7692	0.08235	Low_Efficiency
4	4	Atlantic Breeze	Container	15000	20	5000	420	10MAR2025	22/03/2025	12	2025-09-10	MAR2025	11.9048	0.33333	Low_Efficiency
5	5	Southern Cross	LNG Carrier	140000	30	11000	980	05APR2025	28/04/2025	23	2025-10-05	APR2025	11.2245	0.07857	Low_Efficiency
6	6	Northern Light	Research Vessel	8000	40	3500	260	18JAN2025	05/02/2025	18	2025-07-18	JAN2025	13.4615	0.43750	Low_Efficiency
7	7	Ocean Spirit	Car Carrier	20000	26	8000	700	25FEB2025	18/03/2025	21	2025-08-25	FEB2025	11.4286	0.40000	Low_Efficiency
8	8	Cargo King	Bulk Carrier	95000	24	9500	900	02MAR2025	27/03/2025	25	2025-09-02	MAR2025	10.5556	0.10000	Low_Efficiency
9	9	Eastern Pearl	Container	13000	18	4300	380	12APR2025	24/04/2025	12	2025-10-12	APR2025	11.3158	0.33077	Low_Efficiency
10	10	Western Horizon	Oil Tanker	280000	27	10000	1250	01MAY2025	20/05/2025	19	2025-11-01	MAY2025	8.0000	0.03571	Low_Efficiency
11	11	Blue Dolphin	LNG Carrier	135000	29	8200	770	15MAR2025	05/04/2025	21	2025-09-15	MAR2025	10.6494	0.06074	Low_Efficiency
12	12	Red Mariner	Bulk Carrier	90000	23	7600	820	08MAY2025	30/05/2025	22	2025-11-08	MAY2025	9.2683	0.08444	Low_Efficiency
13	13	Silver Wave	Research Vessel	7500	38	4200	310	28FEB2025	20/03/2025	20	2025-08-28	FEB2025	13.5484	0.56000	Low_Efficiency
14	14	Golden Anchor	Car Carrier	22000	25	6700	560	10APR2025	30/04/2025	20	2025-10-10	APR2025	11.9643	0.30455	Low_Efficiency

/* Quick check */

proc freq data=work.ships_fe_grouped;

    tables Fuel_Group*Type / nocum nopercent;

    title "Fuel Efficiency Group by Ship Type";

run;

OUTPUT:

Fuel Efficiency Group by Ship Type

The FREQ Procedure

Frequency Row Pct Col Pct

Table of Fuel_Group by Type Fuel_Group Type Bulk Carrier Car Carrier Container LNG Carrier Oil Tanker Research Vessel Total Low_Efficiency 3 21.43 100.00 2 14.29 100.00 3 21.43 100.00 2 14.29 100.00 2 14.29 100.00 2 14.29 100.00 14     Total 3 2 3 2 2 2 14

STEP 5: PROC MEANS – SUMMARY STATISTICS

proc means data=work.ships_fe_grouped mean std min max maxdec=2;

    class Type Fuel_Group;

    var Cargo_Capacity Crew_Size Distance_Travelled Fuel_Consumption Fuel_Efficiency;

    title "Descriptive Statistics by Ship Type and Fuel Efficiency Group";

run;

OUTPUT:

Descriptive Statistics by Ship Type and Fuel Efficiency Group

The MEANS Procedure

Type	Fuel_Group	N Obs	Variable	Label	Mean	Std Dev	Minimum	Maximum
Bulk Carrier	Low_Efficiency	3	Cargo_Capacity Crew_Size Distance_Travelled Fuel_Consumption Fuel_Efficiency	Nm per Ton of Fuel	90000.00 23.00 8033.33 790.00 10.20	5000.00 1.00 1305.12 127.67 0.81	85000.00 22.00 7000.00 650.00 9.27	95000.00 24.00 9500.00 900.00 10.77
Car Carrier	Low_Efficiency	2	Cargo_Capacity Crew_Size Distance_Travelled Fuel_Consumption Fuel_Efficiency	Nm per Ton of Fuel	21000.00 25.50 7350.00 630.00 11.70	1414.21 0.71 919.24 98.99 0.38	20000.00 25.00 6700.00 560.00 11.43	22000.00 26.00 8000.00 700.00 11.96
Container	Low_Efficiency	3	Cargo_Capacity Crew_Size Distance_Travelled Fuel_Consumption Fuel_Efficiency	Nm per Ton of Fuel	15333.33 20.67 7100.00 583.33 11.95	2516.61 3.06 4257.93 318.17 0.66	13000.00 18.00 4300.00 380.00 11.32	18000.00 24.00 12000.00 950.00 12.63
LNG Carrier	Low_Efficiency	2	Cargo_Capacity Crew_Size Distance_Travelled Fuel_Consumption Fuel_Efficiency	Nm per Ton of Fuel	137500.00 29.50 9600.00 875.00 10.94	3535.53 0.71 1979.90 148.49 0.41	135000.00 29.00 8200.00 770.00 10.65	140000.00 30.00 11000.00 980.00 11.22
Oil Tanker	Low_Efficiency	2	Cargo_Capacity Crew_Size Distance_Travelled Fuel_Consumption Fuel_Efficiency	Nm per Ton of Fuel	290000.00 27.50 9500.00 1175.00 8.09	14142.14 0.71 707.11 106.07 0.13	280000.00 27.00 9000.00 1100.00 8.00	300000.00 28.00 10000.00 1250.00 8.18
Research Vessel	Low_Efficiency	2	Cargo_Capacity Crew_Size Distance_Travelled Fuel_Consumption Fuel_Efficiency	Nm per Ton of Fuel	7750.00 39.00 3850.00 285.00 13.50	353.55 1.41 494.97 35.36 0.06	7500.00 38.00 3500.00 260.00 13.46	8000.00 40.00 4200.00 310.00 13.55

STEP 6: PROC CORR – RELATIONSHIPS BETWEEN NUMERIC VARIABLES

proc corr data=work.ships_fe_grouped plots=none;

    var Cargo_Capacity Crew_Size Distance_Travelled Fuel_Consumption Fuel_Efficiency;

    title "Correlation between Capacity, Distance, Fuel, Crew, and Fuel Efficiency";

run;

OUTPUT:

Correlation between Capacity, Distance, Fuel, Crew, and Fuel Efficiency

The CORR Procedure

5 Variables:	Cargo_Capacity Crew_Size Distance_Travelled Fuel_Consumption Fuel_Efficiency

Simple Statistics
Variable	N	Mean	Std Dev	Sum	Minimum	Maximum	Label
Cargo_Capacity	14	87750	97860	1228500	7500	300000
Crew_Size	14	26.71429	6.18221	374.00000	18.00000	40.00000
Distance_Travelled	14	7571	2630	106000	3500	12000
Fuel_Consumption	14	717.85714	304.68593	10050	260.00000	1250
Fuel_Efficiency	14	11.06455	1.69964	154.90365	8.00000	13.54839	Nm per Ton of Fuel

Pearson Correlation Coefficients, N = 14 Prob > |r| under H0: Rho=0
	Cargo_Capacity	Crew_Size	Distance_Travelled	Fuel_Consumption	Fuel_Efficiency
Cargo_Capacity	1.00000	0.01494 0.9596	0.52020 0.0565	0.80718 0.0005	-0.87072 <.0001
Crew_Size	0.01494 0.9596	1.00000	-0.21104 0.4689	-0.20495 0.4821	0.36919 0.1939
Distance_Travelled	0.52020 0.0565	-0.21104 0.4689	1.00000	0.90520 <.0001	-0.46355 0.0950
Fuel_Consumption	0.80718 0.0005	-0.20495 0.4821	0.90520 <.0001	1.00000	-0.77222 0.0012
Fuel_Efficiency Nm per Ton of Fuel	-0.87072 <.0001	0.36919 0.1939	-0.46355 0.0950	-0.77222 0.0012	1.00000

STEP 7: PROC UNIVARIATE – DISTRIBUTION OF FUEL EFFICIENCY

proc univariate data=work.ships_fe_grouped normal;

    var Fuel_Efficiency;

    histogram Fuel_Efficiency;

    qqplot Fuel_Efficiency / normal(mu=est sigma=est);

    title "Distribution of Fuel Efficiency Across Ships";

run;

OUTPUT:

Distribution of Fuel Efficiency Across Ships

The UNIVARIATE Procedure

Variable: Fuel_Efficiency (Nm per Ton of Fuel)

Moments
N	14	Sum Weights	14
Mean	11.0645465	Sum Observations	154.903651
Std Deviation	1.69964031	Variance	2.88877717
Skewness	-0.4464886	Kurtosis	-0.2326239
Uncorrected SS	1751.49275	Corrected SS	37.5541032
Coeff Variation	15.3611385	Std Error Mean	0.45424798

Basic Statistical Measures
Location		Variability
Mean	11.06455	Std Deviation	1.69964
Median	11.27014	Variance	2.88878
Mode	.	Range	5.54839
		Interquartile Range	1.40873

Tests for Location: Mu0=0
Test	Statistic		p Value
Student's t	t	24.35794	Pr > |t|	<.0001
Sign	M	7	Pr >= |M|	0.0001
Signed Rank	S	52.5	Pr >= |S|	0.0001

Tests for Normality
Test	Statistic		p Value
Shapiro-Wilk	W	0.943332	Pr < W	0.4627
Kolmogorov-Smirnov	D	0.168005	Pr > D	>0.1500
Cramer-von Mises	W-Sq	0.047555	Pr > W-Sq	>0.2500
Anderson-Darling	A-Sq	0.317834	Pr > A-Sq	>0.2500

Quantiles (Definition 5)
Level	Quantile
100% Max	13.54839
99%	13.54839
95%	13.54839
90%	13.46154
75% Q3	11.96429
50% Median	11.27014
25% Q1	10.55556
10%	8.18182
5%	8.00000
1%	8.00000
0% Min	8.00000

Extreme Observations
Lowest		Highest
Value	Obs	Value	Obs
8.00000	10	11.9048	4
8.18182	2	11.9643	14
9.26829	12	12.6316	1
10.55556	8	13.4615	6
10.64935	11	13.5484	13

Distribution of Fuel Efficiency Across Ships

The UNIVARIATE Procedure

Distribution of Fuel Efficiency Across Ships

The UNIVARIATE Procedure

STEP 8: PROC SGPLOT – VISUALIZATIONS

/* Scatter plot – Distance vs Fuel Consumption by Type */

proc sgplot data=work.ships_fe_grouped;

    scatter x=Distance_Travelled y=Fuel_Consumption / group=Type;

    xaxis label="Distance Travelled (Nautical Miles)";

    yaxis label="Fuel Consumption (Metric Tons)";

    title "Scatter Plot of Distance vs Fuel Consumption by Ship Type";

run;

OUTPUT:

/* Bar chart – Average Fuel Efficiency by Type and Group */

proc sgplot data=work.ships_fe_grouped;

    vbar Type / response=Fuel_Efficiency stat=mean

                group=Fuel_Group groupdisplay=cluster;

    yaxis label="Mean Fuel Efficiency (Nm per Ton of Fuel)";

    title "Mean Fuel Efficiency by Ship Type and Fuel Group";

run;

OUTPUT:

To Visit My Previous Different Tablets Market Analysis Dataset:Click Here

To Visit My Previous Self Description Analysis Dataset:Click Here

To Visit My Previous Birds Of India Dataset:Click Here

To Visit My Previous Ganesh Mandaps In India Dataset:Click Here  

Follow Us On : 

 

--- FOLLOW OUR BLOG FOR MORE INFORMATION.

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