283.How Can SAS Analyze Different Types of Biryani Across the World Using PROC SQL, PROC FREQ, and PROC REPORT?

How Can SAS Analyze Different Types of Biryani Across the World Using PROC SQL, PROC FREQ, and PROC REPORT?

/*Creating a dataset of biryani types*/

 1) Create formats for readable outputs

PURPOSE: Define user-friendly labels for categorical variables

proc format;

  value $meatfmt

    'Mutton' = 'Mutton'

    'Chicken' = 'Chicken'

    'Beef' = 'Beef'

    'Fish' = 'Fish'

    'Vegetarian' = 'Vegetarian';

  value slevel

    1 = 'Mild'

    2 = 'Medium'

    3 = 'Hot'

    4 = 'Very Hot';

run;

Log:

NOTE: Format $MEATFMT has been output.

NOTE: Format SLEVEL has been output.

2) Creating the dataset with 7 variables and 12 observations

PURPOSE: Simulate a small but realistic global biryani dataset

options nocenter;

data biryani_master;

  infile datalines dsd truncover;

  length Biryani_ID 8 Biryani_Name $40 Country $30 Meat_Type $12 Rice_Type $20;

  input Biryani_ID Biryani_Name :$40. Country :$30. Meat_Type :$12. Spice_Level 

        Rice_Type :$20. Price_INR;

  format Spice_Level slevel.;

datalines;

1,Hyderabadi Dum Biryani,India,Mutton,3,Basmati,420

2,Kacchi Biryani,Bangladesh,Mutton,3,Basmati,380

3,Ambur Biryani,India,Chicken,2,Short-Grain,220

4,Beary Biryani,India,Beef,3,Parboiled,250

5,Thalassery Biryani,India,Chicken,2,Jeerakasala,300

6,Lucknowi (Awadhi) Biryani,India,Mutton,2,Basmati,450

7,Tehari Vegetarian Biryani,Pakistan,Vegetarian,1,Long-Grain,150

8,Thai-style Seafood Biryani,Thailand,Fish,2,Long-Grain,320

9,Calcutta Biryani,India,Chicken,2,Basmati,200

10,Sindhi Biryani,Pakistan,Beef,4,Basmati,360

11,Malabar Biryani,India,Chicken,3,Jeerakasala,340

12,Mughlai Hyderabadi (Kachchi variant),India,Mutton,3,Basmati,480

;

run;

proc print;run;

Output:

Obs	Biryani_ID	Biryani_Name	Country	Meat_Type	Rice_Type	Spice_Level	Price_INR
1	1	Hyderabadi Dum Biryani	India	Mutton	Basmati	Hot	420
2	2	Kacchi Biryani	Bangladesh	Mutton	Basmati	Hot	380
3	3	Ambur Biryani	India	Chicken	Short-Grain	Medium	220
4	4	Beary Biryani	India	Beef	Parboiled	Hot	250
5	5	Thalassery Biryani	India	Chicken	Jeerakasala	Medium	300
6	6	Lucknowi (Awadhi) Biryani	India	Mutton	Basmati	Medium	450
7	7	Tehari Vegetarian Biryani	Pakistan	Vegetarian	Long-Grain	Mild	150
8	8	Thai-style Seafood Biryani	Thailand	Fish	Long-Grain	Medium	320
9	9	Calcutta Biryani	India	Chicken	Basmati	Medium	200
10	10	Sindhi Biryani	Pakistan	Beef	Basmati	Very Hot	360
11	11	Malabar Biryani	India	Chicken	Jeerakasala	Hot	340
12	12	Mughlai Hyderabadi (Kachchi variant)	India	Mutton	Basmati	Hot	480

3) Quick metadata check

PURPOSE: Show dataset structure and variable attributes for QC

proc contents data=biryani_master varnum;

  title "PROC CONTENTS: Biryani Master - Metadata and Attributes";

run;

Output:

