349.INTERNET SERVICE PROVIDER(ISP) DATA ANALYSIS USING DATA STEP | PROC SQL | PROC MEANS | PROC FREQ | PROC SGPLOT | MACROS | DATE FUNCTIONS | PROC SORT | PROC APPEND | MERGE | PROC TRANSPOSE OPERATIONS

INTERNET SERVICE PROVIDER(ISP) DATA ANALYSIS USING DATA STEP | PROC SQL | PROC MEANS | PROC FREQ | PROC SGPLOT | MACROS | DATE FUNCTIONS | PROC SORT | PROC APPEND | MERGE |  PROC TRANSPOSE OPERATIONS

options nocenter;

1.DATASET CREATION

data isp_master;

    length Provider_Name $20 Coverage_Area $12;

    format Launch_Date Review_Date Analysis_Date date9.;

    input Provider_Name $ Speed Coverage_Area $ Cost Subscribers Ratings

          Launch_Date :date9. Review_Date :date9.;

     Analysis_Date = '01JAN2025'd;      

     datalines;

Airtel       200 Urban      999  5000000 4.5 15JAN2015 10DEC2024

Jio          300 Urban      699  8000000 4.7 01SEP2016 05DEC2024

BSNL         100 Rural      499  2000000 3.8 10MAR2010 20NOV2024

ACT          250 Urban      899  1800000 4.4 01APR2012 15DEC2024

Hathway      150 SemiUrban  599  1200000 4.0 05MAY2014 18NOV2024

Tikona        80 Rural      399   900000 3.6 20JUN2013 01DEC2024

Spectra      500 Urban     1299   600000 4.8 01JAN2018 22DEC2024

Alliance     120 SemiUrban  549   750000 3.9 12AUG2016 30NOV2024

Excitel      400 Urban      799  1400000 4.6 10OCT2017 25DEC2024

YouBroadband 180 SemiUrban  649  1000000 4.1 08FEB2011 05DEC2024

RailWire      90 Rural      349   850000 3.7 15JUL2015 28NOV2024

GTPL         160 SemiUrban  699  1100000 4.2 01JAN2014 12DEC2024

;

run;

proc print data=isp_master;

run;

OUTPUT:

Obs	Provider_Name	Coverage_Area	Launch_Date	Review_Date	Analysis_Date	Speed	Cost	Subscribers	Ratings
1	Airtel	Urban	15JAN2015	10DEC2024	01JAN2025	200	999	5000000	4.5
2	Jio	Urban	01SEP2016	05DEC2024	01JAN2025	300	699	8000000	4.7
3	BSNL	Rural	10MAR2010	20NOV2024	01JAN2025	100	499	2000000	3.8
4	ACT	Urban	01APR2012	15DEC2024	01JAN2025	250	899	1800000	4.4
5	Hathway	SemiUrban	05MAY2014	18NOV2024	01JAN2025	150	599	1200000	4.0
6	Tikona	Rural	20JUN2013	01DEC2024	01JAN2025	80	399	900000	3.6
7	Spectra	Urban	01JAN2018	22DEC2024	01JAN2025	500	1299	600000	4.8
8	Alliance	SemiUrban	12AUG2016	30NOV2024	01JAN2025	120	549	750000	3.9
9	Excitel	Urban	10OCT2017	25DEC2024	01JAN2025	400	799	1400000	4.6
10	YouBroadband	SemiUrban	08FEB2011	05DEC2024	01JAN2025	180	649	1000000	4.1
11	RailWire	Rural	15JUL2015	28NOV2024	01JAN2025	90	349	850000	3.7
12	GTPL	SemiUrban	01JAN2014	12DEC2024	01JAN2025	160	699	1100000	4.2

2.DATE CALCULATIONS USING INTCK, INTNX, MDY

data isp_dates;

    set isp_master;

    

    Years_In_Service = intck('year', Launch_Date, Analysis_Date);

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

    Financial_Year_Start = mdy(4,1,year(Analysis_Date));

    

    format Next_Review_Date Financial_Year_Start date9.;

run;

proc print data=isp_dates;

run;

OUTPUT:

Obs	Provider_Name	Coverage_Area	Launch_Date	Review_Date	Analysis_Date	Speed	Cost	Subscribers	Ratings	Years_In_Service	Next_Review_Date	Financial_Year_Start
1	Airtel	Urban	15JAN2015	10DEC2024	01JAN2025	200	999	5000000	4.5	10	10JUN2025	01APR2025
2	Jio	Urban	01SEP2016	05DEC2024	01JAN2025	300	699	8000000	4.7	9	05JUN2025	01APR2025
3	BSNL	Rural	10MAR2010	20NOV2024	01JAN2025	100	499	2000000	3.8	15	20MAY2025	01APR2025
4	ACT	Urban	01APR2012	15DEC2024	01JAN2025	250	899	1800000	4.4	13	15JUN2025	01APR2025
5	Hathway	SemiUrban	05MAY2014	18NOV2024	01JAN2025	150	599	1200000	4.0	11	18MAY2025	01APR2025
6	Tikona	Rural	20JUN2013	01DEC2024	01JAN2025	80	399	900000	3.6	12	01JUN2025	01APR2025
7	Spectra	Urban	01JAN2018	22DEC2024	01JAN2025	500	1299	600000	4.8	7	22JUN2025	01APR2025
8	Alliance	SemiUrban	12AUG2016	30NOV2024	01JAN2025	120	549	750000	3.9	9	30MAY2025	01APR2025
9	Excitel	Urban	10OCT2017	25DEC2024	01JAN2025	400	799	1400000	4.6	8	25JUN2025	01APR2025
10	YouBroadband	SemiUrban	08FEB2011	05DEC2024	01JAN2025	180	649	1000000	4.1	14	05JUN2025	01APR2025
11	RailWire	Rural	15JUL2015	28NOV2024	01JAN2025	90	349	850000	3.7	10	28MAY2025	01APR2025
12	GTPL	SemiUrban	01JAN2014	12DEC2024	01JAN2025	160	699	1100000	4.2	11	12JUN2025	01APR2025

3.PROC SQL – STRUCTURED DATA EXTRACTION

proc sql;

    create table isp_sql_summary as

    select Provider_Name,Speed,Cost,Subscribers,Ratings,Years_In_Service

    from isp_dates

    where Speed >= 150

    order by Ratings desc;

quit;

proc print data=isp_sql_summary;

run;

OUTPUT:

Obs	Provider_Name	Speed	Cost	Subscribers	Ratings	Years_In_Service
1	Spectra	500	1299	600000	4.8	7
2	Jio	300	699	8000000	4.7	9
3	Excitel	400	799	1400000	4.6	8
4	Airtel	200	999	5000000	4.5	10
5	ACT	250	899	1800000	4.4	13
6	GTPL	160	699	1100000	4.2	11
7	YouBroadband	180	649	1000000	4.1	14
8	Hathway	150	599	1200000	4.0	11

4.PROC MEANS – STATISTICAL SUMMARIZATION

proc means data=isp_dates mean min max sum;

    var Speed Cost Subscribers Ratings Years_In_Service;

run;

OUTPUT:

The MEANS Procedure

Variable	Mean	Minimum	Maximum	Sum
Speed Cost Subscribers Ratings Years_In_Service	210.8333333 703.1666667 2050000.00 4.1916667 10.7500000	80.0000000 349.0000000 600000.00 3.6000000 7.0000000	500.0000000 1299.00 8000000.00 4.8000000 15.0000000	2530.00 8438.00 24600000.00 50.3000000 129.0000000

5.PROC FREQ – CATEGORICAL ANALYSIS

proc freq data=isp_dates;

    tables Coverage_Area Ratings;

run;

OUTPUT:

The FREQ Procedure

Coverage_Area	Frequency	Percent	Cumulative Frequency	Cumulative Percent
Rural	3	25.00	3	25.00
SemiUrban	4	33.33	7	58.33
Urban	5	41.67	12	100.00

Ratings	Frequency	Percent	Cumulative Frequency	Cumulative Percent
3.6	1	8.33	1	8.33
3.7	1	8.33	2	16.67
3.8	1	8.33	3	25.00
3.9	1	8.33	4	33.33
4	1	8.33	5	41.67
4.1	1	8.33	6	50.00
4.2	1	8.33	7	58.33
4.4	1	8.33	8	66.67
4.5	1	8.33	9	75.00
4.6	1	8.33	10	83.33
4.7	1	8.33	11	91.67
4.8	1	8.33	12	100.00

6.MACRO FOR SERVICE-LEVEL CLASSIFICATION

%macro service_class;

data isp_classified;

    set isp_dates;

    length Service_Level $10;

    if Speed >= 300 and Ratings >= 4.5 then Service_Level = "PREMIUM";

    else if Speed >= 150 then Service_Level = "STANDARD";

    else Service_Level = "BASIC";

run;

proc print data=isp_classified;

