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

224.DECADE-WISE ANALYSIS OF MURDER INVESTIGATIONS IN INDIA USING PROC FREQ | PROC SGPLOT | MACROS | STRING FUNCTIONS | CONDITIONAL FILTERING AND DYNAMIC VISUALIZATION TECHNIQUES IN SAS

on

DECADE-WISE ANALYSIS OF MURDER INVESTIGATIONS IN INDIA USING PROC FREQ | PROC SGPLOT | MACROS | STRING FUNCTIONS | CONDITIONAL FILTERING  AND DYNAMIC VISUALIZATION  TECHNIQUES IN SAS

 /*Constructs a fictional yet realistic‐looking dataset of murder investigations in India for the years 1900‑2025*/

1. Setting the Stage – Global OPTIONS

options nocenter nodate nonumber nosource

        linesize=160 pagesize=50

        fullstimer ;         /* track performance */


2. Crafting the Core Data Set 

2A.Custom formats for clarity

proc format;

   value $statfmt

      'OPEN'  = 'Investigation Open'

      'COLD'  = 'Unsolved/Cold Case'

      'CLSD'  = 'Solved – Closed';

   value severity 1='Low' 

                  2='Moderate' 

                  3='High'

                  4='Critical';

run;

Log:

NOTE: Format $STATFMT has been output.

NOTE: Format SEVERITY has been output.


NOTE: PROCEDURE FORMAT used (Total process time):

      real time           0.07 seconds

      user cpu time       0.00 seconds

      system cpu time     0.01 seconds

      memory              133.50k

      OS Memory           7124.00k

      Timestamp           14/09/2015 12:05:52 AM

      Step Count                        1  Switch Count  0


2B.Build the data set WORK.INDIA_MURDER_INVEST

data work.india_murder_invest;

   length Case_ID $10 State $20 City $18 Method $15

          Investigator $25 Status $4 ;

   format Case_Open_Date Case_Close_Date date9.

          Status $statfmt. Severity severity.;

   infile datalines dsd truncover;  /* allow commas inside fields */


   input Case_ID $ State :$20. City :$18. Year Method :$15. Victims Severity

         Investigator :$25. Status :$4. Close_Days ;


   /* Derive date fields ------------------------------------------------*/

   Case_Open_Date = mdy(1,1,Year); /* assume Jan‑01 of that year */

   if Close_Days ne . then

        Case_Close_Date = intnx('day',Case_Open_Date,Close_Days,'s'); 

 

   /* Build a composite location using CATX (adds a comma automatically) */

   Location = catx(', ', upcase(City), upcase(State));


   /* Flag for century using conditional (IFN) */

   length Century_Flag $4;

   Century_Flag = ifc(year(Case_Open_Date) < 2000,'20th','21st');


   /* Ensure Method trimmed and uppercase */

   Method = upcase(strip(Method));


   /* Auto‑assign missing Close_Days if status still OPEN */

   if Status='OPEN' then Close_Days=.;


   /* Keep only final set of vars in desired order */

   keep Case_ID State City Year Method Victims Severity

        Investigator Status Case_Open_Date Case_Close_Date

        Location Century_Flag Close_Days;


datalines;

IND001,West Bengal,Calcutta,1900,STABBING,1,2,Raja Mukherjee,CLSD,210

IND002,Maharashtra,Mumbai,1915,POISONING,2,3,Sara Deshmukh,COLD,.

IND003,Uttar Pradesh,Kanpur,1928,SHOOTING,1,3,Gopal Tiwari,CLSD,95

IND004,Tamil Nadu,Madras,1936,BLUNT FORCE,4,4,M. Subramaniam,COLD,.

IND005,Bihar,Patna,1944,ARSON,7,4,Lata Sinha,CLSD,300

IND006,Delhi,Delhi,1957,SHOOTING,3,3,Vikram Singh,OPEN,.

IND007,Karnataka,Bangalore,1962,STRANGULATION,1,2,Savitri Rao,CLSD,60

IND008,Gujarat,Ahmedabad,1971,DROWNING,2,2,Mehul Shah,CLSD,44

