309.WORLD FESTIVALS DATASET CREATION AND STATISTICAL REPORTING USING PROC SQL | PROC SORT | PROC PRINT | PROC SUMMARY | PROC FORMAT | AND AUTOMATED MACRO-BASED ANALYSIS

WORLD FESTIVALS DATASET CREATION AND STATISTICAL REPORTING USING PROC SQL | PROC SORT | PROC PRINT | PROC SUMMARY | PROC FORMAT | AND AUTOMATED MACRO-BASED ANALYSIS

options validvarname=any nodate nonumber nocenter;

1) Create Worlds Festival Dataset

data work.world_festivals;

    infile datalines dlm='|' dsd truncover;

    length Festival_ID $6 Festival_Name $60 Country $40 Month $9 Type $20;

    format Visitors_per_Year comma15.;

    input Festival_ID :$6. Festival_Name :$60. Country :$40. Month :$9.

          Duration_Days :8. Type :$20. Visitors_per_Year :comma15.;

    /* Basic validation/cleaning */

    Festival_Name = strip(Festival_Name);

    Country = strip(Country);

    Month = propcase(strip(Month));

    Type = upcase(strip(Type));

    if missing(Duration_Days) then Duration_Days = .;

    if Visitors_per_Year = . then Visitors_per_Year = 0;

datalines;

F001|Diwali|India|October|5|Religious|100000000

F002|Carnival (Rio)|Brazil|February|7|Cultural|2000000

F003|Oktoberfest|Germany|September|16|Cultural|6000000

F004|Cherry Blossom|Japan|April|14|Seasonal|30000000

F005|Songkran|Thailand|April|3|Religious|2000000

F006|Mardi Gras|USA|February|9|Cultural|1000000

F007|La Tomatina|Spain|August|1|Cultural|220000

F008|Harbin Ice Festival|China|January|60|Seasonal|1000000

F009|Holi|India|March|2|Religious|25000000

F010|Day of the Dead|Mexico|November|3|Religious|1500000

F011|Glastonbury|United Kingdom|June|5|Music|210000

F012|Coachella|USA|April|3|Music|250000

F013|Eid al-Fitr|Various|Varies|3|Religious|500000000

F014|Running of the Bulls|Spain|July|3|Cultural|80000

F015|Lantern Festival|Taiwan|February|2|Cultural|1200000

;

run;

proc print data=work.world_festivals;

run;

OUTPUT:

Obs	Festival_ID	Festival_Name	Country	Month	Type	Visitors_per_Year	Duration_Days
1	F001	Diwali	India	October	RELIGIOUS	100,000,000	5
2	F002	Carnival (Rio)	Brazil	February	CULTURAL	2,000,000	7
3	F003	Oktoberfest	Germany	September	CULTURAL	6,000,000	16
4	F004	Cherry Blossom	Japan	April	SEASONAL	30,000,000	14
5	F005	Songkran	Thailand	April	RELIGIOUS	2,000,000	3
6	F006	Mardi Gras	USA	February	CULTURAL	1,000,000	9
7	F007	La Tomatina	Spain	August	CULTURAL	220,000	1
8	F008	Harbin Ice Festival	China	January	SEASONAL	1,000,000	60
9	F009	Holi	India	March	RELIGIOUS	25,000,000	2
10	F010	Day of the Dead	Mexico	November	RELIGIOUS	1,500,000	3
11	F011	Glastonbury	United Kingdom	June	MUSIC	210,000	5
12	F012	Coachella	USA	April	MUSIC	250,000	3
13	F013	Eid al-Fitr	Various	Varies	RELIGIOUS	500,000,000	3
14	F014	Running of the Bulls	Spain	July	CULTURAL	80,000	3
15	F015	Lantern Festival	Taiwan	February	CULTURAL	1,200,000	2

2) Define formats for visitor size categories and months (PROC FORMAT)