run;

%mend;

%service_class;

OUTPUT:

Obs	Provider_Name	Coverage_Area	Launch_Date	Review_Date	Analysis_Date	Speed	Cost	Subscribers	Ratings	Years_In_Service	Next_Review_Date	Financial_Year_Start	Service_Level
1	Airtel	Urban	15JAN2015	10DEC2024	01JAN2025	200	999	5000000	4.5	10	10JUN2025	01APR2025	STANDARD
2	Jio	Urban	01SEP2016	05DEC2024	01JAN2025	300	699	8000000	4.7	9	05JUN2025	01APR2025	PREMIUM
3	BSNL	Rural	10MAR2010	20NOV2024	01JAN2025	100	499	2000000	3.8	15	20MAY2025	01APR2025	BASIC
4	ACT	Urban	01APR2012	15DEC2024	01JAN2025	250	899	1800000	4.4	13	15JUN2025	01APR2025	STANDARD
5	Hathway	SemiUrban	05MAY2014	18NOV2024	01JAN2025	150	599	1200000	4.0	11	18MAY2025	01APR2025	STANDARD
6	Tikona	Rural	20JUN2013	01DEC2024	01JAN2025	80	399	900000	3.6	12	01JUN2025	01APR2025	BASIC
7	Spectra	Urban	01JAN2018	22DEC2024	01JAN2025	500	1299	600000	4.8	7	22JUN2025	01APR2025	PREMIUM
8	Alliance	SemiUrban	12AUG2016	30NOV2024	01JAN2025	120	549	750000	3.9	9	30MAY2025	01APR2025	BASIC
9	Excitel	Urban	10OCT2017	25DEC2024	01JAN2025	400	799	1400000	4.6	8	25JUN2025	01APR2025	PREMIUM
10	YouBroadband	SemiUrban	08FEB2011	05DEC2024	01JAN2025	180	649	1000000	4.1	14	05JUN2025	01APR2025	STANDARD
11	RailWire	Rural	15JUL2015	28NOV2024	01JAN2025	90	349	850000	3.7	10	28MAY2025	01APR2025	BASIC
12	GTPL	SemiUrban	01JAN2014	12DEC2024	01JAN2025	160	699	1100000	4.2	11	12JUN2025	01APR2025	STANDARD

7.PROC SGPLOT – VISUAL ANALYTICS

proc sgplot data=isp_classified;

    vbar Provider_Name / response=Subscribers;

    title "SUBSCRIBER DISTRIBUTION BY ISP";

run;

OUTPUT:

8.DATA INTEGRATION – MERGE OPERATION

data isp_cost;

    set isp_master;

    keep Provider_Name Cost;

run;

proc print data=isp_cost;

run;

OUTPUT:

Obs	Provider_Name	Cost
1	Airtel	999
2	Jio	699
3	BSNL	499
4	ACT	899
5	Hathway	599
6	Tikona	399
7	Spectra	1299
8	Alliance	549
9	Excitel	799
10	YouBroadband	649
11	RailWire	349
12	GTPL	699

proc sort data=isp_classified;

    by Provider_Name;

run;

proc print data=isp_classified;

run;

OUTPUT:

Obs	Provider_Name	Coverage_Area	Launch_Date	Review_Date	Analysis_Date	Speed	Cost	Subscribers	Ratings	Years_In_Service	Next_Review_Date	Financial_Year_Start	Service_Level
1	ACT	Urban	01APR2012	15DEC2024	01JAN2025	250	899	1800000	4.4	13	15JUN2025	01APR2025	STANDARD
2	Airtel	Urban	15JAN2015	10DEC2024	01JAN2025	200	999	5000000	4.5	10	10JUN2025	01APR2025	STANDARD
3	Alliance	SemiUrban	12AUG2016	30NOV2024	01JAN2025	120	549	750000	3.9	9	30MAY2025	01APR2025	BASIC
4	BSNL	Rural	10MAR2010	20NOV2024	01JAN2025	100	499	2000000	3.8	15	20MAY2025	01APR2025	BASIC
5	Excitel	Urban	10OCT2017	25DEC2024	01JAN2025	400	799	1400000	4.6	8	25JUN2025	01APR2025	PREMIUM
6	GTPL	SemiUrban	01JAN2014	12DEC2024	01JAN2025	160	699	1100000	4.2	11	12JUN2025	01APR2025	STANDARD
7	Hathway	SemiUrban	05MAY2014	18NOV2024	01JAN2025	150	599	1200000	4.0	11	18MAY2025	01APR2025	STANDARD
8	Jio	Urban	01SEP2016	05DEC2024	01JAN2025	300	699	8000000	4.7	9	05JUN2025	01APR2025	PREMIUM
9	RailWire	Rural	15JUL2015	28NOV2024	01JAN2025	90	349	850000	3.7	10	28MAY2025	01APR2025	BASIC
10	Spectra	Urban	01JAN2018	22DEC2024	01JAN2025	500	1299	600000	4.8	7	22JUN2025	01APR2025	PREMIUM
11	Tikona	Rural	20JUN2013	01DEC2024	01JAN2025	80	399	900000	3.6	12	01JUN2025	01APR2025	BASIC
12	YouBroadband	SemiUrban	08FEB2011	05DEC2024	01JAN2025	180	649	1000000	4.1	14	05JUN2025	01APR2025	STANDARD

