383.Which Pharma Distributors Are High-Risk? Can SAS Detect Delays, Temperature Violations, and Fraud?

Which Pharma Distributors Are High-Risk? Can SAS Detect Delays, Temperature Violations, and Fraud?

HERE IN THIS PROJECT WE USED THESE SAS STATEMENTS —
DATA STEP | PROC SQL |  PROC PRINT | PROC SGPLOT | MACROS | PROC CORR | PROC MEANS | PROC FREQ | PROC UNIVARIATE | APPEND | PROC SORT | PROC DATASETS DELETE

Introduction:

In the pharmaceutical industry, distributing medicines safely and on time is just as important as manufacturing them. Drugs must travel through multiple distributors, regions, and storage conditions before reaching hospitals and patients. During this process, companies must track shipment delays, temperature control, and regulatory compliance to ensure that every medicine remains safe and effective.

Contents:

* Understanding Drug Distribution Channels

* Creating the Drug Shipment Dataset in SAS

* Using Date Functions (MDY, INTNX, INTCK) for Delivery Analysis

* Cleaning and Formatting Data with SAS Functions

* Distributor Utilization Classification Using Macros

* Fraud and Compliance Risk Detection

* Performance Analysis Using PROC SQL

* Statistical Analysis with PROC MEANS and PROC UNIVARIATE

* Frequency and Correlation Analysis

* Visualization Using PROC SGPLOT

* Data Reshaping with PROC TRANSPOSE and APPEND

* Final Results and Insights

* Conclusion

1. Business Context

Pharmaceutical companies distribute drugs across multiple channels:

·       Manufacturers

·       Central warehouses

·       Regional distributors

·       Hospitals

·       Pharmacies

·       Clinical trial sites

Every shipment must follow:

·       Temperature control (Cold chain, Controlled, Ambient)

·       Delivery timelines

·       Regulatory compliance

·       Batch traceability

Failures can cause:

·       Drug spoilage

·       Regulatory violations (FDA, EMA)

·       Patient safety risk

·       Financial fraud

This project simulates how pharma companies monitor:

·       Units shipped

·       Temperature violations

·       Delays

·       Distributor behavior

·       Compliance risk

using SAS analytics.

Why Drug Distribution Data Matters

In pharmaceutical operations, drug shipment data is as important as clinical trial data because:

·       Temperature failure can make drugs ineffective

·       Delays cause shortages in hospitals

·       Non-compliance can result in FDA warning letters

·       Distributors can commit billing fraud

DATA Step

Used to simulate raw supply chain feeds from distributors.

Variables:

Variable	Why
Drug_Name	Identifies medicine
Distributor	Who handled shipment
Region	Geography
Units_Shipped	Volume
Temperature_Control	Cold / Controlled / Ambient
Delay_Days	Logistics risk
Compliance_Status	Regulatory adherence
Ship_Date	Dispatch
Delivery_Date	Receipt

2. SAS Program

STEP-1: Create Raw Drug Distribution Dataset

data drug_shipments_raw;

    input Drug_Name:$12. Distributor:$12. Region $ Units_Shipped Temperature_Control:$10. 

          Delay_Days Compliance_Status $ Ship_Date :date9. Delivery_Date :date9.;

    format Ship_Date Delivery_Date date9.;

datalines;

Paracetamol MedLink North 12000 Cold 1 Yes 01JAN2025 03JAN2025

Ibuprofen HealthFlow South 8000 Ambient 5 No 05JAN2025 12JAN2025

Amoxicillin MedLink East 15000 Cold 0 Yes 07JAN2025 07JAN2025

Metformin PharmaRoute West 20000 Controlled 2 Yes 10JAN2025 13JAN2025

Aspirin HealthFlow North 9000 Ambient 8 No 15JAN2025 25JAN2025

Ciprofloxacin MedLink South 11000 Cold 1 Yes 18JAN2025 20JAN2025

Insulin PharmaRoute East 5000 Cold 10 No 20JAN2025 05FEB2025

Atorvastatin HealthFlow West 14000 Controlled 3 Yes 22JAN2025 26JAN2025

