Road Bikes, BMX & Broken Records: Building Regulatory-Grade Datasets Using SAS and R

-

Analysis-Ready Bike Intelligence with SAS and R

Introduction: When Dirty Data Derails Analytics

Imagine a multinational bicycle manufacturer selling Road Bikes, Mountain Bikes, BMX Bikes, Gravel Bikes, Electric Bikes, Hybrid Bikes, Touring Bikes, Folding Bikes, Cyclocross Bikes, Fat Bikes, Track Bikes, Cargo Bikes, Recumbent Bikes, Cruiser Bikes, Kids Bikes, Tandem Bikes, Triathlon Bikes, Commuter Bikes, Dirt Jump Bikes, and Enduro Bikes across multiple countries.

A quarterly executive dashboard suddenly reports that electric bike sales declined by 40%.

Management prepares a restructuring plan.

However, the problem isn't sales.

The problem is dirty data.

Duplicate Bike IDs were counted twice.

Several bike launch dates contained invalid timestamps.

Negative revenue values entered during system migration were interpreted as losses.

Region codes appeared as:

  • us
  • USA
  • U.S.
  • Usa
  • NULL

Customer emails were malformed.

Bike categories contained spelling variations:

  • MTB
  • MountainBike
  • mountain bike
  • MOUNTAIN BIKE

The result?

Incorrect AI forecasts.

Broken dashboards.

Misleading executive decisions.

Regulatory reporting failures.

Poor quality data creates poor quality intelligence.

This is exactly why enterprise organizations invest heavily in SAS and R based data-engineering pipelines.

Global Bike Dataset with Intentional Errors

1.SAS Raw Dataset

data bike_raw;

length Bike_ID $8 Bike_Type $30 Region $15 Email $60 Launch_Date $20;

infile datalines dlm='|' dsd truncover;

input Bike_ID $ Bike_Type $ Price Units_Sold Customer_Age $ Region $

      Email $ Launch_Date $ Warranty_Years;

datalines;

BK001|Mountain Bike|45000|120|35|US|john@gmail.com|12JAN2025|3

BK001|mountain bike|-45000|120|35|usa|john@gmail|32JAN2025|3

BK002|Road Bike|60000|85|150|EU|mary@gmail.com|15FEB2025|5

BK003|E Bike|85000|.|28|NULL|abc.com|20MAR2025|2

BK004|BMX|30000|55|-5|APAC|bike@@mail.com|10APR2025|2

BK005|Hybrid Bike|52000|78|40| apac |user@gmail.com|15MAY2025|4

BK006|Gravel Bike|67000|92|NULL|EU|email.com|99JUN2025|3

BK007|Touring Bike|58000|66|30|US|tour@gmail.com|12JUL2025|5

BK008|Fat Bike|72000|80|29|U.S.|fat@gmail.com|15AUG2025|4

BK009|Cargo Bike|95000|22|33|USA|cargo@gmail.com|25SEP2025|6

BK010|Track Bike|62000|48|27|EU|track@gmail.com|11OCT2025|3

BK011|Cruiser Bike|41000|100|31|APAC|cruiser@gmail.com|09NOV2025|2

BK012|Folding Bike|55000|75|45|APAC|fold@gmail|22DEC2025|4

BK013|Kids Bike|15000|130|8|US|kids@gmail.com|15JAN2025|1

BK014|Tandem Bike|78000|20|38|EU|tandem@gmail.com|12FEB2025|5

BK015|Enduro Bike|82000|44|29|NULL|enduro@gmail.com|20MAR2025|4

;

run;

proc print data=bike_raw;

run;

OUTPUT:

ObsBike_IDBike_TypeRegionEmailLaunch_DatePriceUnits_SoldCustomer_AgeWarranty_Years
1BK001Mountain BikeUSjohn@gmail.com12JAN202545000120353
2BK001mountain bikeusajohn@gmail32JAN2025-45000120353
3BK002Road BikeEUmary@gmail.com15FEB202560000851505
4BK003E BikeNULLabc.com20MAR202585000.282
5BK004BMXAPACbike@@mail.com10APR20253000055-52
6BK005Hybrid Bikeapacuser@gmail.com15MAY20255200078404
7BK006Gravel BikeEUemail.com99JUN20256700092NULL3
8BK007Touring BikeUStour@gmail.com12JUL20255800066305
9BK008Fat BikeU.S.fat@gmail.com15AUG20257200080294
10BK009Cargo BikeUSAcargo@gmail.com25SEP20259500022336
11BK010Track BikeEUtrack@gmail.com11OCT20256200048273
12BK011Cruiser BikeAPACcruiser@gmail.com09NOV202541000100312
13BK012Folding BikeAPACfold@gmail22DEC20255500075454
14BK013Kids BikeUSkids@gmail.com15JAN20251500013081
15BK014Tandem BikeEUtandem@gmail.com12FEB20257800020385
16BK015Enduro BikeNULLenduro@gmail.com20MAR20258200044294

Explanation

This dataset intentionally contains enterprise-quality issues:

  • Duplicate Bike_ID
  • Negative prices
  • Invalid ages
  • Missing values
  • Corrupted regions
  • Invalid emails
  • Invalid dates
  • Mixed capitalization
  • NULL text values

These problems commonly appear after acquisitions, system migrations, manual entry errors, and API integration failures.

2.Character Truncation Risk in SAS

data demo;

length Bike_Type $40;

set bike_raw;

Bike_Type='Electric Mountain Bike';

run;

proc print data=demo;

run;

OUTPUT:

ObsBike_TypeBike_IDRegionEmailLaunch_DatePriceUnits_SoldCustomer_AgeWarranty_Years
1Electric Mountain BikeBK001USjohn@gmail.com12JAN202545000120353
2Electric Mountain BikeBK001usajohn@gmail32JAN2025-45000120353
3Electric Mountain BikeBK002EUmary@gmail.com15FEB202560000851505
4Electric Mountain BikeBK003NULLabc.com20MAR202585000.282
5Electric Mountain BikeBK004APACbike@@mail.com10APR20253000055-52
6Electric Mountain BikeBK005apacuser@gmail.com15MAY20255200078404
7Electric Mountain BikeBK006EUemail.com99JUN20256700092NULL3
8Electric Mountain BikeBK007UStour@gmail.com12JUL20255800066305
9Electric Mountain BikeBK008U.S.fat@gmail.com15AUG20257200080294
10Electric Mountain BikeBK009USAcargo@gmail.com25SEP20259500022336
11Electric Mountain BikeBK010EUtrack@gmail.com11OCT20256200048273
12Electric Mountain BikeBK011APACcruiser@gmail.com09NOV202541000100312
13Electric Mountain BikeBK012APACfold@gmail22DEC20255500075454
14Electric Mountain BikeBK013USkids@gmail.com15JAN20251500013081
15Electric Mountain BikeBK014EUtandem@gmail.com12FEB20257800020385
16Electric Mountain BikeBK015NULLenduro@gmail.com20MAR20258200044294

Why LENGTH Must Come First

SAS allocates character storage during compilation.

If LENGTH is declared after assignment:

Bike_Type='Electric Mountain Bike';

length Bike_Type $10;

Only first 10 characters remain.

Result:

Electric M

This truncation can silently corrupt production datasets.

R dynamically manages string memory and does not suffer from identical truncation behavior.

3.DATA Step Cleaning Workflow

data bike_clean;

set bike_raw;

Bike_Type=propcase(strip(Bike_Type));

Region=upcase(strip(Region));

if Region in ('USA','US','U.S.') then Region='NORTH_AMERICA';

if Region='APAC' then Region='ASIA_PACIFIC';

Price=abs(Price);

length Age_Num 8;

if upcase(strip(Customer_Age))='NULL' then Age_Num=.;

else Age_Num=input(Customer_Age,?? best12.);

Age_Num=abs(Age_Num);

if Age_Num>100 then Age_Num=.;

Email=lowcase(strip(Email));

if index(Email,'@')=0 then Email='INVALID_EMAIL';

drop Customer_Age;

rename Age_Num=Customer_Age;

run;

proc print data=bike_clean;

run;

OUTPUT:

ObsBike_IDBike_TypeRegionEmailLaunch_DatePriceUnits_SoldWarranty_YearsCustomer_Age
1BK001Mountain BikeNORTH_AMERICAjohn@gmail.com12JAN202545000120335
2BK001Mountain BikeNORTH_AMERICAjohn@gmail32JAN202545000120335
3BK002Road BikeEUmary@gmail.com15FEB202560000855.
4BK003E BikeNULLINVALID_EMAIL20MAR202585000.228
5BK004BmxASIA_PACIFICbike@@mail.com10APR2025300005525
6BK005Hybrid BikeASIA_PACIFICuser@gmail.com15MAY20255200078440
7BK006Gravel BikeEUINVALID_EMAIL99JUN202567000923.
8BK007Touring BikeNORTH_AMERICAtour@gmail.com12JUL20255800066530
9BK008Fat BikeNORTH_AMERICAfat@gmail.com15AUG20257200080429
10BK009Cargo BikeNORTH_AMERICAcargo@gmail.com25SEP20259500022633
11BK010Track BikeEUtrack@gmail.com11OCT20256200048327
12BK011Cruiser BikeASIA_PACIFICcruiser@gmail.com09NOV202541000100231
13BK012Folding BikeASIA_PACIFICfold@gmail22DEC20255500075445
14BK013Kids BikeNORTH_AMERICAkids@gmail.com15JAN20251500013018
15BK014Tandem BikeEUtandem@gmail.com12FEB20257800020538
16BK015Enduro BikeNULLenduro@gmail.com20MAR20258200044429

Explanation

This DATA step demonstrates:

  • PROPCASE
  • STRIP
  • UPCASE
  • ABS
  • INDEX
  • LOWCASE

The objective is standardization before analytics. Consistent values improve joins, reporting, and statistical outputs.

4.Arrays and DO Loop Cleaning

data bike_quality;

set bike_clean;

array nums {*} Price Units_Sold Warranty_Years;

do i=1 to dim(nums);

if nums{i}<0 then nums{i}=abs(nums{i});

end;

drop i;

run;

proc print data=bike_quality;

run;

OUTPUT:

ObsBike_IDBike_TypeRegionEmailLaunch_DatePriceUnits_SoldWarranty_YearsCustomer_Age
1BK001Mountain BikeNORTH_AMERICAjohn@gmail.com12JAN202545000120335
2BK001Mountain BikeNORTH_AMERICAjohn@gmail32JAN202545000120335
3BK002Road BikeEUmary@gmail.com15FEB202560000855.
4BK003E BikeNULLINVALID_EMAIL20MAR202585000.228
5BK004BmxASIA_PACIFICbike@@mail.com10APR2025300005525
6BK005Hybrid BikeASIA_PACIFICuser@gmail.com15MAY20255200078440
7BK006Gravel BikeEUINVALID_EMAIL99JUN202567000923.
8BK007Touring BikeNORTH_AMERICAtour@gmail.com12JUL20255800066530
9BK008Fat BikeNORTH_AMERICAfat@gmail.com15AUG20257200080429
10BK009Cargo BikeNORTH_AMERICAcargo@gmail.com25SEP20259500022633
11BK010Track BikeEUtrack@gmail.com11OCT20256200048327
12BK011Cruiser BikeASIA_PACIFICcruiser@gmail.com09NOV202541000100231
13BK012Folding BikeASIA_PACIFICfold@gmail22DEC20255500075445
14BK013Kids BikeNORTH_AMERICAkids@gmail.com15JAN20251500013018
15BK014Tandem BikeEUtandem@gmail.com12FEB20257800020538
16BK015Enduro BikeNULLenduro@gmail.com20MAR20258200044429