PROC CONTENTS: Biryani Master - Metadata and Attributes

The CONTENTS Procedure

Data Set Name	WORK.BIRYANI_MASTER	Observations	12
Member Type	DATA	Variables	7
Engine	V9	Indexes	0
Created	09/08/2025 18:02:10	Observation Length	128
Last Modified	09/08/2025 18:02:10	Deleted Observations	0
Protection		Compressed	NO
Data Set Type		Sorted	NO
Label
Data Representation	SOLARIS_X86_64, LINUX_X86_64, ALPHA_TRU64, LINUX_IA64
Encoding	utf-8 Unicode (UTF-8)

Engine/Host Dependent Information
Data Set Page Size	131072
Number of Data Set Pages	1
First Data Page	1
Max Obs per Page	1022
Obs in First Data Page	12
Number of Data Set Repairs	0
Filename	/saswork/SAS_work225800008908_odaws01-apse1-2.oda.sas.com/SAS_work65CF00008908_odaws01-apse1-2.oda.sas.com/biryani_master.sas7bdat
Release Created	9.0401M8
Host Created	Linux
Inode Number	1091090
Access Permission	rw-r--r--
Owner Name	u63247146
File Size	256KB
File Size (bytes)	262144

Variables in Creation Order
#	Variable	Type	Len	Format
1	Biryani_ID	Num	8
2	Biryani_Name	Char	40
3	Country	Char	30
4	Meat_Type	Char	12
5	Rice_Type	Char	20
6	Spice_Level	Num	8	SLEVEL.
7	Price_INR	Num	8

4) Display full dataset

PURPOSE: Print the dataset to inspect values visually

proc print data=biryani_master label noobs;

  title "PROC PRINT: Full Biryani Dataset";

  var Biryani_ID Biryani_Name Country Meat_Type Spice_Level Rice_Type Price_INR;

run;

Output:

PROC PRINT: Full Biryani Dataset

Biryani_ID	Biryani_Name	Country	Meat_Type	Spice_Level	Rice_Type	Price_INR
1	Hyderabadi Dum Biryani	India	Mutton	Hot	Basmati	420
2	Kacchi Biryani	Bangladesh	Mutton	Hot	Basmati	380
3	Ambur Biryani	India	Chicken	Medium	Short-Grain	220
4	Beary Biryani	India	Beef	Hot	Parboiled	250
5	Thalassery Biryani	India	Chicken	Medium	Jeerakasala	300
6	Lucknowi (Awadhi) Biryani	India	Mutton	Medium	Basmati	450
7	Tehari Vegetarian Biryani	Pakistan	Vegetarian	Mild	Long-Grain	150
8	Thai-style Seafood Biryani	Thailand	Fish	Medium	Long-Grain	320
9	Calcutta Biryani	India	Chicken	Medium	Basmati	200
10	Sindhi Biryani	Pakistan	Beef	Very Hot	Basmati	360
11	Malabar Biryani	India	Chicken	Hot	Jeerakasala	340
12	Mughlai Hyderabadi (Kachchi variant)	India	Mutton	Hot	Basmati	480

5) Numeric summary: price statistics

PURPOSE: Use PROC MEANS to compute count, mean, std, min, max for Price_INR

proc means data=biryani_master n mean std min max maxdec=2;

  var Price_INR Spice_Level;

  title "PROC MEANS: Price and Spice Level Summary";

run;

Output:

PROC MEANS: Price and Spice Level Summary

The MEANS Procedure

Variable	N	Mean	Std Dev	Minimum	Maximum
Price_INR Spice_Level	12 12	322.50 2.50	102.97 0.80	150.00 1.00	480.00 4.00

6) Frequency tables for categorical variables

PURPOSE: Use PROC FREQ to get frequency distribution of Meat_Type and Spice_Level

proc freq data=biryani_master;

  tables Meat_Type Spice_Level / nocum nopercent;

  format Spice_Level slevel.;

  title "PROC FREQ: Meat Type and Spice Level Distribution";