proc format;

    value visitors_fmt

        low - <100000 = 'Small (<100k)'

        100000 - <500000 = 'Medium (100k-500k)'

        500000 - <2000000 = 'Large (500k-2M)'

        2000000 - <20000000 = 'Very Large (2M-20M)'

        20000000 - high = 'Mega (20M+)';

    value $typefmt

        'RELIGIOUS' = 'Religious'

        'CULTURAL'  = 'Cultural'

        'MUSIC'     = 'Music'

        'SEASONAL'  = 'Seasonal'

        other       = 'Other';

run;

LOG:

NOTE: Format VISITORS_FMT has been output.

NOTE: Format $TYPEFMT has been output.

3) Use PROC SQL to create derived tables:

proc sql;

    create table work.world_festivals_summary as

    select Type,

           count(*) as N_Festivals,

           mean(Visitors_per_Year) as Mean_Visitors format=comma15.,

           median(Visitors_per_Year) as Median_Visitors format=comma15.,

           min(Visitors_per_Year) as Min_Visitors format=comma15.,

           max(Visitors_per_Year) as Max_Visitors format=comma15.,

           sum(Visitors_per_Year) as Total_Visitors format=comma15.

    from work.world_festivals

    group by Type

    order by Total_Visitors desc;

quit;

proc print data=work.world_festivals_summary;

run;

OUTPUT:

Obs	Type	N_Festivals	Mean_Visitors	Median_Visitors	Min_Visitors	Max_Visitors	Total_Visitors
1	RELIGIOUS	5	125,700,000	25,000,000	1,500,000	500,000,000	628,500,000
2	SEASONAL	2	15,500,000	15,500,000	1,000,000	30,000,000	31,000,000
3	CULTURAL	6	1,750,000	1,100,000	80,000	6,000,000	10,500,000
4	MUSIC	2	230,000	230,000	210,000	250,000	460,000

proc sql;

    create table work.top_festivals as

    select * 

    from work.world_festivals

    order by Visitors_per_Year desc;

quit;

proc print data=work.top_festivals;

run;

OUTPUT:

Obs	Festival_ID	Festival_Name	Country	Month	Type	Visitors_per_Year	Duration_Days
1	F013	Eid al-Fitr	Various	Varies	RELIGIOUS	500,000,000	3
2	F001	Diwali	India	October	RELIGIOUS	100,000,000	5
3	F004	Cherry Blossom	Japan	April	SEASONAL	30,000,000	14
4	F009	Holi	India	March	RELIGIOUS	25,000,000	2
5	F003	Oktoberfest	Germany	September	CULTURAL	6,000,000	16
6	F005	Songkran	Thailand	April	RELIGIOUS	2,000,000	3
7	F002	Carnival (Rio)	Brazil	February	CULTURAL	2,000,000	7
8	F010	Day of the Dead	Mexico	November	RELIGIOUS	1,500,000	3
9	F015	Lantern Festival	Taiwan	February	CULTURAL	1,200,000	2
10	F006	Mardi Gras	USA	February	CULTURAL	1,000,000	9
11	F008	Harbin Ice Festival	China	January	SEASONAL	1,000,000	60
12	F012	Coachella	USA	April	MUSIC	250,000	3
13	F007	La Tomatina	Spain	August	CULTURAL	220,000	1
14	F011	Glastonbury	United Kingdom	June	MUSIC	210,000	5
15	F014	Running of the Bulls	Spain	July	CULTURAL	80,000	3

4) Sort datasets for printing and reporting

proc sort data=work.world_festivals out=work.world_festivals_1;

    by descending Visitors_per_Year Festival_Name;

run;

proc print data=work.world_festivals_1;

run;

OUTPUT:

Obs	Festival_ID	Festival_Name	Country	Month	Type	Visitors_per_Year	Duration_Days
1	F013	Eid al-Fitr	Various	Varies	RELIGIOUS	500,000,000	3
2	F001	Diwali	India	October	RELIGIOUS	100,000,000	5
3	F004	Cherry Blossom	Japan	April	SEASONAL	30,000,000	14
4	F009	Holi	India	March	RELIGIOUS	25,000,000	2
5	F003	Oktoberfest	Germany	September	CULTURAL	6,000,000	16
6	F002	Carnival (Rio)	Brazil	February	CULTURAL	2,000,000	7
7	F005	Songkran	Thailand	April	RELIGIOUS	2,000,000	3
8	F010	Day of the Dead	Mexico	November	RELIGIOUS	1,500,000	3
9	F015	Lantern Festival	Taiwan	February	CULTURAL	1,200,000	2
10	F008	Harbin Ice Festival	China	January	SEASONAL	1,000,000	60
11	F006	Mardi Gras	USA	February	CULTURAL	1,000,000	9
12	F012	Coachella	USA	April	MUSIC	250,000	3
13	F007	La Tomatina	Spain	August	CULTURAL	220,000	1
14	F011	Glastonbury	United Kingdom	June	MUSIC	210,000	5
15	F014	Running of the Bulls	Spain	July	CULTURAL	80,000	3

proc sort data=work.world_festivals_summary out=work.world_festivals_summary_sorted;

    by descending Total_Visitors;

run;

proc print data=work.world_festivals_summary_sorted;

run;

OUTPUT:

Obs	Type	N_Festivals	Mean_Visitors	Median_Visitors	Min_Visitors	Max_Visitors	Total_Visitors
1	RELIGIOUS	5	125,700,000	25,000,000	1,500,000	500,000,000	628,500,000
2	SEASONAL	2	15,500,000	15,500,000	1,000,000	30,000,000	31,000,000
3	CULTURAL	6	1,750,000	1,100,000	80,000	6,000,000	10,500,000
4	MUSIC	2	230,000	230,000	210,000	250,000	460,000

5) Basic print of the master dataset (PROC PRINT)

title "WORLD FESTIVALS MASTER LIST (sorted by Visitors)";

proc print data=work.world_festivals_1 label noobs;

    var Festival_ID Festival_Name Country Month Duration_Days Type Visitors_per_Year;

    format Visitors_per_Year comma15.;

    label Festival_ID='ID'

          Festival_Name='Festival'

          Country='Country'

          Month='Month'

          Duration_Days='Duration (days)'

          Type='Type'

          Visitors_per_Year='Visitors/Year';

run;

title;

OUTPUT:

WORLD FESTIVALS MASTER LIST (sorted by Visitors)

ID	Festival	Country	Month	Duration (days)	Type	Visitors/Year
F013	Eid al-Fitr	Various	Varies	3	RELIGIOUS	500,000,000
F001	Diwali	India	October	5	RELIGIOUS	100,000,000
F004	Cherry Blossom	Japan	April	14	SEASONAL	30,000,000
F009	Holi	India	March	2	RELIGIOUS	25,000,000
F003	Oktoberfest	Germany	September	16	CULTURAL	6,000,000
F002	Carnival (Rio)	Brazil	February	7	CULTURAL	2,000,000
F005	Songkran	Thailand	April	3	RELIGIOUS	2,000,000
F010	Day of the Dead	Mexico	November	3	RELIGIOUS	1,500,000
F015	Lantern Festival	Taiwan	February	2	CULTURAL	1,200,000
F008	Harbin Ice Festival	China	January	60	SEASONAL	1,000,000
F006	Mardi Gras	USA	February	9	CULTURAL	1,000,000
F012	Coachella	USA	April	3	MUSIC	250,000
F007	La Tomatina	Spain	August	1	CULTURAL	220,000
F011	Glastonbury	United Kingdom	June	5	MUSIC	210,000
F014	Running of the Bulls	Spain	July	3	CULTURAL	80,000

6) Summary statistics (PROC SUMMARY) - overall and by Month/Type

proc summary data=work.world_festivals_1 nway;

    class Type;

    var Visitors_per_Year Duration_Days;

    output out=work.summary_by_type

        mean=Mean_Visitors Mean_Duration

        median=Median_Visitors Median_Duration

        sum=Total_Visitors Total_Duration

        min=Min_Visitors Min_Duration

        max=Max_Visitors Max_Duration

        / autoname;