Explanation

Arrays eliminate repetitive coding.

Instead of writing multiple IF statements, a single loop validates several variables simultaneously.

This technique improves maintainability and scalability.

5.SELECT-WHEN Logic

data bike_when;

set bike_clean;

length Price_Category $12;

select;

when (Price<30000) Price_Category='Budget';

when (Price<70000) Price_Category='Midrange';

otherwise Price_Category='Premium';

end;

run;

proc print data=bike_when;

run;

OUTPUT:

ObsBike_IDBike_TypeRegionEmailLaunch_DatePriceUnits_SoldWarranty_YearsCustomer_AgePrice_Category
1BK001Mountain BikeNORTH_AMERICAjohn@gmail.com12JAN202545000120335Midrange
2BK001Mountain BikeNORTH_AMERICAjohn@gmail32JAN202545000120335Midrange
3BK002Road BikeEUmary@gmail.com15FEB202560000855.Midrange
4BK003E BikeNULLINVALID_EMAIL20MAR202585000.228Premium
5BK004BmxASIA_PACIFICbike@@mail.com10APR2025300005525Midrange
6BK005Hybrid BikeASIA_PACIFICuser@gmail.com15MAY20255200078440Midrange
7BK006Gravel BikeEUINVALID_EMAIL99JUN202567000923.Midrange
8BK007Touring BikeNORTH_AMERICAtour@gmail.com12JUL20255800066530Midrange
9BK008Fat BikeNORTH_AMERICAfat@gmail.com15AUG20257200080429Premium
10BK009Cargo BikeNORTH_AMERICAcargo@gmail.com25SEP20259500022633Premium
11BK010Track BikeEUtrack@gmail.com11OCT20256200048327Midrange
12BK011Cruiser BikeASIA_PACIFICcruiser@gmail.com09NOV202541000100231Midrange
13BK012Folding BikeASIA_PACIFICfold@gmail22DEC20255500075445Midrange
14BK013Kids BikeNORTH_AMERICAkids@gmail.com15JAN20251500013018Budget
15BK014Tandem BikeEUtandem@gmail.com12FEB20257800020538Premium
16BK015Enduro BikeNULLenduro@gmail.com20MAR20258200044429Premium

Explanation

SELECT-WHEN is cleaner than lengthy IF-THEN chains.

Useful for:

  • Risk classification
  • Product segmentation
  • Customer categorization
  • Clinical severity grouping

6.FIRST. LAST. Processing

proc sort data=bike_clean;

by Bike_ID;

run;

proc print data=bike_clean;

run;

OUTPUT:

ObsBike_IDBike_TypeRegionEmailLaunch_DatePriceUnits_SoldWarranty_YearsCustomer_Age
1BK001Mountain BikeNORTH_AMERICAjohn@gmail.com12JAN202545000120335
2BK001Mountain BikeNORTH_AMERICAjohn@gmail32JAN202545000120335
3BK002Road BikeEUmary@gmail.com15FEB202560000855.
4BK003E BikeNULLINVALID_EMAIL20MAR202585000.228
5BK004BmxASIA_PACIFICbike@@mail.com10APR2025300005525
6BK005Hybrid BikeASIA_PACIFICuser@gmail.com15MAY20255200078440
7BK006Gravel BikeEUINVALID_EMAIL99JUN202567000923.
8BK007Touring BikeNORTH_AMERICAtour@gmail.com12JUL20255800066530
9BK008Fat BikeNORTH_AMERICAfat@gmail.com15AUG20257200080429
10BK009Cargo BikeNORTH_AMERICAcargo@gmail.com25SEP20259500022633
11BK010Track BikeEUtrack@gmail.com11OCT20256200048327
12BK011Cruiser BikeASIA_PACIFICcruiser@gmail.com09NOV202541000100231
13BK012Folding BikeASIA_PACIFICfold@gmail22DEC20255500075445
14BK013Kids BikeNORTH_AMERICAkids@gmail.com15JAN20251500013018
15BK014Tandem BikeEUtandem@gmail.com12FEB20257800020538
16BK015Enduro BikeNULLenduro@gmail.com20MAR20258200044429

data bike_dedup;

set bike_clean;

by Bike_ID;

if first.Bike_ID;

run;

proc print data=bike_dedup;

run;

OUTPUT:

ObsBike_IDBike_TypeRegionEmailLaunch_DatePriceUnits_SoldWarranty_YearsCustomer_Age
1BK001Mountain BikeNORTH_AMERICAjohn@gmail.com12JAN202545000120335
2BK002Road BikeEUmary@gmail.com15FEB202560000855.
3BK003E BikeNULLINVALID_EMAIL20MAR202585000.228
4BK004BmxASIA_PACIFICbike@@mail.com10APR2025300005525
5BK005Hybrid BikeASIA_PACIFICuser@gmail.com15MAY20255200078440
6BK006Gravel BikeEUINVALID_EMAIL99JUN202567000923.
7BK007Touring BikeNORTH_AMERICAtour@gmail.com12JUL20255800066530
8BK008Fat BikeNORTH_AMERICAfat@gmail.com15AUG20257200080429
9BK009Cargo BikeNORTH_AMERICAcargo@gmail.com25SEP20259500022633
10BK010Track BikeEUtrack@gmail.com11OCT20256200048327
11BK011Cruiser BikeASIA_PACIFICcruiser@gmail.com09NOV202541000100231
12BK012Folding BikeASIA_PACIFICfold@gmail22DEC20255500075445
13BK013Kids BikeNORTH_AMERICAkids@gmail.com15JAN20251500013018
14BK014Tandem BikeEUtandem@gmail.com12FEB20257800020538
15BK015Enduro BikeNULLenduro@gmail.com20MAR20258200044429

