When Billions of App Downloads Meet Broken Data | Enterprise SAS and R Cleaning Frameworks That Save Analytics

-

FROM APP STORE CHAOS TO EXECUTIVE INTELLIGENCE

Cleaning the World's Highest Downloaded Apps Dataset Using SAS and R

Introduction:Business Crisis Scenario

A global mobile analytics company publishes a quarterly report identifying the world's highest downloaded applications. Executives use the report for investment decisions, marketing budgets, AI recommendation systems, and competitive benchmarking.

Then disaster strikes.

The analytics dashboard shows:

  • Negative download counts
  • Duplicate App IDs
  • Invalid launch dates
  • Missing categories
  • Corrupted country codes
  • Broken email contacts
  • Mixed-case application names
  • Impossible ratings above 5
  • Invalid timestamps

Suddenly:

  • AI demand forecasting models fail.
  • Marketing budgets are allocated incorrectly.
  • Investors receive misleading intelligence.
  • Regional expansion strategies become inaccurate.
  • Regulatory reports contain inconsistencies.

Dirty data doesn't merely create ugly tables.

It creates expensive business mistakes.

Global Highest Downloaded Apps Raw Dataset

Variables (9 Variables)

Variable

Description

APP_ID

Unique App Identifier

APP_NAME

Application Name

CATEGORY

Category

COUNTRY

Country Code

DOWNLOADS

Total Downloads

RATING

App Rating

RELEASE_DATE

Release Date

SUPPORT_EMAIL

Contact Email

ACTIVE_USERS

Monthly Active Users

1.SAS Raw Dataset With Intentional Errors

data apps_raw;

length app_id $8 app_name $40 category $20 country $12

       support_email $60 release_dt_raw $25;

informat release_dt_raw $25.;

infile datalines dlm='|' truncover;

input app_id $ app_name $ category $ country $ downloads

rating release_dt_raw $ support_email $active_users;

datalines;

APP001|WhatsApp|Messaging|IND|2500000000|4.8|2009-01-15|support@whatsapp.com|2000000000

APP002|facebook|Social|usa|3000000000|4.7|2004-02-04|supportfacebook.com|2500000000

APP002|facebook|Social|USA|3000000000|4.7|2004-02-04|supportfacebook.com|2500000000

APP003| TikTok |Entertainment|CHN|-1500000000|4.9|2016-09-15|help@tiktok.com|1800000000

APP004|Instagram|NULL|USA|2000000000|5.8|2010-10-06|support@instagram|1700000000

APP005|Telegram|Messaging|INDIA|900000000|4.6|2026-15-01|help@telegram.org|850000000

APP006|Snapchat|Social|UK| |4.2|2011-09-16|support@snapchat.com|700000000

APP007|CapCut|editing|chn|800000000|4.4|invalid_date|support@capcut.com|600000000

APP008|Spotify|Music|EUROPE|700000000|-2|2008-10-07|spotify.com|650000000

APP009|ChatGPT|AI|USA|900000000|4.9|2022-11-30|support@openai.com|500000000

APP010| Temu |Shopping|UsA|1200000000|4.5|2022-09-01|support@temu.com|450000000

APP011|Threads|social|USA|750000000|4.3|2023-07-05|contact@threads.com|300000000

APP012|NULL|Gaming|JPN|650000000|4.1|2020-04-20|help@gaming.com|250000000

APP013|Zoom|Business|USA|1000000000|4.7|2013-01-21|zoom@@zoom.com|400000000

APP014|Netflix|Entertainment|US|1100000000|4.8|2012-04-10|support@netflix.com|350000000

APP015|Uber|Travel|USA|950000000|4.4|2011-03-11|support@uber.com|600000000

;

run;

proc print data=apps_raw;

run;

OUTPUT:

Obsapp_idapp_namecategorycountrysupport_emailrelease_dt_rawdownloadsratingactive_users
1APP001WhatsAppMessagingINDsupport@whatsapp.com2009-01-1525000000004.82000000000
2APP002facebookSocialusasupportfacebook.com2004-02-0430000000004.72500000000
3APP002facebookSocialUSAsupportfacebook.com2004-02-0430000000004.72500000000
4APP003TikTokEntertainmentCHNhelp@tiktok.com2016-09-15-15000000004.91800000000
5APP004InstagramNULLUSAsupport@instagram2010-10-0620000000005.81700000000
6APP005TelegramMessagingINDIAhelp@telegram.org2026-15-019000000004.6850000000
7APP006SnapchatSocialUKsupport@snapchat.com2011-09-16.4.2700000000
8APP007CapCuteditingchnsupport@capcut.cominvalid_date8000000004.4600000000
9APP008SpotifyMusicEUROPEspotify.com2008-10-07700000000-2.0650000000
10APP009ChatGPTAIUSAsupport@openai.com2022-11-309000000004.9500000000
11APP010TemuShoppingUsAsupport@temu.com2022-09-0112000000004.5450000000
12APP011ThreadssocialUSAcontact@threads.com2023-07-057500000004.3300000000
13APP012NULLGamingJPNhelp@gaming.com2020-04-206500000004.1250000000
14APP013ZoomBusinessUSAzoom@@zoom.com2013-01-2110000000004.7400000000
15APP014NetflixEntertainmentUSsupport@netflix.com2012-04-1011000000004.8350000000
16APP015UberTravelUSAsupport@uber.com2011-03-119500000004.4600000000

