203.ANALYZING | GLOBAL | AUDI | AQUARIUM | FISH | INVENTORY | USING | PROC PRINT | PROC MEANS | PROC FREQ | PROC SQL | PROC MACRO | PROC FORMAT | PROC REPORT | PROC TRANSPOSE | PROC SORT | PROC GCHART | PROC UNIVARIATE | PROC RANK | DATA CLEANING| METADATA | AUTOMATION | VALIDATION | MERGING | SAS POWER

ANALYZING | GLOBAL | AUDI | AQUARIUM | FISH | INVENTORY | USING | PROC PRINT | PROC MEANS | PROC FREQ | PROC SQL | PROC MACRO | PROC FORMAT | PROC REPORT | PROC TRANSPOSE | PROC SORT | PROC GCHART | PROC UNIVARIATE | PROC RANK | DATA CLEANING| METADATA | AUTOMATION | VALIDATION | MERGING | SAS POWER

/*A dataset of different types of fishes */

1.Creating the Fishes Dataset

data audi_fishes;

    length FishID $8 Species $15 Origin $15 Tank_Type $15;

    input FishID $ Species $ Origin $ Tank_Type $ Size_cm Temperature $ Price_per_fish;

    datalines;

F001 Guppy India Freshwater 5 Cold 200

F002 Betta Thailand Freshwater 7 Warm 350

F003 Goldfish China Freshwater 15 Cold 250

F004 Clownfish Australia Saltwater 11 Warm 1200

F005 NeonTetra Brazil Freshwater 4 Cold 180

F006 Discus Peru Freshwater 18 Warm 1500

F007 Angelfish Colombia Freshwater 12 Warm 950

F008 Koi Japan Freshwater 25 Cold 2500

F009 Molly Mexico Freshwater 6 Warm 300

F010 Tetra Congo Freshwater 5 Cold 170

F011 Gourami India Freshwater 10 Warm 600

F012 Oscar USA Freshwater 20 Warm 1800

F013 Puffer Maldives Saltwater 14 Warm 2200

F014 Seahorse Indonesia Saltwater 6 Warm 2600

F015 Snapper Australia Saltwater 30 Warm 3000

F016 Arowana Malaysia Freshwater 35 Warm 5000

F017 Catfish Vietnam Freshwater 22 Cold 1000

;

run;

proc print;run;

Output:

Obs	FishID	Species	Origin	Tank_Type	Size_cm	Temperature	Price_per_fish
1	F001	Guppy	India	Freshwater	5	Cold	200
2	F002	Betta	Thailand	Freshwater	7	Warm	350
3	F003	Goldfish	China	Freshwater	15	Cold	250
4	F004	Clownfish	Australia	Saltwater	11	Warm	1200
5	F005	NeonTetra	Brazil	Freshwater	4	Cold	180
6	F006	Discus	Peru	Freshwater	18	Warm	1500
7	F007	Angelfish	Colombia	Freshwater	12	Warm	950
8	F008	Koi	Japan	Freshwater	25	Cold	2500
9	F009	Molly	Mexico	Freshwater	6	Warm	300
10	F010	Tetra	Congo	Freshwater	5	Cold	170
11	F011	Gourami	India	Freshwater	10	Warm	600
12	F012	Oscar	USA	Freshwater	20	Warm	1800
13	F013	Puffer	Maldives	Saltwater	14	Warm	2200
14	F014	Seahorse	Indonesia	Saltwater	6	Warm	2600
15	F015	Snapper	Australia	Saltwater	30	Warm	3000
16	F016	Arowana	Malaysia	Freshwater	35	Warm	5000
17	F017	Catfish	Vietnam	Freshwater	22	Cold	1000

2.PROC PRINT – Displaying the Dataset

proc print data=audi_fishes;

    title "Audi Aquarium Fish Inventory - Initial Dataset";

run;

Output:

Audi Aquarium Fish Inventory - Initial Dataset

Obs	FishID	Species	Origin	Tank_Type	Size_cm	Temperature	Price_per_fish
1	F001	Guppy	India	Freshwater	5	Cold	200
2	F002	Betta	Thailand	Freshwater	7	Warm	350
3	F003	Goldfish	China	Freshwater	15	Cold	250
4	F004	Clownfish	Australia	Saltwater	11	Warm	1200
5	F005	NeonTetra	Brazil	Freshwater	4	Cold	180
6	F006	Discus	Peru	Freshwater	18	Warm	1500
7	F007	Angelfish	Colombia	Freshwater	12	Warm	950
8	F008	Koi	Japan	Freshwater	25	Cold	2500
9	F009	Molly	Mexico	Freshwater	6	Warm	300
10	F010	Tetra	Congo	Freshwater	5	Cold	170
11	F011	Gourami	India	Freshwater	10	Warm	600
12	F012	Oscar	USA	Freshwater	20	Warm	1800
13	F013	Puffer	Maldives	Saltwater	14	Warm	2200
14	F014	Seahorse	Indonesia	Saltwater	6	Warm	2600
15	F015	Snapper	Australia	Saltwater	30	Warm	3000
16	F016	Arowana	Malaysia	Freshwater	35	Warm	5000
17	F017	Catfish	Vietnam	Freshwater	22	Cold	1000