run;

proc print data=work.summary_by_type;

run;

OUTPUT:

Obs	Type	_TYPE_	_FREQ_	Mean_Visitors	Mean_Duration	Median_Visitors	Median_Duration	Total_Visitors	Total_Duration	Min_Visitors	Min_Duration	Max_Visitors	Max_Duration
1	CULTURAL	1	6	1,750,000	6.3333	1,100,000	5	10,500,000	38	80,000	1	6,000,000	16
2	MUSIC	1	2	230,000	4.0000	230,000	4	460,000	8	210,000	3	250,000	5
3	RELIGIOUS	1	5	125,700,000	3.2000	25,000,000	3	628,500,000	16	1,500,000	2	500,000,000	5
4	SEASONAL	1	2	15,500,000	37.0000	15,500,000	37	31,000,000	74	1,000,000	14	30,000,000	60

proc summary data=work.world_festivals nway;

    class Month;

    var Visitors_per_Year;

    output out=work.summary_by_month

        mean=Mean_Visitors

        sum=Total_Visitors

        n=Count

        / autoname;

run;

proc print data=work.summary_by_month;

run;

OUTPUT:

Obs	Month	_TYPE_	_FREQ_	Mean_Visitors	Total_Visitors	Count
1	April	1	3	10,750,000	32,250,000	3
2	August	1	1	220,000	220,000	1
3	February	1	3	1,400,000	4,200,000	3
4	January	1	1	1,000,000	1,000,000	1
5	July	1	1	80,000	80,000	1
6	June	1	1	210,000	210,000	1
7	March	1	1	25,000,000	25,000,000	1
8	November	1	1	1,500,000	1,500,000	1
9	October	1	1	100,000,000	100,000,000	1
10	September	1	1	6,000,000	6,000,000	1
11	Varies	1	1	500,000,000	500,000,000	1

7) Create a visitors-size categorical variable and add to dataset using PROC SQL

proc sql;

    create table work.world_festivals_enriched as

    select a.*,

           put(a.Visitors_per_Year, visitors_fmt.) as Visitor_Size,

           put(a.Type, $typefmt.) as Type_Label

    from work.world_festivals as a;

quit;

proc print data=work.world_festivals_enriched;

run;

OUTPUT:

Obs	Festival_ID	Festival_Name	Country	Month	Type	Visitors_per_Year	Duration_Days	Visitor_Size	Type_Label
1	F001	Diwali	India	October	RELIGIOUS	100,000,000	5	Mega (20M+)	Religious
2	F002	Carnival (Rio)	Brazil	February	CULTURAL	2,000,000	7	Very Large (2M-20M)	Cultural
3	F003	Oktoberfest	Germany	September	CULTURAL	6,000,000	16	Very Large (2M-20M)	Cultural
4	F004	Cherry Blossom	Japan	April	SEASONAL	30,000,000	14	Mega (20M+)	Seasonal
5	F005	Songkran	Thailand	April	RELIGIOUS	2,000,000	3	Very Large (2M-20M)	Religious
6	F006	Mardi Gras	USA	February	CULTURAL	1,000,000	9	Large (500k-2M)	Cultural
7	F007	La Tomatina	Spain	August	CULTURAL	220,000	1	Medium (100k-500k)	Cultural
8	F008	Harbin Ice Festival	China	January	SEASONAL	1,000,000	60	Large (500k-2M)	Seasonal
9	F009	Holi	India	March	RELIGIOUS	25,000,000	2	Mega (20M+)	Religious
10	F010	Day of the Dead	Mexico	November	RELIGIOUS	1,500,000	3	Large (500k-2M)	Religious
11	F011	Glastonbury	United Kingdom	June	MUSIC	210,000	5	Medium (100k-500k)	Music
12	F012	Coachella	USA	April	MUSIC	250,000	3	Medium (100k-500k)	Music
13	F013	Eid al-Fitr	Various	Varies	RELIGIOUS	500,000,000	3	Mega (20M+)	Religious
14	F014	Running of the Bulls	Spain	July	CULTURAL	80,000	3	Small (<100k)	Cultural
15	F015	Lantern Festival	Taiwan	February	CULTURAL	1,200,000	2	Large (500k-2M)	Cultural

