Transforming Historical War Records into Enterprise Intelligence Using SAS DATA Step, PROC SQL, and R

FROM ANCIENT BATTLEFIELDS TO ANALYTICAL INTELLIGENCE

Engineering the Greatest Wars in World Dataset into Production-Ready Business Intelligence Using SAS and R

Introduction

Data rarely arrives in perfect condition.

Whether you're a Clinical SAS Programmer preparing regulatory submissions, a banking analyst validating financial transactions, an insurance data engineer processing millions of claims, or a retail analyst building executive dashboards, one reality remains constant: raw data is messy.

Although this article uses a "Greatest Wars in World" dataset for educational purposes, the techniques demonstrated are identical to those used in pharmaceutical companies, CROs, banks, insurance organizations, government agencies, and Fortune 500 enterprises.

The objective is not to study military history. Instead, it is to demonstrate how enterprise-grade data engineering transforms corrupted information into trustworthy analytical assets.

Imagine receiving historical records collected from multiple countries over decades. Some records use different date formats. Others contain duplicate identifiers, inconsistent region names, missing casualty estimates, corrupted commander names, impossible duration values, malformed source references, and inconsistent capitalization.

If analysts immediately begin reporting without validating these records, every dashboard, statistical model, and AI prediction becomes vulnerable to hidden data quality issues.

Experienced Clinical SAS Programmers understand that successful analytics begins long before PROC REPORT or machine learning algorithms. It begins with disciplined data validation, metadata standardization, and reproducible cleaning logic.

Throughout this article, we will build an enterprise-quality workflow using both SAS and R, comparing DATA Step programming, PROC SQL, and the tidyverse ecosystem while following production-grade best practices used across regulated industries.

Business Crisis Scenario

Imagine a multinational historical research organization developing a global intelligence platform that analyzes the greatest wars in world history. Universities, policy researchers, documentary producers, publishers, and government analysts rely on the platform to generate reports, visualize timelines, and identify long-term geopolitical trends.

A new quarterly dashboard is scheduled for executive review.

Everything appears normal—until validation begins.

The quality assurance team discovers that the incoming data has serious integrity problems:

• Duplicate War IDs causing double-counting of conflicts.
• Missing start dates breaking timeline visualizations.
• Negative casualty estimates due to incorrect imports.
• Impossible war durations exceeding several centuries.
• Mixed uppercase and lowercase continent names.
• Extra whitespace preventing successful joins.
• "NULL" stored as text instead of true missing values.
• Invalid region abbreviations imported from legacy systems.
• Corrupted source URLs and malformed contact emails.
• Mixed numeric and character values within casualty fields.

At first glance, these problems appear minor. However, once the data enters production, the consequences become significant.

Executive dashboards display incorrect rankings of major conflicts. Interactive maps misclassify geographical regions. AI forecasting models trained on corrupted data produce misleading insights. Research publications cite inaccurate statistics. Historical trend analyses become inconsistent across departments because different analysts clean the data differently.

In a clinical trial, similar failures could misclassify patient populations or invalidate regulatory submissions. In banking, duplicate transactions may distort fraud detection models. In insurance, incorrect claim categories can affect reserve calculations. Regardless of the industry, poor-quality data undermines confidence in every downstream decision.

This scenario illustrates why enterprise organizations invest heavily in automated validation, reusable cleaning frameworks, metadata governance, audit trails, and standardized programming practices. Cleaning data is not simply a preprocessing step it is a risk management strategy that protects analytical integrity and business credibility.

Project Objectives

By the end of this project, you will learn how to:

• Design an analysis-ready dataset from corrupted raw information.
• Identify common enterprise data-quality problems before analysis begins.
• Build reusable SAS DATA Step and PROC SQL cleaning workflows.
• Develop equivalent data-cleaning pipelines in R using the tidyverse.
• Standardize character variables, dates, numeric values, and categorical fields.
• Remove duplicates while preserving auditability.
• Compare SAS and R approaches for enterprise-scale data engineering.
• Produce reporting-ready datasets suitable for dashboards, statistical analysis, and executive reporting.
• Understand how production-grade validation supports reproducibility and regulatory compliance.

Dataset Design

To demonstrate real-world production challenges, we will create a fictional educational dataset named Greatest Wars in World. The records are intentionally corrupted to simulate issues commonly encountered during enterprise data integration projects.

Unlike traditional employee datasets, this project focuses on historical operational records, allowing us to apply the same engineering principles used in healthcare, banking, insurance, and retail environments.

The dataset contains more than 20 observations and 9 business variables.

