Transforming Famous Ice Cream Datasets into Production-Grade Analytics Using SAS and R

-

Frozen Dreams, Corrupted Rows Famous Ice Cream Intelligence Using SAS PROC SQL, DATA Step, and Modern R Pipelines

Introduction

In enterprise analytics, bad data rarely announces itself loudly. It quietly destroys dashboards, regulatory outputs, AI models, operational reports, and executive trust. One corrupted field inside a production dataset can trigger millions of dollars in losses, delayed submissions, or incorrect strategic decisions.

Imagine a multinational frozen-dessert company conducting global customer behavior analysis across premium ice cream brands. Their operational datasets contain famous ice creams from multiple countries including Italy, India, the United States, Turkey, Japan, and France. The company wants to understand regional demand patterns, sugar preferences, sales performance, allergy categories, and manufacturing quality metrics.

Unfortunately, the raw production dataset is severely corrupted.

Duplicate IceCream IDs exist.
Negative revenue values appear in financial columns.
Ages are unrealistic.
Email addresses are malformed.
Manufacturing dates are invalid.
Category labels contain inconsistent capitalization.
Whitespace corruption affects region codes.
Missing timestamps break reporting pipelines.
Mixed character/numeric variables crash validation macros.

The consequences become dangerous:

  • AI demand forecasts become unreliable
  • Executive dashboards show incorrect regional profitability
  • Clinical nutrition studies using frozen dairy consumption become statistically invalid
  • Regulatory reporting fails QC checks
  • Insurance risk models misclassify allergy-sensitive consumers
  • Machine learning pipelines propagate corrupted metadata

This is where enterprise-grade SAS and R data engineering becomes critical.

Raw Dataset Creation in SAS

Raw Corrupted Dataset with Intentional Errors

data icecream_raw;

length IceCream_ID $8 Brand $25 Country $20 Flavor $20

       Customer_Email $50 Region_Code $10 Category $15;

informat Manufacture_Date anydtdte20.;

format Manufacture_Date date9.;

infile datalines dlm='|' truncover;

input IceCream_ID $ Brand $ Country $ Flavor $ Customer_Age 

Revenue Manufacture_Date Customer_Email $ Region_Code $

Category $;

datalines;

IC001|GelatoRoma|Italy|Vanilla|25|4500|12JAN2025|abc@gmail.com| eu01 |Premium

IC001|gelatoroma|ITALY|vanilla|25|-4500|31FEB2025|wrongmail.com|EU01|premium

IC002|Amul|India|Mango|150|3200|15MAR2025|amul@ice.com|in02|Regular

IC003|BaskinRobbins|USA|Chocolate|-5|5500|.|baskin@gmail|US01|Luxury

IC004|Cornetto|France|Strawberry|32|.|11APR2025|cornetto@yahoo.com| FR03|regular

IC005|NULL|Turkey|Pistachio|41|7000|15MAY2025|turkmail.com|TR01|Premium

IC006|HaagenDazs|USA|Cookie|29|6500|14JUN2025|haagen@gmail.com|us01|Luxury

IC007|KwalityWalls|India|Kulfi|38|-800|17JUL2025|kwality@gmail.com|IN-01|Regular

IC008|Movenpick|Switzerland|Coffee|999|9200|18AUG2025|move@gmail.com|SW01|Premium

IC009|Ben&Jerrys|USA|Brownie|44|8700|19SEP2025|ben@icecream.com|US 01|Luxury

IC010|Magnum|Belgium|Almond|33|7800|INVALID|magnum@gmail.com|BE01|Premium

IC011|Yili|China|Matcha|27|4500|25OCT2025|yili@@gmail.com|CN01|Regular

IC012|Walls|India|Chocolate|.|5000|12NOV2025|walls@gmail.com|IN01|Regular

IC013|Nirulas|India|Butterscotch|45|6500|15DEC2025|nirulasgmail.com|IN01|Premium

IC014|BlueBell|USA|Mint|29|7200|18JAN2025|blue@gmail.com|US01|Luxury

IC015|Grom|Italy|Hazelnut|31|8100|20FEB2025|grom@gmail.com|EU01|Premium

IC016|ColdStone|USA|Cheesecake|36|9100|22MAR2025|coldstone@gmail.com|US02|Luxury

IC017|Natural's|India|TenderCoconut|28|6200|24APR2025|natural@gmail.com|IN03|Premium

IC018|DairyDay|India|Vanilla|42|5800|26MAY2025|dairy@gmail.com|IN04|Regular

IC019|CarteDor|France|Caramel|35|7700|28JUN2025|carte@gmail.com|FR01|Luxury

IC020|TokyoCream|Japan|Matcha|30|8900|30JUL2025|tokyo@gmail.com|JP01|Premium

;

run;

proc print data = icecream_raw;

run;

OUTPUT:

ObsIceCream_IDBrandCountryFlavorCustomer_EmailRegion_CodeCategoryManufacture_DateCustomer_AgeRevenue
1IC001GelatoRomaItalyVanillaabc@gmail.comeu01Premium12JAN2025254500
2IC001gelatoromaITALYvanillawrongmail.comEU01premium.25-4500
3IC002AmulIndiaMangoamul@ice.comin02Regular15MAR20251503200
4IC003BaskinRobbinsUSAChocolatebaskin@gmailUS01Luxury.-55500
5IC004CornettoFranceStrawberrycornetto@yahoo.comFR03regular11APR202532.
6IC005NULLTurkeyPistachioturkmail.comTR01Premium15MAY2025417000
7IC006HaagenDazsUSACookiehaagen@gmail.comus01Luxury14JUN2025296500
8IC007KwalityWallsIndiaKulfikwality@gmail.comIN-01Regular17JUL202538-800
9IC008MovenpickSwitzerlandCoffeemove@gmail.comSW01Premium18AUG20259999200
10IC009Ben&JerrysUSABrownieben@icecream.comUS 01Luxury19SEP2025448700
11IC010MagnumBelgiumAlmondmagnum@gmail.comBE01Premium.337800
12IC011YiliChinaMatchayili@@gmail.comCN01Regular25OCT2025274500
13IC012WallsIndiaChocolatewalls@gmail.comIN01Regular12NOV2025.5000
14IC013NirulasIndiaButterscotchnirulasgmail.comIN01Premium15DEC2025456500
15IC014BlueBellUSAMintblue@gmail.comUS01Luxury18JAN2025297200
16IC015GromItalyHazelnutgrom@gmail.comEU01Premium20FEB2025318100
17IC016ColdStoneUSACheesecakecoldstone@gmail.comUS02Luxury22MAR2025369100
18IC017Natural'sIndiaTenderCoconutnatural@gmail.comIN03Premium24APR2025286200
19IC018DairyDayIndiaVanilladairy@gmail.comIN04Regular26MAY2025425800
20IC019CarteDorFranceCaramelcarte@gmail.comFR01Luxury28JUN2025357700
21IC020TokyoCreamJapanMatchatokyo@gmail.comJP01Premium30JUL2025308900