8) Macros for automated reporting

%macro report_by_type(type=);

    %local t;

    %let t = %upcase(&type);

    %put NOTE: Generating report for TYPE = &t;

    title "Festival Report - Type: &t";

    proc print data=work.world_festivals_enriched;

        where Type = "&t";

        var Festival_ID Festival_Name Country Month Duration_Days Visitor_Size

            Visitors_per_Year;

        format Visitors_per_Year comma15.;

        label Visitors_per_Year='Visitors/Year';

    run;

    proc summary data=work.world_festivals_enriched nway;

        where Type = "&t";

        var Visitors_per_Year Duration_Days;

        output out=work._summary_&t mean= meanDuration meanVisitors

               sum= totalDuration totalVisitors / autoname;

    run;

    proc print data=work._summary_&t noobs;

        title2 "Summary Statistics for &t";

    run;

    title;

%mend report_by_type;

%macro report_by_month(month=);

    %local m;

    %let m = %sysfunc(propcase(&month));

    %put NOTE: Generating report for MONTH = &m;

    title "Festival Report - Month: &m";

    proc print data=work.world_festivals_enriched;

        where Month = "&m";

        var Festival_ID Festival_Name Country Type Duration_Days Visitor_Size Visitors_per_Year;

        format Visitors_per_Year comma15.;

    run;

    proc summary data=work.world_festivals_enriched nway;

        where Month = "&m";

        var Visitors_per_Year Duration_Days;

        output out=work._summary_&m mean= meanDuration meanVisitors

               sum= totalDuration totalVisitors / autoname;

    run;

    proc print data=work._summary_&m noobs;

        title2 "Summary Statistics for &m";

    run;

    title;

%mend report_by_month;

%macro full_report(outlib=work);

    %put NOTE: Generating full automated report...;

    ods html file="&outlib..world_festivals_report.html" style=statistical;

    title "World Festivals — Comprehensive Report";

    /* Print master dataset */

    proc print data=work.world_festivals_enriched label;

        var Festival_ID Festival_Name Country Month Duration_Days Type_Label Visitor_Size Visitors_per_Year;

        format Visitors_per_Year comma15.;

    run;

    /* Print summary by Type */

    proc print data=work.world_festivals_summary_sorted label;

    run;

    /* Print monthly summary */

    proc print data=work.summary_by_month label;

    run;

    ods html close;

    title;

    %put NOTE: Report saved to &outlib..world_festivals_report.html ;

%mend full_report;

 %report_by_type(type=Religious); 

OUTPUT:

Festival Report - Type: RELIGIOUS

Obs	Festival_ID	Festival_Name	Country	Month	Duration_Days	Visitor_Size	Visitors_per_Year
1	F001	Diwali	India	October	5	Mega (20M+)	100,000,000
5	F005	Songkran	Thailand	April	3	Very Large (2M-20M)	2,000,000
9	F009	Holi	India	March	2	Mega (20M+)	25,000,000
10	F010	Day of the Dead	Mexico	November	3	Large (500k-2M)	1,500,000
13	F013	Eid al-Fitr	Various	Varies	3	Mega (20M+)	500,000,000

Festival Report - Type: RELIGIOUS

Summary Statistics for RELIGIOUS

_TYPE_	_FREQ_	meanDuration	meanVisitors	totalDuration	totalVisitors
0	5	125,700,000	3.2	628,500,000	16

 %report_by_month(month=April); 

OUTPUT:

Festival Report - Month: April