Problems Found

Issue

Impact

Duplicate APP_ID

Double counting downloads

Negative downloads

Wrong forecasting

Invalid dates

Incorrect trend analysis

Rating >5

Dashboard errors

Missing category

Segmentation failures

Malformed emails

Communication failures

Mixed country codes

Incorrect regional summaries

NULL strings

Missing value confusion

Leading spaces

Join failures

Character Truncation Risk in SAS

One of the biggest production issues in SAS occurs when developers place LENGTH statements after assignments.

Bad example:

data test;

name="International Business Machines";

length name $10;

run;

Result:

Internatio

The variable length was determined before the explicit LENGTH statement.

Correct approach:

data test;

length name $50;

name="International Business Machines";

run;

Unlike SAS, R dynamically allocates string memory and does not suffer from character truncation behavior.

2.SAS Cleaning Workflow

proc format;

value ratingfmt low-0='Invalid'

                              0<-5='Valid'

                        5<-high='Invalid';

run;

data apps_clean;

set apps_raw;

app_name=propcase(strip(app_name));

category=upcase(strip(category));

country=upcase(strip(country));

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

if downloads<0 then downloads=abs(downloads);

rating=round(rating,.1);

if rating>5 then rating=.;

format rating ratingfmt.;

if verify(support_email,

'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789@._')>0

then email_flag='INVALID';

release_date=input(release_dt_raw,?? yymmdd10.);

format release_date date9.;

if country in ('INDIA') then country='IND';

if country='US' then country='USA';

if country='CHN' then country='CHN';

if cmiss(of _all_)>0 then missing_flag='Y';

downloads_band=put(downloads,comma20.);

run;

proc print data=apps_clean;

run;

OUTPUT:

Obsapp_idapp_namecategorycountrysupport_emailrelease_dt_rawdownloadsratingactive_usersemail_flagrelease_datemissing_flagdownloads_band
1APP001WhatsappMESSAGINGINDsupport@whatsapp.com2009-01-152500000000Valid2000000000INVALID15JAN2009 2,500,000,000
2APP002FacebookSOCIALUSAsupportfacebook.com2004-02-043000000000Valid2500000000INVALID04FEB2004 3,000,000,000
3APP002FacebookSOCIALUSAsupportfacebook.com2004-02-043000000000Valid2500000000INVALID04FEB2004 3,000,000,000
4APP003TiktokENTERTAINMENTCHNhelp@tiktok.com2016-09-151500000000Valid1800000000INVALID15SEP2016 1,500,000,000
5APP004InstagramUNKNOWNUSAsupport@instagram2010-10-062000000000.1700000000INVALID06OCT2010Y2,000,000,000
6APP005TelegramMESSAGINGINDhelp@telegram.org2026-15-01900000000Valid850000000INVALID.Y900,000,000
7APP006SnapchatSOCIALUKsupport@snapchat.com2011-09-16.Valid700000000INVALID16SEP2011Y.
8APP007CapcutEDITINGCHNsupport@capcut.cominvalid_date800000000Valid600000000INVALID.Y800,000,000
9APP008SpotifyMUSICEUROPEspotify.com2008-10-07700000000Invalid650000000INVALID07OCT2008 700,000,000
10APP009ChatgptAIUSAsupport@openai.com2022-11-30900000000Valid500000000INVALID30NOV2022 900,000,000
11APP010TemuSHOPPINGUSAsupport@temu.com2022-09-011200000000Valid450000000INVALID01SEP2022 1,200,000,000
12APP011ThreadsSOCIALUSAcontact@threads.com2023-07-05750000000Valid300000000INVALID05JUL2023 750,000,000
13APP012NullGAMINGJPNhelp@gaming.com2020-04-20650000000Valid250000000INVALID20APR2020 650,000,000
14APP013ZoomBUSINESSUSAzoom@@zoom.com2013-01-211000000000Valid400000000INVALID21JAN2013 1,000,000,000
15APP014NetflixENTERTAINMENTUSAsupport@netflix.com2012-04-101100000000Valid350000000INVALID10APR2012 1,100,000,000
16APP015UberTRAVELUSAsupport@uber.com2011-03-11950000000Valid600000000INVALID11MAR2011 950,000,000

Explanation

This workflow demonstrates enterprise-grade cleansing logic:

  • ABS() corrects impossible download values.
  • VERIFY() identifies illegal email characters.
  • TRANWRD() replaces NULL values.
  • PROPCASE() standardizes naming.
  • ROUND() controls reporting precision.
  • INPUT() converts character dates.
  • CMISS() identifies missing values.

These techniques appear in banking fraud systems, SDTM production pipelines, and insurance claim processing environments.