Why LENGTH Statements Matter in SAS

One of the most misunderstood production risks in SAS is character truncation.

If LENGTH statements are declared after assignments, SAS automatically allocates variable length based on first encounter.

Example:

data test;

name='ChocolateBrownieSupreme';

length name $10;

run;

Result:

ChocolateB

The string becomes truncated permanently.

In enterprise clinical environments, truncation can destroy:

  • SDTM variable integrity
  • Medication names
  • Region mappings
  • Audit traceability
  • Regulatory consistency

R behaves differently because character vectors dynamically allocate memory. SAS requires proactive metadata governance.

SAS Cleaning Workflow

1.Standardization Layer

data icecream_clean1;

retain Data_Source "GLOBAL_ICECREAM_SYSTEM";

set icecream_raw;

Brand = propcase(strip(Brand));

Country = upcase(strip(Country));

Flavor = propcase(compbl(Flavor));

Region_Code = compress(upcase(Region_Code));

Category = propcase(Category);

Customer_Email = lowcase(strip(Customer_Email));

Revenue = abs(Revenue);

if Customer_Age < 1 or Customer_Age > 100 then

Customer_Age = .;

if find(Customer_Email,'@')=0 then

Customer_Email='invalid@email.com';

if Manufacture_Date=. then

Manufacture_Date=intnx('month',today(),-1,'same');

Revenue=round(Revenue,.01);

run;

proc print data = icecream_clean1;

run;

OUTPUT:

ObsData_SourceIceCream_IDBrandCountryFlavorCustomer_EmailRegion_CodeCategoryManufacture_DateCustomer_AgeRevenue
1GLOBAL_ICECREAM_SYSTEMIC001GelatoromaITALYVanillaabc@gmail.comEU01Premium12JAN2025254500
2GLOBAL_ICECREAM_SYSTEMIC001GelatoromaITALYVanillainvalid@email.comEU01Premium04MAY2026254500
3GLOBAL_ICECREAM_SYSTEMIC002AmulINDIAMangoamul@ice.comIN02Regular15MAR2025.3200
4GLOBAL_ICECREAM_SYSTEMIC003BaskinrobbinsUSAChocolatebaskin@gmailUS01Luxury04MAY2026.5500
5GLOBAL_ICECREAM_SYSTEMIC004CornettoFRANCEStrawberrycornetto@yahoo.comFR03Regular11APR202532.
6GLOBAL_ICECREAM_SYSTEMIC005NullTURKEYPistachioinvalid@email.comTR01Premium15MAY2025417000
7GLOBAL_ICECREAM_SYSTEMIC006HaagendazsUSACookiehaagen@gmail.comUS01Luxury14JUN2025296500
8GLOBAL_ICECREAM_SYSTEMIC007KwalitywallsINDIAKulfikwality@gmail.comIN-01Regular17JUL202538800
9GLOBAL_ICECREAM_SYSTEMIC008MovenpickSWITZERLANDCoffeemove@gmail.comSW01Premium18AUG2025.9200
10GLOBAL_ICECREAM_SYSTEMIC009Ben&jerrysUSABrownieben@icecream.comUS01Luxury19SEP2025448700
11GLOBAL_ICECREAM_SYSTEMIC010MagnumBELGIUMAlmondmagnum@gmail.comBE01Premium04MAY2026337800
12GLOBAL_ICECREAM_SYSTEMIC011YiliCHINAMatchayili@@gmail.comCN01Regular25OCT2025274500
13GLOBAL_ICECREAM_SYSTEMIC012WallsINDIAChocolatewalls@gmail.comIN01Regular12NOV2025.5000
14GLOBAL_ICECREAM_SYSTEMIC013NirulasINDIAButterscotchinvalid@email.comIN01Premium15DEC2025456500
15GLOBAL_ICECREAM_SYSTEMIC014BluebellUSAMintblue@gmail.comUS01Luxury18JAN2025297200
16GLOBAL_ICECREAM_SYSTEMIC015GromITALYHazelnutgrom@gmail.comEU01Premium20FEB2025318100
17GLOBAL_ICECREAM_SYSTEMIC016ColdstoneUSACheesecakecoldstone@gmail.comUS02Luxury22MAR2025369100
18GLOBAL_ICECREAM_SYSTEMIC017Natural'sINDIATendercoconutnatural@gmail.comIN03Premium24APR2025286200
19GLOBAL_ICECREAM_SYSTEMIC018DairydayINDIAVanilladairy@gmail.comIN04Regular26MAY2025425800
20GLOBAL_ICECREAM_SYSTEMIC019CartedorFRANCECaramelcarte@gmail.comFR01Luxury28JUN2025357700
21GLOBAL_ICECREAM_SYSTEMIC020TokyocreamJAPANMatchatokyo@gmail.comJP01Premium30JUL2025308900

Explanation

This DATA step performs enterprise normalization and corruption repair. PROPCASE, UPCASE, and LOWCASE standardize inconsistent text formatting. COMPRESS removes whitespace corruption from region codes. ABS converts negative revenue into valid business values. Invalid ages are converted to missing for downstream imputation logic. FIND detects malformed email structures. INTNX imputes missing dates intelligently. ROUND ensures financial precision consistency. The RETAIN statement preserves audit lineage metadata across rows. This mirrors production-grade SDTM cleaning pipelines used in regulated environments.

2.Deduplication Using PROC SORT NODUPKEY

proc sort data=icecream_clean1

out=icecream_nodup nodupkey;

by IceCream_ID;

run;

proc print data = icecream_nodup;

run;

OUTPUT:

ObsData_SourceIceCream_IDBrandCountryFlavorCustomer_EmailRegion_CodeCategoryManufacture_DateCustomer_AgeRevenue
1GLOBAL_ICECREAM_SYSTEMIC001GelatoromaITALYVanillaabc@gmail.comEU01Premium12JAN2025254500
2GLOBAL_ICECREAM_SYSTEMIC002AmulINDIAMangoamul@ice.comIN02Regular15MAR2025.3200
3GLOBAL_ICECREAM_SYSTEMIC003BaskinrobbinsUSAChocolatebaskin@gmailUS01Luxury04MAY2026.5500
4GLOBAL_ICECREAM_SYSTEMIC004CornettoFRANCEStrawberrycornetto@yahoo.comFR03Regular11APR202532.
5GLOBAL_ICECREAM_SYSTEMIC005NullTURKEYPistachioinvalid@email.comTR01Premium15MAY2025417000
6GLOBAL_ICECREAM_SYSTEMIC006HaagendazsUSACookiehaagen@gmail.comUS01Luxury14JUN2025296500
7GLOBAL_ICECREAM_SYSTEMIC007KwalitywallsINDIAKulfikwality@gmail.comIN-01Regular17JUL202538800
8GLOBAL_ICECREAM_SYSTEMIC008MovenpickSWITZERLANDCoffeemove@gmail.comSW01Premium18AUG2025.9200
9GLOBAL_ICECREAM_SYSTEMIC009Ben&jerrysUSABrownieben@icecream.comUS01Luxury19SEP2025448700
10GLOBAL_ICECREAM_SYSTEMIC010MagnumBELGIUMAlmondmagnum@gmail.comBE01Premium04MAY2026337800
11GLOBAL_ICECREAM_SYSTEMIC011YiliCHINAMatchayili@@gmail.comCN01Regular25OCT2025274500
12GLOBAL_ICECREAM_SYSTEMIC012WallsINDIAChocolatewalls@gmail.comIN01Regular12NOV2025.5000
13GLOBAL_ICECREAM_SYSTEMIC013NirulasINDIAButterscotchinvalid@email.comIN01Premium15DEC2025456500
14GLOBAL_ICECREAM_SYSTEMIC014BluebellUSAMintblue@gmail.comUS01Luxury18JAN2025297200
15GLOBAL_ICECREAM_SYSTEMIC015GromITALYHazelnutgrom@gmail.comEU01Premium20FEB2025318100
16GLOBAL_ICECREAM_SYSTEMIC016ColdstoneUSACheesecakecoldstone@gmail.comUS02Luxury22MAR2025369100
17GLOBAL_ICECREAM_SYSTEMIC017Natural'sINDIATendercoconutnatural@gmail.comIN03Premium24APR2025286200
18GLOBAL_ICECREAM_SYSTEMIC018DairydayINDIAVanilladairy@gmail.comIN04Regular26MAY2025425800
19GLOBAL_ICECREAM_SYSTEMIC019CartedorFRANCECaramelcarte@gmail.comFR01Luxury28JUN2025357700
20GLOBAL_ICECREAM_SYSTEMIC020TokyocreamJAPANMatchatokyo@gmail.comJP01Premium30JUL2025308900

Explanation

Duplicate records are dangerous because they inflate revenue totals, distort AI training data, and corrupt statistical calculations. PROC SORT NODUPKEY removes duplicate IceCream IDs while preserving the first valid occurrence. In enterprise clinical trials, duplicate patient IDs can create catastrophic submission failures. Deduplication must always occur before aggregation or reporting logic.

3.Validation Using PROC FORMAT

proc format;

value agecheck low-0='INVALID'

               1-100='VALID'

            101-high='INVALID';

run;

LOG:

NOTE: Format AGECHECK has been output.

Explanation

PROC FORMAT creates reusable validation frameworks. Instead of repeatedly hardcoding validation logic, formats centralize governance rules. This improves maintainability, traceability, and QC consistency. Enterprise organizations often maintain centralized validation libraries shared across SDTM, ADaM, and operational reporting pipelines.

4.Advanced DATA Step with ARRAYS and DO Loops

data validation_flags;

set icecream_nodup;

array chars(*) Brand Country Flavor Category;

do i=1 to dim(chars);

chars(i)=strip(chars(i));

end;

Missing_Count = cmiss(of _all_);

format Customer_Age agecheck.;

run;

proc print data = validation_flags;

run;

OUTPUT:

ObsData_SourceIceCream_IDBrandCountryFlavorCustomer_EmailRegion_CodeCategoryManufacture_DateCustomer_AgeRevenueiMissing_Count
1GLOBAL_ICECREAM_SYSTEMIC001GelatoromaITALYVanillaabc@gmail.comEU01Premium12JAN2025VALID450051
2GLOBAL_ICECREAM_SYSTEMIC002AmulINDIAMangoamul@ice.comIN02Regular15MAR2025.320052
3GLOBAL_ICECREAM_SYSTEMIC003BaskinrobbinsUSAChocolatebaskin@gmailUS01Luxury04MAY2026.550052
4GLOBAL_ICECREAM_SYSTEMIC004CornettoFRANCEStrawberrycornetto@yahoo.comFR03Regular11APR2025VALID.52
5GLOBAL_ICECREAM_SYSTEMIC005NullTURKEYPistachioinvalid@email.comTR01Premium15MAY2025VALID700051
6GLOBAL_ICECREAM_SYSTEMIC006HaagendazsUSACookiehaagen@gmail.comUS01Luxury14JUN2025VALID650051
7GLOBAL_ICECREAM_SYSTEMIC007KwalitywallsINDIAKulfikwality@gmail.comIN-01Regular17JUL2025VALID80051
8GLOBAL_ICECREAM_SYSTEMIC008MovenpickSWITZERLANDCoffeemove@gmail.comSW01Premium18AUG2025.920052
9GLOBAL_ICECREAM_SYSTEMIC009Ben&jerrysUSABrownieben@icecream.comUS01Luxury19SEP2025VALID870051
10GLOBAL_ICECREAM_SYSTEMIC010MagnumBELGIUMAlmondmagnum@gmail.comBE01Premium04MAY2026VALID780051
11GLOBAL_ICECREAM_SYSTEMIC011YiliCHINAMatchayili@@gmail.comCN01Regular25OCT2025VALID450051
12GLOBAL_ICECREAM_SYSTEMIC012WallsINDIAChocolatewalls@gmail.comIN01Regular12NOV2025.500052
13GLOBAL_ICECREAM_SYSTEMIC013NirulasINDIAButterscotchinvalid@email.comIN01Premium15DEC2025VALID650051
14GLOBAL_ICECREAM_SYSTEMIC014BluebellUSAMintblue@gmail.comUS01Luxury18JAN2025VALID720051
15GLOBAL_ICECREAM_SYSTEMIC015GromITALYHazelnutgrom@gmail.comEU01Premium20FEB2025VALID810051
16GLOBAL_ICECREAM_SYSTEMIC016ColdstoneUSACheesecakecoldstone@gmail.comUS02Luxury22MAR2025VALID910051
17GLOBAL_ICECREAM_SYSTEMIC017Natural'sINDIATendercoconutnatural@gmail.comIN03Premium24APR2025VALID620051
18GLOBAL_ICECREAM_SYSTEMIC018DairydayINDIAVanilladairy@gmail.comIN04Regular26MAY2025VALID580051
19GLOBAL_ICECREAM_SYSTEMIC019CartedorFRANCECaramelcarte@gmail.comFR01Luxury28JUN2025VALID770051
20GLOBAL_ICECREAM_SYSTEMIC020TokyocreamJAPANMatchatokyo@gmail.comJP01Premium30JUL2025VALID890051

