274.CYLINDER DATA MANAGEMENT AND ANALYTICS IN SAS: FROM CREATION TO INSIGHTS USING PROC PRINT | PROC FREQ | PROC MEANS | PROC SQL | DATA STEP | MACROS

CYLINDER DATA MANAGEMENT AND ANALYTICS IN SAS: FROM CREATION TO INSIGHTS USING  PROC PRINT | PROC FREQ | PROC MEANS | PROC SQL | DATA STEP | MACROS

 /*Creating A Dataset Of Cylinders */

Step 1: Create the Cylinder Dataset with >10 Observations 

data cylinders;

  length Cylinder_ID $4 Type $20 Material $10 Use $15 Location $15 Safety_Feature $20;

  input Cylinder_ID $ Type $ Capacity Material $ Use $ Location $ Safety_Feature $;

  datalines;

C001 LPG_Domestic 14.2 Steel Home Delhi Pressure_Valve

C002 LPG_Domestic 14.2 Steel Home Mumbai Pressure_Valve

C003 LPG_Commercial 19 Steel Restaurant Chennai Flame_Retardant

C004 LPG_Commercial 47.5 Steel Hotel Kolkata Pressure_Valve

C005 Mini_LPG 2 Composite Vendor Patna Translucent_Body

C006 Fiber_LPG 5 Composite Home Nagpur Pressure_Valve

C007 Fiber_LPG 10 Composite Hotel Kanpur Overpressure_Relief

C008 Industrial_Type1 50 Steel Factory Pune Overpressure_Relief

C009 Industrial_Type2 60 Aluminum Factory Surat Pressure_Valve

C010 Hydraulic_Single 45 Steel Construction Ahmedabad Overpressure_Relief

C011 Hydraulic_Double 60 Aluminum Machinery Bengaluru Pressure_Valve

C012 Telescopic 80 Steel DumpTruck Jaipur Overpressure_Relief

C013 TieRod 40 Steel Manufacturing Surat Pressure_Valve

C014 WeldedBody 65 Steel Manufacturing Hyderabad Flame_Retardant

C015 LOT_Cylinder 425 Steel Plant Gurgaon HDPE_Tube

;

run;

proc print;run;

Output:

Obs	Cylinder_ID	Type	Material	Use	Location	Safety_Feature	Capacity
1	C001	LPG_Domestic	Steel	Home	Delhi	Pressure_Valve	14.2
2	C002	LPG_Domestic	Steel	Home	Mumbai	Pressure_Valve	14.2
3	C003	LPG_Commercial	Steel	Restaurant	Chennai	Flame_Retardant	19.0
4	C004	LPG_Commercial	Steel	Hotel	Kolkata	Pressure_Valve	47.5
5	C005	Mini_LPG	Composite	Vendor	Patna	Translucent_Body	2.0
6	C006	Fiber_LPG	Composite	Home	Nagpur	Pressure_Valve	5.0
7	C007	Fiber_LPG	Composite	Hotel	Kanpur	Overpressure_Relief	10.0
8	C008	Industrial_Type1	Steel	Factory	Pune	Overpressure_Relief	50.0
9	C009	Industrial_Type2	Aluminum	Factory	Surat	Pressure_Valve	60.0
10	C010	Hydraulic_Single	Steel	Construction	Ahmedabad	Overpressure_Relief	45.0
11	C011	Hydraulic_Double	Aluminum	Machinery	Bengaluru	Pressure_Valve	60.0
12	C012	Telescopic	Steel	DumpTruck	Jaipur	Overpressure_Relief	80.0
13	C013	TieRod	Steel	Manufacturing	Surat	Pressure_Valve	40.0
14	C014	WeldedBody	Steel	Manufacturing	Hyderabad	Flame_Retardant	65.0
15	C015	LOT_Cylinder	Steel	Plant	Gurgaon	HDPE_Tube	425.0

Step 2: View the Data                        