3.Deduplication Using PROC SORT

proc sort data=apps_clean

out=apps_nodup

nodupkey;

by app_id;

run;

proc print data=apps_nodup;

run;

LOG:

NOTE: There were 16 observations read from the data set WORK.APPS_CLEAN.
NOTE: 1 observations with duplicate key values were deleted.
NOTE: The data set WORK.APPS_NODUP has 15 observations and 13 variables.

OUTPUT:

Obsapp_idapp_namecategorycountrysupport_emailrelease_dt_rawdownloadsratingactive_usersemail_flagrelease_datemissing_flagdownloads_band
1APP001WhatsappMESSAGINGINDsupport@whatsapp.com2009-01-152500000000Valid2000000000INVALID15JAN2009 2,500,000,000
2APP002FacebookSOCIALUSAsupportfacebook.com2004-02-043000000000Valid2500000000INVALID04FEB2004 3,000,000,000
3APP003TiktokENTERTAINMENTCHNhelp@tiktok.com2016-09-151500000000Valid1800000000INVALID15SEP2016 1,500,000,000
4APP004InstagramUNKNOWNUSAsupport@instagram2010-10-062000000000.1700000000INVALID06OCT2010Y2,000,000,000
5APP005TelegramMESSAGINGINDhelp@telegram.org2026-15-01900000000Valid850000000INVALID.Y900,000,000
6APP006SnapchatSOCIALUKsupport@snapchat.com2011-09-16.Valid700000000INVALID16SEP2011Y.
7APP007CapcutEDITINGCHNsupport@capcut.cominvalid_date800000000Valid600000000INVALID.Y800,000,000
8APP008SpotifyMUSICEUROPEspotify.com2008-10-07700000000Invalid650000000INVALID07OCT2008 700,000,000
9APP009ChatgptAIUSAsupport@openai.com2022-11-30900000000Valid500000000INVALID30NOV2022 900,000,000
10APP010TemuSHOPPINGUSAsupport@temu.com2022-09-011200000000Valid450000000INVALID01SEP2022 1,200,000,000
11APP011ThreadsSOCIALUSAcontact@threads.com2023-07-05750000000Valid300000000INVALID05JUL2023 750,000,000
12APP012NullGAMINGJPNhelp@gaming.com2020-04-20650000000Valid250000000INVALID20APR2020 650,000,000
13APP013ZoomBUSINESSUSAzoom@@zoom.com2013-01-211000000000Valid400000000INVALID21JAN2013 1,000,000,000
14APP014NetflixENTERTAINMENTUSAsupport@netflix.com2012-04-101100000000Valid350000000INVALID10APR2012 1,100,000,000
15APP015UberTRAVELUSAsupport@uber.com2011-03-11950000000Valid600000000INVALID11MAR2011 950,000,000

Explanation

NODUPKEY removes duplicate business keys.

Clinical submissions often use this strategy for:

  • duplicate subjects
  • duplicate adverse events
  • duplicate laboratory records

Failure to deduplicate can invalidate statistical analysis.

4.PROC SQL Validation

proc sql;

create table app_summary as

select

country,

count(*) as total_apps,

sum(downloads) as total_downloads,

mean(rating) as avg_rating

from apps_nodup

group by country;

quit;

proc print data=app_summary;

run;

OUTPUT:

Obscountrytotal_appstotal_downloadsavg_rating
1CHN223000000004.65000
2EUROPE1700000000-2.00000
3IND234000000004.70000
4JPN16500000004.10000
5UK1.4.20000
6USA8109000000004.61429

Explanation

PROC SQL excels for:

  • aggregation
  • joins
  • validation checks
  • summary reporting

5.DATA Step Alternative

proc sort data=apps_nodup;

by country;

run;

proc print data=apps_nodup;

run;

OUTPUT:

Obsapp_idapp_namecategorycountrysupport_emailrelease_dt_rawdownloadsratingactive_usersemail_flagrelease_datemissing_flagdownloads_band
1APP003TiktokENTERTAINMENTCHNhelp@tiktok.com2016-09-151500000000Valid1800000000INVALID15SEP2016 1,500,000,000
2APP007CapcutEDITINGCHNsupport@capcut.cominvalid_date800000000Valid600000000INVALID.Y800,000,000
3APP008SpotifyMUSICEUROPEspotify.com2008-10-07700000000Invalid650000000INVALID07OCT2008 700,000,000
4APP001WhatsappMESSAGINGINDsupport@whatsapp.com2009-01-152500000000Valid2000000000INVALID15JAN2009 2,500,000,000
5APP005TelegramMESSAGINGINDhelp@telegram.org2026-15-01900000000Valid850000000INVALID.Y900,000,000
6APP012NullGAMINGJPNhelp@gaming.com2020-04-20650000000Valid250000000INVALID20APR2020 650,000,000
7APP006SnapchatSOCIALUKsupport@snapchat.com2011-09-16.Valid700000000INVALID16SEP2011Y.
8APP002FacebookSOCIALUSAsupportfacebook.com2004-02-043000000000Valid2500000000INVALID04FEB2004 3,000,000,000
9APP004InstagramUNKNOWNUSAsupport@instagram2010-10-062000000000.1700000000INVALID06OCT2010Y2,000,000,000
10APP009ChatgptAIUSAsupport@openai.com2022-11-30900000000Valid500000000INVALID30NOV2022 900,000,000
11APP010TemuSHOPPINGUSAsupport@temu.com2022-09-011200000000Valid450000000INVALID01SEP2022 1,200,000,000
12APP011ThreadsSOCIALUSAcontact@threads.com2023-07-05750000000Valid300000000INVALID05JUL2023 750,000,000
13APP013ZoomBUSINESSUSAzoom@@zoom.com2013-01-211000000000Valid400000000INVALID21JAN2013 1,000,000,000
14APP014NetflixENTERTAINMENTUSAsupport@netflix.com2012-04-101100000000Valid350000000INVALID10APR2012 1,100,000,000
15APP015UberTRAVELUSAsupport@uber.com2011-03-11950000000Valid600000000INVALID11MAR2011 950,000,000