Explanation

ARRAYS allow scalable transformation across multiple variables simultaneously. Instead of repetitive code, loops automate enterprise cleaning logic. CMISS calculates total missing values across mixed variable types. This technique becomes extremely powerful in healthcare or insurance systems containing hundreds of columns.

5.PROC SQL Enterprise Cleaning

proc sql;

create table revenue_summary as

select Country,Category,

count(*) as Total_Records,

mean(Revenue) as Avg_Revenue,

sum(Revenue) as Total_Revenue

from validation_flags

group by Country, Category;

quit;

proc print data = revenue_summary;

run;

OUTPUT:

ObsCountryCategoryTotal_RecordsAvg_RevenueTotal_Revenue
1BELGIUMPremium178007800
2CHINARegular145004500
3FRANCELuxury177007700
4FRANCERegular1..
5INDIAPremium2635012700
6INDIARegular4370014800
7ITALYPremium2630012600
8JAPANPremium189008900
9SWITZERLANDPremium192009200
10TURKEYPremium170007000
11USALuxury5740037000

Explanation

PROC SQL simplifies aggregation and reporting workflows. SQL is especially useful when joining transactional systems, operational marts, and external vendor datasets. In enterprise analytics, SQL logic often powers executive dashboards and clinical reporting environments.

6.FIRST./LAST. Processing Example

proc sort data=validation_flags;

by Country;

run;

proc print data = validation_flags;

run;

OUTPUT:

ObsData_SourceIceCream_IDBrandCountryFlavorCustomer_EmailRegion_CodeCategoryManufacture_DateCustomer_AgeRevenueiMissing_Count
1GLOBAL_ICECREAM_SYSTEMIC010MagnumBELGIUMAlmondmagnum@gmail.comBE01Premium04MAY2026VALID780051
2GLOBAL_ICECREAM_SYSTEMIC011YiliCHINAMatchayili@@gmail.comCN01Regular25OCT2025VALID450051
3GLOBAL_ICECREAM_SYSTEMIC004CornettoFRANCEStrawberrycornetto@yahoo.comFR03Regular11APR2025VALID.52
4GLOBAL_ICECREAM_SYSTEMIC019CartedorFRANCECaramelcarte@gmail.comFR01Luxury28JUN2025VALID770051
5GLOBAL_ICECREAM_SYSTEMIC002AmulINDIAMangoamul@ice.comIN02Regular15MAR2025.320052
6GLOBAL_ICECREAM_SYSTEMIC007KwalitywallsINDIAKulfikwality@gmail.comIN-01Regular17JUL2025VALID80051
7GLOBAL_ICECREAM_SYSTEMIC012WallsINDIAChocolatewalls@gmail.comIN01Regular12NOV2025.500052
8GLOBAL_ICECREAM_SYSTEMIC013NirulasINDIAButterscotchinvalid@email.comIN01Premium15DEC2025VALID650051
9GLOBAL_ICECREAM_SYSTEMIC017Natural'sINDIATendercoconutnatural@gmail.comIN03Premium24APR2025VALID620051
10GLOBAL_ICECREAM_SYSTEMIC018DairydayINDIAVanilladairy@gmail.comIN04Regular26MAY2025VALID580051
11GLOBAL_ICECREAM_SYSTEMIC001GelatoromaITALYVanillaabc@gmail.comEU01Premium12JAN2025VALID450051
12GLOBAL_ICECREAM_SYSTEMIC015GromITALYHazelnutgrom@gmail.comEU01Premium20FEB2025VALID810051
13GLOBAL_ICECREAM_SYSTEMIC020TokyocreamJAPANMatchatokyo@gmail.comJP01Premium30JUL2025VALID890051
14GLOBAL_ICECREAM_SYSTEMIC008MovenpickSWITZERLANDCoffeemove@gmail.comSW01Premium18AUG2025.920052
15GLOBAL_ICECREAM_SYSTEMIC005NullTURKEYPistachioinvalid@email.comTR01Premium15MAY2025VALID700051
16GLOBAL_ICECREAM_SYSTEMIC003BaskinrobbinsUSAChocolatebaskin@gmailUS01Luxury04MAY2026.550052
17GLOBAL_ICECREAM_SYSTEMIC006HaagendazsUSACookiehaagen@gmail.comUS01Luxury14JUN2025VALID650051
18GLOBAL_ICECREAM_SYSTEMIC009Ben&jerrysUSABrownieben@icecream.comUS01Luxury19SEP2025VALID870051
19GLOBAL_ICECREAM_SYSTEMIC014BluebellUSAMintblue@gmail.comUS01Luxury18JAN2025VALID720051
20GLOBAL_ICECREAM_SYSTEMIC016ColdstoneUSACheesecakecoldstone@gmail.comUS02Luxury22MAR2025VALID910051


data regional_summary;

set validation_flags;

by Country;

if first.Country then Country_Count=0;

Country_Count+1;

if last.Country;

run;

proc print data = regional_summary;

run;

OUTPUT:

ObsData_SourceIceCream_IDBrandCountryFlavorCustomer_EmailRegion_CodeCategoryManufacture_DateCustomer_AgeRevenueiMissing_CountCountry_Count
1GLOBAL_ICECREAM_SYSTEMIC010MagnumBELGIUMAlmondmagnum@gmail.comBE01Premium04MAY2026VALID7800511
2GLOBAL_ICECREAM_SYSTEMIC011YiliCHINAMatchayili@@gmail.comCN01Regular25OCT2025VALID4500511
3GLOBAL_ICECREAM_SYSTEMIC019CartedorFRANCECaramelcarte@gmail.comFR01Luxury28JUN2025VALID7700512
4GLOBAL_ICECREAM_SYSTEMIC018DairydayINDIAVanilladairy@gmail.comIN04Regular26MAY2025VALID5800516
5GLOBAL_ICECREAM_SYSTEMIC015GromITALYHazelnutgrom@gmail.comEU01Premium20FEB2025VALID8100512
6GLOBAL_ICECREAM_SYSTEMIC020TokyocreamJAPANMatchatokyo@gmail.comJP01Premium30JUL2025VALID8900511
7GLOBAL_ICECREAM_SYSTEMIC008MovenpickSWITZERLANDCoffeemove@gmail.comSW01Premium18AUG2025.9200521
8GLOBAL_ICECREAM_SYSTEMIC005NullTURKEYPistachioinvalid@email.comTR01Premium15MAY2025VALID7000511
9GLOBAL_ICECREAM_SYSTEMIC016ColdstoneUSACheesecakecoldstone@gmail.comUS02Luxury22MAR2025VALID9100515