run;

Output:

PROC FREQ: Meat Type and Spice Level Distribution

The FREQ Procedure

Meat_Type	Frequency
Beef	2
Chicken	4
Fish	1
Mutton	4
Vegetarian	1

Spice_Level	Frequency
Mild	1
Medium	5
Hot	5
Very Hot	1

7) Sort dataset by Price descending

PURPOSE: Use PROC SORT to prepare ordered dataset for reporting

proc sort data=biryani_master out=biryani_sorted;

  by descending Price_INR;

run;

proc print;run;

Output:

Obs	Biryani_ID	Biryani_Name	Country	Meat_Type	Rice_Type	Spice_Level	Price_INR
1	12	Mughlai Hyderabadi (Kachchi variant)	India	Mutton	Basmati	Hot	480
2	6	Lucknowi (Awadhi) Biryani	India	Mutton	Basmati	Medium	450
3	1	Hyderabadi Dum Biryani	India	Mutton	Basmati	Hot	420
4	2	Kacchi Biryani	Bangladesh	Mutton	Basmati	Hot	380
5	10	Sindhi Biryani	Pakistan	Beef	Basmati	Very Hot	360
6	11	Malabar Biryani	India	Chicken	Jeerakasala	Hot	340
7	8	Thai-style Seafood Biryani	Thailand	Fish	Long-Grain	Medium	320
8	5	Thalassery Biryani	India	Chicken	Jeerakasala	Medium	300
9	4	Beary Biryani	India	Beef	Parboiled	Hot	250
10	3	Ambur Biryani	India	Chicken	Short-Grain	Medium	220
11	9	Calcutta Biryani	India	Chicken	Basmati	Medium	200
12	7	Tehari Vegetarian Biryani	Pakistan	Vegetarian	Long-Grain	Mild	150

8) Use PROC SQL to create a summary table: average price by Meat_Type

PURPOSE: Demonstrate SQL aggregation to compute avg price per meat type

proc sql;

  create table avg_price_by_meat as

  select Meat_Type,

         count(*) as N,

         round(mean(Price_INR),0.01) as Avg_Price format=8.2,

         min(Price_INR) as Min_Price,

         max(Price_INR) as Max_Price

  from biryani_master

  group by Meat_Type

  order by Avg_Price desc;

quit;

proc print data=avg_price_by_meat noobs;

  title "PROC SQL: Average Price by Meat Type";

run;

Output:

PROC SQL: Average Price by Meat Type

Meat_Type	N	Avg_Price	Min_Price	Max_Price
Mutton	4	432.50	380	480
Fish	1	320.00	320	320
Beef	2	305.00	250	360
Chicken	4	265.00	200	340
Vegetarian	1	150.00	150	150

9) TRANSPOSE example: pivot Price by Country

PURPOSE: Use PROC TRANSPOSE to pivot table of prices for a country-wise view

proc transpose data=biryani_master out=price_by_country prefix=Price_;

  by Country notsorted;

  id Biryani_ID;

  var Price_INR;

run;

proc print data=price_by_country noobs;

  title "PROC TRANSPOSE: Price by Country (pivoted by Biryani_ID)";

run;

Output:

PROC TRANSPOSE: Price by Country (pivoted by Biryani_ID)

Country	_NAME_	Price_1	Price_2	Price_3	Price_4	Price_5	Price_6	Price_7	Price_8	Price_9	Price_10	Price_11	Price_12
India	Price_INR	420	.	.	.	.	.	.	.	.	.	.	.
Bangladesh	Price_INR	.	380	.	.	.	.	.	.	.	.	.	.
India	Price_INR	.	.	220	250	300	450	.	.	.	.	.	.
Pakistan	Price_INR	.	.	.	.	.	.	150	.	.	.	.	.
Thailand	Price_INR	.	.	.	.	.	.	.	320	.	.	.	.
India	Price_INR	.	.	.	.	.	.	.	.	200	.	.	.
Pakistan	Price_INR	.	.	.	.	.	.	.	.	.	360	.	.
India	Price_INR	.	.	.	.	.	.	.	.	.	.	340	480