proc print data=cylinders;

  title 'Cylinder Inventory (Sample from India)';

run;

Output:

Cylinder Inventory (Sample from India)

Obs	Cylinder_ID	Type	Material	Use	Location	Safety_Feature	Capacity
1	C001	LPG_Domestic	Steel	Home	Delhi	Pressure_Valve	14.2
2	C002	LPG_Domestic	Steel	Home	Mumbai	Pressure_Valve	14.2
3	C003	LPG_Commercial	Steel	Restaurant	Chennai	Flame_Retardant	19.0
4	C004	LPG_Commercial	Steel	Hotel	Kolkata	Pressure_Valve	47.5
5	C005	Mini_LPG	Composite	Vendor	Patna	Translucent_Body	2.0
6	C006	Fiber_LPG	Composite	Home	Nagpur	Pressure_Valve	5.0
7	C007	Fiber_LPG	Composite	Hotel	Kanpur	Overpressure_Relief	10.0
8	C008	Industrial_Type1	Steel	Factory	Pune	Overpressure_Relief	50.0
9	C009	Industrial_Type2	Aluminum	Factory	Surat	Pressure_Valve	60.0
10	C010	Hydraulic_Single	Steel	Construction	Ahmedabad	Overpressure_Relief	45.0
11	C011	Hydraulic_Double	Aluminum	Machinery	Bengaluru	Pressure_Valve	60.0
12	C012	Telescopic	Steel	DumpTruck	Jaipur	Overpressure_Relief	80.0
13	C013	TieRod	Steel	Manufacturing	Surat	Pressure_Valve	40.0
14	C014	WeldedBody	Steel	Manufacturing	Hyderabad	Flame_Retardant	65.0
15	C015	LOT_Cylinder	Steel	Plant	Gurgaon	HDPE_Tube	425.0

Step 3: Frequency of Cylinder Types       

proc freq data=cylinders;

  tables Type;

  title 'Frequency Table for Cylinder Types';

run;

Output:

Frequency Table for Cylinder Types

The FREQ Procedure

Type	Frequency	Percent	Cumulative Frequency	Cumulative Percent
Fiber_LPG	2	13.33	2	13.33
Hydraulic_Double	1	6.67	3	20.00
Hydraulic_Single	1	6.67	4	26.67
Industrial_Type1	1	6.67	5	33.33
Industrial_Type2	1	6.67	6	40.00
LOT_Cylinder	1	6.67	7	46.67
LPG_Commercial	2	13.33	9	60.00
LPG_Domestic	2	13.33	11	73.33
Mini_LPG	1	6.67	12	80.00
Telescopic	1	6.67	13	86.67
TieRod	1	6.67	14	93.33
WeldedBody	1	6.67	15	100.00

Step 4: Summary Statistics for Capacity                   

proc means data=cylinders N MEAN MIN MAX;

  var Capacity;

  title 'Summary Statistics of Cylinder Capacity';

run;

Output:

Summary Statistics of Cylinder Capacity

The MEANS Procedure

Analysis Variable : Capacity
N	Mean	Minimum	Maximum
15	62.4600000	2.0000000	425.0000000

Step 5: SQL Query to Count and Summarize by Location  

proc sql;

  create table Cylinder_Summary as

  select Location, count(*) as Num_Cylinders, avg(Capacity) as Avg_Capacity

  from cylinders

  group by Location;

quit;

proc print data=Cylinder_Summary;

  title 'Summary: Cylinders by Location';

run;

Output:

Summary: Cylinders by Location

Obs	Location	Num_Cylinders	Avg_Capacity
1	Ahmedabad	1	45.0
2	Bengaluru	1	60.0
3	Chennai	1	19.0
4	Delhi	1	14.2
5	Gurgaon	1	425.0
6	Hyderabad	1	65.0
7	Jaipur	1	80.0
8	Kanpur	1	10.0
9	Kolkata	1	47.5
10	Mumbai	1	14.2
11	Nagpur	1	5.0
12	Patna	1	2.0
13	Pune	1	50.0
14	Surat	2	50.0