Explanation

FIRST./LAST. processing enables grouped calculations without SQL. This technique is heavily used in SDTM derivations, visit sequencing, exposure tracking, and adverse event summarization.

7.PROC REPORT Professional Output

proc report data=revenue_summary nowd;

column Country Category Total_Records Avg_Revenue Total_Revenue;

define Country / group;

define Category / group;

define Total_Records / analysis;

define Avg_Revenue / analysis format=dollar10.;

define Total_Revenue / analysis format=dollar12.;

run;

OUTPUT:

CountryCategoryTotal_RecordsAvg_RevenueTotal_Revenue
BELGIUMPremium1$7,800$7,800
CHINARegular1$4,500$4,500
FRANCELuxury1$7,700$7,700
 Regular1..
INDIAPremium2$6,350$12,700
 Regular4$3,700$14,800
ITALYPremium2$6,300$12,600
JAPANPremium1$8,900$8,900
SWITZERLANDPremium1$9,200$9,200
TURKEYPremium1$7,000$7,000
USALuxury5$7,400$37,000

Explanation

PROC REPORT creates production-quality enterprise outputs. Pharmaceutical companies use this extensively for Tables, Listings, and Figures (TLFs). Formatting consistency is critical for executive presentations and regulatory documentation.

8.Reusable SAS Macro

%macro dataset_check(ds);

proc contents data=&ds;

run;

proc means data=&ds n nmiss;

run;

proc freq data=&ds;

tables Country Category;

run;

%mend;

%dataset_check(validation_flags);

OUTPUT:

The CONTENTS Procedure

Data Set NameWORK.VALIDATION_FLAGSObservations20
Member TypeDATAVariables13
EngineV9Indexes0
Created06/04/2026 10:37:27Observation Length216
Last Modified06/04/2026 10:37:27Deleted Observations0
Protection CompressedNO
Data Set Type SortedYES
Label   
Data RepresentationSOLARIS_X86_64, LINUX_X86_64, ALPHA_TRU64, LINUX_IA64  
Encodingutf-8 Unicode (UTF-8)  
Engine/Host Dependent Information
Data Set Page Size131072
Number of Data Set Pages1
First Data Page1
Max Obs per Page606
Obs in First Data Page20
Number of Data Set Repairs0
Filename/saswork/SAS_work701100007A7C_odaws02-apse1-2.oda.sas.com/SAS_workB2E300007A7C_odaws02-apse1-2.oda.sas.com/validation_flags.sas7bdat
Release Created9.0401M8
Host CreatedLinux
Inode Number1125598
Access Permissionrw-r--r--
Owner Nameu63247146
File Size256KB
File Size (bytes)262144
Alphabetic List of Variables and Attributes
#VariableTypeLenFormatInformat
3BrandChar25  
8CategoryChar15  
4CountryChar20  
10Customer_AgeNum8AGECHECK. 
6Customer_EmailChar50  
1Data_SourceChar22  
5FlavorChar20  
2IceCream_IDChar8  
9Manufacture_DateNum8DATE9.ANYDTDTE20.
13Missing_CountNum8  
7Region_CodeChar10  
11RevenueNum8  
12iNum8  
Sort Information
SortedbyCountry
ValidatedYES
Character SetASCII

The MEANS Procedure

VariableNN Miss
Manufacture_Date
Customer_Age
Revenue
i
Missing_Count
20
16
19
20
20
0
4
1
0
0

The FREQ Procedure

CountryFrequencyPercentCumulative
Frequency		Cumulative
Percent
BELGIUM15.0015.00
CHINA15.00210.00
FRANCE210.00420.00
INDIA630.001050.00
ITALY210.001260.00
JAPAN15.001365.00
SWITZERLAND15.001470.00
TURKEY15.001575.00
USA525.0020100.00
CategoryFrequencyPercentCumulative
Frequency		Cumulative
Percent
Luxury630.00630.00
Premium840.001470.00
Regular630.0020100.00

Explanation

Macros enable reusable validation frameworks. Instead of rewriting repetitive code, enterprise teams standardize QC logic into centralized macro libraries. This improves productivity, consistency, and deployment scalability.

9.FAMOUS ICE CREAMS WORLDWIDE RAW DATASET CREATION IN R

# using read.delim() with pipe delimiter

icecream_raw <- read.delim(

  text = "

IC001|GelatoRoma|Italy|Vanilla|25|4500|12JAN2025|abc@gmail.com| eu01 |Premium

IC001|gelatoroma|ITALY|vanilla|25|-4500|31FEB2025|wrongmail.com|EU01|premium

IC002|Amul|India|Mango|150|3200|15MAR2025|amul@ice.com|in02|Regular

IC003|BaskinRobbins|USA|Chocolate|-5|5500|.|baskin@gmail|US01|Luxury

IC004|Cornetto|France|Strawberry|32|.|11APR2025|cornetto@yahoo.com| FR03|regular

IC005|NULL|Turkey|Pistachio|41|7000|15MAY2025|turkmail.com|TR01|Premium

IC006|HaagenDazs|USA|Cookie|29|6500|14JUN2025|haagen@gmail.com|us01|Luxury

IC007|KwalityWalls|India|Kulfi|38|-800|17JUL2025|kwality@gmail.com|IN-01|Regular

IC008|Movenpick|Switzerland|Coffee|999|9200|18AUG2025|move@gmail.com|SW01|Premium

IC009|Ben&Jerrys|USA|Brownie|44|8700|19SEP2025|ben@icecream.com|US 01|Luxury

IC010|Magnum|Belgium|Almond|33|7800|INVALID|magnum@gmail.com|BE01|Premium

IC011|Yili|China|Matcha|27|4500|25OCT2025|yili@@gmail.com|CN01|Regular

IC012|Walls|India|Chocolate|.|5000|12NOV2025|walls@gmail.com|IN01|Regular

IC013|Nirulas|India|Butterscotch|45|6500|15DEC2025|nirulasgmail.com|IN01|Premium

IC014|BlueBell|USA|Mint|29|7200|18JAN2025|blue@gmail.com|US01|Luxury

IC015|Grom|Italy|Hazelnut|31|8100|20FEB2025|grom@gmail.com|EU01|Premium

IC016|ColdStone|USA|Cheesecake|36|9100|22MAR2025|coldstone@gmail.com|US02|Luxury

IC017|Natural's|India|TenderCoconut|28|6200|24APR2025|natural@gmail.com|IN03|Premium

IC018|DairyDay|India|Vanilla|42|5800|26MAY2025|dairy@gmail.com|IN04|Regular

IC019|CarteDor|France|Caramel|35|7700|28JUN2025|carte@gmail.com|FR01|Luxury

IC020|TokyoCream|Japan|Matcha|30|8900|30JUL2025|tokyo@gmail.com|JP01|Premium

",  

  sep = "|",

  header = FALSE,

  stringsAsFactors = FALSE,

  na.strings = c(".", "NULL", "INVALID")

)

