362.STOCK SECTOR PERFORMANCE ANALYSIS USING SAS DATA STEP | PROC SQL | PROC MEANS | PROC REG | PROC SGPLOT | MACROS | DATE FUNCTIONS | MERGE | APPEND | SET | TRANSPOSE

STOCK SECTOR PERFORMANCE ANALYSIS USING SAS DATA STEP | PROC SQL | PROC MEANS | PROC REG | PROC SGPLOT | MACROS | DATE FUNCTIONS | MERGE | APPEND | SET | TRANSPOSE 

options nocenter;

1.CREATING THE STOCK SECTORS DATASET

data stock_sectors;

    length Sector_Name $20;

    format Report_Date date9.;

    input Sector_Name $ Companies Index_Value Growth_Rate Risk_Level Report_Date : date9.;

    datalines;

IT 120 18500 12.5 4 01JAN2024

BANKING 95 42100 8.2 6 01JAN2024

PHARMA 60 15600 10.1 5 01JAN2024

FMCG 80 29800 6.5 3 01JAN2024

ENERGY 70 34000 7.8 7 01JAN2024

METALS 55 21400 9.3 8 01JAN2024

AUTO 65 28750 11.2 6 01JAN2024

REALTY 40 17300 5.4 9 01JAN2024

TELECOM 30 9200 4.1 8 01JAN2024

MEDIA 25 8700 3.6 7 01JAN2024

INFRA 50 26600 8.9 7 01JAN2024

CHEMICALS 45 19200 10.7 5 01JAN2024

CEMENT 35 16400 6.8 6 01JAN2024

TEXTILES 28 8900 4.9 7 01JAN2024

POWER 60 23800 7.3 6 01JAN2024

;

run;

proc print data=stock_sectors;

run;

OUTPUT:

Obs	Sector_Name	Report_Date	Companies	Index_Value	Growth_Rate	Risk_Level
1	IT	01JAN2024	120	18500	12.5	4
2	BANKING	01JAN2024	95	42100	8.2	6
3	PHARMA	01JAN2024	60	15600	10.1	5
4	FMCG	01JAN2024	80	29800	6.5	3
5	ENERGY	01JAN2024	70	34000	7.8	7
6	METALS	01JAN2024	55	21400	9.3	8
7	AUTO	01JAN2024	65	28750	11.2	6
8	REALTY	01JAN2024	40	17300	5.4	9
9	TELECOM	01JAN2024	30	9200	4.1	8
10	MEDIA	01JAN2024	25	8700	3.6	7
11	INFRA	01JAN2024	50	26600	8.9	7
12	CHEMICALS	01JAN2024	45	19200	10.7	5
13	CEMENT	01JAN2024	35	16400	6.8	6
14	TEXTILES	01JAN2024	28	8900	4.9	7
15	POWER	01JAN2024	60	23800	7.3	6

2.DATE DERIVATIONS USING MDY, INTNX, INTCK

data stock_sectors_dates;

    set stock_sectors;

    Next_Quarter = intnx('quarter', Report_Date, 1, 'begin');

    Prev_Year_Days = intck('day', intnx('year', Report_Date, -1), Report_Date);

    format Next_Quarter date9.;

run;

proc print data=stock_sectors_dates;

run;

OUTPUT:

Obs	Sector_Name	Report_Date	Companies	Index_Value	Growth_Rate	Risk_Level	Next_Quarter	Prev_Year_Days
1	IT	01JAN2024	120	18500	12.5	4	01APR2024	365
2	BANKING	01JAN2024	95	42100	8.2	6	01APR2024	365
3	PHARMA	01JAN2024	60	15600	10.1	5	01APR2024	365
4	FMCG	01JAN2024	80	29800	6.5	3	01APR2024	365
5	ENERGY	01JAN2024	70	34000	7.8	7	01APR2024	365
6	METALS	01JAN2024	55	21400	9.3	8	01APR2024	365
7	AUTO	01JAN2024	65	28750	11.2	6	01APR2024	365
8	REALTY	01JAN2024	40	17300	5.4	9	01APR2024	365
9	TELECOM	01JAN2024	30	9200	4.1	8	01APR2024	365
10	MEDIA	01JAN2024	25	8700	3.6	7	01APR2024	365
11	INFRA	01JAN2024	50	26600	8.9	7	01APR2024	365
12	CHEMICALS	01JAN2024	45	19200	10.7	5	01APR2024	365
13	CEMENT	01JAN2024	35	16400	6.8	6	01APR2024	365
14	TEXTILES	01JAN2024	28	8900	4.9	7	01APR2024	365
15	POWER	01JAN2024	60	23800	7.3	6	01APR2024	365