data summary_ds;

set apps_nodup;

by country;

retain total_downloads 0 total_apps 0;

total_downloads+downloads;

total_apps+1;

if last.country then output;

run;

proc print data=summary_ds;

run;

OUTPUT:

Obsapp_idapp_namecategorycountrysupport_emailrelease_dt_rawdownloadsratingactive_usersemail_flagrelease_datemissing_flagdownloads_bandtotal_downloadstotal_apps
1APP007CapcutEDITINGCHNsupport@capcut.cominvalid_date800000000Valid600000000INVALID.Y800,000,00023000000002
2APP008SpotifyMUSICEUROPEspotify.com2008-10-07700000000Invalid650000000INVALID07OCT2008 700,000,00030000000003
3APP005TelegramMESSAGINGINDhelp@telegram.org2026-15-01900000000Valid850000000INVALID.Y900,000,00064000000005
4APP012NullGAMINGJPNhelp@gaming.com2020-04-20650000000Valid250000000INVALID20APR2020 650,000,00070500000006
5APP006SnapchatSOCIALUKsupport@snapchat.com2011-09-16.Valid700000000INVALID16SEP2011Y.70500000007
6APP015UberTRAVELUSAsupport@uber.com2011-03-11950000000Valid600000000INVALID11MAR2011 950,000,0001795000000015

DATA Step provides greater row-level control compared with SQL aggregation.

6.Advanced SAS Features

6.1 ARRAY

data apps_array;

set apps_nodup;

array chars {*} app_name category country;

do i=1 to dim(chars);

chars[i]=strip(chars[i]);

end;

run;

proc print data=apps_array;

run;

OUTPUT:

Obsapp_idapp_namecategorycountrysupport_emailrelease_dt_rawdownloadsratingactive_usersemail_flagrelease_datemissing_flagdownloads_bandi
1APP003TiktokENTERTAINMENTCHNhelp@tiktok.com2016-09-151500000000Valid1800000000INVALID15SEP2016 1,500,000,0004
2APP007CapcutEDITINGCHNsupport@capcut.cominvalid_date800000000Valid600000000INVALID.Y800,000,0004
3APP008SpotifyMUSICEUROPEspotify.com2008-10-07700000000Invalid650000000INVALID07OCT2008 700,000,0004
4APP001WhatsappMESSAGINGINDsupport@whatsapp.com2009-01-152500000000Valid2000000000INVALID15JAN2009 2,500,000,0004
5APP005TelegramMESSAGINGINDhelp@telegram.org2026-15-01900000000Valid850000000INVALID.Y900,000,0004
6APP012NullGAMINGJPNhelp@gaming.com2020-04-20650000000Valid250000000INVALID20APR2020 650,000,0004
7APP006SnapchatSOCIALUKsupport@snapchat.com2011-09-16.Valid700000000INVALID16SEP2011Y.4
8APP002FacebookSOCIALUSAsupportfacebook.com2004-02-043000000000Valid2500000000INVALID04FEB2004 3,000,000,0004
9APP004InstagramUNKNOWNUSAsupport@instagram2010-10-062000000000.1700000000INVALID06OCT2010Y2,000,000,0004
10APP009ChatgptAIUSAsupport@openai.com2022-11-30900000000Valid500000000INVALID30NOV2022 900,000,0004
11APP010TemuSHOPPINGUSAsupport@temu.com2022-09-011200000000Valid450000000INVALID01SEP2022 1,200,000,0004
12APP011ThreadsSOCIALUSAcontact@threads.com2023-07-05750000000Valid300000000INVALID05JUL2023 750,000,0004
13APP013ZoomBUSINESSUSAzoom@@zoom.com2013-01-211000000000Valid400000000INVALID21JAN2013 1,000,000,0004
14APP014NetflixENTERTAINMENTUSAsupport@netflix.com2012-04-101100000000Valid350000000INVALID10APR2012 1,100,000,0004
15APP015UberTRAVELUSAsupport@uber.com2011-03-11950000000Valid600000000INVALID11MAR2011 950,000,0004