Step 6: Create Macro for Region-specific Report           

%macro region_report(city);

  proc sql noprint;

    select count(*) into :num_cyl

    from cylinders

    where Location="&city";

  quit;

  %put Number of Cylinders in &city: &num_cyl;

  proc means data=cylinders noprint;

    var Capacity;

    where Location="&city";

    output out=stats mean=avg_capacity;

  run;

  data _null_;

    set stats;

    call symput('avg_cap', avg_capacity);

  run;

  %put Average Capacity in &city: &avg_cap;

%mend;

%region_report(Mumbai);

Log:

Number of Cylinders in Mumbai: 1

Average Capacity in Mumbai: 14.2

%region_report(Surat);

Log:

Number of Cylinders in Surat: 2

Average Capacity in Surat: 50

Step 7: Macro for Observation Count in Any Dataset        

%macro obs_count(dsn);

  %local obs;

  proc sql noprint;

    select count(*) into :obs from &dsn;

  quit;

  %put Number of observations in &dsn: &obs;

%mend;

%obs_count(cylinders);

Log:

Number of observations in cylinders: 15

Step 8: DATA Step Example: Flag Risky Cylinders           

data risky_cylinders;

  set cylinders;

  if Safety_Feature in ("Flame_Retardant","Translucent_Body") then Risky_Flag=1;

  else Risky_Flag=0;

run;

proc print data=risky_cylinders;

  where Risky_Flag=1;

  title 'Cylinders with Elevated Safety Risk';

run;

Output:

Cylinders with Elevated Safety Risk

Obs	Cylinder_ID	Type	Material	Use	Location	Safety_Feature	Capacity	Risky_Flag
3	C003	LPG_Commercial	Steel	Restaurant	Chennai	Flame_Retardant	19	1
5	C005	Mini_LPG	Composite	Vendor	Patna	Translucent_Body	2	1
14	C014	WeldedBody	Steel	Manufacturing	Hyderabad	Flame_Retardant	65	1

Step 9: Using Macro Variables Created with CALL SYMPUT    

data _null_;

  set cylinders end=last;

  if last then call symput('last_capacity', Capacity);

run;

%put Last record capacity: &last_capacity;

Log:

Last record capacity: 425

Step 10: Reporting with Custom Macro                      

%macro type_capacity_report(type);

  proc sql noprint;

    select mean(Capacity) into :type_avg

    from cylinders

    where Type="&type";

  quit;

  %put Average Capacity for &type: &type_avg;

%mend;

%type_capacity_report(Fiber_LPG);

Log:

Average Capacity for Fiber_LPG: 7.5

Step 11: PROC FORMAT for Cylinder Type Description        

proc format;

  value $typfmt

    'LPG_Domestic'='Domestic LPG'

    'LPG_Commercial'='Commercial LPG'

    'Mini_LPG'='Mini LPG'

    'Fiber_LPG'='Fiber Composite LPG'

    'Industrial_Type1'='Type 1 Industrial'

    'Industrial_Type2'='Type 2 Industrial'

    'Hydraulic_Single'='Single Acting Hydraulic'

    'Hydraulic_Double'='Double Acting Hydraulic'

    'Telescopic'='Telescopic Hydraulic'

    'TieRod'='Tie Rod Hydraulic'

    'WeldedBody'='Welded Body Hydraulic'

    'LOT_Cylinder'='Liquid Off-Take Cylinder'

  ;

run;

proc print data=cylinders;

  format Type $typfmt.;

  title 'Cylinders with Formatted Type';

run;

Output:

Cylinders with Formatted Type