Variable	Description
WAR_ID	Unique identifier for each war record
WAR_NAME	Official name of the conflict
CONTINENT	Primary geographical region
START_DATE_RAW	Raw character representation of war start date
DURATION_YEARS	Reported duration in years
EST_CASUALTIES	Estimated casualties
WAR_TYPE	Conflict classification
SOURCE_EMAIL	Contact email for the contributing archive
DATA_PROVIDER	Organization supplying the record

Intentional Data Quality Issues Included

The raw dataset has been deliberately corrupted to resemble real production environments.

Data Quality Issue	Enterprise Impact
Duplicate WAR_ID values	Duplicate reporting and inaccurate summaries
Missing start dates	Broken timelines and invalid trend analysis
Negative casualty estimates	Impossible business metrics
Invalid duration values	Incorrect historical calculations
Mixed uppercase/lowercase text	Failed joins and inconsistent grouping
Leading/trailing whitespace	Matching failures during merges
"NULL" stored as text	Incorrect missing-value interpretation
Malformed email addresses	Failed communication and validation checks
Invalid conflict categories	Incorrect classification and reporting
Mixed numeric/character values	Type conversion failures

Why This Dataset Matters

Although the subject matter is historical, the engineering challenges are universal.

The same validation logic demonstrated in this project can be applied to:

• Clinical trial patient enrollment records
• Banking transaction systems
• Insurance claims processing
• Retail sales operations
• Manufacturing production logs
• Supply chain tracking
• Government census data
• Public health surveillance
• Financial risk reporting
• Executive business intelligence dashboards

Experienced SAS programmers know that the programming language changes very little across industries. What changes is the business domain. The principles of validation, standardization, traceability, deduplication, and reproducibility remain the foundation of trustworthy analytics.

The complete SAS raw dataset using:

• DATALINES
• INFILE
• INPUT
• LENGTH
• FORMAT
• INFORMAT
• Proper character and numeric definitions

Building the "Greatest Wars in World" Raw Dataset with Intentional Enterprise Data Quality Issues

Before writing a single line of cleaning logic, experienced SAS programmers always begin by preserving the raw source exactly as received. Production environments never modify the original dataset directly. Instead, they create a protected raw layer that serves as the single source of truth for validation, auditing, and reproducibility.

In this project, we intentionally introduce multiple data quality issues that resemble those found in real-world healthcare, banking, insurance, retail, and government systems. Although the dataset contains historical information about the world's greatest wars, the engineering concepts are identical to those used in enterprise analytics.

Dataset Variables (9 Variables)

Variable	Type	Description
WAR_ID	Character	Unique War Identifier
WAR_NAME	Character	Name of the War
CONTINENT	Character	Geographic Region
START_DATE_RAW	Character	Raw Start Date
DURATION_YEARS	Numeric	Duration of War
EST_CASUALTIES	Character	Estimated Casualties (contains mixed values intentionally)
WAR_TYPE	Character	Conflict Category
SOURCE_EMAIL	Character	Source Contact Email
DATA_PROVIDER	Character	Historical Data Provider

1.SAS Raw Dataset

data wars_raw;

length war_id $10 war_name $60 continent $25 start_date_raw $25

       est_casualties  $20 war_type $25 source_email $60 data_provider $40;

informat start_date_raw $25.;

format duration_years 8.;

infile datalines dlm='|' truncover;

input war_id $ war_name $ continent $ start_date_raw $ duration_years

est_casualties $ war_type $ source_email $ data_provider $;

datalines;

WAR001|World War II|Europe|1939-09-01|6|70000000|Global|history@archive.org|UNESCO

WAR002|World War I|EUROPE|1914-07-28|4|40000000|Global|recordsarchive.org|HistoryNet

WAR003|Napoleonic Wars| europe |1803-05-18|12|3500000|NULL|napoleon@history.org|Museum

WAR004|American Civil War|North America|1861-04-12|4|-750000|Civil|civilwar@archive.org|Library

WAR005|Thirty Years War|Europe|1618/05/23|30|8000000|Religious|history@wars.org|NULL

WAR006|Crimean War|Europe| |3|650000|Regional|crimea@archive|HistoryNet

WAR007|Vietnam War|Asia|1955-11-01|20|NULL|Proxy|vietnam@history.org|Archive

WAR008|Korean War|ASIA|1950-06-25|-3|1200000|Regional|korea@history.org|Archive

WAR009|Peloponnesian War|Europe|431 BC|27|100000|Ancient|ancient@history.org|Museum