3.PROC SORT – Sorting by Origin

proc sort data=audi_fishes out=sorted_fishes;

    by Origin;

run;

proc print data=sorted_fishes;

    title "Fishes Sorted by Origin";

run;

Output:

Fishes Sorted by Origin

Obs	FishID	Species	Origin	Tank_Type	Size_cm	Temperature	Price_per_fish
1	F004	Clownfish	Australia	Saltwater	11	Warm	1200
2	F015	Snapper	Australia	Saltwater	30	Warm	3000
3	F005	NeonTetra	Brazil	Freshwater	4	Cold	180
4	F003	Goldfish	China	Freshwater	15	Cold	250
5	F007	Angelfish	Colombia	Freshwater	12	Warm	950
6	F010	Tetra	Congo	Freshwater	5	Cold	170
7	F001	Guppy	India	Freshwater	5	Cold	200
8	F011	Gourami	India	Freshwater	10	Warm	600
9	F014	Seahorse	Indonesia	Saltwater	6	Warm	2600
10	F008	Koi	Japan	Freshwater	25	Cold	2500
11	F016	Arowana	Malaysia	Freshwater	35	Warm	5000
12	F013	Puffer	Maldives	Saltwater	14	Warm	2200
13	F009	Molly	Mexico	Freshwater	6	Warm	300
14	F006	Discus	Peru	Freshwater	18	Warm	1500
15	F002	Betta	Thailand	Freshwater	7	Warm	350
16	F012	Oscar	USA	Freshwater	20	Warm	1800
17	F017	Catfish	Vietnam	Freshwater	22	Cold	1000

4.PROC FREQ – Frequency of Tank Types and Temperature Preferences

proc freq data=audi_fishes;

    tables Tank_Type Temperature / nocum;

    title "Distribution of Fish Tank Types and Temperature Preferences";

run;

Output:

Distribution of Fish Tank Types and Temperature Preferences

The FREQ Procedure

Tank_Type	Frequency	Percent
Freshwater	13	76.47
Saltwater	4	23.53

Temperature	Frequency	Percent
Cold	6	35.29
Warm	11	64.71

5.PROC MEANS – Average and Maximum Price by Tank Type

proc means data=audi_fishes mean maxdec=2 max;

    class Tank_Type;

    var Price_per_fish;

    title "Average and Maximum Price by Tank Type";

run;

Output:

Average and Maximum Price by Tank Type

The MEANS Procedure

Analysis Variable : Price_per_fish
Tank_Type	N Obs	Mean	Maximum
Freshwater	13	1138.46	5000.00
Saltwater	4	2250.00	3000.00

6.PROC FORMAT – Custom Format for Fish Size

proc format;

    value sizefmt

        low-10 = 'Small'

        11-20 = 'Medium'

        21-high = 'Large';

run;

data audi_fishes_fmt;

    set audi_fishes;

    Size_Category = put(Size_cm, sizefmt.);

run;

proc print;run;

Output:

Obs	FishID	Species	Origin	Tank_Type	Size_cm	Temperature	Price_per_fish	Size_Category
1	F001	Guppy	India	Freshwater	5	Cold	200	Small
2	F002	Betta	Thailand	Freshwater	7	Warm	350	Small
3	F003	Goldfish	China	Freshwater	15	Cold	250	Medium
4	F004	Clownfish	Australia	Saltwater	11	Warm	1200	Medium
5	F005	NeonTetra	Brazil	Freshwater	4	Cold	180	Small
6	F006	Discus	Peru	Freshwater	18	Warm	1500	Medium
7	F007	Angelfish	Colombia	Freshwater	12	Warm	950	Medium
8	F008	Koi	Japan	Freshwater	25	Cold	2500	Large
9	F009	Molly	Mexico	Freshwater	6	Warm	300	Small
10	F010	Tetra	Congo	Freshwater	5	Cold	170	Small
11	F011	Gourami	India	Freshwater	10	Warm	600	Small
12	F012	Oscar	USA	Freshwater	20	Warm	1800	Medium
13	F013	Puffer	Maldives	Saltwater	14	Warm	2200	Medium
14	F014	Seahorse	Indonesia	Saltwater	6	Warm	2600	Small
15	F015	Snapper	Australia	Saltwater	30	Warm	3000	Large
16	F016	Arowana	Malaysia	Freshwater	35	Warm	5000	Large
17	F017	Catfish	Vietnam	Freshwater	22	Cold	1000	Large