OUTPUT:

 

V1

V2

V3

V4

V5

V6

V7

V8

V9

V10

1

IC001

GelatoRoma

Italy

Vanilla

25

4500

12-Jan-2025

abc@gmail.com

 eu01 

Premium

2

IC001

gelatoroma

ITALY

vanilla

25

-4500

31FEB2025

wrongmail.com

EU01

premium

3

IC002

Amul

India

Mango

150

3200

15-Mar-2025

amul@ice.com

in02

Regular

4

IC003

BaskinRobbins

USA

Chocolate

-5

5500

NA

baskin@gmail

US01

Luxury

5

IC004

Cornetto

France

Strawberry

32

NA

11-Apr-2025

cornetto@yahoo.com

 FR03

regular

6

IC005

NA

Turkey

Pistachio

41

7000

15-May-2025

turkmail.com

TR01

Premium

7

IC006

HaagenDazs

USA

Cookie

29

6500

14-Jun-2025

haagen@gmail.com

us01

Luxury

8

IC007

KwalityWalls

India

Kulfi

38

-800

17-Jul-2025

kwality@gmail.com

IN-01

Regular

9

IC008

Movenpick

Switzerland

Coffee

999

9200

18-Aug-2025

move@gmail.com

SW01

Premium

10

IC009

Ben&Jerrys

USA

Brownie

44

8700

19-Sep-2025

ben@icecream.com

US 01

Luxury

11

IC010

Magnum

Belgium

Almond

33

7800

NA

magnum@gmail.com

BE01

Premium

12

IC011

Yili

China

Matcha

27

4500

25-Oct-2025

yili@@gmail.com

CN01

Regular

13

IC012

Walls

India

Chocolate

NA

5000

12-Nov-2025

walls@gmail.com

IN01

Regular

14

IC013

Nirulas

India

Butterscotch

45

6500

15-Dec-2025

nirulasgmail.com

IN01

Premium

15

IC014

BlueBell

USA

Mint

29

7200

18-Jan-2025

blue@gmail.com

US01

Luxury

16

IC015

Grom

Italy

Hazelnut

31

8100

20-Feb-2025

grom@gmail.com

EU01

Premium

17

IC016

ColdStone

USA

Cheesecake

36

9100

22-Mar-2025

coldstone@gmail.com

US02

Luxury

18

IC017

Natural's

India

TenderCoconut

28

6200

24-Apr-2025

natural@gmail.com

IN03

Premium

19

IC018

DairyDay

India

Vanilla

42

5800

26-May-2025

dairy@gmail.com

IN04

Regular

20

IC019

CarteDor

France

Caramel

35

7700

28-Jun-2025

carte@gmail.com

FR01

Luxury

21

IC020

TokyoCream

Japan

Matcha

30

8900

30-Jul-2025

tokyo@gmail.com

JP01

Premium

# ASSIGNING COLUMN NAMES

colnames(icecream_raw) <- c(

  "IceCream_ID",

  "Brand",

  "Country",

  "Flavor",

  "Customer_Age",

  "Revenue",

  "Manufacture_Date",

  "Customer_Email",

  "Region_Code",

  "Category"

)

# CONVERTING DATA TYPES

icecream_raw$Customer_Age <- 

  as.numeric(icecream_raw$Customer_Age)

icecream_raw$Revenue <- 

  as.numeric(icecream_raw$Revenue)

# DATE CONVERSION

icecream_raw$Manufacture_Date <- as.Date(

  icecream_raw$Manufacture_Date,

  format = "%d%b%Y"

)

OUTPUT:

 

IceCream_ID

Brand

Country

Flavor

Customer_Age

Revenue

Manufacture_Date

Customer_Email

Region_Code

Category

1

IC001

GelatoRoma

Italy

Vanilla

25

4500

12-01-2025

abc@gmail.com

 eu01 

Premium

2

IC001

gelatoroma

ITALY

vanilla

25

-4500

NA

wrongmail.com

EU01

premium

3

IC002

Amul

India

Mango

150

3200

15-03-2025

amul@ice.com

in02

Regular

4

IC003

BaskinRobbins

USA

Chocolate

-5

5500

NA

baskin@gmail

US01

Luxury

5

IC004

Cornetto

France

Strawberry

32

NA

11-04-2025

cornetto@yahoo.com

 FR03

regular

6

IC005

NA

Turkey

Pistachio

41

7000

15-05-2025

turkmail.com

TR01

Premium

7

IC006

HaagenDazs

USA

Cookie

29

6500

14-06-2025

haagen@gmail.com

us01

Luxury

8

IC007

KwalityWalls

India

Kulfi

38

-800

17-07-2025

kwality@gmail.com

IN-01

Regular

9

IC008

Movenpick

Switzerland

Coffee

999

9200

18-08-2025

move@gmail.com

SW01

Premium

10

IC009

Ben&Jerrys

USA

Brownie

44

8700

19-09-2025

ben@icecream.com

US 01

Luxury

11

IC010

Magnum

Belgium

Almond

33

7800

NA

magnum@gmail.com

BE01

Premium

12

IC011

Yili

China

Matcha

27

4500

25-10-2025

yili@@gmail.com

CN01

Regular

13

IC012

Walls

India

Chocolate

NA

5000

12-11-2025

walls@gmail.com

IN01

Regular

14

IC013

Nirulas

India

Butterscotch

45

6500

15-12-2025

nirulasgmail.com

IN01

Premium

15

IC014

BlueBell

USA

Mint

29

7200

18-01-2025

blue@gmail.com

US01

Luxury

16

IC015

Grom

Italy

Hazelnut

31

8100

20-02-2025