3.USING PROC SQL FOR SECTOR INSIGHTS

proc sql;

    create table high_growth_sectors as

    select Sector_Name,Index_Value,Growth_Rate,Risk_Level

    from stock_sectors_dates

    where Growth_Rate > 9

    order by Growth_Rate desc;

quit;

proc print data=high_growth_sectors;

run;

OUTPUT:

Obs	Sector_Name	Index_Value	Growth_Rate	Risk_Level
1	IT	18500	12.5	4
2	AUTO	28750	11.2	6
3	CHEMICALS	19200	10.7	5
4	PHARMA	15600	10.1	5
5	METALS	21400	9.3	8

4.APPEND OPERATION (PROC APPEND)

data new_sectors;

    length Sector_Name $20;

    format Report_Date date9.;

    input Sector_Name $ Companies Index_Value Growth_Rate Risk_Level Report_Date : date9.;

    datalines;

DEFENCE 22 14400 13.4 6 01JAN2024

RENEWABLE 35 19800 14.1 5 01JAN2024

;

run;

proc print data=new_sectors;

run;

OUTPUT:

Obs	Sector_Name	Report_Date	Companies	Index_Value	Growth_Rate	Risk_Level
1	DEFENCE	01JAN2024	22	14400	13.4	6
2	RENEWABLE	01JAN2024	35	19800	14.1	5

proc append base=stock_sectors_dates

            data=new_sectors force;

run;

proc print data=stock_sectors_dates;

run;

OUTPUT:

Obs	Sector_Name	Report_Date	Companies	Index_Value	Growth_Rate	Risk_Level	Next_Quarter	Prev_Year_Days
1	IT	01JAN2024	120	18500	12.5	4	01APR2024	365
2	BANKING	01JAN2024	95	42100	8.2	6	01APR2024	365
3	PHARMA	01JAN2024	60	15600	10.1	5	01APR2024	365
4	FMCG	01JAN2024	80	29800	6.5	3	01APR2024	365
5	ENERGY	01JAN2024	70	34000	7.8	7	01APR2024	365
6	METALS	01JAN2024	55	21400	9.3	8	01APR2024	365
7	AUTO	01JAN2024	65	28750	11.2	6	01APR2024	365
8	REALTY	01JAN2024	40	17300	5.4	9	01APR2024	365
9	TELECOM	01JAN2024	30	9200	4.1	8	01APR2024	365
10	MEDIA	01JAN2024	25	8700	3.6	7	01APR2024	365
11	INFRA	01JAN2024	50	26600	8.9	7	01APR2024	365
12	CHEMICALS	01JAN2024	45	19200	10.7	5	01APR2024	365
13	CEMENT	01JAN2024	35	16400	6.8	6	01APR2024	365
14	TEXTILES	01JAN2024	28	8900	4.9	7	01APR2024	365
15	POWER	01JAN2024	60	23800	7.3	6	01APR2024	365
16	DEFENCE	01JAN2024	22	14400	13.4	6	.	.
17	RENEWABLE	01JAN2024	35	19800	14.1	5	.	.

5.USING SET STATEMENT

data combined_sectors;

    set stock_sectors_dates 

        new_sectors;

run;

proc print data=combined_sectors;

run;

OUTPUT:

Obs	Sector_Name	Report_Date	Companies	Index_Value	Growth_Rate	Risk_Level	Next_Quarter	Prev_Year_Days
1	IT	01JAN2024	120	18500	12.5	4	01APR2024	365
2	BANKING	01JAN2024	95	42100	8.2	6	01APR2024	365
3	PHARMA	01JAN2024	60	15600	10.1	5	01APR2024	365
4	FMCG	01JAN2024	80	29800	6.5	3	01APR2024	365
5	ENERGY	01JAN2024	70	34000	7.8	7	01APR2024	365
6	METALS	01JAN2024	55	21400	9.3	8	01APR2024	365
7	AUTO	01JAN2024	65	28750	11.2	6	01APR2024	365
8	REALTY	01JAN2024	40	17300	5.4	9	01APR2024	365
9	TELECOM	01JAN2024	30	9200	4.1	8	01APR2024	365
10	MEDIA	01JAN2024	25	8700	3.6	7	01APR2024	365
11	INFRA	01JAN2024	50	26600	8.9	7	01APR2024	365
12	CHEMICALS	01JAN2024	45	19200	10.7	5	01APR2024	365
13	CEMENT	01JAN2024	35	16400	6.8	6	01APR2024	365
14	TEXTILES	01JAN2024	28	8900	4.9	7	01APR2024	365
15	POWER	01JAN2024	60	23800	7.3	6	01APR2024	365
16	DEFENCE	01JAN2024	22	14400	13.4	6	.	.
17	RENEWABLE	01JAN2024	35	19800	14.1	5	.	.
18	DEFENCE	01JAN2024	22	14400	13.4	6	.	.
19	RENEWABLE	01JAN2024	35	19800	14.1	5	.	.