10) PROC REPORT: Advanced tabular report with computed column

PURPOSE: Use PROC REPORT to display custom report and compute price category

proc format;

  value pcat

    low - 199 = 'Budget'

    200 - 349 = 'Mid'

    350 - high = 'Premium';

run;

Log:

NOTE: Format PCAT has been output.

proc report data=biryani_sorted nowd;

  column Biryani_ID Biryani_Name Country Meat_Type Spice_Level Rice_Type Price_INR price_cat;

  define price_cat / computed 'Price Category';

  compute price_cat;

    if Price_INR < 200 then price_cat = 'Budget';

    else if 200 <= Price_INR < 350 then price_cat = 'Mid';

    else price_cat = 'Premium';

  endcomp;

  title "PROC REPORT: Biryani Report with Price Category";

run;

Output:

PROC REPORT: Biryani Report with Price Category

Biryani_ID	Biryani_Name	Country	Meat_Type	Spice_Level	Rice_Type	Price_INR	Price Category
12	Mughlai Hyderabadi (Kachchi variant)	India	Mutton	Hot	Basmati	480	.
6	Lucknowi (Awadhi) Biryani	India	Mutton	Medium	Basmati	450	.
1	Hyderabadi Dum Biryani	India	Mutton	Hot	Basmati	420	.
2	Kacchi Biryani	Bangladesh	Mutton	Hot	Basmati	380	.
10	Sindhi Biryani	Pakistan	Beef	Very Hot	Basmati	360	.
11	Malabar Biryani	India	Chicken	Hot	Jeerakasala	340	.
8	Thai-style Seafood Biryani	Thailand	Fish	Medium	Long-Grain	320	.
5	Thalassery Biryani	India	Chicken	Medium	Jeerakasala	300	.
4	Beary Biryani	India	Beef	Hot	Parboiled	250	.
3	Ambur Biryani	India	Chicken	Medium	Short-Grain	220	.
9	Calcutta Biryani	India	Chicken	Medium	Basmati	200	.
7	Tehari Vegetarian Biryani	Pakistan	Vegetarian	Mild	Long-Grain	150	.

11) PROC TABULATE: Cross-tab summary (Meat_Type x Spice_Level)

PURPOSE: Use PROC TABULATE to provide a compact cross-tab of counts and mean prices

proc tabulate data=biryani_master;

  class Meat_Type Spice_Level;

  var Price_INR;

  table Meat_Type,

        Spice_Level*(Price_INR*(n='Count' mean='AvgPrice'*f=8.2)) / rts=20;

  format Spice_Level slevel.;

  title "PROC TABULATE: Meat Type by Spice Level (count & mean price)";

run;

Output:

PROC TABULATE: Meat Type by Spice Level (count & mean price)

	Spice_Level
	Mild		Medium		Hot		Very Hot
	Price_INR		Price_INR		Price_INR		Price_INR
	Count	AvgPrice	Count	AvgPrice	Count	AvgPrice	Count	AvgPrice
Meat_Type	.	.	.	.	1	250.00	1	360.00
Beef
Chicken	.	.	3	240.00	1	340.00	.	.
Fish	.	.	1	320.00	.	.	.	.
Mutton	.	.	1	450.00	3	426.67	.	.
Vegetarian	1	150.00	.	.	.	.	.	.

12) PROC UNIVARIATE: Check distribution of price for outliers

PURPOSE: Use PROC UNIVARIATE to examine price distribution and check outliers

proc univariate data=biryani_master cibasic;

  var Price_INR;

  title "PROC UNIVARIATE: Price Distribution and Outlier Check";

run;

Output:

PROC UNIVARIATE: Price Distribution and Outlier Check

The UNIVARIATE Procedure