WAR010|Gulf War|Middle East|1990-08-02|1|25000A|Regional|gulf@history.org|ResearchLab

WAR011|War of 1812|north america|1812-06-18|3|20000|NULL|war1812history.org|Library

WAR012|Franco-Prussian War|Europe|1870-07-19|1|180000|Regional| franco@history.org |Archive

;

run;

proc print data=wars_raw;

run;

OUTPUT:

Obs	war_id	war_name	continent	start_date_raw	est_casualties	war_type	source_email	data_provider	duration_years
1	WAR001	World War II	Europe	1939-09-01	70000000	Global	history@archive.org	UNESCO	6
2	WAR002	World War I	EUROPE	1914-07-28	40000000	Global	recordsarchive.org	HistoryNet	4
3	WAR003	Napoleonic Wars	europe	1803-05-18	3500000	NULL	napoleon@history.org	Museum	12
4	WAR004	American Civil War	North America	1861-04-12	-750000	Civil	civilwar@archive.org	Library	4
5	WAR005	Thirty Years War	Europe	1618/05/23	8000000	Religious	history@wars.org	NULL	30
6	WAR006	Crimean War	Europe		650000	Regional	crimea@archive	HistoryNet	3
7	WAR007	Vietnam War	Asia	1955-11-01	NULL	Proxy	vietnam@history.org	Archive	20
8	WAR008	Korean War	ASIA	1950-06-25	1200000	Regional	korea@history.org	Archive	-3
9	WAR009	Peloponnesian War	Europe	431 BC	100000	Ancient	ancient@history.org	Museum	27
10	WAR010	Gulf War	Middle East	1990-08-02	25000A	Regional	gulf@history.org	ResearchLab	1
11	WAR011	War of 1812	north america	1812-06-18	20000	NULL	war1812history.org	Library	3
12	WAR012	Franco-Prussian War	Europe	1870-07-19	180000	Regional	franco@history.org	Archive	1

Enterprise Data Quality Issues Introduced (Observations 1–12)

Observation	Intentional Issue	Business Impact
WAR002	Email missing '@'	Invalid contact information
WAR003	Leading/trailing spaces	Failed joins and duplicate categories
WAR003	NULL war type	Missing classification
WAR004	Negative casualties	Impossible analytical values
WAR005	Different date format	Date conversion failure
WAR005	NULL provider	Missing metadata
WAR006	Missing start date	Timeline calculations fail
WAR006	Invalid email domain	Communication failure
WAR007	Character "NULL" casualties	Mixed data types
WAR008	Negative duration	Impossible business rule
WAR009	Non-standard historical date	Invalid date parsing
WAR010	Mixed numeric/character casualties	Numeric conversion failure
WAR011	Mixed case continent	Inconsistent grouping
WAR011	Email missing '@'	Validation failure
WAR012	Leading/trailing blanks	Character matching problems

Why Were These Errors Added?

These are not random mistakes they are modeled after the kinds of problems encountered in enterprise data integration projects. Historical records often come from multiple archives, legacy databases, spreadsheets, OCR scans, and manually entered forms. Each source may follow different conventions for dates, capitalization, missing values, and identifiers.

For example, one provider may record a continent as "Europe", another as "EUROPE", and another as " europe ". Although these values look similar to a human, SAS treats them as different character strings unless they are standardized. Likewise, malformed emails, inconsistent date formats, and mixed character/numeric fields can cause validation failures, reporting errors, and incorrect analytical conclusions.

Explanation

This raw dataset intentionally mirrors the complexity of real production environments. The LENGTH statement is placed before the INPUT statement to ensure character variables receive sufficient storage and avoid truncation. The INFILE statement uses DLM='|' to read pipe-delimited data, while TRUNCOVER and MISSOVER prevent SAS from reading beyond the end of incomplete records. Character and numeric variables are deliberately mixed to demonstrate common enterprise issues such as invalid numeric values, inconsistent text formatting, missing metadata, malformed emails, and heterogeneous date representations. In later sections, we will transform this raw layer into an analysis-ready dataset using robust DATA Step programming, PROC SQL, validation rules, and reusable macros.

Key Interview Points

• Always preserve the raw dataset without modifying it directly.
• Define LENGTH statements before reading or assigning character variables.
• Use TRUNCOVER and MISSOVER to safely read incomplete records.
• Separate raw ingestion from data-cleaning logic.
• Introduce validation flags instead of silently correcting data.
• Treat "NULL" strings differently from true SAS missing values.
• Expect inconsistent formats when integrating data from multiple sources.
• Design raw datasets to be reproducible, auditable, and suitable for downstream quality control.