6.2 SELECT WHEN

data apps_select;

length group $20.;

set apps_nodup;

select(category);

when('SOCIAL') group='Consumer';

when('BUSINESS') group='Enterprise';

otherwise group='Other';

end;

run;

proc print data=apps_select;

run;

OUTPUT:

Obsgroupapp_idapp_namecategorycountrysupport_emailrelease_dt_rawdownloadsratingactive_usersemail_flagrelease_datemissing_flagdownloads_band
1OtherAPP003TiktokENTERTAINMENTCHNhelp@tiktok.com2016-09-151500000000Valid1800000000INVALID15SEP2016 1,500,000,000
2OtherAPP007CapcutEDITINGCHNsupport@capcut.cominvalid_date800000000Valid600000000INVALID.Y800,000,000
3OtherAPP008SpotifyMUSICEUROPEspotify.com2008-10-07700000000Invalid650000000INVALID07OCT2008 700,000,000
4OtherAPP001WhatsappMESSAGINGINDsupport@whatsapp.com2009-01-152500000000Valid2000000000INVALID15JAN2009 2,500,000,000
5OtherAPP005TelegramMESSAGINGINDhelp@telegram.org2026-15-01900000000Valid850000000INVALID.Y900,000,000
6OtherAPP012NullGAMINGJPNhelp@gaming.com2020-04-20650000000Valid250000000INVALID20APR2020 650,000,000
7ConsumerAPP006SnapchatSOCIALUKsupport@snapchat.com2011-09-16.Valid700000000INVALID16SEP2011Y.
8ConsumerAPP002FacebookSOCIALUSAsupportfacebook.com2004-02-043000000000Valid2500000000INVALID04FEB2004 3,000,000,000
9OtherAPP004InstagramUNKNOWNUSAsupport@instagram2010-10-062000000000.1700000000INVALID06OCT2010Y2,000,000,000
10OtherAPP009ChatgptAIUSAsupport@openai.com2022-11-30900000000Valid500000000INVALID30NOV2022 900,000,000
11OtherAPP010TemuSHOPPINGUSAsupport@temu.com2022-09-011200000000Valid450000000INVALID01SEP2022 1,200,000,000
12ConsumerAPP011ThreadsSOCIALUSAcontact@threads.com2023-07-05750000000Valid300000000INVALID05JUL2023 750,000,000
13EnterpriseAPP013ZoomBUSINESSUSAzoom@@zoom.com2013-01-211000000000Valid400000000INVALID21JAN2013 1,000,000,000
14OtherAPP014NetflixENTERTAINMENTUSAsupport@netflix.com2012-04-101100000000Valid350000000INVALID10APR2012 1,100,000,000
15OtherAPP015UberTRAVELUSAsupport@uber.com2011-03-11950000000Valid600000000INVALID11MAR2011 950,000,000

6.3 RETAIN

data apps_retain;

set apps_nodup;

retain cumulative_downloads 0;

cumulative_downloads+downloads;

run;

proc print data=apps_retain;

run;

OUTPUT:

Obsapp_idapp_namecategorycountrysupport_emailrelease_dt_rawdownloadsratingactive_usersemail_flagrelease_datemissing_flagdownloads_bandcumulative_downloads
1APP003TiktokENTERTAINMENTCHNhelp@tiktok.com2016-09-151500000000Valid1800000000INVALID15SEP2016 1,500,000,0001500000000
2APP007CapcutEDITINGCHNsupport@capcut.cominvalid_date800000000Valid600000000INVALID.Y800,000,0002300000000
3APP008SpotifyMUSICEUROPEspotify.com2008-10-07700000000Invalid650000000INVALID07OCT2008 700,000,0003000000000
4APP001WhatsappMESSAGINGINDsupport@whatsapp.com2009-01-152500000000Valid2000000000INVALID15JAN2009 2,500,000,0005500000000
5APP005TelegramMESSAGINGINDhelp@telegram.org2026-15-01900000000Valid850000000INVALID.Y900,000,0006400000000
6APP012NullGAMINGJPNhelp@gaming.com2020-04-20650000000Valid250000000INVALID20APR2020 650,000,0007050000000
7APP006SnapchatSOCIALUKsupport@snapchat.com2011-09-16.Valid700000000INVALID16SEP2011Y.7050000000
8APP002FacebookSOCIALUSAsupportfacebook.com2004-02-043000000000Valid2500000000INVALID04FEB2004 3,000,000,00010050000000
9APP004InstagramUNKNOWNUSAsupport@instagram2010-10-062000000000.1700000000INVALID06OCT2010Y2,000,000,00012050000000
10APP009ChatgptAIUSAsupport@openai.com2022-11-30900000000Valid500000000INVALID30NOV2022 900,000,00012950000000
11APP010TemuSHOPPINGUSAsupport@temu.com2022-09-011200000000Valid450000000INVALID01SEP2022 1,200,000,00014150000000
12APP011ThreadsSOCIALUSAcontact@threads.com2023-07-05750000000Valid300000000INVALID05JUL2023 750,000,00014900000000
13APP013ZoomBUSINESSUSAzoom@@zoom.com2013-01-211000000000Valid400000000INVALID21JAN2013 1,000,000,00015900000000
14APP014NetflixENTERTAINMENTUSAsupport@netflix.com2012-04-101100000000Valid350000000INVALID10APR2012 1,100,000,00017000000000
15APP015UberTRAVELUSAsupport@uber.com2011-03-11950000000Valid600000000INVALID11MAR2011 950,000,00017950000000