6.PROC MEANS – STATISTICAL SUMMARY

proc means data=combined_sectors mean min max;

    var Index_Value Growth_Rate Risk_Level;

run;

OUTPUT:

The MEANS Procedure

Variable	Mean	Minimum	Maximum
Index_Value Growth_Rate Risk_Level	20455.26 9.0684211 6.1052632	8700.00 3.6000000 3.0000000	42100.00 14.1000000 9.0000000

7.MACRO FOR SECTOR RISK CLASSIFICATION

%macro sector_rating;

data sector_rating;

    set combined_sectors;

    length Risk_Category $12;

    if Risk_Level <= 3 then Risk_Category = "LOW";

    else if Risk_Level <= 6 then Risk_Category = "MEDIUM";

    else Risk_Category = "HIGH";

run;

proc print data=sector_rating;

run;

%mend;

%sector_rating;

OUTPUT:

Obs	Sector_Name	Report_Date	Companies	Index_Value	Growth_Rate	Risk_Level	Next_Quarter	Prev_Year_Days	Risk_Category
1	IT	01JAN2024	120	18500	12.5	4	01APR2024	365	MEDIUM
2	BANKING	01JAN2024	95	42100	8.2	6	01APR2024	365	MEDIUM
3	PHARMA	01JAN2024	60	15600	10.1	5	01APR2024	365	MEDIUM
4	FMCG	01JAN2024	80	29800	6.5	3	01APR2024	365	LOW
5	ENERGY	01JAN2024	70	34000	7.8	7	01APR2024	365	HIGH
6	METALS	01JAN2024	55	21400	9.3	8	01APR2024	365	HIGH
7	AUTO	01JAN2024	65	28750	11.2	6	01APR2024	365	MEDIUM
8	REALTY	01JAN2024	40	17300	5.4	9	01APR2024	365	HIGH
9	TELECOM	01JAN2024	30	9200	4.1	8	01APR2024	365	HIGH
10	MEDIA	01JAN2024	25	8700	3.6	7	01APR2024	365	HIGH
11	INFRA	01JAN2024	50	26600	8.9	7	01APR2024	365	HIGH
12	CHEMICALS	01JAN2024	45	19200	10.7	5	01APR2024	365	MEDIUM
13	CEMENT	01JAN2024	35	16400	6.8	6	01APR2024	365	MEDIUM
14	TEXTILES	01JAN2024	28	8900	4.9	7	01APR2024	365	HIGH
15	POWER	01JAN2024	60	23800	7.3	6	01APR2024	365	MEDIUM
16	DEFENCE	01JAN2024	22	14400	13.4	6	.	.	MEDIUM
17	RENEWABLE	01JAN2024	35	19800	14.1	5	.	.	MEDIUM
18	DEFENCE	01JAN2024	22	14400	13.4	6	.	.	MEDIUM
19	RENEWABLE	01JAN2024	35	19800	14.1	5	.	.	MEDIUM

8.PROC REG – RELATIONSHIP ANALYSIS

proc reg data=sector_rating;

    model Index_Value = Growth_Rate Risk_Level;

run;

quit;

OUTPUT:

The REG Procedure

Model: MODEL1

Dependent Variable: Index_Value

Number of Observations Read	19
Number of Observations Used	19

Analysis of Variance
Source	DF	Sum of Squares	Mean Square	F Value	Pr > F
Model	2	67790234	33895117	0.41	0.6695
Error	16	1318084240	82380265
Corrected Total	18	1385874474

Root MSE	9076.35747	R-Square	0.0489
Dependent Mean	20455	Adj R-Sq	-0.0700
Coeff Var	44.37175

Parameter Estimates
Variable	DF	Parameter Estimate	Standard Error	t Value	Pr > |t|
Intercept	1	27665	14764	1.87	0.0793
Growth_Rate	1	58.09558	715.28856	0.08	0.9363
Risk_Level	1	-1267.20574	1689.95995	-0.75	0.4642

