286.REAL WORLD BLOOD BANKS ANALYSIS USING PROC PRINT|PROC FORMAT|PROC FREQ|PROC MEANS|PROC SQL|PROC SORT|PROC REPORT|PROC TRANSPOSE WITH MACROS AND QC CHECKS FOR UTILIZATION,LOW INVENTORY ALERTS, TYPE-WISE REPORTS AND COUNTRY LEVEL AGGREGATION

REAL WORLD BLOOD BANKS ANALYSIS USING PROC PRINT|PROC FORMAT|PROC FREQ|PROC MEANS|PROC SQL|PROC SORT|PROC REPORT|PROC TRANSPOSE WITH MACROS AND QC CHECKS FOR UTILIZATION,LOW INVENTORY ALERTS, TYPE-WISE REPORTS AND COUNTRY LEVEL AGGREGATION

/*Creating The dataset of REAL_BLOODBANKS */

options  nocenter;

data work.real_bloodbanks;

  informat LastInspection date9.;

  format LastInspection date9. CapacityUnits comma8. CurrentInventory comma8.;

  input BankID $ BankName :$40. Country :$30. City :$30. Type :$10.

        CapacityUnits CurrentInventory Contact :$15. LastInspection :date9.;

  datalines;

BB001 InternationalBloodCenter USA New_York NGO 50000 23500 +1-212-555-0101 15JAN2025

BB002 RoyalBloodBank UK London Public 60000 41200 +44-20-7946-0001 02FEB2025

BB003 LifelineMedical India Hyderabad Private 30000 11800 +91-40-1234-5678 10MAR2025

BB004 RedCrossCentral Australia Sydney NGO 45000 21000 +61-2-9999-1000 05APR2025

BB005 EuroBloodServices Germany Berlin Public 52000 30100 +49-30-123-4567 20JAN2025

BB006 PacificCare Japan Tokyo Private 40000 19500 +81-3-9876-5432 28FEB2025

BB007 VitalDonorSouth Africa Johannesburg NGO 20000 7200 +27-11-555-1234 12MAR2025

BB008 AndesHealth Chile Santiago Public 18000 8200 +56-2-2345-6789 18FEB2025

BB009 AmazonLife Brazil Manaus NGO 15000 5200 +55-92-9987-6543 22JAN2025

BB010 NorthStarCanada Canada Toronto Private 35000 17800 +1-416-555-0199 30MAR2025

BB011 MedServeFrance France Paris Public 48000 40250 +33-1-2345-6789 08APR2025

BB012 DesertCare UAE Dubai Private 22000 9200 +971-4-555-6789 25FEB2025

;

run;

proc print;run;

Output:

Obs	LastInspection	CapacityUnits	CurrentInventory	BankID	BankName	Country	City	Type	Contact
1	15JAN2025	50,000	23,500	BB001	InternationalBloodCenter	USA	New_York	NGO	+1-212-555-0101
2	02FEB2025	60,000	41,200	BB002	RoyalBloodBank	UK	London	Public	+44-20-7946-000
3	10MAR2025	30,000	11,800	BB003	LifelineMedical	India	Hyderabad	Private	+91-40-1234-567
4	05APR2025	45,000	21,000	BB004	RedCrossCentral	Australia	Sydney	NGO	+61-2-9999-1000
5	20JAN2025	52,000	30,100	BB005	EuroBloodServices	Germany	Berlin	Public	+49-30-123-4567
6	28FEB2025	40,000	19,500	BB006	PacificCare	Japan	Tokyo	Private	+81-3-9876-5432
7	12MAR2025	20,000	7,200	BB007	VitalDonorSouth	Africa	Johannesburg	NGO	+27-11-555-1234
8	18FEB2025	18,000	8,200	BB008	AndesHealth	Chile	Santiago	Public	+56-2-2345-6789
9	22JAN2025	15,000	5,200	BB009	AmazonLife	Brazil	Manaus	NGO	+55-92-9987-654
10	30MAR2025	35,000	17,800	BB010	NorthStarCanada	Canada	Toronto	Private	+1-416-555-0199
11	08APR2025	48,000	40,250	BB011	MedServeFrance	France	Paris	Public	+33-1-2345-6789
12	25FEB2025	22,000	9,200	BB012	DesertCare	UAE	Dubai	Private	+971-4-555-6789

 1) PROC PRINT -  show data rows for inspection 

