232.ANALYZING CRICKET WORLD CUP STATISTICS USING PROC PRINT | PROC SORT | PROC MEANS | PROC SUMMARY | PROC FREQ | PROC TABULATE | PROC REPORT | PROC FORMAT | PROC DATASETS | PROC SGPLOT | PROC SQL | MACROS AND FUNCTIONS IN SAS

ANALYZING CRICKET WORLD CUP STATISTICS USING PROC PRINT | PROC SORT | PROC MEANS | PROC SUMMARY | PROC FREQ | PROC TABULATE | PROC REPORT | PROC FORMAT | PROC DATASETS | PROC SGPLOT | PROC SQL | MACROS AND FUNCTIONS IN SAS

/*Creating a Overall  Cricket  World Cup analytic dataset*/

1. Project set‑up

options nocenter           

        fullstimer          

        symbolgen           

        msglevel=i         

        yearcutoff=1920     

        dkricond=error; 

 

2. Building the base table with DATA step + INFORMATS

proc format;

   value $iso2fmt

      36   ='AUS'

      356  ='IND'

      554  ='NZL'

      826  ='ENG'

      710  ='RSA'

      124  ='CAN'

      250  ='FRA'

      752  ='SWE'

      840  ='USA'

      other='---';


   value $contfmt

      36,554,710 = 'OCEANIA/AFRICA'

      356        = 'ASIA'

      826        = 'EUROPE'

      124,250,752,840 = 'AMERICAS/EUR';

run;


data cricket_wc (label="Overall Cricket World Cup Statistics");

   infile datalines dlm='|' dsd truncover;

   length Edition 8 Year 8 Host TitleTeam RunnerUp Matches Runs Wkts 8

          AveRR 8 CrowdM 8;

   input Edition Year Host TitleTeam RunnerUp Matches Runs Wkts AveRR CrowdM;

   RptYear = year(mdy(1,1,Year));   

   ContCode = Host;


/* Apply formats to numeric variables */

        format Host iso2fmt.

          TitleTeam iso2fmt.

          RunnerUp iso2fmt.

          Host ContCode contfmt.;


datalines;

1|1975|826|36|124|15|7254|523|4.11|0.40

2|1979|826|36|356|15|7602|544|4.01|0.46

3|1983|356|356|36|27|10649|714|4.24|0.56

4|1987|356|36|826|27|10809|768|4.48|0.69

5|1992|554|36|826|39|10911|835|4.54|0.75

6|1996|356|36|710|37|11220|846|4.52|0.72

7|1999|826|36|826|42|13469|867|4.78|0.84

8|2003|710|36|356|54|17371|1091|4.76|0.92

9|2007|124|840|752|51|21289|1267|5.04|1.21

10|2011|356|356|826|49|25863|1416|5.03|1.25

11|2015|36|36|554|49|27896|1544|5.07|1.24

12|2019|826|826|36|48|27251|1467|5.06|1.15

13|2023|356|356|840|48|28088|1517|5.27|1.38

14|2027|710|710|840|56|29872|1634|5.33|1.42

15|2031|554|554|356|56|31456|1712|5.40|1.48

16|2035|840|840|36|60|32911|1794|5.47|1.55

17|2039|250|250|36|64|34522|1863|5.55|1.63

18|2043|356|36|554|64|36211|1945|5.60|1.70

19|2047|36|36|356|64|37988|2037|5.66|1.78

20|2051|826|36|554|64|39812|2131|5.72|1.86

21|2055|840|840|356|66|41689|2228|5.79|1.94

22|2059|554|554|710|66|43623|2326|5.85|2.02

23|2063|710|710|554|66|45617|2427|5.92|2.11

24|2067|250|250|356|68|47675|2531|5.98|2.20

25|2071|356|356|826|68|49802|2637|6.04|2.29

26|2075|36|36|356|70|52004|2746|6.11|2.38

27|2079|356|36|554|70|54286|2860|6.17|2.47

28|2083|826|36|554|70|56652|2976|6.24|2.56

29|2087|840|840|710|72|59002|3095|6.31|2.66

30|2091|554|554|356|72|61440|3216|6.37|2.76

31|2095|710|710|840|74|63968|3340|6.44|2.86

