365.WORLD FAMOUS BRIDGES DATA ANALYSIS USING PROC SQL | PROC MEANS | PROC UNIVARIATE | PROC SGPLOT | MACROS | DATE FUNCTIONS | MERGE | APPEND | TRANSPOSE

WORLD FAMOUS BRIDGES DATA ANALYSIS USING PROC SQL | PROC MEANS | PROC UNIVARIATE | PROC SGPLOT | MACROS | DATE FUNCTIONS | MERGE | APPEND | TRANSPOSE

 options nocenter;

1.Creating the Bridges Master Dataset

data bridges_raw;

    length Bridge_Name $25 Country $15;

    format Year_Built date9.;

    infile datalines dlm='|' dsd;

    input Bridge_Name $ Country $ Length Height Year_Built : date9. Traffic_Level;

datalines;

Brooklyn Bridge|USA|1825|84|24MAY1883|130000

Golden Gate|USA|2737|227|27MAY1937|95000

Sydney Harbour|Australia|1149|134|19MAR1932|160000

Tower Bridge|UK|244|65|30JUN1894|40000

Millau Viaduct|France|2460|343|16DEC2004|27000

Akashi Kaikyo|Japan|3911|298|05APR1998|23000

Vasco Da Gama|Portugal|12345|155|29MAR1998|200000

Danyang Kunshan|China|164800|100|30JUN2011|300000

Howrah Bridge|India|705|82|03FEB1943|100000

Helix Bridge|Singapore|280|30|24APR2010|5000

George Washington|USA|1450|184|24OCT1931|290000

Third Mainland|Nigeria|11700|30|23AUG1990|140000

Rialto Bridge|Italy|48|7|01JAN1591|60000

Hangzhou Bay|China|35600|60|01MAY2008|180000

;

run;

proc print data=bridges_raw;

run;

OUTPUT:

Obs	Bridge_Name	Country	Year_Built	Length	Height	Traffic_Level
1	Brooklyn Bridge	USA	24MAY1883	1825	84	130000
2	Golden Gate	USA	27MAY1937	2737	227	95000
3	Sydney Harbour	Australia	19MAR1932	1149	134	160000
4	Tower Bridge	UK	30JUN1894	244	65	40000
5	Millau Viaduct	France	16DEC2004	2460	343	27000
6	Akashi Kaikyo	Japan	05APR1998	3911	298	23000
7	Vasco Da Gama	Portugal	29MAR1998	12345	155	200000
8	Danyang Kunshan	China	30JUN2011	164800	100	300000
9	Howrah Bridge	India	03FEB1943	705	82	100000
10	Helix Bridge	Singapore	24APR2010	280	30	5000
11	George Washington	USA	24OCT1931	1450	184	290000
12	Third Mainland	Nigeria	23AUG1990	11700	30	140000
13	Rialto Bridge	Italy	01JAN1591	48	7	60000
14	Hangzhou Bay	China	01MAY2008	35600	60	180000

2.Applying Date Functions (MDY, INTNX, INTCK)

data bridges_dates;

    set bridges_raw;

    Built_Year = year(Year_Built);

    Inspection_Date = intnx('year', Year_Built, 10, 'same');

    Bridge_Age = intck('year', Year_Built, today());

    format Inspection_Date date9.;

run;

proc print data=bridges_dates;

run;

OUTPUT:

Obs	Bridge_Name	Country	Year_Built	Length	Height	Traffic_Level	Built_Year	Inspection_Date	Bridge_Age
1	Brooklyn Bridge	USA	24MAY1883	1825	84	130000	1883	24MAY1893	143
2	Golden Gate	USA	27MAY1937	2737	227	95000	1937	27MAY1947	89
3	Sydney Harbour	Australia	19MAR1932	1149	134	160000	1932	19MAR1942	94
4	Tower Bridge	UK	30JUN1894	244	65	40000	1894	30JUN1904	132
5	Millau Viaduct	France	16DEC2004	2460	343	27000	2004	16DEC2014	22
6	Akashi Kaikyo	Japan	05APR1998	3911	298	23000	1998	05APR2008	28
7	Vasco Da Gama	Portugal	29MAR1998	12345	155	200000	1998	29MAR2008	28
8	Danyang Kunshan	China	30JUN2011	164800	100	300000	2011	30JUN2021	15
9	Howrah Bridge	India	03FEB1943	705	82	100000	1943	03FEB1953	83
10	Helix Bridge	Singapore	24APR2010	280	30	5000	2010	24APR2020	16
11	George Washington	USA	24OCT1931	1450	184	290000	1931	24OCT1941	95
12	Third Mainland	Nigeria	23AUG1990	11700	30	140000	1990	23AUG2000	36
13	Rialto Bridge	Italy	01JAN1591	48	7	60000	1591	01JAN1601	435
14	Hangzhou Bay	China	01MAY2008	35600	60	180000	2008	01MAY2018	18

3.Structural Classification Using MACRO

%macro structure_cat;

data bridges_struct;

    length Structure_Type $15.;

    set bridges_dates;

    if Length > 10000 then Structure_Type="Mega Bridge";

    else if Length > 3000 then Structure_Type="Long Span";

    else Structure_Type="Standard Bridge";

run;

proc print data=bridges_struct;

run;

%mend;

%structure_cat;

OUTPUT:

Obs	Structure_Type	Bridge_Name	Country	Year_Built	Length	Height	Traffic_Level	Built_Year	Inspection_Date	Bridge_Age
1	Standard Bridge	Brooklyn Bridge	USA	24MAY1883	1825	84	130000	1883	24MAY1893	143
2	Standard Bridge	Golden Gate	USA	27MAY1937	2737	227	95000	1937	27MAY1947	89
3	Standard Bridge	Sydney Harbour	Australia	19MAR1932	1149	134	160000	1932	19MAR1942	94
4	Standard Bridge	Tower Bridge	UK	30JUN1894	244	65	40000	1894	30JUN1904	132
5	Standard Bridge	Millau Viaduct	France	16DEC2004	2460	343	27000	2004	16DEC2014	22
6	Long Span	Akashi Kaikyo	Japan	05APR1998	3911	298	23000	1998	05APR2008	28
7	Mega Bridge	Vasco Da Gama	Portugal	29MAR1998	12345	155	200000	1998	29MAR2008	28
8	Mega Bridge	Danyang Kunshan	China	30JUN2011	164800	100	300000	2011	30JUN2021	15
9	Standard Bridge	Howrah Bridge	India	03FEB1943	705	82	100000	1943	03FEB1953	83
10	Standard Bridge	Helix Bridge	Singapore	24APR2010	280	30	5000	2010	24APR2020	16
11	Standard Bridge	George Washington	USA	24OCT1931	1450	184	290000	1931	24OCT1941	95
12	Mega Bridge	Third Mainland	Nigeria	23AUG1990	11700	30	140000	1990	23AUG2000	36
13	Standard Bridge	Rialto Bridge	Italy	01JAN1591	48	7	60000	1591	01JAN1601	435
14	Mega Bridge	Hangzhou Bay	China	01MAY2008	35600	60	180000	2008	01MAY2018	18

4.Creating Traffic Category Using Macro

%macro traffic_cat;

data bridges_traffic;

    set bridges_struct;

    if Traffic_Level > 200000 then Traffic_Class="Very Heavy";

    else if Traffic_Level > 100000 then Traffic_Class="Heavy";

    else Traffic_Class="Moderate";

run;

proc print data=bridges_traffic;

run;

%mend;

%traffic_cat;

OUTPUT:

Obs	Structure_Type	Bridge_Name	Country	Year_Built	Length	Height	Traffic_Level	Built_Year	Inspection_Date	Bridge_Age	Traffic_Class
1	Standard Bridge	Brooklyn Bridge	USA	24MAY1883	1825	84	130000	1883	24MAY1893	143	Heavy
2	Standard Bridge	Golden Gate	USA	27MAY1937	2737	227	95000	1937	27MAY1947	89	Moderate
3	Standard Bridge	Sydney Harbour	Australia	19MAR1932	1149	134	160000	1932	19MAR1942	94	Heavy
4	Standard Bridge	Tower Bridge	UK	30JUN1894	244	65	40000	1894	30JUN1904	132	Moderate
5	Standard Bridge	Millau Viaduct	France	16DEC2004	2460	343	27000	2004	16DEC2014	22	Moderate
6	Long Span	Akashi Kaikyo	Japan	05APR1998	3911	298	23000	1998	05APR2008	28	Moderate
7	Mega Bridge	Vasco Da Gama	Portugal	29MAR1998	12345	155	200000	1998	29MAR2008	28	Heavy
8	Mega Bridge	Danyang Kunshan	China	30JUN2011	164800	100	300000	2011	30JUN2021	15	Very Heavy
9	Standard Bridge	Howrah Bridge	India	03FEB1943	705	82	100000	1943	03FEB1953	83	Moderate
10	Standard Bridge	Helix Bridge	Singapore	24APR2010	280	30	5000	2010	24APR2020	16	Moderate
11	Standard Bridge	George Washington	USA	24OCT1931	1450	184	290000	1931	24OCT1941	95	Very Heavy
12	Mega Bridge	Third Mainland	Nigeria	23AUG1990	11700	30	140000	1990	23AUG2000	36	Heavy
13	Standard Bridge	Rialto Bridge	Italy	01JAN1591	48	7	60000	1591	01JAN1601	435	Moderate
14	Mega Bridge	Hangzhou Bay	China	01MAY2008	35600	60	180000	2008	01MAY2018	18	Heavy

5.PROC SQL – Business Queries

proc sql;

    create table high_traffic as

    select Bridge_Name, Country, Traffic_Level

    from bridges_traffic

    where Traffic_Level > 150000;

quit;

proc print data=high_traffic;

run;

OUTPUT:

Obs	Bridge_Name	Country	Traffic_Level
1	Sydney Harbour	Australia	160000
2	Vasco Da Gama	Portugal	200000
3	Danyang Kunshan	China	300000
4	George Washington	USA	290000
5	Hangzhou Bay	China	180000

6.PROC MEANS – Statistical Summary

proc means data=bridges_traffic mean min max std;

    var Length Height Traffic_Level Bridge_Age;

run;

OUTPUT:

The MEANS Procedure

Variable	Mean	Minimum	Maximum	Std Dev
Length Height Traffic_Level Bridge_Age	17089.57 128.5000000 125000.00 88.1428571	48.0000000 7.0000000 5000.00 15.0000000	164800.00 343.0000000 300000.00 435.0000000	43556.48 102.3198229 94200.28 109.2469730

7.PROC UNIVARIATE – Distribution Analysis

proc univariate data=bridges_traffic;

    var Length Traffic_Level;

    histogram;

run;

OUTPUT:

The UNIVARIATE Procedure

Variable: Length

Moments
N	14	Sum Weights	14
Mean	17089.5714	Sum Observations	239254
Std Deviation	43556.4797	Variance	1897166922
Skewness	3.46467169	Kurtosis	12.3698523
Uncorrected SS	2.87519E10	Corrected SS	2.46632E10
Coeff Variation	254.871691	Std Error Mean	11640.9589

Basic Statistical Measures
Location		Variability
Mean	17089.57	Std Deviation	43556
Median	2142.50	Variance	1897166922
Mode	.	Range	164752
		Interquartile Range	10995

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

Quantiles (Definition 5)
Level	Quantile
100% Max	164800.0
99%	164800.0
95%	164800.0
90%	35600.0
75% Q3	11700.0
50% Median	2142.5
25% Q1	705.0
10%	244.0
5%	48.0
1%	48.0
0% Min	48.0

Extreme Observations
Lowest		Highest
Value	Obs	Value	Obs
48	13	3911	6
244	4	11700	12
280	10	12345	7
705	9	35600	14
1149	3	164800	8

The UNIVARIATE Procedure

The UNIVARIATE Procedure

Variable: Traffic_Level

Moments
N	14	Sum Weights	14
Mean	125000	Sum Observations	1750000
Std Deviation	94200.2776	Variance	8873692308
Skewness	0.62530853	Kurtosis	-0.4209584
Uncorrected SS	3.34108E11	Corrected SS	1.15358E11
Coeff Variation	75.3602221	Std Error Mean	25176.0832