Explanation

FIRST./LAST. variables enable group-level processing.

Commonly used for:

  • Deduplication
  • Visit tracking
  • Longitudinal studies
  • Customer journey analysis

7.PROC SQL Deduplication

proc sql;

create table bike_sql as

select distinct *

from bike_clean;

quit;

proc print data=bike_sql;

run;

OUTPUT:

ObsBike_IDBike_TypeRegionEmailLaunch_DatePriceUnits_SoldWarranty_YearsCustomer_Age
1BK001Mountain BikeNORTH_AMERICAjohn@gmail32JAN202545000120335
2BK001Mountain BikeNORTH_AMERICAjohn@gmail.com12JAN202545000120335
3BK002Road BikeEUmary@gmail.com15FEB202560000855.
4BK003E BikeNULLINVALID_EMAIL20MAR202585000.228
5BK004BmxASIA_PACIFICbike@@mail.com10APR2025300005525
6BK005Hybrid BikeASIA_PACIFICuser@gmail.com15MAY20255200078440
7BK006Gravel BikeEUINVALID_EMAIL99JUN202567000923.
8BK007Touring BikeNORTH_AMERICAtour@gmail.com12JUL20255800066530
9BK008Fat BikeNORTH_AMERICAfat@gmail.com15AUG20257200080429
10BK009Cargo BikeNORTH_AMERICAcargo@gmail.com25SEP20259500022633
11BK010Track BikeEUtrack@gmail.com11OCT20256200048327
12BK011Cruiser BikeASIA_PACIFICcruiser@gmail.com09NOV202541000100231
13BK012Folding BikeASIA_PACIFICfold@gmail22DEC20255500075445
14BK013Kids BikeNORTH_AMERICAkids@gmail.com15JAN20251500013018
15BK014Tandem BikeEUtandem@gmail.com12FEB20257800020538
16BK015Enduro BikeNULLenduro@gmail.com20MAR20258200044429

Explanation

PROC SQL provides relational-style processing.

Advantages:

  • Familiar SQL syntax
  • Easy joins
  • Aggregations
  • Flexible filtering

DATA Step is usually faster for row-wise processing.

PROC SQL excels at relational operations.

8.PROC FORMAT

proc format;

value pricefmt low-29999='Budget'

             30000-69999='Midrange'

              70000-high='Premium';

run;

LOG:

NOTE: Format PRICEFMT has been output.

Explanation

Formats separate business logic from physical data.

Benefits:

  • Reusability
  • Consistency
  • Central governance

9.Create Lookup Dataset

data region_lookup;

length Region $20 Region_Name $50;

input Region $ Region_Name & $50.;

datalines;

NORTH_AMERICA North America Region

EU Europe Region

ASIA_PACIFIC Asia Pacific Region

NULL Unknown Region

;

run;

proc print data=region_lookup;

run;

OUTPUT:

ObsRegionRegion_Name
1NORTH_AMERICANorth America Region
2EUEurope Region
3ASIA_PACIFICAsia Pacific Region
4NULLUnknown Region

10.PROC SQL Join Example And Report

proc sql;

create table bike_report as

select a.*,

       b.Region_Name

from bike_clean a

left join region_lookup b

on a.Region=b.Region;

quit;

proc print data=bike_report;

run;

OUTPUT:

ObsBike_IDBike_TypeRegionEmailLaunch_DatePriceUnits_SoldWarranty_YearsCustomer_AgeRegion_Name
1BK011Cruiser BikeASIA_PACIFICcruiser@gmail.com09NOV202541000100231Asia Pacific Region
2BK005Hybrid BikeASIA_PACIFICuser@gmail.com15MAY20255200078440Asia Pacific Region
3BK004BmxASIA_PACIFICbike@@mail.com10APR2025300005525Asia Pacific Region
4BK012Folding BikeASIA_PACIFICfold@gmail22DEC20255500075445Asia Pacific Region
5BK006Gravel BikeEUINVALID_EMAIL99JUN202567000923.Europe Region
6BK010Track BikeEUtrack@gmail.com11OCT20256200048327Europe Region
7BK002Road BikeEUmary@gmail.com15FEB202560000855.Europe Region
8BK014Tandem BikeEUtandem@gmail.com12FEB20257800020538Europe Region
9BK013Kids BikeNORTH_AMERICAkids@gmail.com15JAN20251500013018North America Region
10BK009Cargo BikeNORTH_AMERICAcargo@gmail.com25SEP20259500022633North America Region
11BK008Fat BikeNORTH_AMERICAfat@gmail.com15AUG20257200080429North America Region
12BK001Mountain BikeNORTH_AMERICAjohn@gmail.com12JAN202545000120335North America Region
13BK001Mountain BikeNORTH_AMERICAjohn@gmail32JAN202545000120335North America Region
14BK007Touring BikeNORTH_AMERICAtour@gmail.com12JUL20255800066530North America Region
15BK003E BikeNULLINVALID_EMAIL20MAR202585000.228Unknown Region
16BK015Enduro BikeNULLenduro@gmail.com20MAR20258200044429Unknown Region

Explanation

SQL joins enrich analytical datasets using reference tables.

Widely used in:

  • SDTM mapping
  • Customer analytics
  • Banking risk models
  • Insurance claims

data bike_report;

set bike_report;

drop Region;

rename Region_Name=Region;

run;

proc print data=bike_report;

run;

OUTPUT:

ObsBike_IDBike_TypeEmailLaunch_DatePriceUnits_SoldWarranty_YearsCustomer_AgeRegion
1BK011Cruiser Bikecruiser@gmail.com09NOV202541000100231Asia Pacific Region
2BK005Hybrid Bikeuser@gmail.com15MAY20255200078440Asia Pacific Region
3BK004Bmxbike@@mail.com10APR2025300005525Asia Pacific Region
4BK012Folding Bikefold@gmail22DEC20255500075445Asia Pacific Region
5BK006Gravel BikeINVALID_EMAIL99JUN202567000923.Europe Region
6BK010Track Biketrack@gmail.com11OCT20256200048327Europe Region
7BK002Road Bikemary@gmail.com15FEB202560000855.Europe Region
8BK014Tandem Biketandem@gmail.com12FEB20257800020538Europe Region
9BK013Kids Bikekids@gmail.com15JAN20251500013018North America Region
10BK009Cargo Bikecargo@gmail.com25SEP20259500022633North America Region
11BK008Fat Bikefat@gmail.com15AUG20257200080429North America Region
12BK001Mountain Bikejohn@gmail.com12JAN202545000120335North America Region
13BK001Mountain Bikejohn@gmail32JAN202545000120335North America Region
14BK007Touring Biketour@gmail.com12JUL20255800066530North America Region
15BK003E BikeINVALID_EMAIL20MAR202585000.228Unknown Region
16BK015Enduro Bikeenduro@gmail.com20MAR20258200044429Unknown Region

11.Enterprise Reporting Procedures

proc freq data=bike_clean;

tables Region Bike_Type;

run;

OUTPUT:

The FREQ Procedure

RegionFrequencyPercentCumulative
Frequency		Cumulative
Percent
ASIA_PACIFIC425.00425.00
EU425.00850.00
NORTH_AMERICA637.501487.50
NULL212.5016100.00
Bike_TypeFrequencyPercentCumulative
Frequency		Cumulative
Percent
Bmx16.2516.25
Cargo Bike16.25212.50
Cruiser Bike16.25318.75
E Bike16.25425.00
Enduro Bike16.25531.25
Fat Bike16.25637.50
Folding Bike16.25743.75
Gravel Bike16.25850.00
Hybrid Bike16.25956.25
Kids Bike16.251062.50
Mountain Bike212.501275.00
Road Bike16.251381.25
Tandem Bike16.251487.50
Touring Bike16.251593.75
Track Bike16.2516100.00

proc means data=bike_clean n mean median min max;

var Price Units_Sold;

run;

OUTPUT:

The MEANS Procedure

VariableNMeanMedianMinimumMaximum
Price
Units_Sold
16
15
58875.00
75.6666667
59000.00
78.0000000
15000.00
20.0000000
95000.00
130.0000000

proc summary data=bike_clean;

class Region;

var Price;

output out=summary_stats mean=;

run;

proc print data=summary_stats;

run;

OUTPUT:

ObsRegion_TYPE__FREQ_Price
1 01658875
2ASIA_PACIFIC1444500
3EU1466750
4NORTH_AMERICA1655000
5NULL1283500

Explanation

These procedures provide:

  • Frequency distributions
  • Descriptive statistics
  • Aggregated reporting

They serve as the foundation for executive dashboards and regulatory reporting.

12.PROC TRANSPOSE and PROC REPORT

proc transpose data=summary_stats

out=transpose_stats;

run;

proc print data=transpose_stats;

run;

OUTPUT:

Obs_NAME_COL1COL2COL3COL4COL5
1_TYPE_01111
2_FREQ_164462
3Price5887544500667505500083500

proc report data=bike_clean nowd;

column Region Bike_Type Price;

run;

OUTPUT:

RegionBike_TypePrice
NORTH_AMERICAMountain Bike45000
NORTH_AMERICAMountain Bike45000
EURoad Bike60000
NULLE Bike85000
ASIA_PACIFICBmx30000
ASIA_PACIFICHybrid Bike52000
EUGravel Bike67000
NORTH_AMERICATouring Bike58000
NORTH_AMERICAFat Bike72000
NORTH_AMERICACargo Bike95000
EUTrack Bike62000
ASIA_PACIFICCruiser Bike41000
ASIA_PACIFICFolding Bike55000
NORTH_AMERICAKids Bike15000
EUTandem Bike78000
NULLEnduro Bike82000

Explanation

PROC TRANSPOSE reshapes data.

PROC REPORT produces publication-quality outputs frequently used in:

  • Clinical trial reporting
  • Regulatory submissions
  • Executive scorecards

13.Reusable SAS Macro

%macro dqcheck(ds);

proc freq data=&ds;

tables Region Bike_Type / missing;

run;

proc means data=&ds n nmiss;

run;

%mend;

%dqcheck(bike_clean);

OUTPUT:

The FREQ Procedure

RegionFrequencyPercentCumulative
Frequency		Cumulative
Percent
ASIA_PACIFIC425.00425.00
EU425.00850.00
NORTH_AMERICA637.501487.50
NULL212.5016100.00
Bike_TypeFrequencyPercentCumulative
Frequency		Cumulative
Percent
Bmx16.2516.25
Cargo Bike16.25212.50
Cruiser Bike16.25318.75
E Bike16.25425.00
Enduro Bike16.25531.25
Fat Bike16.25637.50
Folding Bike16.25743.75
Gravel Bike16.25850.00
Hybrid Bike16.25956.25
Kids Bike16.251062.50
Mountain Bike212.501275.00
Road Bike16.251381.25
Tandem Bike16.251487.50
Touring Bike16.251593.75
Track Bike16.2516100.00

The MEANS Procedure

VariableNN Miss
Price
Units_Sold
Warranty_Years
Customer_Age
16
15
16
14
0
1
0
2

Explanation

Macros standardize validation activities.

Advantages:

  • Reusability
  • Consistency
  • Faster deployment
  • Reduced coding errors

14.R Raw Data

library(tibble)