Obs	Cylinder_ID	Type	Material	Use	Location	Safety_Feature	Capacity
1	C001	Domestic LPG	Steel	Home	Delhi	Pressure_Valve	14.2
2	C002	Domestic LPG	Steel	Home	Mumbai	Pressure_Valve	14.2
3	C003	Commercial LPG	Steel	Restaurant	Chennai	Flame_Retardant	19.0
4	C004	Commercial LPG	Steel	Hotel	Kolkata	Pressure_Valve	47.5
5	C005	Mini LPG	Composite	Vendor	Patna	Translucent_Body	2.0
6	C006	Fiber Composite LPG	Composite	Home	Nagpur	Pressure_Valve	5.0
7	C007	Fiber Composite LPG	Composite	Hotel	Kanpur	Overpressure_Relief	10.0
8	C008	Type 1 Industrial	Steel	Factory	Pune	Overpressure_Relief	50.0
9	C009	Type 2 Industrial	Aluminum	Factory	Surat	Pressure_Valve	60.0
10	C010	Single Acting Hydraulic	Steel	Construction	Ahmedabad	Overpressure_Relief	45.0
11	C011	Double Acting Hydraulic	Aluminum	Machinery	Bengaluru	Pressure_Valve	60.0
12	C012	Telescopic Hydraulic	Steel	DumpTruck	Jaipur	Overpressure_Relief	80.0
13	C013	Tie Rod Hydraulic	Steel	Manufacturing	Surat	Pressure_Valve	40.0
14	C014	Welded Body Hydraulic	Steel	Manufacturing	Hyderabad	Flame_Retardant	65.0
15	C015	Liquid Off-Take Cylinder	Steel	Plant	Gurgaon	HDPE_Tube	425.0

Step 12: Subsetting Observations                          

data high_capacity;

  set cylinders;

  if Capacity >= 50;

run;

proc print data=high_capacity;

  title 'High Capacity (>50) Cylinders';

run;

Output:

High Capacity (>50) Cylinders

Obs	Cylinder_ID	Type	Material	Use	Location	Safety_Feature	Capacity
1	C008	Industrial_Type1	Steel	Factory	Pune	Overpressure_Relief	50
2	C009	Industrial_Type2	Aluminum	Factory	Surat	Pressure_Valve	60
3	C011	Hydraulic_Double	Aluminum	Machinery	Bengaluru	Pressure_Valve	60
4	C012	Telescopic	Steel	DumpTruck	Jaipur	Overpressure_Relief	80
5	C014	WeldedBody	Steel	Manufacturing	Hyderabad	Flame_Retardant	65
6	C015	LOT_Cylinder	Steel	Plant	Gurgaon	HDPE_Tube	425

Step 13: Merge Example: Adding Inspection Dates           

data inspections;

  input Cylinder_ID $ Last_Inspection :date9.;

  format Last_Inspection date9.;

  datalines;

C001 10JAN2025

C005 05MAR2025

C008 20APR2025

C010 11JUN2025

C012 12AUG2025

C013 07JUL2025

;

run;

proc print;run;

Output:

Obs	Cylinder_ID	Last_Inspection
1	C001	10JAN2025
2	C005	05MAR2025
3	C008	20APR2025
4	C010	11JUN2025
5	C012	12AUG2025
6	C013	07JUL2025

data cylinders_full;

  merge cylinders inspections;

  by Cylinder_ID;

run;

proc print data=cylinders_full;

  title 'Cylinders with Last Inspection Date';

run;

Output:

Cylinders with Last Inspection Date