7.R Raw Dataset

library(tibble)

apps_raw <- tribble(

  ~app_id, ~app_name,   ~category,       ~country, ~downloads,   ~rating, ~release_dt_raw, ~support_email,         ~active_users,

  "APP001","WhatsApp",  "Messaging",     "IND",    2500000000,   4.8,     "2009-01-15",    "support@whatsapp.com", 2000000000,

  "APP002","facebook",  "Social",        "usa",    3000000000,   4.7,     "2004-02-04",    "supportfacebook.com",  2500000000,  

  "APP002","facebook",  "Social",        "USA",    3000000000,   4.7,     "2004-02-04",    "supportfacebook.com",  2500000000,  

  "APP003"," TikTok ",  "Entertainment", "CHN",   -1500000000,   4.9,     "2016-09-15",    "help@tiktok.com",      1800000000,  

  "APP004","Instagram", "NULL",          "USA",    2000000000,   5.8,     "2010-10-06",    "support@instagram",    1700000000,  

  "APP005","Telegram",  "Messaging",     "INDIA",   900000000,   4.6,     "2026-15-01",    "help@telegram.org",     850000000,  

  "APP006","Snapchat",  "Social",        "UK",              NA,  4.2,     "2011-09-16",    "support@snapchat.com",  700000000,  

  "APP007","CapCut",    "editing",       "chn",     800000000,   4.4,     "invalid_date",  "support@capcut.com",    600000000,  

  "APP008","Spotify",   "Music",         "EUROPE",  700000000,  -2.0,     "2008-10-07",    "spotify.com",           650000000, 

  "APP009","ChatGPT",   "AI",            "USA",     900000000,   4.9,     "2022-11-30",    "support@openai.com",    500000000,

  "APP010"," Temu ",    "Shopping",      "UsA",    1200000000,   4.5,     "2022-09-01",    "support@temu.com",      450000000, 

  "APP011","Threads",   "social",        "USA",     750000000,   4.3,     "2023-07-05",    "contact@threads.com",   300000000,  

  "APP012","NULL",      "Gaming",        "JPN",     650000000,   4.1,     "2020-04-20",    "help@gaming.com",       250000000,  

  "APP013","Zoom",      "Business",      "USA",    1000000000,   4.7,     "2013-01-21",    "zoom@@zoom.com",        400000000,  

  "APP014","Netflix",   "Entertainment", "US",     1100000000,   4.8,     "2012-04-10",    "support@netflix.com",   350000000,  

  "APP015","Uber",      "Travel",        "USA",     950000000,   4.4,     "2011-03-11",    "support@uber.com",      600000000,  

  "APP016","  Canva",   "Productivity",  "Aus",     500000000,   4.7,     "2013-08-19",    "help@canva.com",        300000000,  

  "APP017","Shein",     "Shopping",      "china",  1300000000,   4.2,     "2015-11-30",    "support@shein",         800000000,  

  "APP018","Discord",   "Communication", "USA ",    600000000,   4.5,     "",              "support@discord.com",   250000000,  

  "APP019","Spotify ",  "music",         "EU",      700000000,   4.3,     "2008/10/07",    "music@spotify.com",     650000000,  

  "APP020","Pinterest", "Social",        "usa",     550000000,   6.2,     "2010-03-10",    "contact@pinterest.com", 450000000

)

OUTPUT:

app_id

app_name

category

country

downloads

rating

release_dt_raw

support_email

active_users

APP001

WhatsApp

Messaging

IND

2500000000

4.8

2009-01-15

support@whatsapp.com

2000000000

APP002

facebook

Social

usa

3000000000

4.7

2004-02-04

supportfacebook.com

2500000000

APP002

facebook

Social

USA

3000000000

4.7

2004-02-04

supportfacebook.com

2500000000

APP003

 TikTok

Entertainment

CHN

-1500000000

4.9

2016-09-15

help@tiktok.com

1800000000

APP004

Instagram

NULL

USA

2000000000

5.8

2010-10-06

support@instagram

1700000000

APP005

Telegram

Messaging

INDIA

900000000

4.6

2026-15-01

help@telegram.org

850000000

APP006

Snapchat

Social

UK

4.2

2011-09-16

support@snapchat.com

700000000

APP007

CapCut

editing

chn

800000000

4.4

invalid_date

support@capcut.com

600000000

APP008

Spotify

Music

EUROPE

700000000

-2

2008-10-07

spotify.com

650000000

APP009

ChatGPT

AI

