362.How Can SAS Be Used to Analyze Stock Sector Performance Using Data Step, PROC SQL, Regression, and Advanced Reporting Techniques?

How Can SAS Be Used to Analyze Stock Sector Performance Using Data Step, PROC SQL, Regression, and Advanced Reporting Techniques?

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.