bike_raw <- tibble(

  Bike_ID = c("BK001","BK001","BK002","BK003","BK004",

              "BK005","BK006","BK007","BK008","BK009",

              "BK010","BK011","BK012","BK013","BK014",

              "BK015","BK016","BK017","BK018","BK019" ),

  Bike_Type = c("Mountain Bike", "mountain bike","Road Bike",

                "E Bike", "BMX","Hybrid Bike","Gravel Bike","Touring Bike",

                "Fat Bike","Cargo Bike","Track Bike","Cruiser Bike","Folding Bike",

                "Kids Bike", "Tandem Bike","Enduro Bike", "Cyclocross Bike",

                "Electric Bike","Commuter Bike"," Dirt Jump Bike " ),

  Price = c(45000,-45000,60000,85000,30000,52000,67000,58000,

            72000,95000,62000,41000,55000,15000,78000,82000,69000,-90000,

            47000,56000 ),

  Units_Sold = c(120,120,85,NA,55,78,92,66,80,22,48,100,75,130,20,

                 44,35,60,NA,71),

  Customer_Age = c(35,35,150,28,-5,40,NA,30,29,33,27,31,45,8,38,

                   29,200,25,41,36),

  Region = c("US","usa","EU","NULL","APAC"," apac ","EU",

             "US","U.S.","USA","EU","APAC","APAC","US","EU","NULL",

             "EUROPE","ASIA","USA","NA"),

  Email = c("john@gmail.com","john@gmail","mary@gmail.com",

            "abc.com","bike@@mail.com","user@gmail.com","email.com",

            "tour@gmail.com","fat@gmail.com","cargo@gmail.com",

            "track@gmail.com","cruiser@gmail.com","fold@gmail",

            "kids@gmail.com","tandem@gmail.com","enduro@gmail.com",

            "cyclo.gmail.com","electric@gmail.com","commuter@gmail",

            "dirt@gmail.com"),

  Launch_Date = c("12JAN2025","32JAN2025","15FEB2025",

                  "20MAR2025","10APR2025","15MAY2025","99JUN2025",

                  "12JUL2025","15AUG2025","25SEP2025","11OCT2025",

                  "09NOV2025","22DEC2025","15JAN2025","12FEB2025",

                  "20MAR2025","18APR2025","31FEB2025","01MAY2025",

                  "14JUN2025" ),

  Warranty_Years = c(3,3,5,2,2,4,3,5,4,6, 3,2,4,1,5,4,3,5,2,4 )

)

OUTPUT:

Bike_ID

Bike_Type

Price

Units_Sold

Customer_Age

Region

Email

Launch_Date

Warranty_Years

BK001

Mountain Bike

45000

120

35

US

john@gmail.com

12JAN2025

3

BK001

mountain bike

-45000

120

35

usa

john@gmail

32JAN2025

3

BK002

Road Bike

60000

85

150

EU

mary@gmail.com

15FEB2025

5

BK003

E Bike

85000

28

NULL

abc.com

20MAR2025

2

BK004

BMX

30000

55

-5

APAC

bike@@mail.com

10APR2025

2

BK005

Hybrid Bike

52000

78

40

 apac

user@gmail.com

15MAY2025

4

BK006

Gravel Bike

67000

92

EU

email.com

99JUN2025

3

BK007

Touring Bike

58000

66

30

US

tour@gmail.com

12JUL2025

5

BK008

Fat Bike

72000

80

29

U.S.

fat@gmail.com

15AUG2025

4

BK009

Cargo Bike

95000

22

33

USA

cargo@gmail.com

25SEP2025

6

BK010

Track Bike

62000

48

27

EU

track@gmail.com

11OCT2025

3

BK011

Cruiser Bike

41000

100

31

APAC

cruiser@gmail.com

09NOV2025

2

BK012

Folding Bike

55000

75

45

APAC

fold@gmail

22DEC2025

4

BK013

Kids Bike

15000

130

8

US

kids@gmail.com

15JAN2025

1

BK014

Tandem Bike

78000

20

38

EU

tandem@gmail.com

12FEB2025

5

BK015

Enduro Bike

82000

44

29

NULL

enduro@gmail.com

20MAR2025

4

BK016

Cyclocross Bike

69000

35

200

EUROPE

cyclo.gmail.com

18APR2025

3

BK017

Electric Bike

-90000

60

25

ASIA

electric@gmail.com

31FEB2025

5

BK018

Commuter Bike

47000

41

USA

commuter@gmail

01MAY2025

2

BK019

 Dirt Jump Bike

56000

71

36

NA

dirt@gmail.com

14JUN2025

4

15.R Data Cleaning Workflow

library(tidyverse)

library(janitor)

library(lubridate)

bike_clean <- bike_raw %>%

  clean_names() %>%

  mutate(bike_type = str_to_title(str_trim(bike_type)),

         region = case_when(

         region %in% c("US","USA","U.S.") ~ "NORTH_AMERICA",

         region=="APAC" ~ "ASIA_PACIFIC",

         TRUE ~ region),

         price = abs(price),

         customer_age = if_else(customer_age>100,

                           NA_real_,abs(customer_age)),

         email = if_else(grepl("@",email),

                 email,"INVALID_EMAIL"),

         launch_date =suppressWarnings(parse_date_time(

               launch_date,orders = "dby"))

    ) %>%

        distinct()

OUTPUT:

bike_id

bike_type

price

units_sold

customer_age

region

email

launch_date

warranty_years

BK001

Mountain Bike

45000

120

35

NORTH_AMERICA

john@gmail.com

2025-01-12 00:00:00 UTC

3

BK001

Mountain Bike

45000

120

35

usa

john@gmail

3

BK002

Road Bike

60000

85

EU

mary@gmail.com

2025-02-15 00:00:00 UTC

5

BK003

E Bike

85000

28

NULL

INVALID_EMAIL

2025-03-20 00:00:00 UTC

2

BK004

Bmx

30000

55

5

ASIA_PACIFIC

bike@@mail.com

2025-04-10 00:00:00 UTC

2

BK005

Hybrid Bike

52000

78

40

 apac