Losartan MedLink North 13000 Controlled 2 Yes 25JAN2025 28JAN2025

Azithromycin PharmaRoute South 16000 Cold 7 No 28JAN2025 10FEB2025

Omeprazole HealthFlow East 10000 Ambient 1 Yes 30JAN2025 02FEB2025

Warfarin MedLink West 7000 Controlled 6 No 02FEB2025 10FEB2025

Heparin PharmaRoute North 4000 Cold 0 Yes 05FEB2025 05FEB2025

Clopidogrel HealthFlow South 9000 Ambient 4 Yes 07FEB2025 12FEB2025

Lisinopril MedLink East 12000 Controlled 3 Yes 10FEB2025 15FEB2025

;

run;

proc print data=drug_shipments_raw;

run;

OUTPUT:

Obs	Drug_Name	Distributor	Region	Units_Shipped	Temperature_Control	Delay_Days	Compliance_Status	Ship_Date	Delivery_Date
1	Paracetamol	MedLink	North	12000	Cold	1	Yes	01JAN2025	03JAN2025
2	Ibuprofen	HealthFlow	South	8000	Ambient	5	No	05JAN2025	12JAN2025
3	Amoxicillin	MedLink	East	15000	Cold	0	Yes	07JAN2025	07JAN2025
4	Metformin	PharmaRoute	West	20000	Controlled	2	Yes	10JAN2025	13JAN2025
5	Aspirin	HealthFlow	North	9000	Ambient	8	No	15JAN2025	25JAN2025
6	Ciprofloxaci	MedLink	South	11000	Cold	1	Yes	18JAN2025	20JAN2025
7	Insulin	PharmaRoute	East	5000	Cold	10	No	20JAN2025	05FEB2025
8	Atorvastatin	HealthFlow	West	14000	Controlled	3	Yes	22JAN2025	26JAN2025
9	Losartan	MedLink	North	13000	Controlled	2	Yes	25JAN2025	28JAN2025
10	Azithromycin	PharmaRoute	South	16000	Cold	7	No	28JAN2025	10FEB2025
11	Omeprazole	HealthFlow	East	10000	Ambient	1	Yes	30JAN2025	02FEB2025
12	Warfarin	MedLink	West	7000	Controlled	6	No	02FEB2025	10FEB2025
13	Heparin	PharmaRoute	North	4000	Cold	0	Yes	05FEB2025	05FEB2025
14	Clopidogrel	HealthFlow	South	9000	Ambient	4	Yes	07FEB2025	12FEB2025
15	Lisinopril	MedLink	East	12000	Controlled	3	Yes	10FEB2025	15FEB2025

STEP-2: Add Derived Dates Using INTNX and INTCK

data drug_dates;

    set drug_shipments_raw;

    format Expected_Delivery date9.;

    Expected_Delivery = intnx('day', Ship_Date, 3);

    Actual_Days = intck('day', Ship_Date, Delivery_Date);

run;

proc print data=drug_dates;

 var Drug_Name Distributor Region Expected_Delivery Actual_Days;

run;

OUTPUT:

Obs	Drug_Name	Distributor	Region	Expected_Delivery	Actual_Days
1	Paracetamol	MedLink	North	04JAN2025	2
2	Ibuprofen	HealthFlow	South	08JAN2025	7
3	Amoxicillin	MedLink	East	10JAN2025	0
4	Metformin	PharmaRoute	West	13JAN2025	3
5	Aspirin	HealthFlow	North	18JAN2025	10
6	Ciprofloxaci	MedLink	South	21JAN2025	2
7	Insulin	PharmaRoute	East	23JAN2025	16
8	Atorvastatin	HealthFlow	West	25JAN2025	4
9	Losartan	MedLink	North	28JAN2025	3
10	Azithromycin	PharmaRoute	South	31JAN2025	13
11	Omeprazole	HealthFlow	East	02FEB2025	3
12	Warfarin	MedLink	West	05FEB2025	8
13	Heparin	PharmaRoute	North	08FEB2025	0
14	Clopidogrel	HealthFlow	South	10FEB2025	5
15	Lisinopril	MedLink	East	13FEB2025	5