IND009,Kerala,Kochi,1978,POISONING,1,1,Rajani Menon,CLSD,15

IND010,Rajasthan,Jaipur,1984,SHOOTING,5,4,Mohit Rathore,COLD,.

IND011,Assam,Guwahati,1989,BLUNT FORCE,2,2,K. Gogoi,CLSD,120

IND012,Telangana,Hyderabad,1992,STABBING,3,3,Nandini Reddy,OPEN,.

IND013,Odisha,Bhubaneswar,1997,ARSON,4,3,Sanjay Mahapatra,CLSD,200

IND014,Madhya Pradesh,Bhopal,2001,SHOOTING,6,4,Priya Chaturvedi,CLSD,365

IND015,Punjab,Amritsar,2005,STRANGULATION,1,2,Harpreet Singh,COLD,.

IND016,Chhattisgarh,Raipur,2008,BLUNT FORCE,3,3,Shraddha Verma,CLSD,80

IND017,Haryana,Gurugram,2012,STABBING,2,3,Aryan Malik,CLSD,45

IND018,Delhi,New Delhi,2016,SHOOTING,5,4,Anita Khurana,OPEN,.

IND019,Tamil Nadu,Chennai,2018,POISONING,1,1,V. Ramesh,CLSD,30

IND020,Maharashtra,Pune,2019,ARSON,2,2,Ritika Kulkarni,CLSD,210

IND021,Uttar Pradesh,Noida,2020,BLUNT FORCE,4,4,Manoj Pandey,OPEN,.

IND022,Bihar,Gaya,2021,DROWNING,1,1,Shweta Prasad,CLSD,10

IND023,Karnataka,Mysuru,2022,STRANGULATION,1,2,Raghu Shetty,CLSD,35

IND024,Gujarat,Surat,2023,STABBING,3,3,Dipika Patel,OPEN,.

IND025,Telangana,Warangal,2025,SHOOTING,2,3,Sai Teja,OPEN,.

;

run;

proc print;run;

Output:

Obs Case_ID State City Method Investigator Status Case_Open_Date Case_Close_Date Severity Year Victims Close_Days Location Century_Flag
1 IND001 West Bengal Calcutta STABBING Raja Mukherjee Solved – Closed 01JAN1900 30JUL1900 Moderate 1900 1 210 CALCUTTA, WEST BENGAL 20th
2 IND002 Maharashtra Mumbai POISONING Sara Deshmukh Unsolved/Cold Case 01JAN1915 . High 1915 2 . MUMBAI, MAHARASHTRA 20th
3 IND003 Uttar Pradesh Kanpur SHOOTING Gopal Tiwari Solved – Closed 01JAN1928 05APR1928 High 1928 1 95 KANPUR, UTTAR PRADESH 20th
4 IND004 Tamil Nadu Madras BLUNT FORCE M. Subramaniam Unsolved/Cold Case 01JAN1936 . Critical 1936 4 . MADRAS, TAMIL NADU 20th
5 IND005 Bihar Patna ARSON Lata Sinha Solved – Closed 01JAN1944 27OCT1944 Critical 1944 7 300 PATNA, BIHAR 20th
6 IND006 Delhi Delhi SHOOTING Vikram Singh Investigation Open 01JAN1957 . High 1957 3 . DELHI, DELHI 20th
7 IND007 Karnataka Bangalore STRANGULATION Savitri Rao Solved – Closed 01JAN1962 02MAR1962 Moderate 1962 1 60 BANGALORE, KARNATAKA 20th
8 IND008 Gujarat Ahmedabad DROWNING Mehul Shah Solved – Closed 01JAN1971 14FEB1971 Moderate 1971 2 44 AHMEDABAD, GUJARAT 20th
9 IND009 Kerala Kochi POISONING Rajani Menon Solved – Closed 01JAN1978 16JAN1978 Low 1978 1 15 KOCHI, KERALA 20th
10 IND010 Rajasthan Jaipur SHOOTING Mohit Rathore Unsolved/Cold Case 01JAN1984 . Critical 1984 5 . JAIPUR, RAJASTHAN 20th
11 IND011 Assam Guwahati BLUNT FORCE K. Gogoi Solved – Closed 01JAN1989 01MAY1989 Moderate 1989 2 120 GUWAHATI, ASSAM 20th
12 IND012 Telangana Hyderabad STABBING Nandini Reddy Investigation Open 01JAN1992 . High 1992 3 . HYDERABAD, TELANGANA 20th
13 IND013 Odisha Bhubaneswar ARSON Sanjay Mahapatra Solved – Closed 01JAN1997 20JUL1997 High 1997 4 200 BHUBANESWAR, ODISHA 20th
14 IND014 Madhya Pradesh Bhopal SHOOTING Priya Chaturvedi Solved – Closed 01JAN2001 01JAN2002 Critical 2001 6 365 BHOPAL, MADHYA PRADESH 21st
15 IND015 Punjab Amritsar STRANGULATION Harpreet Singh Unsolved/Cold Case 01JAN2005 . Moderate 2005 1 . AMRITSAR, PUNJAB 21st
16 IND016 Chhattisgarh Raipur BLUNT FORCE Shraddha Verma Solved – Closed 01JAN2008 21MAR2008 High 2008 3 80 RAIPUR, CHHATTISGARH 21st
17 IND017 Haryana Gurugram STABBING Aryan Malik Solved – Closed 01JAN2012 15FEB2012 High 2012 2 45 GURUGRAM, HARYANA 21st
18 IND018 Delhi New Delhi SHOOTING Anita Khurana Investigation Open 01JAN2016 . Critical 2016 5 . NEW DELHI, DELHI 21st
19 IND019 Tamil Nadu Chennai POISONING V. Ramesh Solved – Closed 01JAN2018 31JAN2018 Low 2018 1 30 CHENNAI, TAMIL NADU 21st
20 IND020 Maharashtra Pune ARSON Ritika Kulkarni Solved – Closed 01JAN2019 30JUL2019 Moderate 2019 2 210 PUNE, MAHARASHTRA 21st
21 IND021 Uttar Pradesh Noida BLUNT FORCE Manoj Pandey Investigation Open 01JAN2020 . Critical 2020 4 . NOIDA, UTTAR PRADESH 21st
22 IND022 Bihar Gaya DROWNING Shweta Prasad Solved – Closed 01JAN2021 11JAN2021 Low 2021 1 10 GAYA, BIHAR 21st
23 IND023 Karnataka Mysuru STRANGULATION Raghu Shetty Solved – Closed 01JAN2022 05FEB2022 Moderate 2022 1 35 MYSURU, KARNATAKA 21st
24 IND024 Gujarat Surat STABBING Dipika Patel Investigation Open 01JAN2023 . High 2023 3 . SURAT, GUJARAT 21st
25 IND025 Telangana Warangal SHOOTING Sai Teja Investigation Open 01JAN2025 . High 2025 2 . WARANGAL, TELANGANA 21st

3. First Glimpse – PROC PRINT | PROC FREQ

3A.PROC PRINT to eyeball the first few cases

proc print data=work.india_murder_invest (obs=10) label;

   title "Snapshot of Indian Murder Investigations (First 10 Rows)";

   var Case_ID Year State City Method Victims Severity Status;

run;

Output:

Snapshot of Indian Murder Investigations (First 10 Rows)

Obs Case_ID Year State City Method Victims Severity Status
1 IND001 1900 West Bengal Calcutta STABBING 1 Moderate Solved – Closed
2 IND002 1915 Maharashtra Mumbai POISONING 2 High Unsolved/Cold Case
3 IND003 1928 Uttar Pradesh Kanpur SHOOTING 1 High Solved – Closed
4 IND004 1936 Tamil Nadu Madras BLUNT FORCE 4 Critical Unsolved/Cold Case
5 IND005 1944 Bihar Patna ARSON 7 Critical Solved – Closed
6 IND006 1957 Delhi Delhi SHOOTING 3 High Investigation Open
7 IND007 1962 Karnataka Bangalore STRANGULATION 1 Moderate Solved – Closed
8 IND008 1971 Gujarat Ahmedabad DROWNING 2 Moderate Solved – Closed
9 IND009 1978 Kerala Kochi POISONING 1 Low Solved – Closed
10 IND010 1984 Rajasthan Jaipur SHOOTING 5 Critical Unsolved/Cold Case