user@gmail.com

2025-05-15 00:00:00 UTC

4

BK006

Gravel Bike

67000

92

EU

INVALID_EMAIL

3

BK007

Touring Bike

58000

66

30

NORTH_AMERICA

tour@gmail.com

2025-07-12 00:00:00 UTC

5

BK008

Fat Bike

72000

80

29

NORTH_AMERICA

fat@gmail.com

2025-08-15 00:00:00 UTC

4

BK009

Cargo Bike

95000

22

33

NORTH_AMERICA

cargo@gmail.com

2025-09-25 00:00:00 UTC

6

BK010

Track Bike

62000

48

27

EU

track@gmail.com

2025-10-11 00:00:00 UTC

3

BK011

Cruiser Bike

41000

100

31

ASIA_PACIFIC

cruiser@gmail.com

2025-11-09 00:00:00 UTC

2

BK012

Folding Bike

55000

75

45

ASIA_PACIFIC

fold@gmail

2025-12-22 00:00:00 UTC

4

BK013

Kids Bike

15000

130

8

NORTH_AMERICA

kids@gmail.com

2025-01-15 00:00:00 UTC

1

BK014

Tandem Bike

78000

20

38

EU

tandem@gmail.com

2025-02-12 00:00:00 UTC

5

BK015

Enduro Bike

82000

44

29

NULL

enduro@gmail.com

2025-03-20 00:00:00 UTC

4

BK016

Cyclocross Bike

69000

35

EUROPE

INVALID_EMAIL

2025-04-18 00:00:00 UTC

3

BK017

Electric Bike

90000

60

25

ASIA

electric@gmail.com

5

BK018

Commuter Bike

47000

41

NORTH_AMERICA

commuter@gmail

2025-05-01 00:00:00 UTC

2

BK019

Dirt Jump Bike

56000

71

36

NA

dirt@gmail.com

2025-06-14 00:00:00 UTC

4

Explanation

Equivalent SAS Functions:

SAS

R

PROPCASE

str_to_title

STRIP

str_trim

ABS

abs

IF THEN

if_else

SELECT WHEN

case_when

INPUT

parse_date_time

PROC SORT NODUPKEY

distinct

R provides elegant pipeline processing while SAS provides stronger enterprise auditability.

Enterprise Validation & Compliance

Clinical environments require:

  • SDTM compliance
  • ADaM traceability
  • Independent QC
  • Audit trails
  • Validation documentation
  • Reproducibility

One dangerous SAS behavior:

if value < 10

Missing numeric values are treated as smaller than valid numbers.

Thus:

. < 10

returns TRUE.

This can produce catastrophic analytical errors if missing values are not handled explicitly.

Always use:

if not missing(value) and value <10;

20 Data Cleaning Best Practices

  1. Validate metadata first.
  2. Standardize variable names.
  3. Remove duplicates early.
  4. Normalize categorical values.
  5. Validate email structures.
  6. Check date ranges.
  7. Use audit logs.
  8. Maintain lineage.
  9. Version-control code.
  10. Separate raw and clean layers.
  11. Implement QC review.
  12. Use reusable macros.
  13. Track derivations.
  14. Validate missing values.
  15. Use controlled terminology.
  16. Create lookup tables.
  17. Avoid hardcoding.
  18. Automate validations.
  19. Document assumptions.
  20. Archive production outputs.

Business Logic Behind Data Cleaning

Data cleaning exists because business systems rarely capture perfect information. A bike customer aged 150 years is unrealistic and would distort customer segmentation models. Negative bike prices may originate from accounting reversals but should not be interpreted as true sales values. Missing launch dates can prevent accurate product lifecycle calculations and forecasting. In healthcare, missing patient visit dates affect treatment exposure calculations and survival analyses. Text normalization is equally important. “mountain bike,” “Mountain Bike,” and “MOUNTAIN BIKE” should represent the same category. Standardization improves aggregation accuracy and reporting consistency. Date standardization ensures proper interval calculations using functions like INTCK and INTNX. Missing values may be imputed when justified by protocol or business rules, reducing analytical bias. The ultimate objective is transforming raw operational records into trustworthy analytical assets that support dashboards, AI models, regulatory reporting, and executive decisions.

20 One-Line Insights

  1. Dirty data creates expensive business mistakes.
  2. Standardized variables improve reproducibility.
  3. Validation logic is stronger than visual inspection.
  4. Duplicates distort analytics.
  5. Metadata drives consistency.
  6. Auditability builds trust.
  7. Missing values require explicit handling.
  8. Automation reduces human error.
  9. Formats simplify governance.
  10. Macros improve scalability.
  11. Traceability supports compliance.
  12. Defensive programming prevents failures.
  13. Data lineage matters.
  14. Clean inputs improve AI outputs.
  15. QC independence reduces risk.
  16. Documentation accelerates audits.
  17. Standardization improves joins.
  18. Governance improves reliability.
  19. Reusable code increases efficiency.
  20. Analytical intelligence begins with clean data.

SAS vs R Comparison

Feature

SAS

R

Auditability

Excellent

Moderate

Regulatory Acceptance

Excellent

Growing

Flexibility

High

Very High

Scalability

Excellent

High

Reporting

Excellent

Strong

Open Source

No

Yes

Validation Support

Excellent

Good

Clinical Industry Usage

Dominant

Increasing

Summary

SAS excels in regulated enterprise environments where traceability, audit readiness, validation, and controlled reporting are mandatory. DATA Step processing is highly optimized for large-scale data engineering, while PROC SQL supports relational transformations efficiently. R offers unmatched flexibility through the tidyverse ecosystem, enabling rapid development and advanced analytics. SAS provides stronger governance, whereas R provides broader innovation. Together they create a powerful hybrid framework capable of handling data ingestion, cleaning, transformation, validation, visualization, and advanced modeling. Organizations increasingly use SAS for validated production pipelines and R for exploratory analytics, machine learning, and modern reporting. The combination delivers scalability, analytical reliability, operational transparency, and reproducible intelligence.