MDY, INTNX, INTCK:

These functions mimic how pharma companies calculate:

·       Expected delivery dates

·       Shipping SLA

·       Actual vs expected delays

This is critical in cold chain compliance.

STEP-3: Character & Numeric Cleaning

data drug_clean;

    set drug_dates;

    Drug_Name = propcase(strip(Drug_Name));

    Distributor = upcase(trim(Distributor));

    Region = lowcase(Region);

    Temp_Type = catx('-', Temperature_Control, Region);

    Delay_Flag = coalesce(Delay_Days,0);

    Shipment_Size = round(Units_Shipped/1000,1);

run;

proc print data=drug_clean;

 var Drug_Name Distributor Region Temperature_Control Temp_Type Delay_Flag Shipment_Size;

run;

OUTPUT:

Obs	Drug_Name	Distributor	Region	Temperature_Control	Temp_Type	Delay_Flag	Shipment_Size
1	Paracetamol	MEDLINK	north	Cold	Cold-north	1	12
2	Ibuprofen	HEALTHFLOW	south	Ambient	Ambient-south	5	8
3	Amoxicillin	MEDLINK	east	Cold	Cold-east	0	15
4	Metformin	PHARMAROUTE	west	Controlled	Controlled-west	2	20
5	Aspirin	HEALTHFLOW	north	Ambient	Ambient-north	8	9
6	Ciprofloxaci	MEDLINK	south	Cold	Cold-south	1	11
7	Insulin	PHARMAROUTE	east	Cold	Cold-east	10	5
8	Atorvastatin	HEALTHFLOW	west	Controlled	Controlled-west	3	14
9	Losartan	MEDLINK	north	Controlled	Controlled-north	2	13
10	Azithromycin	PHARMAROUTE	south	Cold	Cold-south	7	16
11	Omeprazole	HEALTHFLOW	east	Ambient	Ambient-east	1	10
12	Warfarin	MEDLINK	west	Controlled	Controlled-west	6	7
13	Heparin	PHARMAROUTE	north	Cold	Cold-north	0	4
14	Clopidogrel	HEALTHFLOW	south	Ambient	Ambient-south	4	9
15	Lisinopril	MEDLINK	east	Controlled	Controlled-east	3	12

Character Functions:

Used for:

·       Data standardization

·       Removing errors from distributor feeds

·       Ensuring joins and SQL grouping work correctly

Example:
propcase, upcase, strip remove inconsistent text.

STEP-4: Utilization Classification Macro

%macro utilization(input=, output=);

data &output;

    set &input;

    length Utilization $15;

    if Units_Shipped > 15000 then Utilization="High";

    else if 8000 <= Units_Shipped <= 15000 then Utilization="Medium";

    else Utilization="Low";

run;

proc print data=&output;

  var Drug_Name Distributor Region Temperature_Control Units_Shipped Utilization;

run;

%mend;

%utilization(input=drug_clean, output=drug_util);

OUTPUT:

Obs	Drug_Name	Distributor	Region	Temperature_Control	Units_Shipped	Utilization
1	Paracetamol	MEDLINK	north	Cold	12000	Medium
2	Ibuprofen	HEALTHFLOW	south	Ambient	8000	Medium
3	Amoxicillin	MEDLINK	east	Cold	15000	Medium
4	Metformin	PHARMAROUTE	west	Controlled	20000	High
5	Aspirin	HEALTHFLOW	north	Ambient	9000	Medium
6	Ciprofloxaci	MEDLINK	south	Cold	11000	Medium
7	Insulin	PHARMAROUTE	east	Cold	5000	Low
8	Atorvastatin	HEALTHFLOW	west	Controlled	14000	Medium
9	Losartan	MEDLINK	north	Controlled	13000	Medium
10	Azithromycin	PHARMAROUTE	south	Cold	16000	High
11	Omeprazole	HEALTHFLOW	east	Ambient	10000	Medium
12	Warfarin	MEDLINK	west	Controlled	7000	Low
13	Heparin	PHARMAROUTE	north	Cold	4000	Low
14	Clopidogrel	HEALTHFLOW	south	Ambient	9000	Medium
15	Lisinopril	MEDLINK	east	Controlled	12000	Medium