Obs	Cylinder_ID	Type	Material	Use	Location	Safety_Feature	Capacity	Last_Inspection
1	C001	LPG_Domestic	Steel	Home	Delhi	Pressure_Valve	14.2	10JAN2025
2	C002	LPG_Domestic	Steel	Home	Mumbai	Pressure_Valve	14.2	.
3	C003	LPG_Commercial	Steel	Restaurant	Chennai	Flame_Retardant	19.0	.
4	C004	LPG_Commercial	Steel	Hotel	Kolkata	Pressure_Valve	47.5	.
5	C005	Mini_LPG	Composite	Vendor	Patna	Translucent_Body	2.0	05MAR2025
6	C006	Fiber_LPG	Composite	Home	Nagpur	Pressure_Valve	5.0	.
7	C007	Fiber_LPG	Composite	Hotel	Kanpur	Overpressure_Relief	10.0	.
8	C008	Industrial_Type1	Steel	Factory	Pune	Overpressure_Relief	50.0	20APR2025
9	C009	Industrial_Type2	Aluminum	Factory	Surat	Pressure_Valve	60.0	.
10	C010	Hydraulic_Single	Steel	Construction	Ahmedabad	Overpressure_Relief	45.0	11JUN2025
11	C011	Hydraulic_Double	Aluminum	Machinery	Bengaluru	Pressure_Valve	60.0	.
12	C012	Telescopic	Steel	DumpTruck	Jaipur	Overpressure_Relief	80.0	12AUG2025
13	C013	TieRod	Steel	Manufacturing	Surat	Pressure_Valve	40.0	07JUL2025
14	C014	WeldedBody	Steel	Manufacturing	Hyderabad	Flame_Retardant	65.0	.
15	C015	LOT_Cylinder	Steel	Plant	Gurgaon	HDPE_Tube	425.0	.

Step 14: SQL Join Example                                 

proc sql;

  create table inspected_composite as

  select a.*, b.Last_Inspection

    from cylinders as a left join inspections as b

      on a.Cylinder_ID = b.Cylinder_ID

    where a.Material = 'Composite';

quit;

proc print data=inspected_composite;

  title 'Composite Cylinders Inspection Details';

run;

Output:

Composite Cylinders Inspection Details

Obs	Cylinder_ID	Type	Material	Use	Location	Safety_Feature	Capacity	Last_Inspection
1	C005	Mini_LPG	Composite	Vendor	Patna	Translucent_Body	2	05MAR2025
2	C006	Fiber_LPG	Composite	Home	Nagpur	Pressure_Valve	5	.
3	C007	Fiber_LPG	Composite	Hotel	Kanpur	Overpressure_Relief	10	.

Step 15: Macro Loop and Summary for All Locations         

proc sql noprint;

  select distinct Location into :all_cities separated by ' '

    from cylinders;

quit;

%macro city_loop;

  %local i city;

  %do i=1 %to %sysfunc(countw(&all_cities));

    %let city = %scan(&all_cities, &i);

    %region_report(&city);

  %end;

%mend;

%city_loop;

Log:

Number of Cylinders in Ahmedabad: 1

Average Capacity in Ahmedabad: 45

Number of Cylinders in Bengaluru: 1

Average Capacity in Bengaluru: 60

Number of Cylinders in Chennai: 1

Average Capacity in Chennai: 19

Number of Cylinders in Delhi: 1

Average Capacity in Delhi: 14.2

Number of Cylinders in Gurgaon: 1

Average Capacity in Gurgaon: 425

Number of Cylinders in Hyderabad: 1

Average Capacity in Hyderabad: 65

Number of Cylinders in Jaipur: 1

Average Capacity in Jaipur: 80

Number of Cylinders in Kanpur: 1

Average Capacity in Kanpur: 10

Number of Cylinders in Kolkata: 1

Average Capacity in Kolkata: 47.5

Number of Cylinders in Mumbai: 1

Average Capacity in Mumbai: 14.2

Number of Cylinders in Nagpur: 1

Average Capacity in Nagpur: 5

Number of Cylinders in Patna: 1

Average Capacity in Patna: 2

Number of Cylinders in Pune: 1

Average Capacity in Pune: 50

Number of Cylinders in Surat: 2

Average Capacity in Surat: 50

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

Follow Us On : 

 

--- FOLLOW OUR BLOG FOR MORE INFORMATION.

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