Basic Statistical Measures
Location		Variability
Mean	125000.0	Std Deviation	94200
Median	115000.0	Variance	8873692308
Mode	.	Range	295000
		Interquartile Range	140000

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

Quantiles (Definition 5)
Level	Quantile
100% Max	300000
99%	300000
95%	300000
90%	290000
75% Q3	180000
50% Median	115000
25% Q1	40000
10%	23000
5%	5000
1%	5000
0% Min	5000

Extreme Observations
Lowest		Highest
Value	Obs	Value	Obs
5000	10	160000	3
23000	6	180000	14
27000	5	200000	7
40000	4	290000	11
60000	13	300000	8

The UNIVARIATE Procedure

8.PROC SGPLOT – Visual Analytics

proc sgplot data=bridges_traffic;

    vbar Country / response=Traffic_Level stat=mean;

run;

OUTPUT:

proc sgplot data=bridges_traffic;

    scatter x=Length y=Traffic_Level;

run;

OUTPUT:

9.Using PROC TRANSPOSE

proc transpose data=bridges_traffic out=bridges_transposed;

    var Length Height Traffic_Level;

    id Bridge_Name;

run;

proc print data=bridges_transposed;

run;

OUTPUT:

Obs	_NAME_	Brooklyn Bridge	Golden Gate	Sydney Harbour	Tower Bridge	Millau Viaduct	Akashi Kaikyo	Vasco Da Gama	Danyang Kunshan	Howrah Bridge	Helix Bridge	George Washington	Third Mainland	Rialto Bridge	Hangzhou Bay
1	Length	1825	2737	1149	244	2460	3911	12345	164800	705	280	1450	11700	48	35600
2	Height	84	227	134	65	343	298	155	100	82	30	184	30	7	60
3	Traffic_Level	130000	95000	160000	40000	27000	23000	200000	300000	100000	5000	290000	140000	60000	180000

10.Using PROC APPEND

data new_bridge;

    format Year_Built date9.;

    Bridge_Name="ChenabRail";

    Country="India";

    Length=1315;

    Height=359;

    Year_Built='13AUG2022'd;

    Traffic_Level=40000;

run;

proc print data=new_bridge;

run;

OUTPUT:

Obs	Year_Built	Bridge_Name	Country	Length	Height	Traffic_Level
1	13AUG2022	ChenabRail	India	1315	359	40000

proc append base=bridges_raw 

            data=new_bridge force;

run;

proc print data=bridges_raw;

run;

OUTPUT:

Obs	Bridge_Name	Country	Year_Built	Length	Height	Traffic_Level
1	Brooklyn Bridge	USA	24MAY1883	1825	84	130000
2	Golden Gate	USA	27MAY1937	2737	227	95000
3	Sydney Harbour	Australia	19MAR1932	1149	134	160000
4	Tower Bridge	UK	30JUN1894	244	65	40000
5	Millau Viaduct	France	16DEC2004	2460	343	27000
6	Akashi Kaikyo	Japan	05APR1998	3911	298	23000
7	Vasco Da Gama	Portugal	29MAR1998	12345	155	200000
8	Danyang Kunshan	China	30JUN2011	164800	100	300000
9	Howrah Bridge	India	03FEB1943	705	82	100000
10	Helix Bridge	Singapore	24APR2010	280	30	5000
11	George Washington	USA	24OCT1931	1450	184	290000
12	Third Mainland	Nigeria	23AUG1990	11700	30	140000
13	Rialto Bridge	Italy	01JAN1591	48	7	60000
14	Hangzhou Bay	China	01MAY2008	35600	60	180000
15	ChenabRail	India	13AUG2022	1315	359	40000

11.Using SET Statement

data bridges_combined;

    set bridges_traffic 

        high_traffic;

run;

proc print data=bridges_combined;

run;

OUTPUT:

Obs	Structure_Type	Bridge_Name	Country	Year_Built	Length	Height	Traffic_Level	Built_Year	Inspection_Date	Bridge_Age	Traffic_Class
1	Standard Bridge	Brooklyn Bridge	USA	24MAY1883	1825	84	130000	1883	24MAY1893	143	Heavy
2	Standard Bridge	Golden Gate	USA	27MAY1937	2737	227	95000	1937	27MAY1947	89	Moderate
3	Standard Bridge	Sydney Harbour	Australia	19MAR1932	1149	134	160000	1932	19MAR1942	94	Heavy
4	Standard Bridge	Tower Bridge	UK	30JUN1894	244	65	40000	1894	30JUN1904	132	Moderate
5	Standard Bridge	Millau Viaduct	France	16DEC2004	2460	343	27000	2004	16DEC2014	22	Moderate
6	Long Span	Akashi Kaikyo	Japan	05APR1998	3911	298	23000	1998	05APR2008	28	Moderate
7	Mega Bridge	Vasco Da Gama	Portugal	29MAR1998	12345	155	200000	1998	29MAR2008	28	Heavy
8	Mega Bridge	Danyang Kunshan	China	30JUN2011	164800	100	300000	2011	30JUN2021	15	Very Heavy
9	Standard Bridge	Howrah Bridge	India	03FEB1943	705	82	100000	1943	03FEB1953	83	Moderate
10	Standard Bridge	Helix Bridge	Singapore	24APR2010	280	30	5000	2010	24APR2020	16	Moderate
11	Standard Bridge	George Washington	USA	24OCT1931	1450	184	290000	1931	24OCT1941	95	Very Heavy
12	Mega Bridge	Third Mainland	Nigeria	23AUG1990	11700	30	140000	1990	23AUG2000	36	Heavy
13	Standard Bridge	Rialto Bridge	Italy	01JAN1591	48	7	60000	1591	01JAN1601	435	Moderate
14	Mega Bridge	Hangzhou Bay	China	01MAY2008	35600	60	180000	2008	01MAY2018	18	Heavy
15		Sydney Harbour	Australia	.	.	.	160000	.	.	.
16		Vasco Da Gama	Portugal	.	.	.	200000	.	.	.
17		Danyang Kunshan	China	.	.	.	300000	.	.	.
18		George Washington	USA	.	.	.	290000	.	.	.
19		Hangzhou Bay	China	.	.	.	180000	.	.	.

12.Using MERGE

proc sort data=bridges_traffic; by Bridge_Name; run;

proc print data=bridges_traffic;

run;

OUTPUT:

Obs	Structure_Type	Bridge_Name	Country	Year_Built	Length	Height	Traffic_Level	Built_Year	Inspection_Date	Bridge_Age	Traffic_Class
1	Long Span	Akashi Kaikyo	Japan	05APR1998	3911	298	23000	1998	05APR2008	28	Moderate
2	Standard Bridge	Brooklyn Bridge	USA	24MAY1883	1825	84	130000	1883	24MAY1893	143	Heavy
3	Mega Bridge	Danyang Kunshan	China	30JUN2011	164800	100	300000	2011	30JUN2021	15	Very Heavy
4	Standard Bridge	George Washington	USA	24OCT1931	1450	184	290000	1931	24OCT1941	95	Very Heavy
5	Standard Bridge	Golden Gate	USA	27MAY1937	2737	227	95000	1937	27MAY1947	89	Moderate
6	Mega Bridge	Hangzhou Bay	China	01MAY2008	35600	60	180000	2008	01MAY2018	18	Heavy
7	Standard Bridge	Helix Bridge	Singapore	24APR2010	280	30	5000	2010	24APR2020	16	Moderate
8	Standard Bridge	Howrah Bridge	India	03FEB1943	705	82	100000	1943	03FEB1953	83	Moderate
9	Standard Bridge	Millau Viaduct	France	16DEC2004	2460	343	27000	2004	16DEC2014	22	Moderate
10	Standard Bridge	Rialto Bridge	Italy	01JAN1591	48	7	60000	1591	01JAN1601	435	Moderate
11	Standard Bridge	Sydney Harbour	Australia	19MAR1932	1149	134	160000	1932	19MAR1942	94	Heavy
12	Mega Bridge	Third Mainland	Nigeria	23AUG1990	11700	30	140000	1990	23AUG2000	36	Heavy
13	Standard Bridge	Tower Bridge	UK	30JUN1894	244	65	40000	1894	30JUN1904	132	Moderate
14	Mega Bridge	Vasco Da Gama	Portugal	29MAR1998	12345	155	200000	1998	29MAR2008	28	Heavy