proc freq data=audi_fishes_fmt;

    tables Size_Category;

    title "Frequency of Fish by Size Category";

run;

Output:

Frequency of Fish by Size Category

The FREQ Procedure

Size_Category	Frequency	Percent	Cumulative Frequency	Cumulative Percent
Large	4	23.53	4	23.53
Medium	6	35.29	10	58.82
Small	7	41.18	17	100.00

7.PROC SQL – Top 5 Most Expensive Fish

proc sql outobs=5;

    title "Top 5 Most Expensive Fishes in Audi Aquarium";

    select FishID, Species, Price_per_fish

    from audi_fishes

    order by Price_per_fish desc;

quit;

Output:

Top 5 Most Expensive Fishes in Audi Aquarium

FishID	Species	Price_per_fish
F016	Arowana	5000
F015	Snapper	3000
F014	Seahorse	2600
F008	Koi	2500
F013	Puffer	2200

8.PROC TRANSPOSE – Convert Species per Origin

proc transpose data=audi_fishes out=transposed_fishes prefix=Fish_;

    by Origin notsorted;

    id Species;

    var Price_per_fish;

run;

proc print data=transposed_fishes;

    title "Transposed Fish Prices by Origin";

run;

Output:

Transposed Fish Prices by Origin

Obs	Origin	_NAME_	Fish_Guppy	Fish_Betta	Fish_Goldfish	Fish_Clownfish	Fish_NeonTetra	Fish_Discus	Fish_Angelfish	Fish_Koi	Fish_Molly	Fish_Tetra	Fish_Gourami	Fish_Oscar	Fish_Puffer	Fish_Seahorse	Fish_Snapper	Fish_Arowana	Fish_Catfish
1	India	Price_per_fish	200	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.
2	Thailand	Price_per_fish	.	350	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.
3	China	Price_per_fish	.	.	250	.	.	.	.	.	.	.	.	.	.	.	.	.	.
4	Australia	Price_per_fish	.	.	.	1200	.	.	.	.	.	.	.	.	.	.	.	.	.
5	Brazil	Price_per_fish	.	.	.	.	180	.	.	.	.	.	.	.	.	.	.	.	.
6	Peru	Price_per_fish	.	.	.	.	.	1500	.	.	.	.	.	.	.	.	.	.	.
7	Colombia	Price_per_fish	.	.	.	.	.	.	950	.	.	.	.	.	.	.	.	.	.
8	Japan	Price_per_fish	.	.	.	.	.	.	.	2500	.	.	.	.	.	.	.	.	.
9	Mexico	Price_per_fish	.	.	.	.	.	.	.	.	300	.	.	.	.	.	.	.	.
10	Congo	Price_per_fish	.	.	.	.	.	.	.	.	.	170	.	.	.	.	.	.	.
11	India	Price_per_fish	.	.	.	.	.	.	.	.	.	.	600	.	.	.	.	.	.
12	USA	Price_per_fish	.	.	.	.	.	.	.	.	.	.	.	1800	.	.	.	.	.
13	Maldives	Price_per_fish	.	.	.	.	.	.	.	.	.	.	.	.	2200	.	.	.	.
14	Indonesia	Price_per_fish	.	.	.	.	.	.	.	.	.	.	.	.	.	2600	.	.	.
15	Australia	Price_per_fish	.	.	.	.	.	.	.	.	.	.	.	.	.	.	3000	.	.
16	Malaysia	Price_per_fish	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	5000	.
17	Vietnam	Price_per_fish	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	1000

9.PROC GCHART – Bar Chart of Fish Counts by Origin

proc gchart data=audi_fishes;

    vbar Origin / discrete type=freq;

    title "Fish Count by Country of Origin";

run;

quit;

Log:

NOTE: 17413 bytes written to C:\Users\Lenovo\AppData\Local\Temp\SAS Temporary

      Files\_TD12356_DESKTOP-QFAA4KV_\gchart.png.

141  quit;

NOTE: There were 17 observations read from the data set WORK.AUDI_FISHES.