Excellent. This section introduces the first stage of enterprise data engineering. The goal is not simply to fix bad values, but to build an analysis-ready dataset while preserving traceability and regulatory compliance. This is similar to how Clinical SAS programmers prepare SDTM and ADaM datasets before statistical analysis.

Enterprise SAS DATA Step Cleaning Framework

Transforming the Greatest Wars in World Raw Dataset into an Analysis-Ready Dataset

2. Create Business Formats

Before cleaning begins, enterprise programmers centralize business rules using PROC FORMAT. 

Instead of repeatedly writing IF statements throughout programs, formats make validation reusable, 

readable, and easier to maintain.

proc format;

value durationfmt low-0      = 'Invalid'

                  0<-50      = 'Valid'

                  50<-high   = 'Review';

LOG:

NOTE: Format DURATIONFMT has been output.

value $warfmt 'GLOBAL'      = 'International Conflict'

              'REGIONAL'    = 'Regional Conflict'

              'CIVIL'       = 'Civil Conflict'

              'RELIGIOUS'   = 'Religious Conflict'

              'PROXY'       = 'Proxy Conflict'

              'ANCIENT'     = 'Ancient Conflict'

              other         = 'Unknown';

run;

LOG:

NOTE: Format $WARFMT has been output.

Explanation

PROC FORMAT separates business logic from DATA Step programming. Instead of repeatedly checking whether a duration is valid or a conflict type belongs to a known category, SAS can reference centralized rules. This improves maintainability, especially in regulated environments where business definitions frequently change. Clinical programming teams often use formats to standardize treatment groups, laboratory ranges, adverse event severities, and controlled terminology. Updating one format automatically updates every downstream program, reducing duplication and improving consistency across production reporting.

Key Points

·       Business rules should not be hardcoded repeatedly.

·       Formats improve readability.

·       Easy to maintain.

·       Reusable across multiple programs.

·       Widely used in regulated industries.

3. Enterprise Cleaning DATA Step Explanation

PROC FORMAT separates business logic from DATA Step programming. Instead of repeatedly checking whether a duration is valid or a conflict type belongs to a known category, SAS can reference centralized rules. This improves maintainability, especially in regulated environments where business definitions frequently change. Clinical programming teams often use formats to standardize treatment groups, laboratory ranges, adverse event severities, and controlled terminology. Updating one format automatically updates every downstream program, reducing duplication and improving consistency across production reporting.

Key Points

·       Business rules should not be hardcoded repeatedly.

·       Formats improve readability.

·       Easy to maintain.

·       Reusable across multiple programs.

·       Widely used in regulated industries.

data wars_clean;

length email_flag $12 date_flag $12 record_status $15;

set wars_raw;

/*---------------------------------------------------

 Standardize Character Variables

----------------------------------------------------*/

war_name = propcase(strip(war_name));

continent = upcase(strip(continent));

war_type = upcase(strip(war_type));

data_provider = propcase(strip(data_provider));

source_email = lowcase(strip(source_email));

/*---------------------------------------------------

 Replace Character NULL values

----------------------------------------------------*/

war_type = tranwrd(war_type,'NULL','UNKNOWN');

data_provider = tranwrd(data_provider,'NULL','UNKNOWN');

est_casualties = tranwrd(est_casualties,'NULL','');

/*---------------------------------------------------

 Remove Embedded Blanks

----------------------------------------------------*/

continent = compress(continent,' ');

/*---------------------------------------------------

 Standardize Continents

----------------------------------------------------*/

if continent='EUROPE' then continent_std='EUROPE';

else if continent='ASIA' then continent_std='ASIA';

else if continent='NORTHAMERICA'

then continent_std='NORTH AMERICA';

else if continent='MIDDLEEAST'

then continent_std='MIDDLE EAST';

else continent_std='OTHER';

/*---------------------------------------------------

 Convert Character Casualties

----------------------------------------------------*/

casualties=input(est_casualties,?? comma20.);

/*---------------------------------------------------

 Negative Values

----------------------------------------------------*/

casualties=abs(casualties);

duration_years=abs(duration_years);

/*---------------------------------------------------

 Convert Character Date

----------------------------------------------------*/

start_date=input(start_date_raw,?? anydtdte15.);

format start_date date9.;

drop start_date_raw; 

/*---------------------------------------------------

 Date Validation

----------------------------------------------------*/

if missing(start_date) then date_flag='INVALID';

else date_flag='VALID';