USA

900000000

4.9

2022-11-30

support@openai.com

500000000

APP010

 Temu

Shopping

UsA

1200000000

4.5

2022-09-01

support@temu.com

450000000

APP011

Threads

social

USA

750000000

4.3

2023-07-05

contact@threads.com

300000000

APP012

NULL

Gaming

JPN

650000000

4.1

2020-04-20

help@gaming.com

250000000

APP013

Zoom

Business

USA

1000000000

4.7

2013-01-21

zoom@@zoom.com

400000000

APP014

Netflix

Entertainment

US

1100000000

4.8

2012-04-10

support@netflix.com

350000000

APP015

Uber

Travel

USA

950000000

4.4

2011-03-11

support@uber.com

600000000

APP016

  Canva

Productivity

Aus

500000000

4.7

2013-08-19

help@canva.com

300000000

APP017

Shein

Shopping

china

1300000000

4.2

2015-11-30

support@shein

800000000

APP018

Discord

Communication

USA

600000000

4.5

support@discord.com

250000000

APP019

Spotify

music

EU

700000000

4.3

2008/10/07

music@spotify.com

650000000

APP020

Pinterest

Social

usa

550000000

6.2

2010-03-10

contact@pinterest.com

450000000

8.R Equivalent Cleaning Layer

library(tidyverse)

library(lubridate)

library(janitor)

options(scipen = 999)

apps_clean <-

  apps_raw %>%

  clean_names() %>%

  mutate(

    app_id=str_to_title(str_trim(app_id)),

    app_name=str_to_title(str_trim(app_name)),

    category=case_when(category=="NULL" ~ "UNKNOWN",

      TRUE ~ category),

    downloads=abs(downloads),

    rating=if_else(rating>5,NA_real_,rating),

    country=str_to_upper(country),

    release_date = suppressWarnings(parse_date_time(

        release_dt_raw,orders = c("ymd","dmy"))),

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

      "VALID","INVALID")

  )%>%

  distinct(app_id,.keep_all = TRUE)

OUTPUT:

app_id

app_name

category

country

downloads

rating

release_dt_raw

support_email

active_users

release_date

email_flag

App001

Whatsapp

Messaging

IND

2500000000

4.8

2009-01-15

support@whatsapp.com

2000000000

2009-01-15 00:00:00 UTC

VALID

App002

Facebook

Social

USA

3000000000

4.7

2004-02-04

supportfacebook.com

2500000000

2004-02-04 00:00:00 UTC

INVALID

App003

Tiktok

Entertainment

CHN

1500000000

4.9

2016-09-15

help@tiktok.com

1800000000

2016-09-15 00:00:00 UTC

VALID

App004

Instagram

UNKNOWN

USA

2000000000

2010-10-06

support@instagram

1700000000

2010-10-06 00:00:00 UTC

VALID

App005

Telegram

Messaging

INDIA

900000000

4.6

2026-15-01

help@telegram.org

850000000

VALID

App006

Snapchat

Social

UK

4.2

2011-09-16

support@snapchat.com

700000000

2011-09-16 00:00:00 UTC

VALID

App007

Capcut

editing

CHN

800000000

4.4

invalid_date

support@capcut.com

600000000

VALID

App008

Spotify

Music

EUROPE

700000000

-2

2008-10-07

spotify.com

650000000

2008-10-07 00:00:00 UTC

INVALID

App009

Chatgpt

AI

USA

900000000

4.9

2022-11-30

support@openai.com

500000000

2022-11-30 00:00:00 UTC

VALID

App010

Temu

Shopping

USA

1200000000

4.5

2022-09-01

support@temu.com

450000000

2022-09-01 00:00:00 UTC

VALID

App011

Threads

social

USA

750000000

4.3

2023-07-05

contact@threads.com

300000000

2023-07-05 00:00:00 UTC

VALID

App012

Null

Gaming

JPN

650000000

4.1

2020-04-20

help@gaming.com

250000000

2020-04-20 00:00:00 UTC

VALID

App013

Zoom

Business

USA

1000000000

4.7

2013-01-21

zoom@@zoom.com

400000000

2013-01-21 00:00:00 UTC

VALID

App014

Netflix

Entertainment

US

1100000000

4.8

2012-04-10

support@netflix.com

350000000

2012-04-10 00:00:00 UTC

VALID

App015

Uber

Travel

USA

950000000

4.4

2011-03-11

support@uber.com

600000000

2011-03-11 00:00:00 UTC

VALID

App016

Canva

Productivity

AUS

500000000

4.7

2013-08-19

help@canva.com

300000000

2013-08-19 00:00:00 UTC

VALID

App017

Shein

Shopping

CHINA

1300000000

4.2

2015-11-30

support@shein

800000000

2015-11-30 00:00:00 UTC

VALID

App018

Discord

Communication

USA

600000000

4.5

support@discord.com

250000000

VALID

App019

Spotify

music

EU

700000000

4.3

2008/10/07

music@spotify.com

650000000

2008-10-07 00:00:00 UTC

VALID

App020

