361.RIVER TRADE AND POLLUTION ANALYSIS USING SAS DATA STEP | PROC SQL | PROC MEANS | PROC REG | PROC SGPLOT | PROC COPY | APPEND | MERGE | SET | TRANSPOSE | MACROS | DATE FUNCTIONS (MDY-INTNX-INTCK)

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

option nocenter;

1.RIVERS BASE DATASET CREATION USING DATA STEP

data rivers_base;

    length River_Name $20;

    format Survey_Date date9.;

    input River_Name $ Length Countries_Crossed Pollution_Level Trade_Importance

           Survey_Date :date9.;

    datalines;

Ganges 2525 2 78 9 01JAN2022

Nile 6650 11 65 10 15FEB2022

Amazon 6400 7 45 8 10MAR2022

Yangtze 6300 1 72 9 05APR2022

Mississippi 3730 1 60 8 20MAY2022

Danube 2860 10 55 7 18JUN2022

Volga 3530 1 50 6 30JUL2022

Rhine 1230 6 68 8 15AUG2022

Mekong 4350 6 70 7 01SEP2022

Indus 3180 4 62 8 10OCT2022

Tigris 1850 3 75 6 15NOV2022

Euphrates 2800 4 73 7 20DEC2022

;

run;

proc print data=rivers_base;

run;

OUTPUT:

Obs	River_Name	Survey_Date	Length	Countries_Crossed	Pollution_Level	Trade_Importance
1	Ganges	01JAN2022	2525	2	78	9
2	Nile	15FEB2022	6650	11	65	10
3	Amazon	10MAR2022	6400	7	45	8
4	Yangtze	05APR2022	6300	1	72	9
5	Mississippi	20MAY2022	3730	1	60	8
6	Danube	18JUN2022	2860	10	55	7
7	Volga	30JUL2022	3530	1	50	6
8	Rhine	15AUG2022	1230	6	68	8
9	Mekong	01SEP2022	4350	6	70	7
10	Indus	10OCT2022	3180	4	62	8
11	Tigris	15NOV2022	1850	3	75	6
12	Euphrates	20DEC2022	2800	4	73	7

2.DATE DERIVATIONS USING INTNX, INTCK, MDY

data rivers_dates;

    set rivers_base;

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

    Years_Since_Survey = intck('year', Survey_Date, '01JAN2025'd);

    Review_Year_Start = mdy(1,1,year(Survey_Date));

    format Next_Review_Date Review_Year_Start date9.;

run;

proc print data=rivers_dates;

run;

OUTPUT:

Obs	River_Name	Survey_Date	Length	Countries_Crossed	Pollution_Level	Trade_Importance	Next_Review_Date	Years_Since_Survey	Review_Year_Start
1	Ganges	01JAN2022	2525	2	78	9	01JUL2022	3	01JAN2022
2	Nile	15FEB2022	6650	11	65	10	15AUG2022	3	01JAN2022
3	Amazon	10MAR2022	6400	7	45	8	10SEP2022	3	01JAN2022
4	Yangtze	05APR2022	6300	1	72	9	05OCT2022	3	01JAN2022
5	Mississippi	20MAY2022	3730	1	60	8	20NOV2022	3	01JAN2022
6	Danube	18JUN2022	2860	10	55	7	18DEC2022	3	01JAN2022
7	Volga	30JUL2022	3530	1	50	6	30JAN2023	3	01JAN2022
8	Rhine	15AUG2022	1230	6	68	8	15FEB2023	3	01JAN2022
9	Mekong	01SEP2022	4350	6	70	7	01MAR2023	3	01JAN2022
10	Indus	10OCT2022	3180	4	62	8	10APR2023	3	01JAN2022
11	Tigris	15NOV2022	1850	3	75	6	15MAY2023	3	01JAN2022
12	Euphrates	20DEC2022	2800	4	73	7	20JUN2023	3	01JAN2022

3.SUMMARY STATISTICS USING PROC MEANS

proc means data=rivers_dates mean min max std;

    var Length Pollution_Level Trade_Importance Countries_Crossed;

run;

OUTPUT:

The MEANS Procedure

