264.TEMPLES OF INDIA DATASET ANALYSIS USING PROC PRINT | PROC CONTENTS | PROC SORT | PROC FREQ | PROC MEANS | PROC SQL | PROC TABULATE | PROC REPORT | PROC TRANSPOSE | PROC FORMAT | PROC RANK | PROC SGPLOT | MACROS

on
0

169.COMPREHENSIVE SMART HOME ENERGY ANALYSIS USING SAS: PROC SQL FOR DATA EXTRACTION | PROC PRINT FOR REPORTING | PROC TRANSPOSE FOR DATA PIVOTING AND COMPARATIVE INSIGHTS

on

COMPREHENSIVE SMART HOME ENERGY ANALYSIS USING SAS: PROC SQL FOR DATA EXTRACTION | PROC PRINT FOR REPORTING | PROC TRANSPOSE FOR DATA PIVOTING AND COMPARATIVE INSIGHTS


/*Designing a unique dataset centered around Smart Home Energy Consumption*/

Dataset Structure

We'll create a dataset named smart_home_energy with the following variables:

Home_ID: Unique identifier for each smart home.

Room: Name of the room (e.g., Living Room, Kitchen).

Appliance: Name of the appliance (e.g., Air Conditioner, Refrigerator).

Energy_Consumption: Energy consumed in kilowatt-hours (kWh).

Timestamp: Date and time of the energy reading.


/*Creating the Dataset*/

data smart_home_energy;

    format Timestamp datetime20.;

    do Home_ID = 1 to 5;

        do Room = 'Living Room', 'Kitchen', 'Bedroom', 'Bathroom';

            do Appliance = 'Air Conditioner', 'Refrigerator', 'Heater', 'Washing Machine';

                Energy_Consumption = round(rand('uniform')*2, 0.01);

                Timestamp = datetime() - rand('uniform')*1e6;

                output;

                if _N_ >= 20 then stop;

            end;

        end;

    end;

run;

proc print data= smart_home_energy (obs=20);run;

Output:

Obs Timestamp Home_ID Room Appliance Energy_Consumption
1 05SEP2015:16:31:57 1 Living Room Air Conditioner 1.01
2 13SEP2015:08:21:19 1 Living Room Refrigerator 1.65
3 05SEP2015:03:51:42 1 Living Room Heater 1.68
4 09SEP2015:09:00:54 1 Living Room Washing Machine 0.66
5 05SEP2015:10:37:46 1 Kitchen Air Conditioner 0.54
6 05SEP2015:21:52:44 1 Kitchen Refrigerator 1.17
7 13SEP2015:05:10:10 1 Kitchen Heater 0.41
8 03SEP2015:03:18:31 1 Kitchen Washing Machine 0.89
9 06SEP2015:21:51:23 1 Bedroom Air Conditioner 1.78
10 05SEP2015:19:06:15 1 Bedroom Refrigerator 1.22
11 11SEP2015:13:40:58 1 Bedroom Heater 1.13
12 12SEP2015:17:33:42 1 Bedroom Washing Machine 0.58
13 03SEP2015:21:30:35 1 Bathroom Air Conditioner 1.99
14 05SEP2015:21:49:37 1 Bathroom Refrigerator 1.55
15 11SEP2015:05:09:30 1 Bathroom Heater 0.57
16 13SEP2015:12:26:20 1 Bathroom Washing Machine 1.28
17 05SEP2015:00:14:19 2 Living Room Air Conditioner 1.34
18 07SEP2015:08:05:29 2 Living Room Refrigerator 1.31
19 09SEP2015:17:58:10 2 Living Room Heater 0.72
20 04SEP2015:05:45:24 2 Living Room Washing Machine 1.15


/*Total Energy Consumption per Home*/

proc sql;

    create table total_consumption as

    select Home_ID, sum(Energy_Consumption) as Total_Energy

    from smart_home_energy

    group by Home_ID;

quit;

proc print;run;

Output:

Obs Home_ID Total_Energy
1 1 18.11
2 2 17.88
3 3 21.35
4 4 17.20
5 5 16.46

/*Average Consumption per Appliance*/

proc sql;

    create table avg_appliance as

    select Appliance, avg(Energy_Consumption) as Avg_Energy

    from smart_home_energy

    group by Appliance;

quit;

proc print;run;

Output:

Obs Appliance Avg_Energy
1 Air Conditioner 1.1515
2 Heater 1.1465
3 Refrigerator 1.2405
4 Washing Machine 1.0115

/*Detecting Anomalies*/