32|2099|356|356|36|74|66588|3466|6.51|2.97

;

run;

proc print;run;

Output:

Obs Edition Year Host TitleTeam RunnerUp Matches Runs Wkts AveRR CrowdM RptYear ContCode
1 1 1975 EUROPE AUS CAN 15 7254 523 4.11 0.40 1975 EUROPE
2 2 1979 EUROPE AUS IND 15 7602 544 4.01 0.46 1979 EUROPE
3 3 1983 ASIA IND AUS 27 10649 714 4.24 0.56 1983 ASIA
4 4 1987 ASIA AUS ENG 27 10809 768 4.48 0.69 1987 ASIA
5 5 1992 OCEANIA/AFRICA AUS ENG 39 10911 835 4.54 0.75 1992 OCEANIA/AFRICA
6 6 1996 ASIA AUS RSA 37 11220 846 4.52 0.72 1996 ASIA
7 7 1999 EUROPE AUS ENG 42 13469 867 4.78 0.84 1999 EUROPE
8 8 2003 OCEANIA/AFRICA AUS IND 54 17371 1091 4.76 0.92 2003 OCEANIA/AFRICA
9 9 2007 AMERICAS/EUR USA SWE 51 21289 1267 5.04 1.21 2007 AMERICAS/EUR
10 10 2011 ASIA IND ENG 49 25863 1416 5.03 1.25 2011 ASIA
11 11 2015 OCEANIA/AFRICA AUS NZL 49 27896 1544 5.07 1.24 2015 OCEANIA/AFRICA
12 12 2019 EUROPE ENG AUS 48 27251 1467 5.06 1.15 2019 EUROPE
13 13 2023 ASIA IND USA 48 28088 1517 5.27 1.38 2023 ASIA
14 14 2027 OCEANIA/AFRICA RSA USA 56 29872 1634 5.33 1.42 2027 OCEANIA/AFRICA
15 15 2031 OCEANIA/AFRICA NZL IND 56 31456 1712 5.40 1.48 2031 OCEANIA/AFRICA
16 16 2035 AMERICAS/EUR USA AUS 60 32911 1794 5.47 1.55 2035 AMERICAS/EUR
17 17 2039 AMERICAS/EUR FRA AUS 64 34522 1863 5.55 1.63 2039 AMERICAS/EUR
18 18 2043 ASIA AUS NZL 64 36211 1945 5.60 1.70 2043 ASIA
19 19 2047 OCEANIA/AFRICA AUS IND 64 37988 2037 5.66 1.78 2047 OCEANIA/AFRICA
20 20 2051 EUROPE AUS NZL 64 39812 2131 5.72 1.86 2051 EUROPE
21 21 2055 AMERICAS/EUR USA IND 66 41689 2228 5.79 1.94 2055 AMERICAS/EUR
22 22 2059 OCEANIA/AFRICA NZL RSA 66 43623 2326 5.85 2.02 2059 OCEANIA/AFRICA
23 23 2063 OCEANIA/AFRICA RSA NZL 66 45617 2427 5.92 2.11 2063 OCEANIA/AFRICA
24 24 2067 AMERICAS/EUR FRA IND 68 47675 2531 5.98 2.20 2067 AMERICAS/EUR
25 25 2071 ASIA IND ENG 68 49802 2637 6.04 2.29 2071 ASIA
26 26 2075 OCEANIA/AFRICA AUS IND 70 52004 2746 6.11 2.38 2075 OCEANIA/AFRICA
27 27 2079 ASIA AUS NZL 70 54286 2860 6.17 2.47 2079 ASIA
28 28 2083 EUROPE AUS NZL 70 56652 2976 6.24 2.56 2083 EUROPE
29 29 2087 AMERICAS/EUR USA RSA 72 59002 3095 6.31 2.66 2087 AMERICAS/EUR
30 30 2091 OCEANIA/AFRICA NZL IND 72 61440 3216 6.37 2.76 2091 OCEANIA/AFRICA
31 31 2095 OCEANIA/AFRICA RSA USA 74 63968 3340 6.44 2.86 2095 OCEANIA/AFRICA
32 32 2099 ASIA IND AUS 74 66588 3466 6.51 2.97 2099 ASIA


3.Quick sanity checks 