proc print data=work.real_bloodbanks(obs=10) label noobs;

  title "REAL_BLOODBANKS - SAMPLE INSPECTION";

  var BankID BankName Country City Type CapacityUnits CurrentInventory LastInspection Contact;

run;

Output:

REAL_BLOODBANKS - SAMPLE INSPECTION

BankID	BankName	Country	City	Type	CapacityUnits	CurrentInventory	LastInspection	Contact
BB001	InternationalBloodCenter	USA	New_York	NGO	50,000	23,500	15JAN2025	+1-212-555-0101
BB002	RoyalBloodBank	UK	London	Public	60,000	41,200	02FEB2025	+44-20-7946-000
BB003	LifelineMedical	India	Hyderabad	Private	30,000	11,800	10MAR2025	+91-40-1234-567
BB004	RedCrossCentral	Australia	Sydney	NGO	45,000	21,000	05APR2025	+61-2-9999-1000
BB005	EuroBloodServices	Germany	Berlin	Public	52,000	30,100	20JAN2025	+49-30-123-4567
BB006	PacificCare	Japan	Tokyo	Private	40,000	19,500	28FEB2025	+81-3-9876-5432
BB007	VitalDonorSouth	Africa	Johannesburg	NGO	20,000	7,200	12MAR2025	+27-11-555-1234
BB008	AndesHealth	Chile	Santiago	Public	18,000	8,200	18FEB2025	+56-2-2345-6789
BB009	AmazonLife	Brazil	Manaus	NGO	15,000	5,200	22JAN2025	+55-92-9987-654
BB010	NorthStarCanada	Canada	Toronto	Private	35,000	17,800	30MAR2025	+1-416-555-0199

 2) PROC FORMAT - map Type values to friendly labels 

proc format;

  value $ttype

    'Public' = 'PUBLIC'

    'Private' = 'PRIVATE'

    'NGO' = 'NGO/NOT-FOR-PROFIT';

run;

Log:

NOTE: Format $TTYPE has been output.

 3) PROC FREQ -  frequency of bank types and countries 

proc freq data=work.real_bloodbanks;

  tables Type Country / nocum nopercent;

  format Type $ttype.;

  title "FREQUENCY: TYPE & COUNTRY";

run;

Output:

FREQUENCY: TYPE & COUNTRY

The FREQ Procedure

Type	Frequency
NGO/NOT-FOR-PROFIT	4
PRIVATE	4
PUBLIC	4

Country	Frequency
Africa	1
Australia	1
Brazil	1
Canada	1
Chile	1
France	1
Germany	1
India	1
Japan	1
UAE	1
UK	1
USA	1

 4) PROC MEANS -  numeric summary of capacities/inventory 

proc means data=work.real_bloodbanks n mean median min max std maxdec=0;

  var CapacityUnits CurrentInventory;

  title "SUMMARY STATISTICS: Capacity and CurrentInventory";

run;

Output:

SUMMARY STATISTICS: Capacity and CurrentInventory

The MEANS Procedure

Variable	N	Mean	Median	Minimum	Maximum	Std Dev
CapacityUnits CurrentInventory	12 12	36250 19579	37500 18650	15000 5200	60000 41200	15143 12372

 5) PROC SQL - compute inventory utilization and create summary table 

proc sql;

  create table work.bb_utilization as

  select BankID,BankName,Country,City,Type,

         CapacityUnits,

         CurrentInventory,

         (CurrentInventory / CapacityUnits) as Utilization format=percent8.2

  from work.real_bloodbanks

  order by Utilization desc;

quit;

proc print;run;

Output:

Obs	BankID	BankName	Country	City	Type	CapacityUnits	CurrentInventory	Utilization
1	BB011	MedServeFrance	France	Paris	Public	48,000	40,250	83.85%
2	BB002	RoyalBloodBank	UK	London	Public	60,000	41,200	68.67%
3	BB005	EuroBloodServices	Germany	Berlin	Public	52,000	30,100	57.88%
4	BB010	NorthStarCanada	Canada	Toronto	Private	35,000	17,800	50.86%
5	BB006	PacificCare	Japan	Tokyo	Private	40,000	19,500	48.75%
6	BB001	InternationalBloodCenter	USA	New_York	NGO	50,000	23,500	47.00%
7	BB004	RedCrossCentral	Australia	Sydney	NGO	45,000	21,000	46.67%
8	BB008	AndesHealth	Chile	Santiago	Public	18,000	8,200	45.56%
9	BB012	DesertCare	UAE	Dubai	Private	22,000	9,200	41.82%
10	BB003	LifelineMedical	India	Hyderabad	Private	30,000	11,800	39.33%
11	BB007	VitalDonorSouth	Africa	Johannesburg	NGO	20,000	7,200	36.00%
12	BB009	AmazonLife	Brazil	Manaus	NGO	15,000	5,200	34.67%

6) PROC SORT - sort by utilization for reporting 

proc sort data=work.bb_utilization out=work.bb_utilization_srt;

  by descending Utilization BankName;

run;

proc print;run;

Output:

Obs	BankID	BankName	Country	City	Type	CapacityUnits	CurrentInventory	Utilization
1	BB011	MedServeFrance	France	Paris	Public	48,000	40,250	83.85%
2	BB002	RoyalBloodBank	UK	London	Public	60,000	41,200	68.67%
3	BB005	EuroBloodServices	Germany	Berlin	Public	52,000	30,100	57.88%
4	BB010	NorthStarCanada	Canada	Toronto	Private	35,000	17,800	50.86%
5	BB006	PacificCare	Japan	Tokyo	Private	40,000	19,500	48.75%
6	BB001	InternationalBloodCenter	USA	New_York	NGO	50,000	23,500	47.00%
7	BB004	RedCrossCentral	Australia	Sydney	NGO	45,000	21,000	46.67%
8	BB008	AndesHealth	Chile	Santiago	Public	18,000	8,200	45.56%
9	BB012	DesertCare	UAE	Dubai	Private	22,000	9,200	41.82%
10	BB003	LifelineMedical	India	Hyderabad	Private	30,000	11,800	39.33%
11	BB007	VitalDonorSouth	Africa	Johannesburg	NGO	20,000	7,200	36.00%
12	BB009	AmazonLife	Brazil	Manaus	NGO	15,000	5,200	34.67%

7) PROC REPORT - formatted report with computed column for alert 

proc report data=work.bb_utilization_srt nowd headline;

  column BankID BankName Country City Type CapacityUnits CurrentInventory Utilization 

         Alert;

  define BankID / group 'ID';

  define BankName / display 'Center Name';

  define Country / display 'Country';

  define City / display 'City';

  define Type / display 'Type' format=$ttype.;

  define CapacityUnits / analysis sum 'Capacity (U)';

  define CurrentInventory / analysis sum 'Inventory (U)';

  define Utilization / display 'Utilization';

  compute Utilization;

    Utilization = Utilization;

  endcomp;

  compute Alert / character length=12;

    if Utilization < 0.25 then Alert='LOW-STOCK';

    else if Utilization < 0.5 then Alert='WATCH';

    else Alert='OK';

  endcomp;

  title "BLOOD BANK UTILIZATION REPORT WITH ALERTS";

run;

Output:

BLOOD BANK UTILIZATION REPORT WITH ALERTS

ID	Center Name	Country	City	Type	Capacity (U)	Inventory (U)	Utilization	Alert
BB001	InternationalBloodCenter	USA	New_York	NGO/NOT-FOR-PROFIT	50,000	23,500	47.00%	WATCH
BB002	RoyalBloodBank	UK	London	PUBLIC	60,000	41,200	68.67%	OK
BB003	LifelineMedical	India	Hyderabad	PRIVATE	30,000	11,800	39.33%	WATCH
BB004	RedCrossCentral	Australia	Sydney	NGO/NOT-FOR-PROFIT	45,000	21,000	46.67%	WATCH
BB005	EuroBloodServices	Germany	Berlin	PUBLIC	52,000	30,100	57.88%	OK
BB006	PacificCare	Japan	Tokyo	PRIVATE	40,000	19,500	48.75%	WATCH
BB007	VitalDonorSouth	Africa	Johannesburg	NGO/NOT-FOR-PROFIT	20,000	7,200	36.00%	WATCH
BB008	AndesHealth	Chile	Santiago	PUBLIC	18,000	8,200	45.56%	WATCH
BB009	AmazonLife	Brazil	Manaus	NGO/NOT-FOR-PROFIT	15,000	5,200	34.67%	WATCH
BB010	NorthStarCanada	Canada	Toronto	PRIVATE	35,000	17,800	50.86%	OK
BB011	MedServeFrance	France	Paris	PUBLIC	48,000	40,250	83.85%	OK
BB012	DesertCare	UAE	Dubai	PRIVATE	22,000	9,200	41.82%	WATCH

 8) PROC TRANSPOSE - create wide view (country-level inventory) 