data anomalies;

    set smart_home_energy;

    if Energy_Consumption > 1.5;

run;

proc print;run;

Output:

Obs Timestamp Home_ID Room Appliance Energy_Consumption
1 13SEP2015:08:21:19 1 Living Room Refrigerator 1.65
2 05SEP2015:03:51:42 1 Living Room Heater 1.68
3 06SEP2015:21:51:23 1 Bedroom Air Conditioner 1.78
4 03SEP2015:21:30:35 1 Bathroom Air Conditioner 1.99
5 05SEP2015:21:49:37 1 Bathroom Refrigerator 1.55
6 04SEP2015:05:52:36 2 Kitchen Refrigerator 2.00
7 07SEP2015:16:43:41 2 Bedroom Air Conditioner 1.88
8 08SEP2015:17:14:08 2 Bedroom Heater 1.56
9 10SEP2015:19:24:49 3 Living Room Air Conditioner 1.66
10 13SEP2015:02:01:23 3 Living Room Heater 1.83
11 07SEP2015:01:56:26 3 Living Room Washing Machine 1.93
12 07SEP2015:11:59:55 3 Kitchen Air Conditioner 1.94
13 12SEP2015:15:14:23 3 Kitchen Refrigerator 1.89
14 13SEP2015:15:13:26 3 Bedroom Refrigerator 1.59
15 03SEP2015:22:24:06 3 Bedroom Washing Machine 1.72
16 09SEP2015:02:41:22 3 Bathroom Air Conditioner 1.90
17 06SEP2015:03:29:11 4 Living Room Refrigerator 1.69
18 10SEP2015:13:09:59 4 Bedroom Heater 1.94
19 10SEP2015:16:20:47 4 Bedroom Washing Machine 1.90
20 13SEP2015:21:02:35 4 Bathroom Refrigerator 1.69
21 06SEP2015:06:34:35 5 Living Room Washing Machine 1.62
22 04SEP2015:05:18:34 5 Kitchen Heater 1.85
23 07SEP2015:21:41:27 5 Kitchen Washing Machine 1.55
24 12SEP2015:14:02:26 5 Bedroom Heater 1.72
25 03SEP2015:03:07:59 5 Bathroom Refrigerator 1.79
26 13SEP2015:06:35:33 5 Bathroom Heater 1.51

/*Energy Consumption by Room*/

proc means data=smart_home_energy ;

    class Room;

    var Energy_Consumption;

    output out=room_summary mean=Avg_Energy;

run;

Output:

                                                                     The MEANS Procedure

Analysis Variable : Energy_Consumption
Room N Obs N Mean Std Dev Minimum Maximum
Bathroom 20 20 1.1085000 0.4869051 0.3500000 1.9900000
Bedroom 20 20 1.1390000 0.6266948 0.0500000 1.9400000
Kitchen 20 20 1.0530000 0.6078010 0.0600000 2.0000000
Living Room 20 20 1.2495000 0.4804765 0.2000000 1.9300000

/*Visualizing Energy Consumption*/

proc sgplot data=room_summary;

    vbar Room / response=Avg_Energy;

    title "Average Energy Consumption by Room";

run;

Log:

NOTE: PROCEDURE SGPLOT used (Total process time):

      real time           3.07 seconds

      cpu time            0.78 seconds


NOTE: Listing image output written to SGPlot1.png.

NOTE: There were 5 observations read from the data set WORK.ROOM_SUMMARY.


/*Automating with Macros*/

%macro appliance_summary(appliance_name);

    %let safe_name = %sysfunc(translate(&appliance_name, _, %str( )));

    proc sql;

        create table summary_&safe_name as

        select Home_ID, avg(Energy_Consumption) as Avg_Energy

        from smart_home_energy

        where Appliance = "&appliance_name"

        group by Home_ID;

proc print;run;

    quit;

%mend;


%appliance_summary(Air Conditioner);

Output:

Obs Home_ID Avg_Energy
1 1 1.3300
2 2 1.2200
3 3 1.6800
4 4 1.1125
5 5 0.4150

%appliance_summary(Refrigerator);

Output:

Obs Home_ID Avg_Energy
1 1 1.3975
2 2 1.2225
3 3 1.4925
4 4 1.1300
5 5 0.9600

/*Time-Based Analysis*/

data energy_time;

    set smart_home_energy;

    Hour = hour(Timestamp);

run;

proc print;run;

Output:

Obs Timestamp Home_ID Room Appliance Energy_Consumption Hour
1 05SEP2015:16:31:57 1 Living Room Air Conditioner 1.01 16
2 13SEP2015:08:21:19 1 Living Room Refrigerator 1.65 8
3 05SEP2015:03:51:42 1 Living Room Heater 1.68 3
4 09SEP2015:09:00:54 1 Living Room Washing Machine 0.66 9
5 05SEP2015:10:37:46 1 Kitchen Air Conditioner 0.54 10
6 05SEP2015:21:52:44 1 Kitchen Refrigerator 1.17 21
7 13SEP2015:05:10:10 1 Kitchen Heater 0.41 5
8 03SEP2015:03:18:31 1 Kitchen Washing Machine 0.89 3
9 06SEP2015:21:51:23 1 Bedroom Air Conditioner 1.78 21
10 05SEP2015:19:06:15 1 Bedroom Refrigerator 1.22 19
11 11SEP2015:13:40:58 1 Bedroom Heater 1.13 13
12 12SEP2015:17:33:42 1 Bedroom Washing Machine 0.58 17
13 03SEP2015:21:30:35 1 Bathroom Air Conditioner 1.99 21
14 05SEP2015:21:49:37 1 Bathroom Refrigerator 1.55 21
15 11SEP2015:05:09:30 1 Bathroom Heater 0.57 5
16 13SEP2015:12:26:20 1 Bathroom Washing Machine 1.28 12
17 05SEP2015:00:14:19 2 Living Room Air Conditioner 1.34 0
18 07SEP2015:08:05:29 2 Living Room Refrigerator 1.31 8
19 09SEP2015:17:58:10 2 Living Room Heater 0.72 17
20 04SEP2015:05:45:24 2 Living Room Washing Machine 1.15 5


proc sql;

    create table hourly_consumption as

    select Hour, avg(Energy_Consumption) as Avg_Energy

    from energy_time

    group by Hour;

quit;

proc print;run;

Output:

Obs Hour Avg_Energy
1 0 0.65400
2 1 1.25333
3 2 1.26400
4 3 1.45200
5 4 0.81500
6 5 1.05000
7 6 1.21333
8 7 1.05000
9 8 1.12000
10 9 0.66000
11 10 0.78667
12 11 1.94000
13 12 0.76667
14 13 1.53500
15 14 1.35000
16 15 1.74000
17 16 1.40600
18 17 1.00125
19 18 1.07000
20 19 1.44000
21 20 0.52750
22 21 1.62167
23 22 0.96000
24 23 0.91000


/*Visualizing Hourly Consumption*/

proc sgplot data=hourly_consumption;

    series x=Hour y=Avg_Energy;

    title "Average Energy Consumption by Hour";

run;

Log:

NOTE: PROCEDURE SGPLOT used (Total process time):

      real time           0.59 seconds

      cpu time            0.06 seconds


NOTE: Listing image output written to SGPlot3.png.

NOTE: There were 24 observations read from the data set WORK.HOURLY_CONSUMPTION.


/*Creating Views for Efficient Access*/

proc sql;

    create view high_consumption as

    select * from smart_home_energy

    where Energy_Consumption > 1.5;

quit;

proc print;run;

Output:

Obs Timestamp Home_ID Room Appliance Energy_Consumption
1 13SEP2015:08:21:19 1 Living Room Refrigerator 1.65
2 05SEP2015:03:51:42 1 Living Room Heater 1.68
3 06SEP2015:21:51:23 1 Bedroom Air Conditioner 1.78
4 03SEP2015:21:30:35 1 Bathroom Air Conditioner 1.99
5 05SEP2015:21:49:37 1 Bathroom Refrigerator 1.55
6 04SEP2015:05:52:36 2 Kitchen Refrigerator 2.00
7 07SEP2015:16:43:41 2 Bedroom Air Conditioner 1.88
8 08SEP2015:17:14:08 2 Bedroom Heater 1.56
9 10SEP2015:19:24:49 3 Living Room Air Conditioner 1.66
10 13SEP2015:02:01:23 3 Living Room Heater 1.83
11 07SEP2015:01:56:26 3 Living Room Washing Machine 1.93
12 07SEP2015:11:59:55 3 Kitchen Air Conditioner 1.94
13 12SEP2015:15:14:23 3 Kitchen Refrigerator 1.89
14 13SEP2015:15:13:26 3 Bedroom Refrigerator 1.59
15 03SEP2015:22:24:06 3 Bedroom Washing Machine 1.72
16 09SEP2015:02:41:22 3 Bathroom Air Conditioner 1.90
17 06SEP2015:03:29:11 4 Living Room Refrigerator 1.69
18 10SEP2015:13:09:59 4 Bedroom Heater 1.94
19 10SEP2015:16:20:47 4 Bedroom Washing Machine 1.90
20 13SEP2015:21:02:35 4 Bathroom Refrigerator 1.69
21 06SEP2015:06:34:35 5 Living Room Washing Machine 1.62
22 04SEP2015:05:18:34 5 Kitchen Heater 1.85
23 07SEP2015:21:41:27 5 Kitchen Washing Machine 1.55
24 12SEP2015:14:02:26 5 Bedroom Heater 1.72
25 03SEP2015:03:07:59 5 Bathroom Refrigerator 1.79
26 13SEP2015:06:35:33 5 Bathroom Heater 1.51


