213.ANALYZING TOP CRIME SCENES WORLDWIDE USING PROC SQL AND PROC MACRO IN SAS FOR PATTERN DETECTION AND REGION BASED COMPARISONS AND CRIME CATEGORY ANALYSIS AND TRENDS

-

ANALYZING TOP CRIME SCENES WORLDWIDE USING PROC SQL AND PROC MACRO IN SAS FOR PATTERN DETECTION AND REGION BASED COMPARISONS AND CRIME CATEGORY ANALYSIS AND TRENDS

/*Creating a unique dataset about different types of top crime scenes worldwide.*/

Step 1: Creating the Dataset Using PROC SQL

 Define a dataset called Top_Crime_Scenes with fields:

Crime_ID: Unique numeric identifier

Country: Country name

City: City or metro region

Crime_Type: Category (e.g., Homicide, Robbery, Kidnapping)

Scene_Type: Location of crime (e.g., Street, Home, Bank)

Victims_Count: Number of victims

Year: Year of occurrence

Solved: Whether the case is solved (Yes/No)

Notorious_Rating: Severity or media impact (1–10 scale)


proc sql;

    create table Top_Crime_Scenes (

        Crime_ID           num,

        Country            char(20),

        City               char(20),

        Crime_Type         char(25),

        Scene_Type         char(25),

        Victims_Count      num,

        Year               num,

        Solved             char(3),

        Notorious_Rating   num

    );

quit;

proc sql;

    insert into Top_Crime_Scenes 

    values(1, 'USA', 'Chicago', 'Homicide', 'Street', 3, 2019, 'No', 9);

    insert into Top_Crime_Scenes 

    values(2, 'Mexico', 'Juarez', 'Drug Trafficking', 'Warehouse', 0, 2020, 'No', 10);

    insert into Top_Crime_Scenes 

    values(3, 'India', 'Delhi', 'Robbery', 'Bank', 1, 2021, 'Yes', 7);

    insert into Top_Crime_Scenes 

    values(4, 'Brazil', 'Rio', 'Kidnapping', 'Forest', 2, 2018, 'No', 8);

    insert into Top_Crime_Scenes 

    values(5, 'Russia', 'Moscow', 'Assassination', 'Hotel', 1, 2019, 'Yes', 8);

    insert into Top_Crime_Scenes 

    values(6, 'South Africa', 'Cape Town', 'Homicide', 'Home', 4, 2020, 'Yes', 9);

    insert into Top_Crime_Scenes 

    values(7, 'UK', 'London', 'Robbery', 'ATM', 0, 2019, 'Yes', 6);

    insert into Top_Crime_Scenes 

    values(8, 'Colombia', 'Medellin', 'Drug Trafficking', 'Jungle', 0, 2021, 'No', 10);

    insert into Top_Crime_Scenes 

    values(9, 'Japan', 'Tokyo', 'Cybercrime', 'Data Center', 0, 2022, 'Yes', 7);

    insert into Top_Crime_Scenes

    values(10, 'Australia', 'Sydney', 'Arson', 'Warehouse', 2, 2017, 'Yes', 5);

    insert into Top_Crime_Scenes 

    values(11, 'Germany', 'Berlin', 'Kidnapping', 'School', 1, 2020, 'No', 7);

    insert into Top_Crime_Scenes

    values(12, 'USA', 'New York', 'Homicide', 'Apartment', 2, 2022, 'Yes', 8);

    insert into Top_Crime_Scenes

    values(13, 'Canada', 'Toronto', 'Cybercrime', 'Server Farm', 0, 2021, 'Yes', 6);

    insert into Top_Crime_Scenes

    values(14, 'Italy', 'Naples', 'Robbery', 'Store', 1, 2019, 'No', 7);

    insert into Top_Crime_Scenes 

    values(15, 'Nigeria', 'Lagos', 'Terrorism', 'Market', 10, 2020, 'No', 10);

    insert into Top_Crime_Scenes

    values(16, 'Pakistan', 'Karachi', 'Bombing', 'Bus Station', 7, 2019, 'No', 10);

    insert into Top_Crime_Scenes 

    values(17, 'France', 'Paris', 'Assassination', 'Office', 1, 2018, 'Yes', 8);

    insert into Top_Crime_Scenes 

    values(18, 'China', 'Beijing', 'Homicide', 'School', 2, 2021, 'Yes', 9);

quit;

proc print;run;

Output:

Obs Crime_ID Country City Crime_Type Scene_Type Victims_Count Year Solved Notorious_Rating
1 1 USA Chicago Homicide Street 3 2019 No 9
2 2 Mexico Juarez Drug Trafficking Warehouse 0 2020 No 10
3 3 India Delhi Robbery Bank 1 2021 Yes 7
4 4 Brazil Rio Kidnapping Forest 2 2018 No 8
5 5 Russia Moscow Assassination Hotel 1 2019 Yes 8
6 6 South Africa Cape Town Homicide Home 4 2020 Yes 9
7 7 UK London Robbery ATM 0 2019 Yes 6
8 8 Colombia Medellin Drug Trafficking Jungle 0 2021 No 10
9 9 Japan Tokyo Cybercrime Data Center 0 2022 Yes 7
10 10 Australia Sydney Arson Warehouse 2 2017 Yes 5
11 11 Germany Berlin Kidnapping School 1 2020 No 7
12 12 USA New York Homicide Apartment 2 2022 Yes 8
13 13 Canada Toronto Cybercrime Server Farm 0 2021 Yes 6
14 14 Italy Naples Robbery Store 1 2019 No 7
15 15 Nigeria Lagos Terrorism Market 10 2020 No 10
16 16 Pakistan Karachi Bombing Bus Station 7 2019 No 10
17 17 France Paris Assassination Office 1 2018 Yes 8
18 18 China Beijing Homicide School 2 2021 Yes 9