proc sql;

  create table work.country_inventory as

  select Country,

         sum(CapacityUnits) as TotalCapacity,

         sum(CurrentInventory) as TotalInventory,

         calculated TotalInventory / calculated TotalCapacity as CountryUtilization

  from work.real_bloodbanks

  group by Country;

quit;

proc print;run;

Output:

Obs	Country	TotalCapacity	TotalInventory	CountryUtilization
1	Africa	20000	7200	0.36000
2	Australia	45000	21000	0.46667
3	Brazil	15000	5200	0.34667
4	Canada	35000	17800	0.50857
5	Chile	18000	8200	0.45556
6	France	48000	40250	0.83854
7	Germany	52000	30100	0.57885
8	India	30000	11800	0.39333
9	Japan	40000	19500	0.48750
10	UAE	22000	9200	0.41818
11	UK	60000	41200	0.68667
12	USA	50000	23500	0.47000

proc transpose data=work.country_inventory out=work.country_inventory_t(drop=_NAME_);

  id Country;

  var TotalCapacity TotalInventory CountryUtilization;

run;

proc print;run;

Output:

Obs	Africa	Australia	Brazil	Canada	Chile	France	Germany	India	Japan	UAE	UK	USA
1	20000.00	45000.00	15000.00	35000.00	18000.00	48000.00	52000.00	30000.00	40000.00	22000.00	60000.00	50000.00
2	7200.00	21000.00	5200.00	17800.00	8200.00	40250.00	30100.00	11800.00	19500.00	9200.00	41200.00	23500.00
3	0.36	0.47	0.35	0.51	0.46	0.84	0.58	0.39	0.49	0.42	0.69	0.47

 

9) Macro: %low_inventory_alert - make a dataset of banks below a threshold 

%macro low_inventory_alert(thresh=0.25, outds=work.low_alerts);

/*   Creates &outds with banks with utilization < &thresh */

  proc sql;

    create table &outds as

    select BankID, BankName, Country, City, Type, CapacityUnits, CurrentInventory,

           CurrentInventory / CapacityUnits as Utilization format=percent8.2

    from work.real_bloodbanks

    where (CurrentInventory / CapacityUnits) < &thresh

    order by Utilization;

  quit;

  proc print data=&outds;run;

%mend low_inventory_alert;

%low_inventory_alert(thresh=0.25, outds=work.low_alerts);

Log:

NOTE: Table WORK.LOW_ALERTS created, with 0 rows and 8 columns.

 10) Macro: %bb_report_by_type - generate type-wise PDF/CENTRIC report (simulated) 

%macro bb_report_by_type(bbtype=Public);

  %let bbtype_clean = %sysfunc(strip(&bbtype));

  proc sql noprint;

    create table work._tmp as

    select * from work.real_bloodbanks

    where Type = "&bbtype_clean";

  quit;

  proc print data=work._tmp noobs;

    title "Report for Type=&bbtype_clean";

  run;

  proc sql;

    drop table work._tmp;

  quit;

%mend bb_report_by_type;

%bb_report_by_type(bbtype=NGO);

Output:

Report for Type=NGO

LastInspection	CapacityUnits	CurrentInventory	BankID	BankName	Country	City	Type	Contact
15JAN2025	50,000	23,500	BB001	InternationalBloodCenter	USA	New_York	NGO	+1-212-555-0101
05APR2025	45,000	21,000	BB004	RedCrossCentral	Australia	Sydney	NGO	+61-2-9999-1000
12MAR2025	20,000	7,200	BB007	VitalDonorSouth	Africa	Johannesburg	NGO	+27-11-555-1234
22JAN2025	15,000	5,200	BB009	AmazonLife	Brazil	Manaus	NGO	+55-92-9987-654

 11) Simple validation/QC - check for missing or invalid values 