/*Dynamic Reporting with Macros*/

proc sql noprint;

    select distinct Room into :room_list separated by ' ' from smart_home_energy;

quit;


%macro room_reports;

    %let room_list = Living Room|Kitchen|Bedroom|Bathroom;

    %let count = %sysfunc(countw(&room_list, |));

    %do i = 1 %to &count;

        %let room = %scan(&room_list, &i, |);

        %let safe_room = %sysfunc(translate(&room, _, %str( )));

        proc sql;

            create table report_&safe_room as

            select * from smart_home_energy

            where Room = "&room";

        quit;

        proc print data=report_&safe_room; run;

    %end;

%mend;


options mlogic symbolgen;

%room_reports;

Output:

Obs Timestamp Home_ID Room Appliance Energy_Consumption
1 05SEP2015:16:31:57 1 Living Room Air Conditioner 1.01
2 13SEP2015:08:21:19 1 Living Room Refrigerator 1.65
3 05SEP2015:03:51:42 1 Living Room Heater 1.68
4 09SEP2015:09:00:54 1 Living Room Washing Machine 0.66
5 05SEP2015:00:14:19 2 Living Room Air Conditioner 1.34
6 07SEP2015:08:05:29 2 Living Room Refrigerator 1.31
7 09SEP2015:17:58:10 2 Living Room Heater 0.72
8 04SEP2015:05:45:24 2 Living Room Washing Machine 1.15
9 10SEP2015:19:24:49 3 Living Room Air Conditioner 1.66
10 09SEP2015:07:46:07 3 Living Room Refrigerator 1.38
11 13SEP2015:02:01:23 3 Living Room Heater 1.83
12 07SEP2015:01:56:26 3 Living Room Washing Machine 1.93
13 02SEP2015:18:36:20 4 Living Room Air Conditioner 1.24
14 06SEP2015:03:29:11 4 Living Room Refrigerator 1.69
15 09SEP2015:00:12:20 4 Living Room Heater 0.33
16 12SEP2015:22:54:17 4 Living Room Washing Machine 0.20
17 08SEP2015:17:53:51 5 Living Room Air Conditioner 1.20
18 11SEP2015:16:09:47 5 Living Room Refrigerator 1.02
19 04SEP2015:18:25:48 5 Living Room Heater 1.37
20 06SEP2015:06:34:35 5 Living Room Washing Machine 1.62


Obs Timestamp Home_ID Room Appliance Energy_Consumption
1 05SEP2015:10:37:46 1 Kitchen Air Conditioner 0.54
2 05SEP2015:21:52:44 1 Kitchen Refrigerator 1.17
3 13SEP2015:05:10:10 1 Kitchen Heater 0.41
4 03SEP2015:03:18:31 1 Kitchen Washing Machine 0.89
5 09SEP2015:05:23:56 2 Kitchen Air Conditioner 0.45
6 04SEP2015:05:52:36 2 Kitchen Refrigerator 2.00
7 09SEP2015:02:52:11 2 Kitchen Heater 1.48
8 08SEP2015:04:06:30 2 Kitchen Washing Machine 1.37
9 07SEP2015:11:59:55 3 Kitchen Air Conditioner 1.94
10 12SEP2015:15:14:23 3 Kitchen Refrigerator 1.89
11 13SEP2015:00:01:14 3 Kitchen Heater 0.49
12 05SEP2015:20:29:22 3 Kitchen Washing Machine 0.11
13 08SEP2015:12:06:31 4 Kitchen Air Conditioner 0.96
14 12SEP2015:10:49:28 4 Kitchen Refrigerator 0.88
15 05SEP2015:01:03:48 4 Kitchen Heater 1.03
16 09SEP2015:17:53:09 4 Kitchen Washing Machine 1.27
17 11SEP2015:12:21:26 5 Kitchen Air Conditioner 0.06
18 05SEP2015:07:34:33 5 Kitchen Refrigerator 0.72
19 04SEP2015:05:18:34 5 Kitchen Heater 1.85
20 07SEP2015:21:41:27 5 Kitchen Washing Machine 1.55