Variable: Price_INR

Moments
N	12	Sum Weights	12
Mean	322.5	Sum Observations	3870
Std Deviation	102.967338	Variance	10602.2727
Skewness	-0.1284958	Kurtosis	-0.9046522
Uncorrected SS	1364700	Corrected SS	116625
Coeff Variation	31.9278568	Std Error Mean	29.7241102

Basic Statistical Measures
Location		Variability
Mean	322.5000	Std Deviation	102.96734
Median	330.0000	Variance	10602
Mode	.	Range	330.00000
		Interquartile Range	165.00000

Basic Confidence Limits Assuming Normality
Parameter	Estimate	95% Confidence Limits
Mean	322.50000	257.07767	387.92233
Std Deviation	102.96734	72.94156	174.82598
Variance	10602	5320	30564

Tests for Location: Mu0=0
Test	Statistic		p Value
Student's t	t	10.84978	Pr > |t|	<.0001
Sign	M	6	Pr >= |M|	0.0005
Signed Rank	S	39	Pr >= |S|	0.0005

Quantiles (Definition 5)
Level	Quantile
100% Max	480
99%	480
95%	480
90%	450
75% Q3	400
50% Median	330
25% Q1	235
10%	200
5%	150
1%	150
0% Min	150

Extreme Observations
Lowest		Highest
Value	Obs	Value	Obs
150	7	360	10
200	9	380	2
220	3	420	1
250	4	450	6
300	5	480	12

13) Simple visualization: bar chart of counts by Meat_Type (requires ODS graphics)

PURPOSE: Use PROC SGPLOT to visualize counts of biryanis by meat type

proc sgplot data=biryani_master;

  vbar Meat_Type / datalabel;

  title "PROC SGPLOT: Count of Biryani Types by Meat_Type";

run;

Output:

14) MACRO: dynamic summary by any categorical variable

PURPOSE: Macro to compute frequency and average price for a given categorical variable

%macro summary_by(catvar=);

  /* single-line purpose: compute counts and average price by a categorical variable */

  proc sql;

    create table summary_&catvar as

    select &catvar,

           count(*) as N,

           round(mean(Price_INR),0.01) as AvgPrice format=8.2

    from biryani_master

    group by &catvar

    order by N desc;

  quit;

  proc print data=summary_&catvar noobs;

    title "Summary by &catvar";

  run;

%mend summary_by;

%summary_by(catvar=Country);

Output:

Summary by Country

Country	N	AvgPrice
India	8	332.50
Pakistan	2	255.00
Bangladesh	1	380.00
Thailand	1	320.00

%summary_by(catvar=Spice_Level);

Output:

Summary by Spice_Level

Spice_Level	N	AvgPrice
Hot	5	374.00
Medium	5	298.00
Mild	1	150.00
Very Hot	1	360.00

15) Basic QC: check for missing values and duplicates

PURPOSE: Run checks for missingness and duplicate Biryani_IDs

proc sql;

  select sum(case when Biryani_ID is null then 1 else 0 end) as missing_id,

         sum(case when Biryani_Name is null then 1 else 0 end) as missing_name,

         count(distinct Biryani_ID) as distinct_ids,

         count(*) as total_rows

  from biryani_master;

quit;

proc sort data=biryani_master out=dup_check nodupkey dupout=dup_biryani;

  by Biryani_ID;

run;

proc print data=dup_biryani noobs;

  title "Duplicate Biryani_IDs (should be empty)";

run;

Output:

missing_id	missing_name	distinct_ids	total_rows
0	0	12	12

To Visit My Previous E-Commerce Dataset:Click Here

To Visit My Previous Length,Input,Retain Statements:Click Here

To Visit My Previous Urban Traffic Dataset:Click Here

To Visit My Previous Home Energy Consumption Dataset:Click Here

Follow Us On : 

 

--- FOLLOW OUR BLOG FOR MORE INFORMATION.

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