Pinterest

Social

USA

550000000

2010-03-10

contact@pinterest.com

450000000

2010-03-10 00:00:00 UTC

VALID

SAS vs R Transformation Comparison

SAS

R

PROPCASE

str_to_title

STRIP

str_trim

TRANWRD

str_replace_all

COALESCEC

coalesce

IF THEN ELSE

if_else

SELECT WHEN

case_when

PROC SQL

dplyr summarise

PROC SORT NODUPKEY

distinct

Enterprise Validation and Compliance

Clinical environments require:

  • SDTM traceability
  • ADaM reproducibility
  • Independent QC
  • Audit trails
  • Metadata governance
  • Controlled terminology

Missing numeric values in SAS are dangerous.

Example:

if age<18 then pediatric='Y';

If AGE is missing:

. < 18 evaluates TRUE

This could classify missing-age patients as pediatric subjects.

That single issue can invalidate regulatory submissions.

20 Data Cleaning Best Practices

  1. Validate before transformation.
  2. Standardize metadata.
  3. Maintain audit trails.
  4. Never overwrite source data.
  5. Use controlled terminology.
  6. Validate ranges.
  7. Standardize dates.
  8. Deduplicate early.
  9. Version control macros.
  10. Separate QC programming.
  11. Use defensive programming.
  12. Track lineage.
  13. Automate validations.
  14. Use reusable formats.
  15. Document assumptions.
  16. Maintain code reviews.
  17. Use parameterized macros.
  18. Store validation logs.
  19. Protect production libraries.
  20. Reproduce outputs consistently.

Business Logic Behind Cleaning

Missing values are rarely random.

A missing patient age may indicate failed enrollment collection. A negative billing amount may represent a reversed transaction. Invalid dates may reflect system migration issues.

Standardization ensures:

  • age groups become accurate
  • AI models receive valid inputs
  • dashboards remain trustworthy
  • forecasts remain reproducible

Correcting "india" and "IND" into "IND" prevents duplicate regional reporting.

Normalizing text improves joins.

Date imputation prevents timeline fragmentation.

Cleaning is not cosmetic.

It is analytical risk management.

20 One-Line Insights

  • Dirty data creates expensive business mistakes.
  • Validation logic beats visual inspection.
  • Standardized variables improve reproducibility.
  • Missing values deserve investigation.
  • Duplicate records distort trends.
  • Metadata drives consistency.
  • Defensive programming saves projects.
  • Audit trails build trust.
  • Reusable macros reduce errors.
  • Controlled terminology improves reporting.
  • Data lineage matters.
  • Automation reduces risk.
  • SQL excels at aggregation.
  • DATA Step excels at row logic.
  • Formats simplify business rules.
  • Validation is continuous.
  • Governance protects analytics.
  • Documentation prevents confusion.
  • Traceability matters.
  • Quality data creates quality decisions.

SAS vs R Strength Comparison

Feature

SAS

R

Auditability

Excellent

Moderate

Regulatory Use

Excellent

Growing

Scalability

Excellent

Excellent

Visualization

Good

Excellent

Flexibility

Moderate

Excellent

Traceability

Excellent

Moderate

Statistical Ecosystem

Excellent

Excellent

SAS dominates heavily regulated industries such as clinical research, banking, and insurance due to traceability and compliance support.

R dominates exploratory analytics and advanced modeling because of its flexibility and package ecosystem.

Together they provide an ideal enterprise cleaning architecture.

Conclusion

Modern analytics platforms consume billions of records every day. Whether the source is a clinical trial database, an insurance claims warehouse, a banking transaction platform, or a global mobile application marketplace, poor data quality spreads silently through every downstream process.

A duplicate identifier can inflate revenue forecasts. An invalid date can destroy longitudinal analysis. Missing demographic information can bias AI predictions. Corrupted categorical values can split populations into false segments.

The world's most downloaded applications generate extraordinary amounts of behavioral and operational data. Turning that raw information into trusted intelligence requires disciplined engineering processes rather than ad hoc fixes.

SAS provides industrial-strength governance, validation, lineage tracking, and regulatory traceability. R provides unmatched flexibility, rapid experimentation, and advanced analytical capabilities.

Together they form a production-grade ecosystem capable of supporting:

  • executive dashboards,
  • AI pipelines,
  • regulatory submissions,
  • statistical reporting,
  • operational intelligence,
  • and enterprise decision-making.

Data cleaning is not a preprocessing task.

It is the foundation of every trustworthy analytical conclusion.

Interview Questions

1. Why is PROC SORT NODUPKEY preferred for deduplication?

Because it removes duplicate business keys while preserving one representative observation.

2. Why are missing numeric values dangerous in SAS?

Because SAS treats missing numeric values as lower than valid numbers.

3. When should PROC SQL be preferred over DATA Step?

For joins, aggregations, and summarization.

4. When should DATA Step be preferred?

For row-by-row transformations and complex derivations.

5. How would you validate email fields?

Using VERIFY(), INDEX(), regular expressions, or pattern validation rules.

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

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 APP 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