/*---------------------------------------------------

 Email Validation

----------------------------------------------------*/

if index(source_email,'@')>0 then email_flag='VALID';

else email_flag='INVALID';

/*---------------------------------------------------

 Missing Value Assessment

----------------------------------------------------*/

missing_total=cmiss(war_id,war_name,continent,war_type,

source_email,data_provider)+

nmiss(duration_years,casualties);

/*---------------------------------------------------

 Record Classification

----------------------------------------------------*/

if missing_total>0 then record_status='REVIEW';

else record_status='READY';

run;

proc print data=wars_clean;

run;

OUTPUT:

Obs	email_flag	date_flag	record_status	war_id	war_name	continent	est_casualties	war_type	source_email	data_provider	duration_years	continent_std	casualties	start_date	missing_total
1	VALID	VALID	READY	WAR001	World War Ii	EUROPE	70000000	GLOBAL	history@archive.org	Unesco	6	EUROPE	70000000	01SEP1939	0
2	INVALID	VALID	READY	WAR002	World War I	EUROPE	40000000	GLOBAL	recordsarchive.org	Historynet	4	EUROPE	40000000	28JUL1914	0
3	VALID	VALID	READY	WAR003	Napoleonic Wars	EUROPE	3500000	UNKNOWN	napoleon@history.org	Museum	12	EUROPE	3500000	18MAY1803	0
4	VALID	VALID	READY	WAR004	American Civil War	NORTHAMERICA	-750000	CIVIL	civilwar@archive.org	Library	4	NORTH	750000	12APR1861	0
5	VALID	VALID	READY	WAR005	Thirty Years War	EUROPE	8000000	RELIGIOUS	history@wars.org	Null	30	EUROPE	8000000	23MAY1618	0
6	VALID	INVALID	READY	WAR006	Crimean War	EUROPE	650000	REGIONAL	crimea@archive	Historynet	3	EUROPE	650000	.	0
7	VALID	VALID	REVIEW	WAR007	Vietnam War	ASIA		PROXY	vietnam@history.org	Archive	20	ASIA	.	01NOV1955	1
8	VALID	VALID	READY	WAR008	Korean War	ASIA	1200000	REGIONAL	korea@history.org	Archive	3	ASIA	1200000	25JUN1950	0
9	VALID	INVALID	READY	WAR009	Peloponnesian War	EUROPE	100000	ANCIENT	ancient@history.org	Museum	27	EUROPE	100000	.	0
10	VALID	VALID	REVIEW	WAR010	Gulf War	MIDDLEEAST	25000A	REGIONAL	gulf@history.org	Researchlab	1	MIDDLE	.	02AUG1990	1
11	INVALID	VALID	READY	WAR011	War Of 1812	NORTHAMERICA	20000	UNKNOWN	war1812history.org	Library	3	NORTH	20000	18JUN1812	0
12	VALID	VALID	READY	WAR012	Franco-Prussian War	EUROPE	180000	REGIONAL	franco@history.org	Archive	1	EUROPE	180000	19JUL1870	0

Explanation

This DATA Step demonstrates a production-style cleaning pipeline similar to those used in pharmaceutical, banking, insurance, and retail environments. Character variables are standardized using PROPCASE, UPCASE, LOWCASE, and STRIP to eliminate inconsistencies caused by user entry and legacy systems. TRANWRD converts placeholder "NULL" strings into meaningful values, while COMPRESS removes embedded blanks that commonly break joins. Numeric corrections are handled using INPUT and ABS, ensuring character-based numbers become analyzable and negative values are normalized according to business rules. Dates are converted with the flexible ANYDTDTE. informat, making the program resilient to multiple date formats. Validation flags (email_flag, date_flag, record_status) preserve traceability by identifying problematic records instead of silently discarding them. This defensive programming style is a hallmark of enterprise SAS development and supports reproducible, audit-ready analytical workflows.

Enterprise Functions Used

SAS Function	Purpose
PROPCASE()	Standardizes war names
UPCASE()	Makes comparisons consistent
LOWCASE()	Normalizes email addresses
STRIP()	Removes leading/trailing blanks
COMPRESS()	Removes embedded spaces
TRANWRD()	Replaces "NULL" text
INPUT()	Converts character to numeric/date
ABS()	Corrects negative numeric values
INDEX()	Checks for @ in email
CMISS()	Counts missing character values
NMISS()	Counts missing numeric values
ANYDTDTE.	Reads multiple date formats

Why We Don't Delete Bad Records

A common mistake among beginners is deleting invalid observations immediately:

if missing(start_date) then delete;

In enterprise projects, this is discouraged because it destroys traceability. Instead, we flag records for review:

if missing(start_date) then date_flag='INVALID';

This preserves the original observation, allows quality control teams to investigate the issue, and supports audit requirements. In regulated industries such as clinical research, removing records without documentation can lead to failed validations and compliance concerns.

Enterprise Data Cleaning in R

Cleaning the Greatest Wars in World Dataset Using tidyverse

1. Load Required Packages

library(tidyverse)

library(lubridate)

library(janitor)

library(stringr)

library(tidyr)

library(purrr)

Explanation

Enterprise R projects typically use the tidyverse ecosystem because it provides consistent syntax for data manipulation. dplyr handles transformations, stringr standardizes character data, lubridate parses inconsistent dates, tidyr manages missing values, and janitor cleans variable names. Using these packages together creates readable, maintainable code similar to enterprise SAS programs.

2. Create the Raw Dataset

wars_raw <- tibble(

  war_id=c("WAR001","WAR002","WAR003","WAR004","WAR005","WAR006",

    "WAR007","WAR008","WAR009","WAR010","WAR011","WAR012"),

  war_name=c("World War II","World War I"," Napoleonic Wars ",

    "American Civil War","Thirty Years War","Crimean War",

    "Vietnam War","Korean War","Peloponnesian War","Gulf War",

    "War of 1812","Franco-Prussian War"),

  continent=c("Europe","EUROPE"," europe ","North America","Europe",

    "Europe","Asia","ASIA","Europe","Middle East","north america",

    "Europe"),

  start_date_raw=c("1939-09-01","1914-07-28","1803-05-18","1861-04-12",

    "1618/05/23","","1955-11-01","1950-06-25","431 BC","1990-08-02",

    "1812-06-18","1870-07-19"),

  duration_years=c(6,4,12,4,30,3,20,-3,27,1,3,1),

  est_casualties=c("70000000","40000000","3500000","-750000",

    "8000000","650000","NULL","1200000","100000","25000A","20000",

    "180000"),

  war_type=c("Global","Global","NULL","Civil","Religious","Regional",

    "Proxy","Regional","Ancient","Regional","NULL","Regional"),

  source_email=c("history@archive.org","recordsarchive.org",

  "napoleon@history.org","civilwar@archive.org",

  "history@wars.org","crimea@archive","vietnam@history.org",

  "korea@history.org","ancient@history.org","gulf@history.org",

  "war1812history.org"," franco@history.org "),

  data_provider=c("UNESCO","HistoryNet","Museum","Library","NULL",

  "HistoryNet","Archive","Archive","Museum","ResearchLab","Library",

  "Archive"))

OUTPUT:

war_id	war_name	continent	start_date_raw	duration_years	est_casualties	war_type	source_email	data_provider
WAR001	World War II	Europe	1939-09-01	6	70000000	Global	history@archive.org	UNESCO
WAR002	World War I	EUROPE	1914-07-28	4	40000000	Global	recordsarchive.org	HistoryNet
WAR003	Napoleonic Wars	europe	1803-05-18	12	3500000	NULL	napoleon@history.org	Museum
WAR004	American Civil War	North America	1861-04-12	4	-750000	Civil	civilwar@archive.org	Library
WAR005	Thirty Years War	Europe	1618/05/23	30	8000000	Religious	history@wars.org	NULL
WAR006	Crimean War	Europe		3	650000	Regional	crimea@archive	HistoryNet
WAR007	Vietnam War	Asia	1955-11-01	20	NULL	Proxy	vietnam@history.org	Archive
WAR008	Korean War	ASIA	1950-06-25	-3	1200000	Regional	korea@history.org	Archive
WAR009	Peloponnesian War	Europe	431 BC	27	100000	Ancient	ancient@history.org	Museum
WAR010	Gulf War	Middle East	1990-08-02	1	25000A	Regional	gulf@history.org	ResearchLab
WAR011	War of 1812	north america	1812-06-18	3	20000	NULL	war1812history.org	Library
WAR012	Franco-Prussian War	Europe	1870-07-19	1	180000	Regional	franco@history.org	Archive

Explanation

This dataset intentionally contains enterprise-style quality issues. Character variables have inconsistent capitalization and extra spaces, some fields contain "NULL" instead of real missing values, casualty values mix numbers and text, dates use multiple formats, and emails are malformed. These issues reflect problems commonly encountered when integrating data from multiple legacy systems, spreadsheets, or external providers. Keeping these errors in the raw layer allows data engineers to test validation rules and create robust cleaning pipelines before producing analysis-ready datasets.