Conclusion

Modern analytics ecosystems depend on trustworthy data rather than sophisticated algorithms alone. Whether the domain is clinical research, banking, insurance, retail, or global bicycle manufacturing, poor-quality data can undermine dashboards, machine learning models, executive reporting, and regulatory submissions. Duplicate identifiers, malformed emails, inconsistent categories, invalid dates, missing values, and unrealistic measurements all introduce analytical risk. Effective data engineering frameworks therefore emphasize prevention, detection, correction, validation, and documentation.

SAS provides a highly structured environment for enterprise-grade cleaning through DATA Step programming, PROC SQL, PROC FORMAT, PROC REPORT, and macro-driven automation. Its strengths include auditability, traceability, and regulatory acceptance. R complements these capabilities with flexible transformation pipelines, advanced string handling, modern data wrangling libraries, and rapid analytical innovation. Together, these technologies enable organizations to convert fragmented operational records into analysis-ready datasets.

The most successful data teams treat data cleaning as a strategic business function rather than a technical afterthought. Every correction, validation check, standardization rule, and audit trail contributes directly to better decisions. Clean data improves forecasting accuracy, strengthens compliance, enhances AI reliability, and increases stakeholder confidence. As data volumes continue growing, structured SAS and R workflows become essential for building scalable, production-grade business intelligence systems that executives, regulators, scientists, and customers can trust.

Interview Questions and Answers

Q1. A clinical dataset contains duplicate Subject IDs. How would you identify and remove them?

Answer:
I would use PROC SORT NODUPKEY or FIRST./LAST. processing in SAS and distinct() in R. After removal, I would validate record counts and compare outputs using PROC FREQ or QC scripts.

Q2. A negative billing amount appears in production data. What would you do?

Answer:
First determine business meaning. If it represents a reversal transaction, preserve it. If it is a data-entry error, correct using business rules and document the derivation in audit logs.

Q3. How do you handle missing numeric values in SAS?

Answer:
Always use missing() checks because SAS treats missing numeric values as smaller than valid numbers. Failure to do so may incorrectly classify records.

Q4. How would you validate a cleaned dataset?

Answer:
Compare pre-cleaning and post-cleaning counts, review missingness, run PROC COMPARE, validate business rules, and perform independent QC review.

Q5. When would you choose PROC SQL instead of DATA Step?

Answer:
PROC SQL is preferred for joins, aggregations, and relational logic. DATA Step is generally preferred for row-by-row transformations, arrays, RETAIN processing, and performance-intensive data engineering workflows.

---------------------------------------------------------------------------------------------------

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. Here we learn about BIKE 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

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Follow Us On : 


 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

---> Support Us By Following Our Blog..

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



--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

About Us | Contact | Privacy Policy

Comments

Post a Comment

Popular posts from this blog

Beyond Fabric and Fashion: Turning the World’s Most Beautiful Sarees Dataset into Structured Intelligence with SAS and R

-
Image
Silk, Culture & Structured Code: Transforming the World’s Most Beautiful Sarees Dataset into Analytical Intelligence with PROC TRANSPOSE in SAS and R 1. Introduction  Imagine a luxury global fashion company collecting saree sales and textile heritage data from different countries. One regional team enters “Kanchipuram” while another types “kanchipuram,” and another leaves it blank as “NULL.” Prices appear in multiple currencies, launch dates are inconsistent, duplicate inventory records exist, and one analyst accidentally enters a negative saree price. Now imagine executives using this corrupted dataset to forecast international textile demand. That is how bad data quietly destroys analytics. In modern data science and business intelligence, messy datasets are more dangerous than missing datasets. Whether you work in clinical trials, banking, retail, or textile analytics, poor-quality data creates misleading insights, regulatory issues, incorrect forecasting, and disa...
Read more

Data Cleaning Secrets Using Famous Food Dataset:Handling Duplicate Records in SAS

-
Image
Global Plate Chaos to Clean Insights: Mastering Duplicate Handling in SAS with Real-World Food Data 1. Introduction Imagine you’re working as a clinical data analyst in a pharmaceutical company. You receive a patient dataset for a Phase III trial. Everything looks fine until your summary statistics show 120 patients enrolled, while the actual study had only 100. Panic. What went wrong? Duplicate records. Now shift that same scenario to a business dataset say, global food analytics. You’re analyzing “most popular foods worldwide” to guide a multinational restaurant expansion strategy. But duplicate rows inflate demand for Pizza in Italy or Sushi in Japan. Suddenly, your insights are misleading, and millions of dollars are at stake. Bad data doesn’t just “look messy” it destroys decision-making . This is where tools like SAS and R come in. They don’t just clean data they enforce discipline, reproducibility, and regulatory compliance (especially critical in clinical trials...
Read more

Global AI Trends Unlocked Through SCAN and SUBSTR Precision in SAS

-
Image
Global AI Nations, Broken Data & Hidden Patterns: Mastering SCAN and SUBSTR in SAS for Real-World Data Rescue 1. Introduction Imagine you’re working as a SAS programmer on a global analytics project analyzing AI adoption across countries . The dataset looks promising countries, AI usage percentages, launch dates, sector details but within minutes, reality hits: “India” appears as india, INDIA, and even NULL Dates look like 2023-01-01, 01/01/2023, and 20230101 AI sector column contains strings like "Healthcare-AI", "Finance_AI", "Edu AI" Duplicate records quietly distort trends Negative usage percentages exist (!) Now imagine presenting insights from this dataset to stakeholders. One wrong number → flawed decision → business loss. This is exactly why data cleaning is not optional it’s foundational . In both SAS and R , data cleaning transforms chaos into intelligence. And in SAS, functions like SCAN ...
Read more