Variable	Mean	Minimum	Maximum	Std Dev
Length Pollution_Level Trade_Importance Countries_Crossed	3783.75 64.4166667 7.7500000 4.6666667	1230.00 45.0000000 6.0000000 1.0000000	6650.00 78.0000000 10.0000000 11.0000000	1804.30 10.3173141 1.2154311 3.4200833

4.ADVANCED QUERYING USING PROC SQL

proc sql;

    create table high_trade_rivers as

    select River_Name,Length,Pollution_Level,Trade_Importance

    from rivers_dates

    where Trade_Importance >= 8

    order by Pollution_Level desc;

quit;

proc print data=high_trade_rivers;

run;

OUTPUT:

Obs	River_Name	Length	Pollution_Level	Trade_Importance
1	Ganges	2525	78	9
2	Yangtze	6300	72	9
3	Rhine	1230	68	8
4	Nile	6650	65	10
5	Indus	3180	62	8
6	Mississippi	3730	60	8
7	Amazon	6400	45	8

5.REGRESSION ANALYSIS – POLLUTION VS TRADE

proc reg data=rivers_dates;

    model Pollution_Level = Trade_Importance Length Countries_Crossed;

run;

quit;

OUTPUT:

The REG Procedure

Model: MODEL1

Dependent Variable: Pollution_Level

Number of Observations Read	12
Number of Observations Used	12

Analysis of Variance
Source	DF	Sum of Squares	Mean Square	F Value	Pr > F
Model	3	401.18558	133.72853	1.39	0.3146
Error	8	769.73108	96.21639
Corrected Total	11	1170.91667

Root MSE	9.80900	R-Square	0.3426
Dependent Mean	64.41667	Adj R-Sq	0.0961
Coeff Var	15.22742

Parameter Estimates
Variable	DF	Parameter Estimate	Standard Error	t Value	Pr > |t|
Intercept	1	44.72367	19.56834	2.29	0.0516
Trade_Importance	1	4.53388	2.89065	1.57	0.1554
Length	1	-0.00317	0.00195	-1.63	0.1416
Countries_Crossed	1	-0.73622	0.89928	-0.82	0.4367

The REG Procedure

Model: MODEL1

Dependent Variable: Pollution_Level

6.VISUALIZATION USING PROC SGPLOT

proc sgplot data=rivers_dates;

    scatter x=Trade_Importance y=Pollution_Level;

    reg x=Trade_Importance y=Pollution_Level;

    title "Pollution vs Trade Importance of Rivers";

run;

OUTPUT:

7.MACRO FOR RIVER RISK RATING

%macro river_rating;

data rivers_rated;

    set rivers_dates;

    if Pollution_Level >= 70 and Trade_Importance >= 8 then Risk_Category = "CRITICAL";

    else if Pollution_Level >= 60 then Risk_Category = "HIGH";

    else if Pollution_Level >= 50 then Risk_Category = "MODERATE";

    else Risk_Category = "LOW";

run;

proc print data=rivers_rated;

run;

%mend;

%river_rating;

OUTPUT:

Obs	River_Name	Survey_Date	Length	Countries_Crossed	Pollution_Level	Trade_Importance	Next_Review_Date	Years_Since_Survey	Review_Year_Start	Risk_Category
1	Ganges	01JAN2022	2525	2	78	9	01JUL2022	3	01JAN2022	CRITICAL
2	Nile	15FEB2022	6650	11	65	10	15AUG2022	3	01JAN2022	HIGH
3	Amazon	10MAR2022	6400	7	45	8	10SEP2022	3	01JAN2022	LOW
4	Yangtze	05APR2022	6300	1	72	9	05OCT2022	3	01JAN2022	CRITICAL
5	Mississippi	20MAY2022	3730	1	60	8	20NOV2022	3	01JAN2022	HIGH
6	Danube	18JUN2022	2860	10	55	7	18DEC2022	3	01JAN2022	MODERATE
7	Volga	30JUL2022	3530	1	50	6	30JAN2023	3	01JAN2022	MODERATE
8	Rhine	15AUG2022	1230	6	68	8	15FEB2023	3	01JAN2022	HIGH
9	Mekong	01SEP2022	4350	6	70	7	01MAR2023	3	01JAN2022	HIGH
10	Indus	10OCT2022	3180	4	62	8	10APR2023	3	01JAN2022	HIGH
11	Tigris	15NOV2022	1850	3	75	6	15MAY2023	3	01JAN2022	HIGH
12	Euphrates	20DEC2022	2800	4	73	7	20JUN2023	3	01JAN2022	HIGH