grom@gmail.com

EU01

Premium

17

IC016

ColdStone

USA

Cheesecake

36

9100

22-03-2025

coldstone@gmail.com

US02

Luxury

18

IC017

Natural's

India

TenderCoconut

28

6200

24-04-2025

natural@gmail.com

IN03

Premium

19

IC018

DairyDay

India

Vanilla

42

5800

26-05-2025

dairy@gmail.com

IN04

Regular

20

IC019

CarteDor

France

Caramel

35

7700

28-06-2025

carte@gmail.com

FR01

Luxury

21

IC020

TokyoCream

Japan

Matcha

30

8900

30-07-2025

tokyo@gmail.com

JP01

Premium

Explanation of the R Code

This R program recreates the same corrupted enterprise-style Famous Ice Cream dataset that was originally built in SAS using DATALINES. Instead of DATALINES, R uses read.delim() with the text= argument to directly load raw pipe-delimited records into memory. The separator sep="|" behaves similarly to SAS dlm='|'.

The na.strings argument converts problematic values like ".", "NULL", and "INVALID" into proper missing values (NA). This is extremely important because enterprise systems often receive corrupted placeholders from APIs, legacy databases, or Excel uploads.

colnames() assigns professional business variable names. Numeric conversion is done using as.numeric() because imported values initially enter R as character vectors. Date conversion uses as.Date() with %d%b%Y, which interprets values like 12JAN2025.

This dataset intentionally contains:

  • Duplicate IceCream IDs
  • Negative revenue
  • Impossible ages
  • Invalid dates
  • Malformed emails
  • Mixed case values
  • Whitespace corruption
  • Missing values

These issues simulate real-world production failures commonly encountered in healthcare, retail, banking, and insurance analytics systems.

Key advantage of R:

  • Flexible string handling
  • Dynamic memory allocation
  • Fast exploratory profiling

10.R Data Cleaning Layer

library(tidyverse)

library(janitor)

library(lubridate)

icecream_clean <- icecream_raw %>%

  clean_names() %>%

  mutate(

    brand =str_to_title(str_trim(brand)),

    country =str_to_upper(str_trim(country)),

    flavor =str_to_title(str_trim(flavor)),

    customer_email =if_else(

        grepl("@", customer_email),

        str_trim(customer_email),

        "invalid@email.com"),

    revenue =abs(revenue),

    customer_age =if_else(customer_age < 1 |

          customer_age > 100,NA_real_,

        customer_age),

    region_code =str_replace_all(

        str_to_upper(region_code)," ",""),

    category =str_to_title(str_trim(category))

  ) %>%

  distinct(ice_cream_id, .keep_all = TRUE)

OUTPUT:

 

ice_cream_id

brand

country

flavor

customer_age

revenue

manufacture_date

customer_email

region_code

category

1

IC001

Gelatoroma

ITALY

Vanilla

25

4500

12-01-2025

abc@gmail.com

EU01

Premium

2

IC002

Amul

INDIA

Mango

NA

3200

15-03-2025

amul@ice.com

IN02

Regular

3

IC003

Baskinrobbins

USA

Chocolate

NA

5500

NA

baskin@gmail

US01

Luxury

4

IC004

Cornetto

FRANCE

Strawberry

32

NA

11-04-2025

cornetto@yahoo.com

FR03

Regular

5

IC005

NA

TURKEY

Pistachio

41

7000

15-05-2025

invalid@email.com

TR01

Premium

6

IC006

Haagendazs

USA

Cookie

29

6500

14-06-2025

haagen@gmail.com

US01

Luxury

7

IC007

Kwalitywalls

INDIA

Kulfi

38

800

17-07-2025

kwality@gmail.com

IN-01

Regular

8

IC008

Movenpick

SWITZERLAND

Coffee

NA

9200

18-08-2025

move@gmail.com

SW01

Premium

9

IC009

Ben&Jerrys

USA

Brownie

44

8700

19-09-2025

ben@icecream.com

US01

Luxury

10

IC010

Magnum

BELGIUM

Almond

33

7800

NA

magnum@gmail.com

BE01

Premium

11

IC011

Yili

CHINA

Matcha

27

4500

25-10-2025

yili@@gmail.com

CN01

Regular

12

IC012

Walls

INDIA

Chocolate

NA

5000

12-11-2025

walls@gmail.com

IN01

Regular

13

IC013

Nirulas

INDIA

Butterscotch

45

6500

15-12-2025

invalid@email.com

IN01

Premium

14

IC014

Bluebell

USA

Mint

29

7200

18-01-2025

blue@gmail.com

US01

Luxury

15

IC015

Grom

ITALY

Hazelnut

31

8100

20-02-2025

grom@gmail.com

EU01

Premium

16

IC016

Coldstone

USA

Cheesecake

36

9100

22-03-2025

coldstone@gmail.com

US02

Luxury

17

IC017

Natural's

INDIA

Tendercoconut

28

6200

24-04-2025

natural@gmail.com

IN03

Premium

18

IC018

Dairyday

INDIA

Vanilla

42

5800

26-05-2025

dairy@gmail.com

IN04

Regular

19

IC019

Cartedor

FRANCE

Caramel

35

7700

28-06-2025

carte@gmail.com

FR01

Luxury

20

IC020

Tokyocream

JAPAN

Matcha

30

8900

30-07-2025

tokyo@gmail.com

JP01

Premium

Explanation

The R workflow mirrors SAS cleaning logic using modern tidyverse functions. mutate() transforms variables, if_else() performs conditional repair, grepl() validates email structure, and parse_date_time() standardizes corrupted dates. Unlike SAS, R handles character memory dynamically, reducing truncation risk. However, SAS remains stronger in metadata governance and auditability.

Enterprise Validation & Compliance

In regulated industries like pharmaceuticals, banking, and insurance, data cleaning is not optional it is a compliance requirement.

Clinical trial standards like CDISC SDTM and ADaM require:

  • traceable derivations
  • reproducible transformations
  • independent QC validation
  • metadata consistency
  • audit-ready outputs

One dangerous SAS behavior is:

if revenue > . then

Missing numeric values in SAS are treated as lower than valid numbers. Improper logic can accidentally include missing values in calculations, creating severe statistical errors.

Regulatory agencies expect:

  • full lineage
  • documented transformations
  • controlled macros
  • version governance
  • reproducible outputs