NOTE: PROCEDURE GCHART used (Total process time):

      real time           2.28 seconds

      cpu time            0.64 seconds

10.PROC REPORT – Summary Report of Freshwater vs Saltwater Fishes

proc report data=audi_fishes nowd;

    column Tank_Type Size_cm Price_per_fish;

    define Tank_Type / group;

    define Size_cm / analysis mean;

    define Price_per_fish / analysis sum;

    title "Summary Report: Tank Type vs Average Size and Total Price";

run;

Output:

Summary Report: Tank Type vs Average Size and Total Price

Tank_Type	Size_cm	Price_per_fish
Freshwater	14.153846	14800
Saltwater	15.25	9000

11.Using Macros – Reusable Analysis by Temperature

%macro analyze_temp(temp);

    proc sql;

        title "Fish Analysis for Temperature: &temp.";

        select Species, Origin, Price_per_fish

        from audi_fishes

        where Temperature="&temp."

        order by Price_per_fish desc;

    quit;

%mend;

%analyze_temp(Warm);

Output:

Fish Analysis for Temperature: Warm

Species	Origin	Price_per_fish
Arowana	Malaysia	5000
Snapper	Australia	3000
Seahorse	Indonesia	2600
Puffer	Maldives	2200
Oscar	USA	1800
Discus	Peru	1500
Clownfish	Australia	1200
Angelfish	Colombia	950
Gourami	India	600
Betta	Thailand	350
Molly	Mexico	300

%analyze_temp(Cold);

Output:

Fish Analysis for Temperature: Cold

Species	Origin	Price_per_fish
Koi	Japan	2500
Catfish	Vietnam	1000
Goldfish	China	250
Guppy	India	200
NeonTetra	Brazil	180
Tetra	Congo	170

12.PROC SQL JOIN – Combine with Inventory Table

data fish_stock;

    input FishID $ Quantity Available;

    datalines;

F001 25 1

F002 30 1

F003 20 1

F004 5 1

F005 40 1

F006 3 1

F007 6 1

F008 2 1

F009 10 1

F010 50 1

F011 7 1

F012 4 1

F013 2 1

F014 1 1

F015 1 1

F016 1 1

F017 8 1

;

run;

proc print;run;

Output:

Obs	FishID	Quantity	Available
1	F001	25	1
2	F002	30	1
3	F003	20	1
4	F004	5	1
5	F005	40	1
6	F006	3	1
7	F007	6	1
8	F008	2	1
9	F009	10	1
10	F010	50	1
11	F011	7	1
12	F012	4	1
13	F013	2	1
14	F014	1	1
15	F015	1	1
16	F016	1	1
17	F017	8	1

proc sql;

    create table fish_full as

    select a.*, b.Quantity, b.Available

    from audi_fishes a

    inner join fish_stock b

    on a.FishID = b.FishID;

quit;

proc print data=fish_full;

    title "Combined Fish Dataset with Quantity and Availability";

run;

Output:

Combined Fish Dataset with Quantity and Availability

Obs	FishID	Species	Origin	Tank_Type	Size_cm	Temperature	Price_per_fish	Quantity	Available
1	F001	Guppy	India	Freshwater	5	Cold	200	25	1
2	F002	Betta	Thailand	Freshwater	7	Warm	350	30	1
3	F003	Goldfish	China	Freshwater	15	Cold	250	20	1
4	F004	Clownfish	Australia	Saltwater	11	Warm	1200	5	1
5	F005	NeonTetra	Brazil	Freshwater	4	Cold	180	40	1
6	F006	Discus	Peru	Freshwater	18	Warm	1500	3	1
7	F007	Angelfish	Colombia	Freshwater	12	Warm	950	6	1
8	F008	Koi	Japan	Freshwater	25	Cold	2500	2	1
9	F009	Molly	Mexico	Freshwater	6	Warm	300	10	1
10	F010	Tetra	Congo	Freshwater	5	Cold	170	50	1
11	F011	Gourami	India	Freshwater	10	Warm	600	7	1
12	F012	Oscar	USA	Freshwater	20	Warm	1800	4	1
13	F013	Puffer	Maldives	Saltwater	14	Warm	2200	2	1
14	F014	Seahorse	Indonesia	Saltwater	6	Warm	2600	1	1
15	F015	Snapper	Australia	Saltwater	30	Warm	3000	1	1
16	F016	Arowana	Malaysia	Freshwater	35	Warm	5000	1	1
17	F017	Catfish	Vietnam	Freshwater	22	Cold	1000	8	1

13.PROC MEANS on Joined Dataset – Total Inventory Value

data fish_full;

    set fish_full;

    Total_Value = Price_per_fish * Quantity;