3B.PROC FREQ for categorical distributions

proc freq data=work.india_murder_invest order=freq;

   tables State*Status severity*Status / nocum nopercent;

   title "State vs Status and Severity vs Status Profiles";

run;

Output:

State vs Status and Severity vs Status Profiles

The FREQ Procedure

Frequency
Row Pct
Col Pct
Table of State by Status
State Status
Solved – Closed Investigation
Open		 Unsolved/Cold
Case		 Total
Bihar
2
100.00
13.33
0
0.00
0.00
0
0.00
0.00
2
 
 
Delhi
0
0.00
0.00
2
100.00
33.33
0
0.00
0.00
2
 
 
Gujarat
1
50.00
6.67
1
50.00
16.67
0
0.00
0.00
2
 
 
Karnataka
2
100.00
13.33
0
0.00
0.00
0
0.00
0.00
2
 
 
Maharashtra
1
50.00
6.67
0
0.00
0.00
1
50.00
25.00
2
 
 
Tamil Nadu
1
50.00
6.67
0
0.00
0.00
1
50.00
25.00
2
 
 
Telangana
0
0.00
0.00
2
100.00
33.33
0
0.00
0.00
2
 
 
Assam
1
100.00
6.67
0
0.00
0.00
0
0.00
0.00
1
 
 
Chhattisgarh
1
100.00
6.67
0
0.00
0.00
0
0.00
0.00
1
 
 
Haryana
1
100.00
6.67
0
0.00
0.00
0
0.00
0.00
1
 
 
Kerala
1
100.00
6.67
0
0.00
0.00
0
0.00
0.00
1
 
 
Madhya Pradesh
1
100.00
6.67
0
0.00
0.00
0
0.00
0.00
1
 
 
Odisha
1
100.00
6.67
0
0.00
0.00
0
0.00
0.00
1
 
 
Punjab
0
0.00
0.00
0
0.00
0.00
1
100.00
25.00
1
 
 
Rajasthan
0
0.00
0.00
0
0.00
0.00
1
100.00
25.00
1
 
 
Uttar Pradesh
1
100.00
6.67
0
0.00
0.00
0
0.00
0.00
1
 
 
Uttar Pradesh
0
0.00
0.00
1
100.00
16.67
0
0.00
0.00
1
 
 
West Bengal
1
100.00
6.67
0
0.00
0.00
0
0.00
0.00
1
 
 
Total
15
6
4
25

Frequency
Row Pct
Col Pct
Table of Severity by Status
Severity Status
Solved – Closed Investigation
Open		 Unsolved/Cold
Case		 Total
High
4
44.44
26.67
4
44.44
66.67
1
11.11
25.00
9
 
 
Moderate
6
85.71
40.00
0
0.00
0.00
1
14.29
25.00
7
 
 
Critical
2
33.33
13.33
2
33.33
33.33
2
33.33
50.00
6
 
 
Low
3
100.00
20.00
0
0.00
0.00
0
0.00
0.00
3
 
 
Total
15
6
4
25

4. Summaries – PROC MEANS | PROC SUMMARY

4A.Central‑tendency checks with PROC MEANS

proc means data=work.india_murder_invest

           n nmiss mean median min max std maxdec=1;

   class Status;

   var Victims Close_Days;

   title "Victims and Close_Days by Investigation Status";

run;

Output:

Victims and Close_Days by Investigation Status

The MEANS Procedure