3. Enterprise Cleaning Workflow

options(scipen = 999)

wars_clean <-

  wars_raw %>%

  clean_names() %>%

  mutate(

    war_name =str_to_title(str_trim(war_name)),

    continent =str_to_upper(str_trim(continent)),

    war_type =str_to_upper(str_trim(war_type)),

    data_provider =str_to_title(str_trim(data_provider)),

    source_email =str_to_lower(str_trim(source_email)),

    war_type =str_replace_all(war_type,"NULL","UNKNOWN"),

    data_provider =str_replace_all(data_provider,"NULL",

                      "UNKNOWN"),

    est_casualties =na_if(est_casualties,"NULL"),

    casualties =readr::parse_number(est_casualties),

    casualties =abs(casualties),

    duration_years =abs(duration_years),

    continent_std =case_when(

        continent=="EUROPE"

        ~ "EUROPE",

        continent=="ASIA"

        ~ "ASIA",

        continent=="NORTH AMERICA"

        ~ "NORTH AMERICA",

        continent=="MIDDLE EAST"

        ~ "MIDDLE EAST",TRUE

        ~ "OTHER"),

    start_date =suppressWarnings(parse_date_time(

        start_date_raw,orders=c("ymd","Y/m/d"))),

    email_flag =if_else(grepl("@",source_email),

        "VALID","INVALID"),

    date_flag =if_else(is.na(start_date),

        "INVALID","VALID")

  ) %>%

  replace_na(list(war_type="UNKNOWN",

      data_provider="UNKNOWN")

  ) %>%

  mutate(

    missing_total = rowSums(

      is.na(

        select(., start_date, casualties)

      )

    )

  )

 OUTPUT:

war_id

war_name

continent

start_date_raw

duration_years

est_casualties

war_type

source_email

data_provider

casualties

continent_std

start_date

email_flag

date_flag

missing_total

WAR001

World War Ii

EUROPE

01-09-1939

6

70000000

GLOBAL

history@archive.org

Unesco

70000000

EUROPE

1939-09-01T00:00:00Z

VALID

VALID

0

WAR002

World War I

EUROPE

28-07-1914

4

40000000

GLOBAL

recordsarchive.org

Historynet

40000000

EUROPE

1914-07-28T00:00:00Z

INVALID

VALID

0

WAR003

Napoleonic Wars

EUROPE

1803-05-18

12

3500000

UNKNOWN

napoleon@history.org

Museum

3500000

EUROPE

1803-05-18T00:00:00Z

VALID

VALID

0

WAR004

American Civil War

NORTH AMERICA

1861-04-12

4

-750000

CIVIL

civilwar@archive.org

Library

750000

NORTH AMERICA

1861-04-12T00:00:00Z

VALID

VALID

0

WAR005

Thirty Years War

EUROPE

1618/05/23

30

8000000

RELIGIOUS

history@wars.org

Null

8000000

EUROPE

1618-05-23T00:00:00Z

VALID

VALID

0

WAR006

Crimean War

EUROPE

3

650000

REGIONAL

crimea@archive

Historynet

650000

EUROPE

NA

VALID

INVALID

1

WAR007

Vietnam War

ASIA

01-11-1955

20

NA

PROXY

vietnam@history.org

Archive

NA

ASIA

1955-11-01T00:00:00Z

VALID

VALID

1

WAR008

Korean War

ASIA

25-06-1950

3

1200000

REGIONAL

korea@history.org

Archive

1200000

ASIA

1950-06-25T00:00:00Z

VALID

VALID

0

WAR009

Peloponnesian War

EUROPE

431 BC

27

100000

ANCIENT

ancient@history.org

Museum

100000

EUROPE

NA

VALID

INVALID

1

WAR010

Gulf War

MIDDLE EAST

02-08-1990

1

25000A

REGIONAL

gulf@history.org

Researchlab

25000

MIDDLE EAST

1990-08-02T00:00:00Z

VALID

VALID

0

WAR011

War Of 1812

NORTH AMERICA

1812-06-18

3

20000

UNKNOWN

war1812history.org

Library

20000

NORTH AMERICA

1812-06-18T00:00:00Z

INVALID

VALID

0

WAR012

Franco-Prussian War

EUROPE

1870-07-19

1

180000

REGIONAL

franco@history.org

Archive

180000

EUROPE

1870-07-19T00:00:00Z

VALID

VALID

0

Explanation