Step 2: Macro to Count Crimes by Type

%macro count_by_crimetype(crime);

    proc sql;

        title "Count of &crime Incidents";

        select count(*) as Total_Crimes

        from Top_Crime_Scenes

        where Crime_Type = "&crime";

    quit;

%mend;


%count_by_crimetype(Homicide);

Output:

Count of Homicide Incidents

Total_Crimes
4

%count_by_crimetype(Robbery);

Output:

Count of Robbery Incidents

Total_Crimes
3

%count_by_crimetype(Drug Trafficking);

Output:

Count of Drug Trafficking Incidents

Total_Crimes
2

Step 3: Macro for Country-Based Summary

%macro summary_by_country(country);

    proc sql;

        title "Summary for &country";

        select Crime_Type, count(*) as Total,

               avg(Victims_Count) as Avg_Victims,

               avg(Notorious_Rating) as Avg_Impact

        from Top_Crime_Scenes

        where Country = "&country"

        group by Crime_Type;

    quit;

%mend;


%summary_by_country(USA);

Output:

Summary for USA

Crime_Type Total Avg_Victims Avg_Impact
Homicide 2 2.5 8.5

%summary_by_country(India);

Output:

Summary for India

Crime_Type Total Avg_Victims Avg_Impact
Robbery 1 1 7

%summary_by_country(Mexico);

Output:

Summary for Mexico

Crime_Type Total Avg_Victims Avg_Impact
Drug Trafficking 1 0 10

Step 4: Macro Looping Over Multiple Countries

%macro loop_countries;

    %let countries = USA India Mexico Brazil Nigeria;

    %do i = 1 %to %sysfunc(countw(&countries));

        %let c = %scan(&countries, &i);

        %summary_by_country(&c);

    %end;

%mend;


%loop_countries;

Output:

Summary for USA

Crime_Type Total Avg_Victims Avg_Impact
Homicide 2 2.5 8.5

Summary for India

Crime_Type Total Avg_Victims Avg_Impact
Robbery 1 1 7

Summary for Mexico

Crime_Type Total Avg_Victims Avg_Impact
Drug Trafficking 1 0 10


Summary for Brazil

Crime_Type Total Avg_Victims Avg_Impact
Kidnapping 1 2 8

Summary for Nigeria

Crime_Type Total Avg_Victims Avg_Impact
Terrorism 1 10 10

Step 5: Ranking Notorious Crimes

proc sql outobs=5;

    title "Top 5 Most Notorious Crimes Worldwide";

    select Crime_ID, Country, City, Crime_Type, Notorious_Rating

    from Top_Crime_Scenes

    order by Notorious_Rating desc;

quit;

Output:

Top 5 Most Notorious Crimes Worldwide

Crime_ID Country City Crime_Type Notorious_Rating
15 Nigeria Lagos Terrorism 10
16 Pakistan Karachi Bombing 10
2 Mexico Juarez Drug Trafficking 10
8 Colombia Medellin Drug Trafficking 10
18 China Beijing Homicide 9

Step 6: Scene Type Distribution

proc sql;

    title "Distribution of Scene Types";

    select Scene_Type, count(*) as Count

    from Top_Crime_Scenes

    group by Scene_Type;

quit;

Output:

Distribution of Scene Types

Scene_Type Count
ATM 1
Apartment 1
Bank 1
Bus Station 1
Data Center 1
Forest 1
Home 1
Hotel 1
Jungle 1
Market 1
Office 1
School 2
Server Farm 1
Store 1
Street 1
Warehouse 2

Step 7: Unsolved Crimes by Region

proc sql;

    title "Unsolved Crimes by Country";

    select Country, count(*) as Unsolved_Count

    from Top_Crime_Scenes

    where Solved = 'No'

    group by Country;

quit;

Output:

Unsolved Crimes by Country

Country Unsolved_Count
Brazil 1
Colombia 1
Germany 1
Italy 1
Mexico 1
Nigeria 1
Pakistan 1
USA 1

Step 8: Year-wise Crime Trend

proc sql;

    title "Crimes Over the Years";

    select Year, count(*) as Crime_Count

    from Top_Crime_Scenes

    group by Year

    order by Year;

quit;

Output:

Crimes Over the Years

Year Crime_Count
2017 1
2018 2
2019 5
2020 4
2021 4
2022 2

 Step 9: Table Summary By Country

 proc sql;

    create table Summary_By_Country as

    select Country, Crime_Type, count(*) as Count

    from Top_Crime_Scenes

    group by Country, Crime_Type;

quit;

Output:

Obs Country Crime_Type Count
1 Australia Arson 1
2 Brazil Kidnapping 1
3 Canada Cybercrime 1
4 China Homicide 1
5 Colombia Drug Trafficking 1
6 France Assassination 1
7 Germany Kidnapping 1
8 India Robbery 1
9 Italy Robbery 1
10 Japan Cybercrime 1
11 Mexico Drug Trafficking 1
12 Nigeria Terrorism 1
13 Pakistan Bombing 1
14 Russia Assassination 1
15 South Africa Homicide 1
16 UK Robbery 1
17 USA Homicide 2




To Visit My Previous Onilne Retail Dataset:Click Here
To Visit My Previous Assignment Statement:Click Here
To Visit My Previous Online Courses Dataset:Click Here
To Visit My Previous Advanced Clinical Trials:Click Here


PRACTICE AND COMMENT YOUR OUTPUT: 

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

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 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- INTRODUCTION Table of Con...
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