Status N Obs Variable N N Miss Mean Median Minimum Maximum Std Dev
Solved – Closed 15
Victims
Close_Days
15
15
0
0
2.3
121.3
2.0
80.0
1.0
10.0
7.0
365.0
1.9
110.4
Unsolved/Cold Case 4
Victims
Close_Days
4
0
0
4
3.0
.
3.0
.
1.0
.
5.0
.
1.8
.
Investigation Open 6
Victims
Close_Days
6
0
0
6
3.3
.
3.0
.
2.0
.
5.0
.
1.0
.

 4B.Alternative view using PROC SUMMARY + OUTPUT statement

proc summary data=work.india_murder_invest completetypes;

   class Century_Flag Status;

   var Victims;

   output out=work.century_summary

          mean=Avg_Victims

          sum =Tot_Victims

          n   =Count;

run;

Output:

Obs Century_Flag Status _TYPE_ _FREQ_ Avg_Victims Tot_Victims Count
1     0 25 2.68000 67 25
2   Solved – Closed 1 15 2.33333 35 15
3   Unsolved/Cold Case 1 4 3.00000 12 4
4   Investigation Open 1 6 3.33333 20 6
5 20th   2 13 2.76923 36 13
6 21st   2 12 2.58333 31 12
7 20th Solved – Closed 3 8 2.37500 19 8
8 20th Unsolved/Cold Case 3 3 3.66667 11 3
9 20th Investigation Open 3 2 3.00000 6 2
10 21st Solved – Closed 3 7 2.28571 16 7
11 21st Unsolved/Cold Case 3 1 1.00000 1 1
12 21st Investigation Open 3 4 3.50000 14 4

5. Deep‑Dive Queries – PROC SQL

PROC SQL: quickest close times by city

proc sql feedback;

   create table fastest_closures as

   select putn(Close_Days, 'comma8.') as Min_Close label='Fastest Closure (days)',

          City,

          State,

          Year

   from work.india_murder_invest

   where Close_Days is not null

     and Close_Days = (select min(Close_Days) from work.india_murder_invest where Close_Days is not null)

   order by Close_Days;

quit;

proc print;run;

Output:

Obs Min_Close City State Year
1 10 Gaya Bihar 2021

6. Building Reusable Analysis – A Macro with Functions Inside

Macro %REPORT_BY_METHOD – summarise for any given murder method

%macro report_by_method(method=);


   %let upmethod = %upcase(&method);   /* Convert input to uppercase */

   %let cleanmethod = %sysfunc(compress(&upmethod, , 'kad')); /* Remove non-alphanum */


   title "== &upmethod Method – Decade Snapshot ==";


   proc freq data=work.india_murder_invest noprint;

      where strip(upcase(Method)) = "&upmethod";

      tables Year / out=work.&cleanmethod._byyear;

   run;


   proc sgplot data=work.&cleanmethod._byyear;

      series x=Year y=COUNT / markers;

      yaxis label="Case Count";

      xaxis label="Year";

      title "Temporal Trend for &upmethod Cases";

   run;


%mend;

%report_by_method(method=SHOOTING)

Output:


%report_by_method(method=POISONING)

Output:



%report_by_method(method=STABBING)

Output:



7. Reshaping for Reporting – PROC TRANSPOSE | PROC REPORT

 7A.Wide format for cross‑tab report

proc transpose data=work.india_murder_invest

               out=work.t_wide prefix=Victims_;

   by State notsorted;

   id Status;

   var Victims;

run;

proc print;run;

Output:

Obs State _NAME_ Victims_Solved___Closed Victims_Unsolved_Cold_Case Victims_Investigation_Open
1 West Bengal Victims 1 . .
2 Maharashtra Victims . 2 .
3 Uttar Pradesh Victims 1 . .
4 Tamil Nadu Victims . 4 .
5 Bihar Victims 7 . .
6 Delhi Victims . . 3
7 Karnataka Victims 1 . .
8 Gujarat Victims 2 . .
9 Kerala Victims 1 . .
10 Rajasthan Victims . 5 .
11 Assam Victims 2 . .
12 Telangana Victims . . 3
13 Odisha Victims 4 . .
14 Madhya Pradesh Victims 6 . .
15 Punjab Victims . 1 .
16 Chhattisgarh Victims 3 . .
17 Haryana Victims 2 . .
18 Delhi Victims . . 5
19 Tamil Nadu Victims 1 . .
20 Maharashtra Victims 2 . .
21 Uttar Pradesh Victims . . 4
22 Bihar Victims 1 . .
23 Karnataka Victims 1 . .
24 Gujarat Victims . . 3
25 Telangana Victims . . 2

  7B.Present the transposed table