proc sql;

  create table work.qc_checks as

  select BankID,

         case when BankName is null then 'MISSING_NAME' else '' end as NameFlag,

         case when CapacityUnits <= 0 then 'INVALID_CAPACITY' else '' end as CapacityFlag,

         case when CurrentInventory < 0 then 'NEG_INV' else '' end as InventoryFlag,

         case when LastInspection is missing then 'NO_INSPECTION_DATE' else '' end as InspectionFlag

  from work.real_bloodbanks;

quit;

proc print;run;

Output:

Obs	BankID	NameFlag	CapacityFlag	InventoryFlag	InspectionFlag
1	BB001
2	BB002
3	BB003
4	BB004
5	BB005
6	BB006
7	BB007
8	BB008
9	BB009
10	BB010
11	BB011
12	BB012

 12)left join with a mock 'donor_drive' table using PROC SQL - demonstrate joins 

data work.donor_drive;

  input BankID $ DriveDate :date9. UnitsCollected;

  format DriveDate date9. UnitsCollected comma6.;

datalines;

BB001 05MAR2025 1200

BB003 15MAR2025 800

BB006 10FEB2025 900

BB009 01JAN2025 450

BB012 12FEB2025 300

;

run;

proc print;run;

Output:

Obs	BankID	DriveDate	UnitsCollected
1	BB001	05MAR2025	1,200
2	BB003	15MAR2025	800
3	BB006	10FEB2025	900
4	BB009	01JAN2025	450
5	BB012	12FEB2025	300

proc sql;

  create table work.bb_with_drives as

  select a.*, b.DriveDate, b.UnitsCollected

  from work.real_bloodbanks as a

  left join work.donor_drive as b

  on a.BankID = b.BankID;

quit;

proc prinr;run;

Output:

Obs	LastInspection	CapacityUnits	CurrentInventory	BankID	BankName	Country	City	Type	Contact	DriveDate	UnitsCollected
1	15JAN2025	50,000	23,500	BB001	InternationalBloodCenter	USA	New_York	NGO	+1-212-555-0101	05MAR2025	1,200
2	02FEB2025	60,000	41,200	BB002	RoyalBloodBank	UK	London	Public	+44-20-7946-000	.	.
3	10MAR2025	30,000	11,800	BB003	LifelineMedical	India	Hyderabad	Private	+91-40-1234-567	15MAR2025	800
4	05APR2025	45,000	21,000	BB004	RedCrossCentral	Australia	Sydney	NGO	+61-2-9999-1000	.	.
5	20JAN2025	52,000	30,100	BB005	EuroBloodServices	Germany	Berlin	Public	+49-30-123-4567	.	.
6	28FEB2025	40,000	19,500	BB006	PacificCare	Japan	Tokyo	Private	+81-3-9876-5432	10FEB2025	900
7	12MAR2025	20,000	7,200	BB007	VitalDonorSouth	Africa	Johannesburg	NGO	+27-11-555-1234	.	.
8	18FEB2025	18,000	8,200	BB008	AndesHealth	Chile	Santiago	Public	+56-2-2345-6789	.	.
9	22JAN2025	15,000	5,200	BB009	AmazonLife	Brazil	Manaus	NGO	+55-92-9987-654	01JAN2025	450
10	30MAR2025	35,000	17,800	BB010	NorthStarCanada	Canada	Toronto	Private	+1-416-555-0199	.	.
11	08APR2025	48,000	40,250	BB011	MedServeFrance	France	Paris	Public	+33-1-2345-6789	.	.
12	25FEB2025	22,000	9,200	BB012	DesertCare	UAE	Dubai	Private	+971-4-555-6789	12FEB2025	300

To Visit My Previous Online Streaming Flatform Dataset:Click Here

To Visit My Previous Statistical Evaluation Of Clinical Trials:Click Here

To Visit My Previous Unlocking Retail Insights Dataset:Click Here

To Visit My Previous Sas Interview Questions-1:Click Here  

Follow Us On : 

 

--- FOLLOW OUR BLOG FOR MORE INFORMATION.

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