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

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

on

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