8.DATA COMBINATION USING SET

data rivers_set;

    set rivers_base 

        rivers_rated;

run;

proc print data=rivers_set;

run;

OUTPUT:

Obs	River_Name	Survey_Date	Length	Countries_Crossed	Pollution_Level	Trade_Importance	Next_Review_Date	Years_Since_Survey	Review_Year_Start	Risk_Category
1	Ganges	01JAN2022	2525	2	78	9	.	.	.
2	Nile	15FEB2022	6650	11	65	10	.	.	.
3	Amazon	10MAR2022	6400	7	45	8	.	.	.
4	Yangtze	05APR2022	6300	1	72	9	.	.	.
5	Mississippi	20MAY2022	3730	1	60	8	.	.	.
6	Danube	18JUN2022	2860	10	55	7	.	.	.
7	Volga	30JUL2022	3530	1	50	6	.	.	.
8	Rhine	15AUG2022	1230	6	68	8	.	.	.
9	Mekong	01SEP2022	4350	6	70	7	.	.	.
10	Indus	10OCT2022	3180	4	62	8	.	.	.
11	Tigris	15NOV2022	1850	3	75	6	.	.	.
12	Euphrates	20DEC2022	2800	4	73	7	.	.	.
13	Ganges	01JAN2022	2525	2	78	9	01JUL2022	3	01JAN2022	CRITICAL
14	Nile	15FEB2022	6650	11	65	10	15AUG2022	3	01JAN2022	HIGH
15	Amazon	10MAR2022	6400	7	45	8	10SEP2022	3	01JAN2022	LOW
16	Yangtze	05APR2022	6300	1	72	9	05OCT2022	3	01JAN2022	CRITICAL
17	Mississippi	20MAY2022	3730	1	60	8	20NOV2022	3	01JAN2022	HIGH
18	Danube	18JUN2022	2860	10	55	7	18DEC2022	3	01JAN2022	MODERATE
19	Volga	30JUL2022	3530	1	50	6	30JAN2023	3	01JAN2022	MODERATE
20	Rhine	15AUG2022	1230	6	68	8	15FEB2023	3	01JAN2022	HIGH
21	Mekong	01SEP2022	4350	6	70	7	01MAR2023	3	01JAN2022	HIGH
22	Indus	10OCT2022	3180	4	62	8	10APR2023	3	01JAN2022	HIGH
23	Tigris	15NOV2022	1850	3	75	6	15MAY2023	3	01JAN2022	HIGH
24	Euphrates	20DEC2022	2800	4	73	7	20JUN2023	3	01JAN2022	HIGH

9.APPEND OPERATION

proc append base=rivers_base 

            data=high_trade_rivers;

run;

proc print data=rivers_base;

run;

OUTPUT:

Obs	River_Name	Survey_Date	Length	Countries_Crossed	Pollution_Level	Trade_Importance
1	Ganges	01JAN2022	2525	2	78	9
2	Nile	15FEB2022	6650	11	65	10
3	Amazon	10MAR2022	6400	7	45	8
4	Yangtze	05APR2022	6300	1	72	9
5	Mississippi	20MAY2022	3730	1	60	8
6	Danube	18JUN2022	2860	10	55	7
7	Volga	30JUL2022	3530	1	50	6
8	Rhine	15AUG2022	1230	6	68	8
9	Mekong	01SEP2022	4350	6	70	7
10	Indus	10OCT2022	3180	4	62	8
11	Tigris	15NOV2022	1850	3	75	6
12	Euphrates	20DEC2022	2800	4	73	7
13	Ganges	.	2525	.	78	9
14	Yangtze	.	6300	.	72	9
15	Rhine	.	1230	.	68	8
16	Nile	.	6650	.	65	10
17	Indus	.	3180	.	62	8
18	Mississippi	.	3730	.	60	8
19	Amazon	.	6400	.	45	8

10.MERGE OPERATION (SORT REQUIRED)

proc sort data=rivers_base; by River_Name; run;

proc print data=rivers_base;

run;

OUTPUT:

Obs	River_Name	Survey_Date	Length	Countries_Crossed	Pollution_Level	Trade_Importance
1	Amazon	10MAR2022	6400	7	45	8
2	Amazon	.	6400	.	45	8
3	Danube	18JUN2022	2860	10	55	7
4	Euphrates	20DEC2022	2800	4	73	7
5	Ganges	01JAN2022	2525	2	78	9
6	Ganges	.	2525	.	78	9
7	Indus	10OCT2022	3180	4	62	8
8	Indus	.	3180	.	62	8
9	Mekong	01SEP2022	4350	6	70	7
10	Mississippi	20MAY2022	3730	1	60	8
11	Mississippi	.	3730	.	60	8
12	Nile	15FEB2022	6650	11	65	10
13	Nile	.	6650	.	65	10
14	Rhine	15AUG2022	1230	6	68	8
15	Rhine	.	1230	.	68	8
16	Tigris	15NOV2022	1850	3	75	6
17	Volga	30JUL2022	3530	1	50	6
18	Yangtze	05APR2022	6300	1	72	9
19	Yangtze	.	6300	.	72	9

proc sort data=rivers_rated; by River_Name; run;

proc print data=rivers_rated;

run;

OUTPUT:

Obs	River_Name	Survey_Date	Length	Countries_Crossed	Pollution_Level	Trade_Importance	Next_Review_Date	Years_Since_Survey	Review_Year_Start	Risk_Category
1	Amazon	10MAR2022	6400	7	45	8	10SEP2022	3	01JAN2022	LOW
2	Danube	18JUN2022	2860	10	55	7	18DEC2022	3	01JAN2022	MODERATE
3	Euphrates	20DEC2022	2800	4	73	7	20JUN2023	3	01JAN2022	HIGH
4	Ganges	01JAN2022	2525	2	78	9	01JUL2022	3	01JAN2022	CRITICAL
5	Indus	10OCT2022	3180	4	62	8	10APR2023	3	01JAN2022	HIGH
6	Mekong	01SEP2022	4350	6	70	7	01MAR2023	3	01JAN2022	HIGH
7	Mississippi	20MAY2022	3730	1	60	8	20NOV2022	3	01JAN2022	HIGH
8	Nile	15FEB2022	6650	11	65	10	15AUG2022	3	01JAN2022	HIGH
9	Rhine	15AUG2022	1230	6	68	8	15FEB2023	3	01JAN2022	HIGH
10	Tigris	15NOV2022	1850	3	75	6	15MAY2023	3	01JAN2022	HIGH
11	Volga	30JUL2022	3530	1	50	6	30JAN2023	3	01JAN2022	MODERATE
12	Yangtze	05APR2022	6300	1	72	9	05OCT2022	3	01JAN2022	CRITICAL

data rivers_merged;

    merge rivers_base(in=a) rivers_rated(in=b);

    by River_Name;

    if a;

run;

proc print data=rivers_merged;

run;

OUTPUT:

Obs	River_Name	Survey_Date	Length	Countries_Crossed	Pollution_Level	Trade_Importance	Next_Review_Date	Years_Since_Survey	Review_Year_Start	Risk_Category
1	Amazon	10MAR2022	6400	7	45	8	10SEP2022	3	01JAN2022	LOW
2	Amazon	.	6400	.	45	8	10SEP2022	3	01JAN2022	LOW
3	Danube	18JUN2022	2860	10	55	7	18DEC2022	3	01JAN2022	MODERATE
4	Euphrates	20DEC2022	2800	4	73	7	20JUN2023	3	01JAN2022	HIGH
5	Ganges	01JAN2022	2525	2	78	9	01JUL2022	3	01JAN2022	CRITICAL
6	Ganges	.	2525	.	78	9	01JUL2022	3	01JAN2022	CRITICAL
7	Indus	10OCT2022	3180	4	62	8	10APR2023	3	01JAN2022	HIGH
8	Indus	.	3180	.	62	8	10APR2023	3	01JAN2022	HIGH
9	Mekong	01SEP2022	4350	6	70	7	01MAR2023	3	01JAN2022	HIGH
10	Mississippi	20MAY2022	3730	1	60	8	20NOV2022	3	01JAN2022	HIGH
11	Mississippi	.	3730	.	60	8	20NOV2022	3	01JAN2022	HIGH
12	Nile	15FEB2022	6650	11	65	10	15AUG2022	3	01JAN2022	HIGH
13	Nile	.	6650	.	65	10	15AUG2022	3	01JAN2022	HIGH
14	Rhine	15AUG2022	1230	6	68	8	15FEB2023	3	01JAN2022	HIGH
15	Rhine	.	1230	.	68	8	15FEB2023	3	01JAN2022	HIGH
16	Tigris	15NOV2022	1850	3	75	6	15MAY2023	3	01JAN2022	HIGH
17	Volga	30JUL2022	3530	1	50	6	30JAN2023	3	01JAN2022	MODERATE
18	Yangtze	05APR2022	6300	1	72	9	05OCT2022	3	01JAN2022	CRITICAL
19	Yangtze	.	6300	.	72	9	05OCT2022	3	01JAN2022	CRITICAL

