218.ANALYZING HAUNTED HOUSES WORLDWIDE USING PROC SQL MACROS IN SAS WITH SUPERNATURAL HISTORY GHOST REPORTS TOURIST RATINGS

-

ANALYZING HAUNTED HOUSES WORLDWIDE USING PROC SQL MACROS IN SAS WITH SUPERNATURAL HISTORY GHOST REPORTS TOURIST RATINGS

 /* Creating a unique project in SAS focused on "Haunted Houses Worldwide*/

1. CREATING THE DATASET USING PROC SQL

/* Creating a Haunted Houses Dataset using PROC SQL */

proc sql;

    create table Haunted_Houses (

        House_ID num,

        House_Name char(40),

        Country char(20),

        City char(25),

        Year_Built num,

        Type_Of_Haunting char(30),

        Ghost_Reports num,

        Haunted_Status char(15),

        Popularity_Rating num,

        Last_Paranormal_Event char(10)

    );


    insert into Haunted_Houses

    values (1, 'Bhangarh Fort', 'India', 'Rajasthan', 1573, 'Cursed Spirits', 210, 'Active', 4.8, '2024-06-01')

    values (2, 'Monte Cristo Homestead', 'Australia', 'New South Wales', 1885, 'Poltergeist', 95, 'Active', 4.5, '2023-11-21')

    values (3, 'Winchester Mystery House', 'USA', 'San Jose', 1884, 'Architectural Maze', 300, 'Active', 4.7, '2024-02-14')

    values (4, 'Château de Brissac', 'France', 'Brissac-Quincé', 1502, 'Lady in Green', 150, 'Active', 4.6, '2023-09-09')

    values (5, 'Aokigahara Forest', 'Japan', 'Fujinomiya', 1600, 'Wandering Spirits', 120, 'Active', 4.2, '2024-01-19')

    values (6, 'Ancient Ram Inn', 'UK', 'Gloucestershire', 1145, 'Devilish Forces', 180, 'Active', 4.9, '2024-03-31')

    values (7, 'Poveglia Island', 'Italy', 'Venice', 1720, 'Plague Victims', 250, 'Sealed', 4.1, '2023-07-01')

    values (8, 'Banff Springs Hotel', 'Canada', 'Alberta', 1888, 'Bride Ghost', 140, 'Active', 4.3, '2024-04-15')

    values (9, 'Casa Matusita', 'Peru', 'Lima', 1800, 'Psychic Disturbance', 110, 'Active', 4.4, '2023-12-22')

    values (10, 'Raynham Hall', 'UK', 'Norfolk', 1600, 'Brown Lady', 85, 'Active', 4.6, '2023-10-31')

    values (11, 'Helltown', 'USA', 'Ohio', 1800, 'Evacuated Ghosts', 75, 'Abandoned', 3.9, '2022-08-20')

    values (12, 'The Langham Hotel', 'UK', 'London', 1865, 'Royal Ghost', 130, 'Active', 4.8, '2024-06-05')

    values (13, 'Island of Dolls', 'Mexico', 'Xochimilco', 1950, 'Doll Hauntings', 160, 'Active', 4.3, '2024-02-02')

    values (14, 'Lawang Sewu', 'Indonesia', 'Semarang', 1904, 'Wartime Spirits', 190, 'Active', 4.0, '2023-05-10')

    values (15, 'Fairmont Banff Springs', 'Canada', 'Banff', 1888, 'Bellhop Ghost', 90, 'Active', 4.5, '2024-01-05')

    values (16, 'Dragsholm Castle', 'Denmark', 'Hørve', 1215, 'White Lady', 100, 'Active', 4.4, '2023-08-08')

    values (17, 'Chillingham Castle', 'UK', 'Northumberland', 1290, 'Execution Ghosts', 200, 'Active', 4.9, '2024-03-01');

quit;

proc print;run;

Output:

Obs House_ID House_Name Country City Year_Built Type_Of_Haunting Ghost_Reports Haunted_Status Popularity_Rating Last_Paranormal_Event
1 1 Bhangarh Fort India Rajasthan 1573 Cursed Spirits 210 Active 4.8 2024-06-01
2 2 Monte Cristo Homestead Australia New South Wales 1885 Poltergeist 95 Active 4.5 2023-11-21
3 3 Winchester Mystery House USA San Jose 1884 Architectural Maze 300 Active 4.7 2024-02-14
4 4 Château de Brissac France Brissac-Quincé 1502 Lady in Green 150 Active 4.6 2023-09-09
5 5 Aokigahara Forest Japan Fujinomiya 1600 Wandering Spirits 120 Active 4.2 2024-01-19
6 6 Ancient Ram Inn UK Gloucestershire 1145 Devilish Forces 180 Active 4.9 2024-03-31
7 7 Poveglia Island Italy Venice 1720 Plague Victims 250 Sealed 4.1 2023-07-01
8 8 Banff Springs Hotel Canada Alberta 1888 Bride Ghost 140 Active 4.3 2024-04-15
9 9 Casa Matusita Peru Lima 1800 Psychic Disturbance 110 Active 4.4 2023-12-22
10 10 Raynham Hall UK Norfolk 1600 Brown Lady 85 Active 4.6 2023-10-31
11 11 Helltown USA Ohio 1800 Evacuated Ghosts 75 Abandoned 3.9 2022-08-20
12 12 The Langham Hotel UK London 1865 Royal Ghost 130 Active 4.8 2024-06-05
13 13 Island of Dolls Mexico Xochimilco 1950 Doll Hauntings 160 Active 4.3 2024-02-02
14 14 Lawang Sewu Indonesia Semarang 1904 Wartime Spirits 190 Active 4.0 2023-05-10
15 15 Fairmont Banff Springs Canada Banff 1888 Bellhop Ghost 90 Active 4.5 2024-01-05
16 16 Dragsholm Castle Denmark Hørve 1215 White Lady 100 Active 4.4 2023-08-08
17 17 Chillingham Castle UK Northumberland 1290 Execution Ghosts 200 Active 4.9 2024-03-01


2. EXPLORATORY ANALYSIS USING PROC SQL

A. Top 5 Haunted Houses by Popularity

proc sql outobs=5;

    title "Top 5 Haunted Houses by Popularity";

    select House_Name, Country, Popularity_Rating

    from Haunted_Houses

    order by Popularity_Rating desc;

quit;

Output:

Top 5 Haunted Houses by Popularity

House_Name Country Popularity_Rating
Ancient Ram Inn UK 4.9
Chillingham Castle UK 4.9
The Langham Hotel UK 4.8
Bhangarh Fort India 4.8
Winchester Mystery House USA 4.7

B. Count of Haunted Houses by Country

proc sql;

    title "Count of Haunted Houses by Country";

    select Country, count(*) as Number_of_Haunted_Houses

    from Haunted_Houses

    group by Country

    order by Number_of_Haunted_Houses desc;

quit;

Output:

Count of Haunted Houses by Country

Country Number_of_Haunted_Houses
UK 4
USA 2
Canada 2
Italy 1
Denmark 1
Indonesia 1
India 1
Peru 1
Australia 1
Mexico 1
France 1
Japan 1

C. Haunted Houses Still Active

proc sql;

    title "Active Haunted Houses";

    select House_Name, Country, Type_Of_Haunting, Haunted_Status

    from Haunted_Houses

    where Haunted_Status = 'Active';

quit;

Output:

Active Haunted Houses

House_Name Country Type_Of_Haunting Haunted_Status
Bhangarh Fort India Cursed Spirits Active
Monte Cristo Homestead Australia Poltergeist Active
Winchester Mystery House USA Architectural Maze Active
Château de Brissac France Lady in Green Active
Aokigahara Forest Japan Wandering Spirits Active
Ancient Ram Inn UK Devilish Forces Active
Banff Springs Hotel Canada Bride Ghost Active
Casa Matusita Peru Psychic Disturbance Active
Raynham Hall UK Brown Lady Active
The Langham Hotel UK Royal Ghost Active
Island of Dolls Mexico Doll Hauntings Active
Lawang Sewu Indonesia Wartime Spirits Active
Fairmont Banff Springs Canada Bellhop Ghost Active
Dragsholm Castle Denmark White Lady Active
Chillingham Castle UK Execution Ghosts Active

D. Haunted Houses with More Than 150 Ghost Reports

proc sql;

    title "Haunted Houses with High Ghost Activity";

    select House_Name, City, Ghost_Reports

    from Haunted_Houses

    where Ghost_Reports > 150

    order by Ghost_Reports desc;

quit;

Output:

Haunted Houses with High Ghost Activity