Obs	Festival_ID	Festival_Name	Country	Type	Duration_Days	Visitor_Size	Visitors_per_Year
4	F004	Cherry Blossom	Japan	SEASONAL	14	Mega (20M+)	30,000,000
5	F005	Songkran	Thailand	RELIGIOUS	3	Very Large (2M-20M)	2,000,000
12	F012	Coachella	USA	MUSIC	3	Medium (100k-500k)	250,000

Festival Report - Month: April

Summary Statistics for April

_TYPE_	_FREQ_	meanDuration	meanVisitors	totalDuration	totalVisitors
0	3	10,750,000	6.66667	32,250,000	20

 %full_report(outlib=/tmp); 

OUTPUT:

World Festivals — Comprehensive Report

Obs	Festival_ID	Festival_Name	Country	Month	Duration_Days	Type_Label	Visitor_Size	Visitors_per_Year
1	F001	Diwali	India	October	5	Religious	Mega (20M+)	100,000,000
2	F002	Carnival (Rio)	Brazil	February	7	Cultural	Very Large (2M-20M)	2,000,000
3	F003	Oktoberfest	Germany	September	16	Cultural	Very Large (2M-20M)	6,000,000
4	F004	Cherry Blossom	Japan	April	14	Seasonal	Mega (20M+)	30,000,000
5	F005	Songkran	Thailand	April	3	Religious	Very Large (2M-20M)	2,000,000
6	F006	Mardi Gras	USA	February	9	Cultural	Large (500k-2M)	1,000,000
7	F007	La Tomatina	Spain	August	1	Cultural	Medium (100k-500k)	220,000
8	F008	Harbin Ice Festival	China	January	60	Seasonal	Large (500k-2M)	1,000,000
9	F009	Holi	India	March	2	Religious	Mega (20M+)	25,000,000
10	F010	Day of the Dead	Mexico	November	3	Religious	Large (500k-2M)	1,500,000
11	F011	Glastonbury	United Kingdom	June	5	Music	Medium (100k-500k)	210,000
12	F012	Coachella	USA	April	3	Music	Medium (100k-500k)	250,000
13	F013	Eid al-Fitr	Various	Varies	3	Religious	Mega (20M+)	500,000,000
14	F014	Running of the Bulls	Spain	July	3	Cultural	Small (<100k)	80,000
15	F015	Lantern Festival	Taiwan	February	2	Cultural	Large (500k-2M)	1,200,000

World Festivals — Comprehensive Report

Obs	Type	N_Festivals	Mean_Visitors	Median_Visitors	Min_Visitors	Max_Visitors	Total_Visitors
1	RELIGIOUS	5	125,700,000	25,000,000	1,500,000	500,000,000	628,500,000
2	SEASONAL	2	15,500,000	15,500,000	1,000,000	30,000,000	31,000,000
3	CULTURAL	6	1,750,000	1,100,000	80,000	6,000,000	10,500,000
4	MUSIC	2	230,000	230,000	210,000	250,000	460,000

World Festivals — Comprehensive Report

Obs	Month	_TYPE_	_FREQ_	Mean_Visitors	Total_Visitors	Count
1	April	1	3	10,750,000	32,250,000	3
2	August	1	1	220,000	220,000	1
3	February	1	3	1,400,000	4,200,000	3
4	January	1	1	1,000,000	1,000,000	1
5	July	1	1	80,000	80,000	1
6	June	1	1	210,000	210,000	1
7	March	1	1	25,000,000	25,000,000	1
8	November	1	1	1,500,000	1,500,000	1
9	October	1	1	100,000,000	100,000,000	1
10	September	1	1	6,000,000	6,000,000	1
11	Varies	1	1	500,000,000	500,000,000	1

To Visit My Previous Different Types Of Oils Dataset:Click Here

To Visit My Previous Different Types Of Series 2025 Dataset:Click Here

To Visit My Previous Analyzing Yoga Asanas Worldwide Dataset:Click Here

To Visit My Previous Analyzing Indian Languages Dataset:Click Here  

Follow Us On : 

 

--- FOLLOW OUR BLOG FOR MORE INFORMATION.

--->PLEASE DO COMMENTS AND SHARE OUR BLOG.