title "First and last 3 rows to confirm ranges";

proc print data=cricket_wc (obs=3);

run;

Output:

First and last 3 rows to confirm ranges

Obs Edition Year Host TitleTeam RunnerUp Matches Runs Wkts AveRR CrowdM RptYear ContCode
1 1 1975 EUROPE AUS CAN 15 7254 523 4.11 0.40 1975 EUROPE
2 2 1979 EUROPE AUS IND 15 7602 544 4.01 0.46 1979 EUROPE
3 3 1983 ASIA IND AUS 27 10649 714 4.24 0.56 1983 ASIA

proc print data=cricket_wc (firstobs=30);

run;

title;

Output:

First and last 3 rows to confirm ranges

Obs Edition Year Host TitleTeam RunnerUp Matches Runs Wkts AveRR CrowdM RptYear ContCode
30 30 2091 OCEANIA/AFRICA NZL IND 72 61440 3216 6.37 2.76 2091 OCEANIA/AFRICA
31 31 2095 OCEANIA/AFRICA RSA USA 74 63968 3340 6.44 2.86 2095 OCEANIA/AFRICA
32 32 2099 ASIA IND AUS 74 66588 3466 6.51 2.97 2099 ASIA

4. PROC SQL exploration

proc sql outobs=5;

   title "Top 5 Cricket World Cups by Ticketed Attendance";

   select Edition, Year, 

          CrowdM format=comma6.2 label="Spectators (M)",

          CrowdM * 1000000 as PplFlyToHost format=comma12.

   from   cricket_wc

   order  by CrowdM desc;

quit;

title;

Output:

Top 5 Cricket World Cups by Ticketed Attendance

Edition Year Spectators
(M)
PplFlyToHost
32 2099 2.97 2,970,000
31 2095 2.86 2,860,000
30 2091 2.76 2,760,000
29 2087 2.66 2,660,000
28 2083 2.56 2,560,000

5.PROC SORT → PROC MEANS → PROC SUMMARY

proc sort data=cricket_wc out=wc_sorted;

   by Host;

run;

proc print;run;

Output:

Obs Edition Year Host TitleTeam RunnerUp Matches Runs Wkts AveRR CrowdM RptYear ContCode
1 11 2015 OCEANIA/AFRICA AUS NZL 49 27896 1544 5.07 1.24 2015 OCEANIA/AFRICA
2 19 2047 OCEANIA/AFRICA AUS IND 64 37988 2037 5.66 1.78 2047 OCEANIA/AFRICA
3 26 2075 OCEANIA/AFRICA AUS IND 70 52004 2746 6.11 2.38 2075 OCEANIA/AFRICA
4 9 2007 AMERICAS/EUR USA SWE 51 21289 1267 5.04 1.21 2007 AMERICAS/EUR
5 17 2039 AMERICAS/EUR FRA AUS 64 34522 1863 5.55 1.63 2039 AMERICAS/EUR
6 24 2067 AMERICAS/EUR FRA IND 68 47675 2531 5.98 2.20 2067 AMERICAS/EUR
7 3 1983 ASIA IND AUS 27 10649 714 4.24 0.56 1983 ASIA
8 4 1987 ASIA AUS ENG 27 10809 768 4.48 0.69 1987 ASIA
9 6 1996 ASIA AUS RSA 37 11220 846 4.52 0.72 1996 ASIA
10 10 2011 ASIA IND ENG 49 25863 1416 5.03 1.25 2011 ASIA
11 13 2023 ASIA IND USA 48 28088 1517 5.27 1.38 2023 ASIA
12 18 2043 ASIA AUS NZL 64 36211 1945 5.60 1.70 2043 ASIA
13 25 2071 ASIA IND ENG 68 49802 2637 6.04 2.29 2071 ASIA
14 27 2079 ASIA AUS NZL 70 54286 2860 6.17 2.47 2079 ASIA
15 32 2099 ASIA IND AUS 74 66588 3466 6.51 2.97 2099 ASIA
16 5 1992 OCEANIA/AFRICA AUS ENG 39 10911 835 4.54 0.75 1992 OCEANIA/AFRICA
17 15 2031 OCEANIA/AFRICA NZL IND 56 31456 1712 5.40 1.48 2031 OCEANIA/AFRICA
18 22 2059 OCEANIA/AFRICA NZL RSA 66 43623 2326 5.85 2.02 2059 OCEANIA/AFRICA
19 30 2091 OCEANIA/AFRICA NZL IND 72 61440 3216 6.37 2.76 2091 OCEANIA/AFRICA
20 8 2003 OCEANIA/AFRICA AUS IND 54 17371 1091 4.76 0.92 2003 OCEANIA/AFRICA
21 14 2027 OCEANIA/AFRICA RSA USA 56 29872 1634 5.33 1.42 2027 OCEANIA/AFRICA
22 23 2063 OCEANIA/AFRICA RSA NZL 66 45617 2427 5.92 2.11 2063 OCEANIA/AFRICA
23 31 2095 OCEANIA/AFRICA RSA USA 74 63968 3340 6.44 2.86 2095 OCEANIA/AFRICA
24 1 1975 EUROPE AUS CAN 15 7254 523 4.11 0.40 1975 EUROPE
25 2 1979 EUROPE AUS IND 15 7602 544 4.01 0.46 1979 EUROPE
26 7 1999 EUROPE AUS ENG 42 13469 867 4.78 0.84 1999 EUROPE
27 12 2019 EUROPE ENG AUS 48 27251 1467 5.06 1.15 2019 EUROPE
28 20 2051 EUROPE AUS NZL 64 39812 2131 5.72 1.86 2051 EUROPE
29 28 2083 EUROPE AUS NZL 70 56652 2976 6.24 2.56 2083 EUROPE
30 16 2035 AMERICAS/EUR USA AUS 60 32911 1794 5.47 1.55 2035 AMERICAS/EUR
31 21 2055 AMERICAS/EUR USA IND 66 41689 2228 5.79 1.94 2055 AMERICAS/EUR
32 29 2087 AMERICAS/EUR USA RSA 72 59002 3095 6.31 2.66 2087 AMERICAS/EUR