20 Enterprise Data Cleaning Best Practices

  1. Standardize metadata early
  2. Validate date formats immediately
  3. Remove duplicates before aggregation
  4. Centralize validation rules
  5. Use reusable macros
  6. Track audit lineage
  7. Separate raw and cleaned datasets
  8. Never overwrite source data
  9. Use controlled terminology
  10. Validate missingness patterns
  11. Normalize text variables
  12. Standardize region mappings
  13. QC independently
  14. Use PROC CONTENTS frequently
  15. Validate variable lengths
  16. Use defensive programming
  17. Create exception reports
  18. Automate data profiling
  19. Maintain reproducible workflows
  20. Document every derivation

Business Logic Behind Cleaning

Enterprise cleaning exists because analytics depends on trust. If patient ages are negative or greater than 150, statistical models become invalid. If revenue values are negative due to system corruption, profitability dashboards mislead executives. Missing dates break time-series forecasting and visit-window calculations. Text normalization matters because “premium,” “Premium,” and “ PREMIUM ” should represent the same category. Otherwise, aggregation logic produces fragmented results. Missing values are often imputed to preserve analytical continuity. Standardized variables improve downstream joins, AI model consistency, and dashboard reliability. Every transformation must support business meaning, traceability, and analytical reproducibility.

20 One-Line Insights

  1. Dirty data creates expensive business mistakes.
  2. Validation logic is stronger than visual inspection.
  3. Duplicate records destroy reporting accuracy.
  4. Standardized variables improve reproducibility.
  5. SAS excels in governed enterprise environments.
  6. R provides flexible transformation workflows.
  7. Metadata drives reliable analytics.
  8. Audit trails protect regulatory integrity.
  9. Missing values silently corrupt models.
  10. Defensive programming reduces production failures.
  11. PROC SQL simplifies enterprise joins.
  12. DATA Step offers unmatched row-level control.
  13. ARRAYS improve scalability.
  14. QC independence improves trustworthiness.
  15. Enterprise cleaning requires documentation discipline.
  16. Standard formats reduce operational chaos.
  17. Automation improves consistency.
  18. Clean datasets accelerate AI reliability.
  19. Reporting accuracy depends on preprocessing quality.
  20. Trustworthy analytics begins with trustworthy data.

SAS vs R Comparison

Feature

SAS

R

Auditability

Excellent

Moderate

Regulatory Acceptance

Very High

Growing

Flexibility

Structured

Extremely Flexible

Enterprise Governance

Strong

Moderate

Visualization

Moderate

Excellent

Metadata Control

Excellent

Limited

Performance

Strong

Strong

Open Source Ecosystem

Limited

Massive

Reproducibility

Excellent

Excellent

Statistical Power

Excellent

Excellent

Summary

SAS and R complement each other exceptionally well in enterprise analytics ecosystems. SAS dominates in regulated production systems because of its metadata governance, auditability, standardized validation capabilities, and mature reporting infrastructure. DATA Step programming provides extremely granular row-level control, while PROC SQL and reporting procedures simplify enterprise summarization. SAS remains deeply trusted across pharmaceutical, banking, and insurance sectors because of reproducibility and compliance strength.

R excels in flexibility, open-source innovation, modern visualization, and rapid experimentation. Packages like tidyverse, stringr, lubridate, janitor, and purrr dramatically simplify complex transformations. R also integrates efficiently with machine learning workflows and modern AI pipelines.

The strongest enterprise strategy is not SAS versus R it is SAS and R together. SAS provides governance, production control, validation frameworks, and audit readiness. R contributes agility, modern analytics, advanced transformations, and scalable data science tooling. Together, they create reliable, scalable, and production-grade analytical intelligence systems.

Conclusion

Modern analytics is fundamentally a data-quality problem disguised as a reporting problem. Organizations often invest millions into AI systems, dashboards, cloud platforms, and predictive models while underestimating the destructive impact of corrupted operational data. Duplicate records, malformed text, inconsistent dates, negative values, missing fields, and metadata corruption silently damage analytical trust long before executives notice the consequences.

This Famous Ice Cream enterprise project demonstrates how SAS and R transform chaotic operational information into trusted analytical intelligence. Using DATA Step programming, PROC SQL, ARRAYS, FIRST./LAST. processing, PROC FORMAT, macros, and enterprise reporting procedures, SAS provides unmatched governance and production stability. Simultaneously, R offers agile transformation frameworks through tidyverse pipelines, modern string processing, and flexible statistical workflows.

In real enterprise ecosystems, data cleaning is not a cosmetic task. It directly impacts:

  • clinical trial integrity
  • regulatory compliance
  • insurance claim accuracy
  • banking fraud detection
  • AI model reliability
  • executive decision-making
  • operational forecasting
  • financial reporting

The most successful analytics organizations treat cleaning pipelines as mission-critical infrastructure. They standardize metadata, enforce governance rules, automate validation frameworks, maintain reproducible audit trails, and separate raw data from analytical datasets.

A single malformed variable can invalidate an entire statistical submission. A duplicate identifier can distort revenue reporting. Improper missing-value handling can silently destroy AI predictions. That is why enterprise-grade cleaning frameworks are essential.

SAS and R together create a powerful ecosystem where governance meets flexibility, compliance meets innovation, and analytical reliability becomes scalable. Clean data is not merely technical hygiene it is the foundation of trustworthy intelligence.

Interview Questions and Answers

1. How would you identify duplicate IceCream IDs in SAS?

Answer

I would first use PROC SORT NODUPKEY to remove duplicates and then use PROC FREQ or PROC SQL with GROUP BY HAVING COUNT(*) > 1 to identify repeated IDs. In enterprise systems, duplicate identifiers can inflate revenue metrics and distort patient or customer analytics.

2. Why is missing numeric handling dangerous in SAS?

Answer

SAS treats missing numeric values as smaller than valid numbers. Improper logic like if amount > . may unintentionally include missing observations. This can produce catastrophic analytical and statistical errors in regulated reporting environments.

3. How would you validate malformed email addresses in R?

Answer

I would use grepl() or str_detect() with regex validation. Invalid emails would either be corrected through reference mapping or flagged into exception datasets for manual review.

4. Why should LENGTH statements appear before assignments in SAS?

Answer

SAS allocates character variable length during first compilation encounter. If LENGTH appears after assignment, truncation may occur permanently. This is a major production risk in SDTM and enterprise reporting environments.

5. When would you prefer DATA Step over PROC SQL?

Answer

DATA Step is better for row-level sequential processing, FIRST./LAST. logic, arrays, and complex conditional transformations. PROC SQL is preferred for joins, aggregation, summarization, and relational operations. Enterprise workflows usually combine both approaches strategically.

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

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 ICE-CREAM 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:



3.Data Disasters to Data Intelligence: Mastering TRANWRD in SAS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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