The cleaning workflow follows the same philosophy as the SAS DATA Step. Character values are standardized with str_trim(), str_to_upper(), and str_to_title() to remove inconsistencies. Placeholder "NULL" values are replaced with "UNKNOWN" or converted to actual missing values using na_if(). parse_number() safely extracts numeric values from mixed text, while abs() corrects negative numbers based on business rules. parse_date_time() handles multiple date formats, and validation flags identify records with invalid emails or dates. Instead of deleting problematic observations, the workflow marks them for review, preserving traceability and supporting enterprise-quality data governance.

4. Quick Data Validation

wars_clean %>%

  summarise(

    total_records = n(),

    invalid_dates = sum(date_flag=="INVALID"),

    invalid_emails = sum(email_flag=="INVALID"),

    missing_casualties = sum(is.na(casualties)),

    average_duration = mean(duration_years,

           na.rm=TRUE))

OUTPUT:

 total_records invalid_dates invalid_emails missing_casualties average_duration
          <int>         <int>          <int>              <int>            <dbl>
1            12             2              2                  1              9.5

Explanation

This validation summary provides an immediate quality snapshot after cleaning. Analysts can quickly see the number of invalid dates, malformed emails, missing casualty values, and average conflict duration. Similar validation reports are commonly generated in enterprise ETL pipelines and clinical programming to verify that data quality has improved before downstream analysis or reporting.

SAS vs R Comparison

SAS	R
PROPCASE()	str_to_title()
STRIP()	str_trim()
UPCASE()	str_to_upper()
LOWCASE()	str_to_lower()
TRANWRD()	str_replace_all()
INPUT()	parse_number() / parse_date_time()
IF-THEN/ELSE	if_else()
SELECT-WHEN	case_when()
CMISS()	is.na()
PROC SUMMARY	summarise()

Summary

Enterprise data cleaning is far more than correcting spelling mistakes or removing duplicates. It is a structured process that ensures data is accurate, consistent, and suitable for analysis. In this example, R's tidyverse packages provided a clean and readable framework for standardizing text, correcting numeric values, parsing dates, handling missing information, and creating validation flags. The workflow closely mirrors the SAS DATA Step, demonstrating that both languages can produce analysis-ready datasets while preserving data lineage and traceability.

Conclusion

Reliable analytics begins with reliable data. Whether working with historical records, clinical trials, banking transactions, or insurance claims, poor-quality data can lead to misleading reports, failed models, and incorrect business decisions. By building a disciplined cleaning workflow in R, we transformed a deliberately corrupted dataset into a standardized, analysis-ready resource. The use of tidyverse functions, validation checks, and clear business rules makes the code maintainable and reproducible. When combined with SAS in enterprise environments, R provides flexibility for exploratory analysis while SAS offers strong governance and regulatory support, creating a powerful foundation for trustworthy business intelligence.

Interview Questions and Answers

1. Why use str_trim() before standardizing text?

Answer: Leading and trailing spaces can cause duplicate categories and failed joins. str_trim() removes these spaces, ensuring consistent comparisons and accurate grouping.

2. Why convert "NULL" strings using na_if() instead of leaving them as text?

Answer: "NULL" is a character string, not a missing value. Converting it to NA allows R functions such as is.na(), replace_na(), and summarise() to treat it correctly during analysis.

3. Why use parse_number() instead of as.numeric()?

Answer: parse_number() extracts numeric values from strings containing non-numeric characters (e.g., "25000A" becomes 25000), whereas as.numeric() would return NA with a warning.

4. Why create validation flags instead of deleting invalid records?

Answer: Validation flags preserve traceability and allow quality assurance teams to review problematic records. This approach supports auditing and regulatory compliance while avoiding accidental data loss.

5. How does the R cleaning workflow compare with the SAS DATA Step?

Answer: Both follow the same principles: standardize text, convert data types, validate records, handle missing values, and create analysis-ready datasets. SAS emphasizes regulatory traceability and enterprise governance, while R provides concise, flexible syntax and a rich ecosystem for data manipulation and analytics.

Interview Tips

·       Always keep the raw dataset unchanged.

·       Create a separate cleaned dataset rather than overwriting the source.

·       Use validation flags instead of deleting records.

·       Prefer ANYDTDTE. when incoming date formats are inconsistent.

·       Standardize text before performing joins or comparisons.

·       Convert character numbers with INPUT() before analysis.

·       Use CMISS() and NMISS() to assess completeness efficiently.

·       Centralize business rules with PROC FORMAT for maintainability.

·       Write defensive code that is robust to unexpected input.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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 WAR 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:
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