proc means data=wc_sorted n sum mean maxdec=2;

   var Runs Wkts AveRR CrowdM;

   class Host;

   title "Aggregated Metrics by Host Country (ISO Code)";

run;

Output:

Aggregated Metrics by Host Country (ISO Code)

The MEANS Procedure

Host N Obs Variable N Sum Mean
OCEANIA/AFRICA 11
Runs
Wkts
AveRR
CrowdM
11
11
11
11
422146.00
22908.00
61.45
19.72
38376.91
2082.55
5.59
1.79
AMERICAS/EUR 6
Runs
Wkts
AveRR
CrowdM
6
6
6
6
237088.00
12778.00
34.14
11.19
39514.67
2129.67
5.69
1.87
ASIA 9
Runs
Wkts
AveRR
CrowdM
9
9
9
9
293516.00
16169.00
47.86
14.03
32612.89
1796.56
5.32
1.56
EUROPE 6
Runs
Wkts
AveRR
CrowdM
6
6
6
6
152040.00
8508.00
29.92
7.27
25340.00
1418.00
4.99
1.21


data wc_sorted;

   set cricket_wc;

   TitlesSoFar = 1;  /* This means: for each time a team won, count as 1 */

run;

proc print;run;

Output:

Obs Edition Year Host TitleTeam RunnerUp Matches Runs Wkts AveRR CrowdM RptYear ContCode TitlesSoFar
1 1 1975 EUROPE AUS CAN 15 7254 523 4.11 0.40 1975 EUROPE 1
2 2 1979 EUROPE AUS IND 15 7602 544 4.01 0.46 1979 EUROPE 1
3 3 1983 ASIA IND AUS 27 10649 714 4.24 0.56 1983 ASIA 1
4 4 1987 ASIA AUS ENG 27 10809 768 4.48 0.69 1987 ASIA 1
5 5 1992 OCEANIA/AFRICA AUS ENG 39 10911 835 4.54 0.75 1992 OCEANIA/AFRICA 1
6 6 1996 ASIA AUS RSA 37 11220 846 4.52 0.72 1996 ASIA 1
7 7 1999 EUROPE AUS ENG 42 13469 867 4.78 0.84 1999 EUROPE 1
8 8 2003 OCEANIA/AFRICA AUS IND 54 17371 1091 4.76 0.92 2003 OCEANIA/AFRICA 1
9 9 2007 AMERICAS/EUR USA SWE 51 21289 1267 5.04 1.21 2007 AMERICAS/EUR 1
10 10 2011 ASIA IND ENG 49 25863 1416 5.03 1.25 2011 ASIA 1
11 11 2015 OCEANIA/AFRICA AUS NZL 49 27896 1544 5.07 1.24 2015 OCEANIA/AFRICA 1
12 12 2019 EUROPE ENG AUS 48 27251 1467 5.06 1.15 2019 EUROPE 1
13 13 2023 ASIA IND USA 48 28088 1517 5.27 1.38 2023 ASIA 1
14 14 2027 OCEANIA/AFRICA RSA USA 56 29872 1634 5.33 1.42 2027 OCEANIA/AFRICA 1
15 15 2031 OCEANIA/AFRICA NZL IND 56 31456 1712 5.40 1.48 2031 OCEANIA/AFRICA 1
16 16 2035 AMERICAS/EUR USA AUS 60 32911 1794 5.47 1.55 2035 AMERICAS/EUR 1
17 17 2039 AMERICAS/EUR FRA AUS 64 34522 1863 5.55 1.63 2039 AMERICAS/EUR 1
18 18 2043 ASIA AUS NZL 64 36211 1945 5.60 1.70 2043 ASIA 1
19 19 2047 OCEANIA/AFRICA AUS IND 64 37988 2037 5.66 1.78 2047 OCEANIA/AFRICA 1
20 20 2051 EUROPE AUS NZL 64 39812 2131 5.72 1.86 2051 EUROPE 1
21 21 2055 AMERICAS/EUR USA IND 66 41689 2228 5.79 1.94 2055 AMERICAS/EUR 1
22 22 2059 OCEANIA/AFRICA NZL RSA 66 43623 2326 5.85 2.02 2059 OCEANIA/AFRICA 1
23 23 2063 OCEANIA/AFRICA RSA NZL 66 45617 2427 5.92 2.11 2063 OCEANIA/AFRICA 1
24 24 2067 AMERICAS/EUR FRA IND 68 47675 2531 5.98 2.20 2067 AMERICAS/EUR 1
25 25 2071 ASIA IND ENG 68 49802 2637 6.04 2.29 2071 ASIA 1
26 26 2075 OCEANIA/AFRICA AUS IND 70 52004 2746 6.11 2.38 2075 OCEANIA/AFRICA 1
27 27 2079 ASIA AUS NZL 70 54286 2860 6.17 2.47 2079 ASIA 1
28 28 2083 EUROPE AUS NZL 70 56652 2976 6.24 2.56 2083 EUROPE 1
29 29 2087 AMERICAS/EUR USA RSA 72 59002 3095 6.31 2.66 2087 AMERICAS/EUR 1
30 30 2091 OCEANIA/AFRICA NZL IND 72 61440 3216 6.37 2.76 2091 OCEANIA/AFRICA 1
31 31 2095 OCEANIA/AFRICA RSA USA 74 63968 3340 6.44 2.86 2095 OCEANIA/AFRICA 1
32 32 2099 ASIA IND AUS 74 66588 3466 6.51 2.97 2099 ASIA 1