proc report data=work.t_wide nowd

      headline headskip split='/';

   column State Victims_Solved___Closed Victims_Unsolved_Cold_Case Victims_Investigation_Open;

   define State / group 'State/UT';

   define Victims_Solved___Closed / analysis sum 'Solved';

   define Victims_Unsolved_Cold_Case / analysis sum 'Cold';

   define Victims_Investigation_Open / analysis sum 'Open';

   compute after;

      line "Note: Summaries auto-generated via PROC TRANPOSE + PROC REPORT.";

   endcomp;

   title  "Victim Counts by Investigation Status – State Wise";

run;

Output:

Victim Counts by Investigation Status – State Wise

State
UT		 Solved Cold Open
Assam 2 . .
Bihar 8 . .
Chhattisgarh 3 . .
Delhi . . 8
Gujarat 2 . 3
Haryana 2 . .
Karnataka 2 . .
Kerala 1 . .
Madhya Pradesh 6 . .
Maharashtra 2 2 .
Odisha 4 . .
Punjab . 1 .
Rajasthan . 5 .
Tamil Nadu 1 4 .
Telangana . . 5
Uttar Pradesh 1 . .
Uttar Pradesh . . 4
West Bengal 1 . .
Note: Summaries auto-generated via PROC TRANPOSE + PROC REPORT.

8. Advanced Formatting – PROC FORMAT with PICTURE

Build a PICTURE format to show closure days as Yr+Mo+Dy

proc format;

   picture days2ymd (round)

      0-<366           ='000' (mult=1)

      366-<732         ='0Y 000' (prefix='1Y ')

      732-high         ='>1Y';

run;


data work.closure_fmt;

   set work.india_murder_invest;

   CloseFmt = put(Close_Days, days2ymd.);

run;


proc print data=work.closure_fmt (obs=8);

   var Case_ID Close_Days CloseFmt;

   title  "Custom PICTURE Format Example";

run;

Output:

Custom PICTURE Format Example

Obs Case_ID Close_Days CloseFmt
1 IND001 210 210
2 IND002 . .
3 IND003 95 95
4 IND004 . .
5 IND005 300 300
6 IND006 . .
7 IND007 60 60
8 IND008 44 44

9. Final Output - PROC EXPORT

proc export data=work.india_murder_invest

            outfile="india_murder_invest.csv"

            dbms=csv replace;

   putnames=yes;

run;

Log:

NOTE: The file 'india_murder_invest.csv' is:

      Filename=C:\sas folder\SASFoundation\9.4\india_murder_invest.csv,

      RECFM=V,LRECL=32767,File Size (bytes)=0,

      Last Modified=28 June 2025 16:06:04,

      Create Time=28 June 2025 16:06:04


NOTE: 26 records were written to the file 'india_murder_invest.csv'.

      The minimum record length was 103.

      The maximum record length was 140.

NOTE: There were 25 observations read from the data set WORK.INDIA_MURDER_INVEST.

NOTE: DATA statement used (Total process time):

      real time           0.07 seconds

      user cpu time       0.01 seconds

      system cpu time     0.01 seconds

      memory              8086.18k

      OS Memory           34804.00k

      Timestamp           14/09/2015 12:13:34 AM

      Step Count                        20  Switch Count  0


25 records created in india_murder_invest.csv from WORK.INDIA_MURDER_INVEST.


NOTE: "india_murder_invest.csv" file was successfully created.

NOTE: PROCEDURE EXPORT used (Total process time):

      real time           1.42 seconds

      user cpu time       0.03 seconds

      system cpu time     0.12 seconds

      memory              8086.18k

      OS Memory           34804.00k

      Timestamp           14/09/2015 12:13:34 AM

      Step Count                        20  Switch Count  7





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





--->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