proc sort data=isp_cost;

    by Provider_Name;

run;

proc print data=isp_cost;

run;

OUTPUT:

Obs	Provider_Name	Cost
1	ACT	899
2	Airtel	999
3	Alliance	549
4	BSNL	499
5	Excitel	799
6	GTPL	699
7	Hathway	599
8	Jio	699
9	RailWire	349
10	Spectra	1299
11	Tikona	399
12	YouBroadband	649

data isp_merge;

    merge isp_classified(in=a) isp_cost(in=b);

    by Provider_Name;

    if a;

run;

proc print data=isp_merge;

run;

OUTPUT:

Obs	Provider_Name	Coverage_Area	Launch_Date	Review_Date	Analysis_Date	Speed	Cost	Subscribers	Ratings	Years_In_Service	Next_Review_Date	Financial_Year_Start	Service_Level
1	ACT	Urban	01APR2012	15DEC2024	01JAN2025	250	899	1800000	4.4	13	15JUN2025	01APR2025	STANDARD
2	Airtel	Urban	15JAN2015	10DEC2024	01JAN2025	200	999	5000000	4.5	10	10JUN2025	01APR2025	STANDARD
3	Alliance	SemiUrban	12AUG2016	30NOV2024	01JAN2025	120	549	750000	3.9	9	30MAY2025	01APR2025	BASIC
4	BSNL	Rural	10MAR2010	20NOV2024	01JAN2025	100	499	2000000	3.8	15	20MAY2025	01APR2025	BASIC
5	Excitel	Urban	10OCT2017	25DEC2024	01JAN2025	400	799	1400000	4.6	8	25JUN2025	01APR2025	PREMIUM
6	GTPL	SemiUrban	01JAN2014	12DEC2024	01JAN2025	160	699	1100000	4.2	11	12JUN2025	01APR2025	STANDARD
7	Hathway	SemiUrban	05MAY2014	18NOV2024	01JAN2025	150	599	1200000	4.0	11	18MAY2025	01APR2025	STANDARD
8	Jio	Urban	01SEP2016	05DEC2024	01JAN2025	300	699	8000000	4.7	9	05JUN2025	01APR2025	PREMIUM
9	RailWire	Rural	15JUL2015	28NOV2024	01JAN2025	90	349	850000	3.7	10	28MAY2025	01APR2025	BASIC
10	Spectra	Urban	01JAN2018	22DEC2024	01JAN2025	500	1299	600000	4.8	7	22JUN2025	01APR2025	PREMIUM
11	Tikona	Rural	20JUN2013	01DEC2024	01JAN2025	80	399	900000	3.6	12	01JUN2025	01APR2025	BASIC
12	YouBroadband	SemiUrban	08FEB2011	05DEC2024	01JAN2025	180	649	1000000	4.1	14	05JUN2025	01APR2025	STANDARD

9.APPEND – HISTORICAL DATA EXTENSION

data isp_new;

    set isp_master(obs=2);

run;

proc print data=isp_new;

run;

OUTPUT:

Obs	Provider_Name	Coverage_Area	Launch_Date	Review_Date	Analysis_Date	Speed	Cost	Subscribers	Ratings
1	Airtel	Urban	15JAN2015	10DEC2024	01JAN2025	200	999	5000000	4.5
2	Jio	Urban	01SEP2016	05DEC2024	01JAN2025	300	699	8000000	4.7

proc append base=isp_master 

            data=isp_new force;

run;

proc print data=isp_master;

run;

OUTPUT:

Obs	Provider_Name	Coverage_Area	Launch_Date	Review_Date	Analysis_Date	Speed	Cost	Subscribers	Ratings
1	Airtel	Urban	15JAN2015	10DEC2024	01JAN2025	200	999	5000000	4.5
2	Jio	Urban	01SEP2016	05DEC2024	01JAN2025	300	699	8000000	4.7
3	BSNL	Rural	10MAR2010	20NOV2024	01JAN2025	100	499	2000000	3.8
4	ACT	Urban	01APR2012	15DEC2024	01JAN2025	250	899	1800000	4.4
5	Hathway	SemiUrban	05MAY2014	18NOV2024	01JAN2025	150	599	1200000	4.0
6	Tikona	Rural	20JUN2013	01DEC2024	01JAN2025	80	399	900000	3.6
7	Spectra	Urban	01JAN2018	22DEC2024	01JAN2025	500	1299	600000	4.8
8	Alliance	SemiUrban	12AUG2016	30NOV2024	01JAN2025	120	549	750000	3.9
9	Excitel	Urban	10OCT2017	25DEC2024	01JAN2025	400	799	1400000	4.6
10	YouBroadband	SemiUrban	08FEB2011	05DEC2024	01JAN2025	180	649	1000000	4.1
11	RailWire	Rural	15JUL2015	28NOV2024	01JAN2025	90	349	850000	3.7
12	GTPL	SemiUrban	01JAN2014	12DEC2024	01JAN2025	160	699	1100000	4.2
13	Airtel	Urban	15JAN2015	10DEC2024	01JAN2025	200	999	5000000	4.5
14	Jio	Urban	01SEP2016	05DEC2024	01JAN2025	300	699	8000000	4.7

10.TRANSPOSE – STRUCTURAL TRANSFORMATION

proc transpose data=isp_classified out=isp_transposed;

    by Provider_Name;

    var Speed Cost Subscribers Ratings;

run;

proc print data=isp_transposed;

run;

OUTPUT:

Obs	Provider_Name	_NAME_	COL1
1	ACT	Speed	250.0
2	ACT	Cost	899.0
3	ACT	Subscribers	1800000.0
4	ACT	Ratings	4.4
5	Airtel	Speed	200.0
6	Airtel	Cost	999.0
7	Airtel	Subscribers	5000000.0
8	Airtel	Ratings	4.5
9	Alliance	Speed	120.0
10	Alliance	Cost	549.0
11	Alliance	Subscribers	750000.0
12	Alliance	Ratings	3.9
13	BSNL	Speed	100.0
14	BSNL	Cost	499.0
15	BSNL	Subscribers	2000000.0
16	BSNL	Ratings	3.8
17	Excitel	Speed	400.0
18	Excitel	Cost	799.0
19	Excitel	Subscribers	1400000.0
20	Excitel	Ratings	4.6
21	GTPL	Speed	160.0
22	GTPL	Cost	699.0
23	GTPL	Subscribers	1100000.0
24	GTPL	Ratings	4.2
25	Hathway	Speed	150.0
26	Hathway	Cost	599.0
27	Hathway	Subscribers	1200000.0
28	Hathway	Ratings	4.0
29	Jio	Speed	300.0
30	Jio	Cost	699.0
31	Jio	Subscribers	8000000.0
32	Jio	Ratings	4.7
33	RailWire	Speed	90.0
34	RailWire	Cost	349.0
35	RailWire	Subscribers	850000.0
36	RailWire	Ratings	3.7
37	Spectra	Speed	500.0
38	Spectra	Cost	1299.0
39	Spectra	Subscribers	600000.0
40	Spectra	Ratings	4.8
41	Tikona	Speed	80.0
42	Tikona	Cost	399.0
43	Tikona	Subscribers	900000.0
44	Tikona	Ratings	3.6
45	YouBroadband	Speed	180.0
46	YouBroadband	Cost	649.0
47	YouBroadband	Subscribers	1000000.0
48	YouBroadband	Ratings	4.1

YESTERDAY'S QUESTION:

10.PROC MEANS

proc means data=work.space_station;

    var Orbit_Height Crew_Capacity;

run;

proc means data=work.space_stations;

    var Orbit_Height Crew_Capacity;

run;

**Dataset name is space_stations

SAS will throw:

ERROR: File WORK.SPACE_STATION.DATA does not exist

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

OUTPUT:

The MEANS Procedure

Variable	N	Mean	Std Dev	Minimum	Maximum
Orbit_Height Crew_Capacity	14 14	390.4285714 4.7142857	94.0357659 1.7288756	200.0000000 3.0000000	600.0000000 8.0000000

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.