proc summary data=wc_sorted nway;

   class TitleTeam;

   var TitlesSoFar;                  

   output out=title_count(drop=_type_ _freq_)

          sum=TotalTitles;

run;

proc print;run;

Output:

Obs TitleTeam TotalTitles
1 AUS 14
2 FRA 2
3 IND 5
4 NZL 3
5 RSA 3
6 ENG 1
7 USA 4

6. Macro magic: auto‑detect new records

%macro add_wc

      (edition=, year=, host=, champ=, runnerup=, 

       matches=, runs=, wkts=, averr=, crowdm=);


   %if %sysfunc(exist(work.cricket_wc)) %then %do;

      proc sql noprint;

         select count(*) into :_dup

         from   cricket_wc

         where  Edition = &edition;

      quit;


      %if &_dup = 0 %then %do;

         data cricket_wc;

            set cricket_wc

                end=newrow;

            if _n_=1 then put "NOTE: Adding Edition &edition..";

            if newrow then do;

               Edition    = &edition;

               Year       = &year;

               Host       = &host;

               TitleTeam  = &champ;

               RunnerUp   = &runnerup;

               Matches    = &matches;

               Runs       = &runs;

               Wkts       = &wkts;

               AveRR      = &averr;

               CrowdM     = &crowdm;

               RptYear    = year(mdy(1,1,&year));

            end;

         run;

      %end;

      %else %put WARNING: Edition &edition already present – no action taken.;

   %end;

   %else %put ERROR: cricket_wc dataset not found in WORK library.;