The REG Procedure

Model: MODEL1

Dependent Variable: Index_Value

9.PROC SGPLOT – VISUALIZATION

proc sgplot data=sector_rating;

    scatter x=Growth_Rate y=Index_Value;

    reg x=Growth_Rate y=Index_Value;

    title "Growth Rate vs Index Value by Sector";

run;

OUTPUT:

10.MERGE OPERATION

proc sort data=sector_rating; by Sector_Name; run;

proc print data=sector_rating;

run;

OUTPUT:

Obs	Sector_Name	Report_Date	Companies	Index_Value	Growth_Rate	Risk_Level	Next_Quarter	Prev_Year_Days	Risk_Category
1	AUTO	01JAN2024	65	28750	11.2	6	01APR2024	365	MEDIUM
2	BANKING	01JAN2024	95	42100	8.2	6	01APR2024	365	MEDIUM
3	CEMENT	01JAN2024	35	16400	6.8	6	01APR2024	365	MEDIUM
4	CHEMICALS	01JAN2024	45	19200	10.7	5	01APR2024	365	MEDIUM
5	DEFENCE	01JAN2024	22	14400	13.4	6	.	.	MEDIUM
6	DEFENCE	01JAN2024	22	14400	13.4	6	.	.	MEDIUM
7	ENERGY	01JAN2024	70	34000	7.8	7	01APR2024	365	HIGH
8	FMCG	01JAN2024	80	29800	6.5	3	01APR2024	365	LOW
9	INFRA	01JAN2024	50	26600	8.9	7	01APR2024	365	HIGH
10	IT	01JAN2024	120	18500	12.5	4	01APR2024	365	MEDIUM
11	MEDIA	01JAN2024	25	8700	3.6	7	01APR2024	365	HIGH
12	METALS	01JAN2024	55	21400	9.3	8	01APR2024	365	HIGH
13	PHARMA	01JAN2024	60	15600	10.1	5	01APR2024	365	MEDIUM
14	POWER	01JAN2024	60	23800	7.3	6	01APR2024	365	MEDIUM
15	REALTY	01JAN2024	40	17300	5.4	9	01APR2024	365	HIGH
16	RENEWABLE	01JAN2024	35	19800	14.1	5	.	.	MEDIUM
17	RENEWABLE	01JAN2024	35	19800	14.1	5	.	.	MEDIUM
18	TELECOM	01JAN2024	30	9200	4.1	8	01APR2024	365	HIGH
19	TEXTILES	01JAN2024	28	8900	4.9	7	01APR2024	365	HIGH

proc sort data=high_growth_sectors; by Sector_Name; run;

proc print data=high_growth_sectors;

run;

OUTPUT:

Obs	Sector_Name	Index_Value	Growth_Rate	Risk_Level
1	AUTO	28750	11.2	6
2	CHEMICALS	19200	10.7	5
3	IT	18500	12.5	4
4	METALS	21400	9.3	8
5	PHARMA	15600	10.1	5

data merged_sectors;

    merge sector_rating(in=a) high_growth_sectors(in=b);

    by Sector_Name;

    if a;

run;

proc print data=merged_sectors;

run;

OUTPUT:

Obs	Sector_Name	Report_Date	Companies	Index_Value	Growth_Rate	Risk_Level	Next_Quarter	Prev_Year_Days	Risk_Category
1	AUTO	01JAN2024	65	28750	11.2	6	01APR2024	365	MEDIUM
2	BANKING	01JAN2024	95	42100	8.2	6	01APR2024	365	MEDIUM
3	CEMENT	01JAN2024	35	16400	6.8	6	01APR2024	365	MEDIUM
4	CHEMICALS	01JAN2024	45	19200	10.7	5	01APR2024	365	MEDIUM
5	DEFENCE	01JAN2024	22	14400	13.4	6	.	.	MEDIUM
6	DEFENCE	01JAN2024	22	14400	13.4	6	.	.	MEDIUM
7	ENERGY	01JAN2024	70	34000	7.8	7	01APR2024	365	HIGH
8	FMCG	01JAN2024	80	29800	6.5	3	01APR2024	365	LOW
9	INFRA	01JAN2024	50	26600	8.9	7	01APR2024	365	HIGH
10	IT	01JAN2024	120	18500	12.5	4	01APR2024	365	MEDIUM
11	MEDIA	01JAN2024	25	8700	3.6	7	01APR2024	365	HIGH
12	METALS	01JAN2024	55	21400	9.3	8	01APR2024	365	HIGH
13	PHARMA	01JAN2024	60	15600	10.1	5	01APR2024	365	MEDIUM
14	POWER	01JAN2024	60	23800	7.3	6	01APR2024	365	MEDIUM
15	REALTY	01JAN2024	40	17300	5.4	9	01APR2024	365	HIGH
16	RENEWABLE	01JAN2024	35	19800	14.1	5	.	.	MEDIUM
17	RENEWABLE	01JAN2024	35	19800	14.1	5	.	.	MEDIUM
18	TELECOM	01JAN2024	30	9200	4.1	8	01APR2024	365	HIGH
19	TEXTILES	01JAN2024	28	8900	4.9	7	01APR2024	365	HIGH