Utilization Macro:

Used to classify:

·       High volume distributors

·       Medium

·       Low

This helps in contract negotiation and capacity planning.

STEP-5: Fraud Detection Macro

%macro fraud(input=, output=);

data &output;

    set &input;

    length Fraud_Risk $10;

    if Delay_Days > 7 or Compliance_Status="No" then Fraud_Risk="High";

    else if 3 <= Delay_Days <= 7 then Fraud_Risk="Medium";

    else Fraud_Risk="Low";

run;

proc print data=&output;

  var Drug_Name Distributor Region Temperature_Control Fraud_Risk Compliance_Status Delay_Days;

run;

%mend;

%fraud(input=drug_util, output=drug_fraud);

OUTPUT:

Obs	Drug_Name	Distributor	Region	Temperature_Control	Fraud_Risk	Compliance_Status	Delay_Days
1	Paracetamol	MEDLINK	north	Cold	Low	Yes	1
2	Ibuprofen	HEALTHFLOW	south	Ambient	High	No	5
3	Amoxicillin	MEDLINK	east	Cold	Low	Yes	0
4	Metformin	PHARMAROUTE	west	Controlled	Low	Yes	2
5	Aspirin	HEALTHFLOW	north	Ambient	High	No	8
6	Ciprofloxaci	MEDLINK	south	Cold	Low	Yes	1
7	Insulin	PHARMAROUTE	east	Cold	High	No	10
8	Atorvastatin	HEALTHFLOW	west	Controlled	Medium	Yes	3
9	Losartan	MEDLINK	north	Controlled	Low	Yes	2
10	Azithromycin	PHARMAROUTE	south	Cold	High	No	7
11	Omeprazole	HEALTHFLOW	east	Ambient	Low	Yes	1
12	Warfarin	MEDLINK	west	Controlled	High	No	6
13	Heparin	PHARMAROUTE	north	Cold	Low	Yes	0
14	Clopidogrel	HEALTHFLOW	south	Ambient	Medium	Yes	4
15	Lisinopril	MEDLINK	east	Controlled	Medium	Yes	3

Fraud Macro:

Rules simulate audit triggers:

·       Large delays

·       Compliance violations

These are exactly how pharma compliance teams flag suspicious shipments.

STEP-6: PROC SQL Analysis

proc sql;

    create table distributor_perf as

    select Distributor,

           count(*) as Shipments,

           sum(Units_Shipped) as Total_Units,

           mean(Delay_Days) as Avg_Delay

    from drug_fraud

    group by Distributor;

quit;

proc print data=distributor_perf;

run;

OUTPUT:

Obs	Distributor	Shipments	Total_Units	Avg_Delay
1	HEALTHFLOW	5	50000	4.20000
2	MEDLINK	6	70000	2.16667
3	PHARMAROUTE	4	45000	4.75000

PROC SQL:

Used for KPI reporting:

·       Which distributor ships the most

·       Who has highest delays

This is the same as vendor performance dashboards.

STEP-7: PROC MEANS & UNIVARIATE

proc means data=drug_fraud mean min max;

    var Units_Shipped Delay_Days Actual_Days;

run;

OUTPUT:

The MEANS Procedure

Variable	Mean	Minimum	Maximum
Units_Shipped Delay_Days Actual_Days	11000.00 3.5333333 5.4000000	4000.00 0 0	20000.00 10.0000000 16.0000000

proc univariate data=drug_fraud;

    var Delay_Days;

run;

OUTPUT:

The UNIVARIATE Procedure

Variable: Delay_Days

Moments
N	15	Sum Weights	15
Mean	3.53333333	Sum Observations	53
Std Deviation	3.06749471	Variance	9.40952381
Skewness	0.78509802	Kurtosis	-0.3195871
Uncorrected SS	319	Corrected SS	131.733333
Coeff Variation	86.8158881	Std Error Mean	0.79202373