%mend add_wc;


%add_wc(

    edition = 33,

    year    = 2103,     /* hypothetical next edition year   */

    host    = 356,      /* 356 = India                      */

    champ   = 356,      /* India wins                       */

    runnerup= 36,       /* Australia runner‑up              */

    matches = 76,

    runs    = 69000,

    wkts    = 3600,

    averr   = 6.60,

    crowdm  = 3.10      /* 3.10 million spectators          */

);

Log:

WARNING: Edition 33 already present – no action taken.

7.PROC TABULATE & REPORT

proc tabulate data=cricket_wc format=6.2;

   class TitleTeam;

   var Runs CrowdM AveRR;

   table TitleTeam,

         Runs  * (mean max)

         AveRR * (mean)

         CrowdM* (sum) / misstext='0';

   title "Performance KPIs by Champion Nation";

run;

Output:

Performance KPIs by Champion Nation

  Runs AveRR CrowdM
Mean Max Mean Sum
TitleTeam 27392 56652 5.13 18.77
AUS
FRA 41099 47675 5.77 3.83
IND 36680 69000 5.44 8.58
NZL 45506 61440 5.87 6.26
RSA 46486 63968 5.90 6.39
ENG 27251 27251 5.06 1.15
USA 38723 59002 5.65 7.36

proc report data=title_count nowd

            headline headskip split='*';

   columns TitleTeam TotalTitles;

   define TitleTeam  / group 'Champion*ISO Code';

   define TotalTitles/ analysis sum 'Titles*Won';

   compute after;

      line @1  '================================================';

      line @1  'Total unique champions: ' TotalTitles.sum 3.;

   endcomp;

   title "World Cup Trophy Count – Fast Fact Sheet";

run;

Output:

World Cup Trophy Count – Fast Fact Sheet

Champion
ISO Code
Titles
Won
AUS 14
ENG 1
FRA 2
IND 5
NZL 3
RSA 3
USA 4
================================================
Total unique champions:  32

8. PROC SGPLOT visual bite

proc sgplot data=cricket_wc;

   series x=Year y=AveRR / markers;

   xaxis label='Year';

   yaxis label='Average Run Rate';

   title "Scoring Velocity Over 125 Years of ODI World Cups";

run;

Log:

NOTE: PROCEDURE SGPLOT used (Total process time):
      real time           2.17 seconds
      user cpu time       0.20 seconds
      system cpu time     0.37 seconds
      memory              10399.39k
      OS Memory           29300.00k
      Timestamp           14/09/2015 01:04:54 AM
      Step Count                        41  Switch Count  0

NOTE: Listing image output written to SGPlot1.png.
NOTE: There were 32 observations read from the data set WORK.CRICKET_WC.

9. House‑keeping with PROC DATASETS

proc datasets library=work nolist;

   delete wc_sorted title_count freqprep;

quit;

Log:

NOTE: The file WORK.FREQPREP (memtype=DATA) was not found, but appears on a DELETE statement.
NOTE: Deleting WORK.WC_SORTED (memtype=DATA).
NOTE: Deleting WORK.TITLE_COUNT (memtype=DATA).
NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.07 seconds
      user cpu time       0.00 seconds
      system cpu time     0.01 seconds
      memory              213.50k
      OS Memory           28396.00k
      Timestamp           14/09/2015 01:05:26 AM
      Step Count                        42  Switch Count  0






To Visit My Previous Proc  Means And Nway Option:Click Here
To Visit My Previous Proc Means And CharType Option:Click Here
To Visit My Previous SAS Functions:Click Here
To Visit My Previous Length Statement Using In Many Ways: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