11.PROC TRANSPOSE

proc transpose data=merged_sectors

               out=sector_transposed

               prefix=Value_;

    by Sector_Name;

    var Index_Value Growth_Rate Risk_Level;

run;

proc print data=sector_transposed;

run;

OUTPUT:

Obs	Sector_Name	_NAME_	Value_1	Value_2
1	AUTO	Index_Value	28750.0	.
2	AUTO	Growth_Rate	11.2	.
3	AUTO	Risk_Level	6.0	.
4	BANKING	Index_Value	42100.0	.
5	BANKING	Growth_Rate	8.2	.
6	BANKING	Risk_Level	6.0	.
7	CEMENT	Index_Value	16400.0	.
8	CEMENT	Growth_Rate	6.8	.
9	CEMENT	Risk_Level	6.0	.
10	CHEMICALS	Index_Value	19200.0	.
11	CHEMICALS	Growth_Rate	10.7	.
12	CHEMICALS	Risk_Level	5.0	.
13	DEFENCE	Index_Value	14400.0	14400.0
14	DEFENCE	Growth_Rate	13.4	13.4
15	DEFENCE	Risk_Level	6.0	6.0
16	ENERGY	Index_Value	34000.0	.
17	ENERGY	Growth_Rate	7.8	.
18	ENERGY	Risk_Level	7.0	.
19	FMCG	Index_Value	29800.0	.
20	FMCG	Growth_Rate	6.5	.
21	FMCG	Risk_Level	3.0	.
22	INFRA	Index_Value	26600.0	.
23	INFRA	Growth_Rate	8.9	.
24	INFRA	Risk_Level	7.0	.
25	IT	Index_Value	18500.0	.
26	IT	Growth_Rate	12.5	.
27	IT	Risk_Level	4.0	.
28	MEDIA	Index_Value	8700.0	.
29	MEDIA	Growth_Rate	3.6	.
30	MEDIA	Risk_Level	7.0	.
31	METALS	Index_Value	21400.0	.
32	METALS	Growth_Rate	9.3	.
33	METALS	Risk_Level	8.0	.
34	PHARMA	Index_Value	15600.0	.
35	PHARMA	Growth_Rate	10.1	.
36	PHARMA	Risk_Level	5.0	.
37	POWER	Index_Value	23800.0	.
38	POWER	Growth_Rate	7.3	.
39	POWER	Risk_Level	6.0	.
40	REALTY	Index_Value	17300.0	.
41	REALTY	Growth_Rate	5.4	.
42	REALTY	Risk_Level	9.0	.
43	RENEWABLE	Index_Value	19800.0	19800.0
44	RENEWABLE	Growth_Rate	14.1	14.1
45	RENEWABLE	Risk_Level	5.0	5.0
46	TELECOM	Index_Value	9200.0	.
47	TELECOM	Growth_Rate	4.1	.
48	TELECOM	Risk_Level	8.0	.
49	TEXTILES	Index_Value	8900.0	.
50	TEXTILES	Growth_Rate	4.9	.
51	TEXTILES	Risk_Level	7.0	.

YESTERDAY'S QUESTION:

data rivers_error;

    set rivers_dates;

    if Pollution_Level between 60 and 70 and and Trade_Importance > 7 then Flag="YES";

run;

data rivers_error;

    set rivers_dates;

    Pollution_Level_N = input(Pollution_Level, best.);

    if Pollution_Level_N >= 60 and Pollution_Level_N <= 70

       and Trade_Importance > 7 then

        Flag = "YES";

run;

-->Where BETWEEN IS ALLOWED

• WHERE statement

• PROC SQL

-->Where BETWEEN IS NOT ALLOWED

• IF condition in a DATA step 

/* 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 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.