Basic Statistical Measures
Location		Variability
Mean	3.533333	Std Deviation	3.06749
Median	3.000000	Variance	9.40952
Mode	1.000000	Range	10.00000
		Interquartile Range	5.00000

Tests for Location: Mu0=0
Test	Statistic		p Value
Student's t	t	4.461146	Pr > |t|	0.0005
Sign	M	6.5	Pr >= |M|	0.0002
Signed Rank	S	45.5	Pr >= |S|	0.0002

Quantiles (Definition 5)
Level	Quantile
100% Max	10
99%	10
95%	10
90%	8
75% Q3	6
50% Median	3
25% Q1	1
10%	0
5%	0
1%	0
0% Min	0

Extreme Observations
Lowest		Highest
Value	Obs	Value	Obs
0	13	5	2
0	3	6	12
1	11	7	10
1	6	8	5
1	1	10	7

 PROC MEANS & UNIVARIATE:

Used to:

·       Detect abnormal delays

·       Identify extreme shipments

·       Perform QC

This is used in FDA inspection readiness.

STEP-8: PROC FREQ

proc freq data=drug_fraud;

    tables Utilization*Fraud_Risk Compliance_Status;

run;

OUTPUT:

The FREQ Procedure

Frequency Percent Row Pct Col Pct

Table of Utilization by Fraud_Risk Utilization Fraud_Risk High Low Medium Total High 1 6.67 50.00 20.00 1 6.67 50.00 14.29 0 0.00 0.00 0.00 2 13.33     Low 2 13.33 66.67 40.00 1 6.67 33.33 14.29 0 0.00 0.00 0.00 3 20.00     Medium 2 13.33 20.00 40.00 5 33.33 50.00 71.43 3 20.00 30.00 100.00 10 66.67     Total 5 33.33 7 46.67 3 20.00 15 100.00

Compliance_Status	Frequency	Percent	Cumulative Frequency	Cumulative Percent
No	5	33.33	5	33.33
Yes	10	66.67	15	100.00

PROC FREQ:

Used to:

·       See if high utilization distributors also have high fraud

·       Monitor compliance rates

STEP-9: PROC CORR

proc corr data=drug_fraud;

    var Units_Shipped Delay_Days Actual_Days;

run;

OUTPUT:

The CORR Procedure

3 Variables:	Units_Shipped Delay_Days Actual_Days

Simple Statistics
Variable	N	Mean	Std Dev	Sum	Minimum	Maximum
Units_Shipped	15	11000	4276	165000	4000	20000
Delay_Days	15	3.53333	3.06749	53.00000	0	10.00000
Actual_Days	15	5.40000	4.64143	81.00000	0	16.00000

Pearson Correlation Coefficients, N = 15 Prob > |r| under H0: Rho=0
	Units_Shipped	Delay_Days	Actual_Days
Units_Shipped	1.00000	-0.29405 0.2874	-0.23393 0.4014
Delay_Days	-0.29405 0.2874	1.00000	0.97228 <.0001
Actual_Days	-0.23393 0.4014	0.97228 <.0001	1.00000

PROC CORR:

Used to understand:

·       Does more volume mean more delays?

·       Is heavy shipping linked to compliance risk?

STEP-10: PROC SGPLOT

proc sgplot data=drug_fraud;

    vbar Distributor / response=Units_Shipped stat=sum;

run;

OUTPUT:

PROC SGPLOT:

Visual dashboards for management.

STEP-11: TRANSPOSE

proc transpose data=drug_fraud out=drug_wide;

    by Distributor NotSorted;

    id Fraud_Risk;

    var Units_Shipped;

run;

proc print data=drug_wide;

run;

OUTPUT:

Obs	Distributor	_NAME_	Low	High	Medium
1	MEDLINK	Units_Shipped	12000	.	.
2	HEALTHFLOW	Units_Shipped	.	8000	.
3	MEDLINK	Units_Shipped	15000	.	.
4	PHARMAROUTE	Units_Shipped	20000	.	.
5	HEALTHFLOW	Units_Shipped	.	9000	.
6	MEDLINK	Units_Shipped	11000	.	.
7	PHARMAROUTE	Units_Shipped	.	5000	.
8	HEALTHFLOW	Units_Shipped	.	.	14000
9	MEDLINK	Units_Shipped	13000	.	.
10	PHARMAROUTE	Units_Shipped	.	16000	.
11	HEALTHFLOW	Units_Shipped	10000	.	.
12	MEDLINK	Units_Shipped	.	7000	.
13	PHARMAROUTE	Units_Shipped	4000	.	.
14	HEALTHFLOW	Units_Shipped	.	.	9000
15	MEDLINK	Units_Shipped	.	.	12000

TRANSPOSE:

Used for:

·       Reporting systems

·       Feeding BI tools

STEP-12: APPEND

data drug_fraud_new;

    set drug_fraud;

    Ship_Date = intnx('day', Ship_Date, 7);

    Delivery_Date = intnx('day', Delivery_Date, 7);

run;

proc print data=drug_fraud_new;

run;

OUTPUT:

Obs	Drug_Name	Distributor	Region	Units_Shipped	Temperature_Control	Delay_Days	Compliance_Status	Ship_Date	Delivery_Date	Expected_Delivery	Actual_Days	Temp_Type	Delay_Flag	Shipment_Size	Utilization	Fraud_Risk
1	Paracetamol	MEDLINK	north	12000	Cold	1	Yes	08JAN2025	10JAN2025	04JAN2025	2	Cold-north	1	12	Medium	Low
2	Ibuprofen	HEALTHFLOW	south	8000	Ambient	5	No	12JAN2025	19JAN2025	08JAN2025	7	Ambient-south	5	8	Medium	High
3	Amoxicillin	MEDLINK	east	15000	Cold	0	Yes	14JAN2025	14JAN2025	10JAN2025	0	Cold-east	0	15	Medium	Low
4	Metformin	PHARMAROUTE	west	20000	Controlled	2	Yes	17JAN2025	20JAN2025	13JAN2025	3	Controlled-west	2	20	High	Low
5	Aspirin	HEALTHFLOW	north	9000	Ambient	8	No	22JAN2025	01FEB2025	18JAN2025	10	Ambient-north	8	9	Medium	High
6	Ciprofloxaci	MEDLINK	south	11000	Cold	1	Yes	25JAN2025	27JAN2025	21JAN2025	2	Cold-south	1	11	Medium	Low
7	Insulin	PHARMAROUTE	east	5000	Cold	10	No	27JAN2025	12FEB2025	23JAN2025	16	Cold-east	10	5	Low	High
8	Atorvastatin	HEALTHFLOW	west	14000	Controlled	3	Yes	29JAN2025	02FEB2025	25JAN2025	4	Controlled-west	3	14	Medium	Medium
9	Losartan	MEDLINK	north	13000	Controlled	2	Yes	01FEB2025	04FEB2025	28JAN2025	3	Controlled-north	2	13	Medium	Low
10	Azithromycin	PHARMAROUTE	south	16000	Cold	7	No	04FEB2025	17FEB2025	31JAN2025	13	Cold-south	7	16	High	High
11	Omeprazole	HEALTHFLOW	east	10000	Ambient	1	Yes	06FEB2025	09FEB2025	02FEB2025	3	Ambient-east	1	10	Medium	Low
12	Warfarin	MEDLINK	west	7000	Controlled	6	No	09FEB2025	17FEB2025	05FEB2025	8	Controlled-west	6	7	Low	High
13	Heparin	PHARMAROUTE	north	4000	Cold	0	Yes	12FEB2025	12FEB2025	08FEB2025	0	Cold-north	0	4	Low	Low
14	Clopidogrel	HEALTHFLOW	south	9000	Ambient	4	Yes	14FEB2025	19FEB2025	10FEB2025	5	Ambient-south	4	9	Medium	Medium
15	Lisinopril	MEDLINK	east	12000	Controlled	3	Yes	17FEB2025	22FEB2025	13FEB2025	5	Controlled-east	3	12	Medium	Medium

proc append base=drug_fraud 

            data=drug_fraud_new force;

run;

proc print data=drug_fraud;

run;

OUTPUT:

Obs	Drug_Name	Distributor	Region	Units_Shipped	Temperature_Control	Delay_Days	Compliance_Status	Ship_Date	Delivery_Date	Expected_Delivery	Actual_Days	Temp_Type	Delay_Flag	Shipment_Size	Utilization	Fraud_Risk
1	Paracetamol	MEDLINK	north	12000	Cold	1	Yes	01JAN2025	03JAN2025	04JAN2025	2	Cold-north	1	12	Medium	Low
2	Ibuprofen	HEALTHFLOW	south	8000	Ambient	5	No	05JAN2025	12JAN2025	08JAN2025	7	Ambient-south	5	8	Medium	High
3	Amoxicillin	MEDLINK	east	15000	Cold	0	Yes	07JAN2025	07JAN2025	10JAN2025	0	Cold-east	0	15	Medium	Low
4	Metformin	PHARMAROUTE	west	20000	Controlled	2	Yes	10JAN2025	13JAN2025	13JAN2025	3	Controlled-west	2	20	High	Low
5	Aspirin	HEALTHFLOW	north	9000	Ambient	8	No	15JAN2025	25JAN2025	18JAN2025	10	Ambient-north	8	9	Medium	High
6	Ciprofloxaci	MEDLINK	south	11000	Cold	1	Yes	18JAN2025	20JAN2025	21JAN2025	2	Cold-south	1	11	Medium	Low
7	Insulin	PHARMAROUTE	east	5000	Cold	10	No	20JAN2025	05FEB2025	23JAN2025	16	Cold-east	10	5	Low	High
8	Atorvastatin	HEALTHFLOW	west	14000	Controlled	3	Yes	22JAN2025	26JAN2025	25JAN2025	4	Controlled-west	3	14	Medium	Medium
9	Losartan	MEDLINK	north	13000	Controlled	2	Yes	25JAN2025	28JAN2025	28JAN2025	3	Controlled-north	2	13	Medium	Low
10	Azithromycin	PHARMAROUTE	south	16000	Cold	7	No	28JAN2025	10FEB2025	31JAN2025	13	Cold-south	7	16	High	High
11	Omeprazole	HEALTHFLOW	east	10000	Ambient	1	Yes	30JAN2025	02FEB2025	02FEB2025	3	Ambient-east	1	10	Medium	Low
12	Warfarin	MEDLINK	west	7000	Controlled	6	No	02FEB2025	10FEB2025	05FEB2025	8	Controlled-west	6	7	Low	High
13	Heparin	PHARMAROUTE	north	4000	Cold	0	Yes	05FEB2025	05FEB2025	08FEB2025	0	Cold-north	0	4	Low	Low
14	Clopidogrel	HEALTHFLOW	south	9000	Ambient	4	Yes	07FEB2025	12FEB2025	10FEB2025	5	Ambient-south	4	9	Medium	Medium
15	Lisinopril	MEDLINK	east	12000	Controlled	3	Yes	10FEB2025	15FEB2025	13FEB2025	5	Controlled-east	3	12	Medium	Medium
16	Paracetamol	MEDLINK	north	12000	Cold	1	Yes	08JAN2025	10JAN2025	04JAN2025	2	Cold-north	1	12	Medium	Low
17	Ibuprofen	HEALTHFLOW	south	8000	Ambient	5	No	12JAN2025	19JAN2025	08JAN2025	7	Ambient-south	5	8	Medium	High
18	Amoxicillin	MEDLINK	east	15000	Cold	0	Yes	14JAN2025	14JAN2025	10JAN2025	0	Cold-east	0	15	Medium	Low
19	Metformin	PHARMAROUTE	west	20000	Controlled	2	Yes	17JAN2025	20JAN2025	13JAN2025	3	Controlled-west	2	20	High	Low
20	Aspirin	HEALTHFLOW	north	9000	Ambient	8	No	22JAN2025	01FEB2025	18JAN2025	10	Ambient-north	8	9	Medium	High
21	Ciprofloxaci	MEDLINK	south	11000	Cold	1	Yes	25JAN2025	27JAN2025	21JAN2025	2	Cold-south	1	11	Medium	Low
22	Insulin	PHARMAROUTE	east	5000	Cold	10	No	27JAN2025	12FEB2025	23JAN2025	16	Cold-east	10	5	Low	High
23	Atorvastatin	HEALTHFLOW	west	14000	Controlled	3	Yes	29JAN2025	02FEB2025	25JAN2025	4	Controlled-west	3	14	Medium	Medium
24	Losartan	MEDLINK	north	13000	Controlled	2	Yes	01FEB2025	04FEB2025	28JAN2025	3	Controlled-north	2	13	Medium	Low
25	Azithromycin	PHARMAROUTE	south	16000	Cold	7	No	04FEB2025	17FEB2025	31JAN2025	13	Cold-south	7	16	High	High
26	Omeprazole	HEALTHFLOW	east	10000	Ambient	1	Yes	06FEB2025	09FEB2025	02FEB2025	3	Ambient-east	1	10	Medium	Low
27	Warfarin	MEDLINK	west	7000	Controlled	6	No	09FEB2025	17FEB2025	05FEB2025	8	Controlled-west	6	7	Low	High
28	Heparin	PHARMAROUTE	north	4000	Cold	0	Yes	12FEB2025	12FEB2025	08FEB2025	0	Cold-north	0	4	Low	Low
29	Clopidogrel	HEALTHFLOW	south	9000	Ambient	4	Yes	14FEB2025	19FEB2025	10FEB2025	5	Ambient-south	4	9	Medium	Medium
30	Lisinopril	MEDLINK	east	12000	Controlled	3	Yes	17FEB2025	22FEB2025	13FEB2025	5	Controlled-east	3	12	Medium	Medium