House_Name City Ghost_Reports
Winchester Mystery House San Jose 300
Poveglia Island Venice 250
Bhangarh Fort Rajasthan 210
Chillingham Castle Northumberland 200
Lawang Sewu Semarang 190
Ancient Ram Inn Gloucestershire 180
Island of Dolls Xochimilco 160

3. MACROS FOR AUTOMATED ANALYSIS

A. Macro to Filter by Country

%macro haunted_by_country(ctry);

    proc sql;

        title "Haunted Houses in &ctry";

        select House_Name, City, Type_Of_Haunting, Ghost_Reports

        from Haunted_Houses

        where Country = "&ctry";

    quit;

%mend;


%haunted_by_country(UK);

Output:

Haunted Houses in UK

House_Name City Type_Of_Haunting Ghost_Reports
Ancient Ram Inn Gloucestershire Devilish Forces 180
Raynham Hall Norfolk Brown Lady 85
The Langham Hotel London Royal Ghost 130
Chillingham Castle Northumberland Execution Ghosts 200

%haunted_by_country(Canada);

Output:

Haunted Houses in Canada

House_Name City Type_Of_Haunting Ghost_Reports
Banff Springs Hotel Alberta Bride Ghost 140
Fairmont Banff Springs Banff Bellhop Ghost 90

B. Macro for Ratings above Threshold

%macro high_rating(min_rating);

    proc sql;

        title "Haunted Houses with Popularity Rating > &min_rating";

        select House_Name, Country, Popularity_Rating

        from Haunted_Houses

        where Popularity_Rating > &min_rating

        order by Popularity_Rating desc;

    quit;

%mend;


%high_rating(4.5);

Output:

Haunted Houses with Popularity Rating > 4.5

House_Name Country Popularity_Rating
Chillingham Castle UK 4.9
Ancient Ram Inn UK 4.9
The Langham Hotel UK 4.8
Bhangarh Fort India 4.8
Winchester Mystery House USA 4.7
Château de Brissac France 4.6
Raynham Hall UK 4.6

C. Macro to List Hauntings by Type

%macro haunt_by_type(htype);

    proc sql;

        title "Haunted Houses with &htype Hauntings";

        select House_Name, Country, City, Ghost_Reports

        from Haunted_Houses

        where Type_Of_Haunting = "&htype";

    quit;

%mend;


%haunt_by_type(Poltergeist);

Output:

Haunted Houses with Poltergeist Hauntings

House_Name Country City Ghost_Reports
Monte Cristo Homestead Australia New South Wales 95

%haunt_by_type(Lady in Green);

Output:

Haunted Houses with Lady in Green Hauntings

House_Name Country City Ghost_Reports
Château de Brissac France Brissac-Quincé 150

4. DERIVED ANALYSIS USING SQL

A. Average Ghost Reports by Country

proc sql;

    title "Average Ghost Reports by Country";

    select Country, avg(Ghost_Reports) as Avg_Ghosts

    from Haunted_Houses

    group by Country;

quit;

Output:

Average Ghost Reports by Country

Country Avg_Ghosts
Australia 95
Canada 115
Denmark 100
France 150
India 210
Indonesia 190
Italy 250
Japan 120
Mexico 160
Peru 110
UK 148.75
USA 187.5

B. Haunted House Age (Current Year - Year Built)

proc sql;

    title "Haunted House Age Analysis";

    select House_Name, Country, (year(today()) - Year_Built) as Age_Years

    from Haunted_Houses

    order by Age_Years desc;

quit;

Output:

Haunted House Age Analysis

House_Name Country Age_Years
Ancient Ram Inn UK 870
Dragsholm Castle Denmark 800
Chillingham Castle UK 725
Château de Brissac France 513
Bhangarh Fort India 442
Aokigahara Forest Japan 415
Raynham Hall UK 415
Poveglia Island Italy 295
Helltown USA 215
Casa Matusita Peru 215
The Langham Hotel UK 150
Winchester Mystery House USA 131
Monte Cristo Homestead Australia 130
Fairmont Banff Springs Canada 127
Banff Springs Hotel Canada 127
Lawang Sewu Indonesia 111
Island of Dolls Mexico 65

5. ADVANCED ANALYSIS: YEAR-WISE PARANORMAL TRENDS

Count of Last Paranormal Events per Year

proc sql;

    title "Number of Paranormal Events by Year";

    select substr(Last_Paranormal_Event, 1, 4) as Year,

           count(*) as Paranormal_Events

    from Haunted_Houses

    group by Year

    order by Year desc;