Obs Timestamp Home_ID Room Appliance Energy_Consumption
1 06SEP2015:21:51:23 1 Bedroom Air Conditioner 1.78
2 05SEP2015:19:06:15 1 Bedroom Refrigerator 1.22
3 11SEP2015:13:40:58 1 Bedroom Heater 1.13
4 12SEP2015:17:33:42 1 Bedroom Washing Machine 0.58
5 07SEP2015:16:43:41 2 Bedroom Air Conditioner 1.88
6 12SEP2015:14:50:10 2 Bedroom Refrigerator 0.98
7 08SEP2015:17:14:08 2 Bedroom Heater 1.56
8 03SEP2015:17:01:35 2 Bedroom Washing Machine 0.41
9 05SEP2015:16:56:39 3 Bedroom Air Conditioner 1.22
10 13SEP2015:15:13:26 3 Bedroom Refrigerator 1.59
11 05SEP2015:02:39:37 3 Bedroom Heater 0.80
12 03SEP2015:22:24:06 3 Bedroom Washing Machine 1.72
13 04SEP2015:17:37:51 4 Bedroom Air Conditioner 1.33
14 07SEP2015:04:18:26 4 Bedroom Refrigerator 0.26
15 10SEP2015:13:09:59 4 Bedroom Heater 1.94
16 10SEP2015:16:20:47 4 Bedroom Washing Machine 1.90
17 09SEP2015:20:05:41 5 Bedroom Air Conditioner 0.05
18 11SEP2015:02:33:26 5 Bedroom Refrigerator 0.31
19 12SEP2015:14:02:26 5 Bedroom Heater 1.72
20 08SEP2015:08:52:25 5 Bedroom Washing Machine 0.40


Obs Timestamp Home_ID Room Appliance Energy_Consumption
1 03SEP2015:21:30:35 1 Bathroom Air Conditioner 1.99
2 05SEP2015:21:49:37 1 Bathroom Refrigerator 1.55
3 11SEP2015:05:09:30 1 Bathroom Heater 0.57
4 13SEP2015:12:26:20 1 Bathroom Washing Machine 1.28
5 05SEP2015:03:28:20 2 Bathroom Air Conditioner 1.21
6 10SEP2015:18:50:18 2 Bathroom Refrigerator 0.60
7 09SEP2015:23:29:22 2 Bathroom Heater 0.91
8 05SEP2015:06:49:38 2 Bathroom Washing Machine 0.51
9 09SEP2015:02:41:22 3 Bathroom Air Conditioner 1.90
10 08SEP2015:20:45:43 3 Bathroom Refrigerator 1.11
11 04SEP2015:20:24:52 3 Bathroom Heater 0.84
12 07SEP2015:10:57:23 3 Bathroom Washing Machine 0.94
13 04SEP2015:05:42:08 4 Bathroom Air Conditioner 0.92
14 13SEP2015:21:02:35 4 Bathroom Refrigerator 1.69
15 13SEP2015:00:32:37 4 Bathroom Heater 0.76
16 12SEP2015:01:06:33 4 Bathroom Washing Machine 0.80
17 04SEP2015:00:52:45 5 Bathroom Air Conditioner 0.35
18 03SEP2015:03:07:59 5 Bathroom Refrigerator 1.79
19 13SEP2015:06:35:33 5 Bathroom Heater 1.51
20 06SEP2015:17:06:13 5 Bathroom Washing Machine 0.94

/*Exporting Data*/

proc export data=anomalies

    outfile='anomalies.csv'

    dbms=csv

    replace;

run;

Question:

What's wrong in this code? comment down...

proc sql;

    create total_consumption as

    select Home_ID, sum(Energy_Consumption) as Total_Energy

    from smart_home_energy

    group by Home_ID;

quit;

proc print;run;


PRACTICE AND COMMENT YOUR CODE: 

--->PLEASE FOLLOW OUR BLOG FOR MORE UPDATES.

PLEASE FOLLOW OUR TELEGRAM CHANNEL CLICK HERE

PLEASE FOLLOW OUR FACEBOOK PAGE  CLICK HERE

Comments

Post a Comment