APPEND:

Used when new shipment batches arrive daily.

STEP-13: PROC DATASETS DELETE

proc datasets library=work nolist;

    delete drug_wide;

quit;

LOG:

NOTE: Deleting WORK.DRUG_WIDE (memtype=DATA).

PROC DATASETS DELETE:

Used for:

·       Housekeeping

·       Validation environment cleanup

Conclusion:

This Drug Distribution Channels SAS project demonstrates how pharmaceutical shipment data can be transformed into meaningful business insights using SAS. By analyzing shipment volumes, delivery delays, temperature control, and compliance status, we were able to evaluate distributor performance and identify potential risk and fraud patterns.

Using tools such as PROC SQL, PROC MEANS, PROC UNIVARIATE, PROC CORR, PROC SGPLOT, and SAS Macros, this project shows how SAS supports real-world drug supply chain monitoring, regulatory compliance, and operational decision-making. This approach is highly relevant for pharma analytics teams, clinical trial logistics, and SAS interview preparation.

INTERVIEW QUESTIONS FOR YOU

1.What is a SAS library and why is it used?

2.What are temporary and permanent SAS datasets?

3.What is a SAS informat?

About the Author:

SAS Learning Hub is a data analytics and SAS programming platform focused on clinical, financial, and real-world data analysis. The content is created by professionals with academic training in Pharmaceutics and hands-on experience in Base SAS, PROC SQL, Macros, SDTM, and ADaM, providing practical and industry-relevant SAS learning resources.

Disclaimer:

The datasets and analysis in this article are created for educational and demonstration purposes only. They do not represent Pharma data.

Our Mission:

This blog provides industry-focused SAS programming tutorials and analytics projects covering finance, healthcare, and technology.

This project is suitable for:

·  Students learning SAS

·  Data analysts building portfolios

·  Professionals preparing for SAS interviews

·  Bloggers writing about analytics and smart cities

·  EV and energy industry professionals

Follow Us On : 

 

--->Follow our blog for more SAS-based analytics projects and industry data models.

To deepen your understanding of SAS analytics, please refer to our other data science and industry-focused projects listed below:

1.How Can SAS Map The Journey And Influence Of India’s Freedom Fighters?

2.Can SAS Identify The Best Project And Tool Combinations For Software Teams?

3.Can SAS Reveal Which Office Environments Are Truly Productive And Which Are Not?

About Us | Contact | Privacy Policy