proc sort data=high_traffic; by Bridge_Name; run;

proc print data=high_traffic;

run;

OUTPUT:

Obs	Bridge_Name	Country	Traffic_Level
1	Danyang Kunshan	China	300000
2	George Washington	USA	290000
3	Hangzhou Bay	China	180000
4	Sydney Harbour	Australia	160000
5	Vasco Da Gama	Portugal	200000

data bridges_merge;

    merge bridges_traffic(in=a) high_traffic(in=b);

    by Bridge_Name;

    if a;

run;

proc print data=bridges_merge;

run;

OUTPUT:

Obs	Structure_Type	Bridge_Name	Country	Year_Built	Length	Height	Traffic_Level	Built_Year	Inspection_Date	Bridge_Age	Traffic_Class
1	Long Span	Akashi Kaikyo	Japan	05APR1998	3911	298	23000	1998	05APR2008	28	Moderate
2	Standard Bridge	Brooklyn Bridge	USA	24MAY1883	1825	84	130000	1883	24MAY1893	143	Heavy
3	Mega Bridge	Danyang Kunshan	China	30JUN2011	164800	100	300000	2011	30JUN2021	15	Very Heavy
4	Standard Bridge	George Washington	USA	24OCT1931	1450	184	290000	1931	24OCT1941	95	Very Heavy
5	Standard Bridge	Golden Gate	USA	27MAY1937	2737	227	95000	1937	27MAY1947	89	Moderate
6	Mega Bridge	Hangzhou Bay	China	01MAY2008	35600	60	180000	2008	01MAY2018	18	Heavy
7	Standard Bridge	Helix Bridge	Singapore	24APR2010	280	30	5000	2010	24APR2020	16	Moderate
8	Standard Bridge	Howrah Bridge	India	03FEB1943	705	82	100000	1943	03FEB1953	83	Moderate
9	Standard Bridge	Millau Viaduct	France	16DEC2004	2460	343	27000	2004	16DEC2014	22	Moderate
10	Standard Bridge	Rialto Bridge	Italy	01JAN1591	48	7	60000	1591	01JAN1601	435	Moderate
11	Standard Bridge	Sydney Harbour	Australia	19MAR1932	1149	134	160000	1932	19MAR1942	94	Heavy
12	Mega Bridge	Third Mainland	Nigeria	23AUG1990	11700	30	140000	1990	23AUG2000	36	Heavy
13	Standard Bridge	Tower Bridge	UK	30JUN1894	244	65	40000	1894	30JUN1904	132	Moderate
14	Mega Bridge	Vasco Da Gama	Portugal	29MAR1998	12345	155	200000	1998	29MAR2008	28	Heavy

13.Advanced PROC SQL – Engineering Insights

proc sql;

    select Country,

           avg(Length) as Avg_Length,

           avg(Traffic_Level) as Avg_Traffic

    from bridges_merge

    group by Country;

quit;

OUTPUT:

Country	Avg_Length	Avg_Traffic
Australia	1149	160000
China	100200	240000
France	2460	27000
India	705	100000
Italy	48	60000
Japan	3911	23000
Nigeria	11700	140000
Portugal	12345	200000
Singapore	280	5000
UK	244	40000
USA	2004	171666.7

To Visit My Previous Software Company Analysis Dataset:Click Here

To Visit My Previous Vote Program Dataset:Click Here

To Visit My Previous Audi Cars Performance Analysis Dataset:Click Here

To Visit My Previous Global Clothing Trends Dataset:Click Here  

Follow Us On : 

 

--->FOLLOW OUR BLOG FOR MORE INFORMATION.
--->PLEASE DO COMMENTS AND SHARE OUR BLOG.