run;

proc print;run;

Output:

Obs	FishID	Species	Origin	Tank_Type	Size_cm	Temperature	Price_per_fish	Quantity	Available	Total_Value
1	F001	Guppy	India	Freshwater	5	Cold	200	25	1	5000
2	F002	Betta	Thailand	Freshwater	7	Warm	350	30	1	10500
3	F003	Goldfish	China	Freshwater	15	Cold	250	20	1	5000
4	F004	Clownfish	Australia	Saltwater	11	Warm	1200	5	1	6000
5	F005	NeonTetra	Brazil	Freshwater	4	Cold	180	40	1	7200
6	F006	Discus	Peru	Freshwater	18	Warm	1500	3	1	4500
7	F007	Angelfish	Colombia	Freshwater	12	Warm	950	6	1	5700
8	F008	Koi	Japan	Freshwater	25	Cold	2500	2	1	5000
9	F009	Molly	Mexico	Freshwater	6	Warm	300	10	1	3000
10	F010	Tetra	Congo	Freshwater	5	Cold	170	50	1	8500
11	F011	Gourami	India	Freshwater	10	Warm	600	7	1	4200
12	F012	Oscar	USA	Freshwater	20	Warm	1800	4	1	7200
13	F013	Puffer	Maldives	Saltwater	14	Warm	2200	2	1	4400
14	F014	Seahorse	Indonesia	Saltwater	6	Warm	2600	1	1	2600
15	F015	Snapper	Australia	Saltwater	30	Warm	3000	1	1	3000
16	F016	Arowana	Malaysia	Freshwater	35	Warm	5000	1	1	5000
17	F017	Catfish	Vietnam	Freshwater	22	Cold	1000	8	1	8000

proc means data=fish_full sum;

    var Total_Value;

    title "Total Inventory Value of All Aquarium Fishes";

run;

Output:

Total Inventory Value of All Aquarium Fishes

The MEANS Procedure

Analysis Variable : Total_Value
Sum
94800.00

14.PROC SQL – Highest Value Fish by Country

proc sql;

    title "Most Valuable Fish by Origin";

    select Origin, Species, max(Total_Value) as Max_Value

    from fish_full

    group by Origin;

quit;

Output:

Most Valuable Fish by Origin

Origin	Species	Max_Value
Australia	Snapper	6000
Australia	Clownfish	6000
Brazil	NeonTetra	7200
China	Goldfish	5000
Colombia	Angelfish	5700
Congo	Tetra	8500
India	Gourami	5000
India	Guppy	5000
Indonesia	Seahorse	2600
Japan	Koi	5000
Malaysia	Arowana	5000
Maldives	Puffer	4400
Mexico	Molly	3000
Peru	Discus	4500
Thailand	Betta	10500
USA	Oscar	7200
Vietnam	Catfish	8000

15.PROC SORT with NODUPKEY – Unique Origins

proc sort data=audi_fishes out=unique_origins nodupkey;

    by Origin;

run;

proc print data=unique_origins;

    title "Unique Fish Origins in Dataset";

run;

Output:

Unique Fish Origins in Dataset

Obs	FishID	Species	Origin	Tank_Type	Size_cm	Temperature	Price_per_fish
1	F004	Clownfish	Australia	Saltwater	11	Warm	1200
2	F005	NeonTetra	Brazil	Freshwater	4	Cold	180
3	F003	Goldfish	China	Freshwater	15	Cold	250
4	F007	Angelfish	Colombia	Freshwater	12	Warm	950
5	F010	Tetra	Congo	Freshwater	5	Cold	170
6	F001	Guppy	India	Freshwater	5	Cold	200
7	F014	Seahorse	Indonesia	Saltwater	6	Warm	2600
8	F008	Koi	Japan	Freshwater	25	Cold	2500
9	F016	Arowana	Malaysia	Freshwater	35	Warm	5000
10	F013	Puffer	Maldives	Saltwater	14	Warm	2200
11	F009	Molly	Mexico	Freshwater	6	Warm	300
12	F006	Discus	Peru	Freshwater	18	Warm	1500
13	F002	Betta	Thailand	Freshwater	7	Warm	350
14	F012	Oscar	USA	Freshwater	20	Warm	1800
15	F017	Catfish	Vietnam	Freshwater	22	Cold	1000

To Visit My Previous Sas Macro Functions:Click Here

To Visit My Previous lnput Proc Sql In Sas:Click Here

To Visit My Previous Advanced Clinical Trials:Click Here

To Visit My Previous Urban Traffic Pattern Dataset: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