11.TRANSPOSE FOR REPORTING

proc transpose data=rivers_base out=rivers_transposed;

    var Pollution_Level Trade_Importance;

    by River_Name;

run;

proc print data=rivers_transposed;

run;

OUTPUT:

Obs	River_Name	_NAME_	COL1	COL2
1	Amazon	Pollution_Level	45	45
2	Amazon	Trade_Importance	8	8
3	Danube	Pollution_Level	55	.
4	Danube	Trade_Importance	7	.
5	Euphrates	Pollution_Level	73	.
6	Euphrates	Trade_Importance	7	.
7	Ganges	Pollution_Level	78	78
8	Ganges	Trade_Importance	9	9
9	Indus	Pollution_Level	62	62
10	Indus	Trade_Importance	8	8
11	Mekong	Pollution_Level	70	.
12	Mekong	Trade_Importance	7	.
13	Mississippi	Pollution_Level	60	60
14	Mississippi	Trade_Importance	8	8
15	Nile	Pollution_Level	65	65
16	Nile	Trade_Importance	10	10
17	Rhine	Pollution_Level	68	68
18	Rhine	Trade_Importance	8	8
19	Tigris	Pollution_Level	75	.
20	Tigris	Trade_Importance	6	.
21	Volga	Pollution_Level	50	.
22	Volga	Trade_Importance	6	.
23	Yangtze	Pollution_Level	72	72
24	Yangtze	Trade_Importance	9	9

12.TRY THIS ON YOUR OWN

data rivers_error;

    set rivers_dates;

    Pollution_Level_N = input(Pollution_Level, best.);

    if Pollution_Level_N >= 60 and Pollution_Level_N <= 70

    and   and Trade_Importance > 7 then

        Flag = "YES";

run;

proc print data=rivers_error;

run;

/* Note: In practice above there is an Invalid in this code Find it,Correct it and Use it /*

OUTPUT:

Obs	River_Name	Survey_Date	Length	Countries_Crossed	Pollution_Level	Trade_Importance	Next_Review_Date	Years_Since_Survey	Review_Year_Start	Pollution_Level_N	Flag
1	Ganges	01JAN2022	2525	2	78	9	01JUL2022	3	01JAN2022	78
2	Nile	15FEB2022	6650	11	65	10	15AUG2022	3	01JAN2022	65	YES
3	Amazon	10MAR2022	6400	7	45	8	10SEP2022	3	01JAN2022	45
4	Yangtze	05APR2022	6300	1	72	9	05OCT2022	3	01JAN2022	72
5	Mississippi	20MAY2022	3730	1	60	8	20NOV2022	3	01JAN2022	60	YES
6	Danube	18JUN2022	2860	10	55	7	18DEC2022	3	01JAN2022	55
7	Volga	30JUL2022	3530	1	50	6	30JAN2023	3	01JAN2022	50
8	Rhine	15AUG2022	1230	6	68	8	15FEB2023	3	01JAN2022	68	YES
9	Mekong	01SEP2022	4350	6	70	7	01MAR2023	3	01JAN2022	70
10	Indus	10OCT2022	3180	4	62	8	10APR2023	3	01JAN2022	62	YES
11	Tigris	15NOV2022	1850	3	75	6	15MAY2023	3	01JAN2022	75
12	Euphrates	20DEC2022	2800	4	73	7	20JUN2023	3	01JAN2022	73

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  

Follow Us On : 

 

--->FOLLOW OUR BLOG FOR MORE INFORMATION.

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