quit;

Output:

Number of Paranormal Events by Year

Year Paranormal_Events
2024 9
2023 7
2022 1

6. MACRO FOR MULTI-CONDITION FILTER

%macro filter_haunted(country, min_ghosts, min_rating);

    proc sql;

        title "Filtered Haunted Houses in &country";

        select House_Name, City, Ghost_Reports, Popularity_Rating

        from Haunted_Houses

        where Country = "&country"

        and Ghost_Reports >= &min_ghosts

        and Popularity_Rating >= &min_rating;

    quit;

%mend;


%filter_haunted(UK, 100, 4.5);

Output:

Filtered Haunted Houses in UK

House_Name City Ghost_Reports Popularity_Rating
Ancient Ram Inn Gloucestershire 180 4.9
The Langham Hotel London 130 4.8
Chillingham Castle Northumberland 200 4.9

 7. FINAL SUMMARY

Total Haunted Houses Worldwide

proc sql;

    title "Total Count of Haunted Houses";

    select count(*) as Total_Haunted_Houses

    from Haunted_Houses;

quit;

Output:

Total Count of Haunted Houses

Total_Haunted_Houses
17




To Visit My Previous E-Commerce Dataset:Click Here
To Visit My Previous Length,Input,Retain Statements:Click Here
To Visit My Previous Urban Traffic Dataset:Click Here
To Visit My Previous Home Energy Consumption Dataset:Click Here





--->PLEASE FOLLOW OUR BLOG FOR MORE INFORMATION.
--->PLEASE DO COMMENTS AND SHARE OUR BLOG.

PLEASE FOLLOW OUR TELEGRAM CHANNEL CLICK HERE

PLEASE FOLLOW OUR FACEBOOK PAGE  CLICK HERE

PLEASE FOLLOW OUR INSTAGRAM PAGE CLICK HERE






Comments

Post a Comment

Popular posts from this blog

409.Can We Build a Reliable Emergency Services Analytics & Fraud Detection System in SAS While Identifying and Fixing Intentional Errors?

-
Image
Building a Reliable Emergency Services Analytics & Fraud Detection System in SAS Through Systematic Error Identification and Correction -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- HERE WE USED THESE SAS STATEMENTS AND FUNCTIONS FOR THIS PROJECT: DATA STEP | SET | INPUT | DATALINES | LENGTH | FORMAT | IF-THEN-ELSE | MDY | INTCK | INTNX | STRIP | TRIM | CAT | CATX | PROPCASE | UPCASE | LOWCASE | COALESCE | MERGE | PROC SQL | PROC MEANS | PROC UNIVARIATE | PROC FREQ | PROC CORR | PROC SGPLOT | PROC TRANSPOSE | PROC APPEND | PROC DATASETS DELETE | %MACRO / %MEND -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Table of Contents 1. ...
Read more

397.If a satellite has excellent signal strength but very high latency, can it still deliver good quality communication? Why or why not?A Sas Study

-
Image
If a satellite has excellent signal strength but very high latency, can it still deliver good quality communication? Why or why not?A Sas Study -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- HERE IN THIS PROJECT WE USED THESE SAS STATEMENTS — DATA STEP | PROC SQL |  PROC PRINT | PROC SGPLOT | MACROS | PROC CORR | PROC MEANS | PROC FREQ | PROC UNIVARIATE | SET | APPEND | PROC TRANSPOSE | PROC DATASETS DELETE | DATA FUNCTIONS -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- INTRODUCTION Satellite communication is a backbone of modern connectivity—supporting  GPS, TV broadcasting, military operations, disaster management, banking, a...
Read more

401.How Efficient Are Global Data Centers? A Complete SAS Analytics Study

-
Image
How Efficient Are Global Data Centers? A Complete SAS Analytics Study -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- HERE IN THIS PROJECT WE USED THESE SAS STATEMENTS — DATA STEP | PROC SQL |  PROC PRINT | PROC SGPLOT | MACROS | PROC CORR | PROC MEANS | PROC FREQ | PROC UNIVARIATE | APPEND PROC TRANSPOSE | PROC DATASETS DELETE | DATA FUNCTIONS -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1. INTRODUCTION Modern data centers are the backbone of cloud computing, AI, banking, healthcare, and government systems. They consume massive power, require efficient cooling, and must ensure zero downtime . This project simulates global data c...
Read more