Validating Large Datasets in SAS Viya: PROC SQL vs. Visual Analytics
After an extract, transform, load (ETL) refresh, a platform migration, or a model re-run, you need to answer one question fast: did the data change the way I expected, and where didn’t it?
SAS Viya gives you two complementary ways to answer that:
- PROC SQL / FedSQL — precise, programmatic validation that fits into a pipeline.
- SAS Visual Analytics (VA) — interactive, visual investigation for stakeholders.
Columns shared by ACCOUNTS_BASELINE and ACCOUNTS_REFRESHED:
Step 0: Connect to CAS and reset
A “session already exists” warning here is harmless. To force a clean start, run cas mySession terminate; first. The QUIET option lets the cleanup run even if the tables do not exist yet, which makes the whole script safe to re-run.
cas mySession;
caslib _ALL_ assign;
libname casuser cas caslib="casuser";
/* Clean slate so the script can be re-run without errors.
QUIET suppresses errors if a table doesn't exist yet. */
proc casutil;
droptable casdata="diff_detail" incaslib="casuser" quiet;
droptable casdata="dropped_records" incaslib="casuser" quiet;
droptable casdata="new_records" incaslib="casuser" quiet;
droptable casdata="accounts_baseline" incaslib="casuser" quiet;
droptable casdata="accounts_refreshed" incaslib="casuser" quiet;
quit;
Step 1: Create the data in WORK, then load to CAS
Why build in WORK first? A DATA step that runs directly against CAS while using rand() and a conditional DELETE can produce an unpredictable or empty output, because CAS distributes rows across workers. Building in WORK (single-threaded, deterministic) and then loading to CAS is the reliable, reproducible pattern.
/* DATASET 1: ACCOUNTS_BASELINE (the "original" - 10,000 rows) */
data work.accounts_baseline;
call streaminit(42);
length Region $6 Status $8;
do Account_ID = 1 to 10000;
r = rand('uniform');
if r < 0.35 then Region = 'East';
else if r < 0.62 then Region = 'West';
else if r < 0.84 then Region = 'South';
else Region = 'North';
Credit_Score = round(rand('normal', 700, 90));
if Credit_Score < 300 then Credit_Score = 300;
if Credit_Score > 850 then Credit_Score = 850;
Balance = round(exp(rand('normal', 9.2, 0.9)), 0.01);
if Balance < 1000 then Status = 'Dormant';
else Status = 'Active';
if rand('uniform') < 0.05 then Status = 'Closed';
output;
end;
drop r;
format Balance dollar12.2;
run;
data work.accounts_refreshed;
call streaminit(1234);
set work.accounts_baseline;
if Region = 'West' then
Balance = round(Balance * (1 + 0.15 + rand('uniform') * 0.15), 0.01);
else if Region = 'South' then
Balance = round(Balance * (1 - 0.10 - rand('uniform') * 0.10), 0.01);
else
Balance = round(Balance * (1 + (rand('uniform') - 0.5) * 0.06), 0.01);
if rand('uniform') < 0.08 then
Credit_Score = Credit_Score + int((rand('uniform') - 0.5) * 40);
if rand('uniform') < 0.01 then do;
pick = ceil(rand('uniform') * 3);
if pick = 1 then Balance = round(Balance * 0.05, 0.01);
else if pick = 2 then Balance = round(Balance * 2.5, 0.01);
else Balance = round(Balance * 3.0, 0.01);
end;
if Credit_Score < 300 then Credit_Score = 300;
if Credit_Score > 850 then Credit_Score = 850;
if Balance < 1000 then Status = 'Dormant';
if rand('uniform') < 0.01 then delete;
drop pick;
run;
data work.accounts_new;
call streaminit(99);
length Region $6 Status $8;
do Account_ID = 10001 to 10020;
r = rand('uniform');
if r < 0.35 then Region = 'East';
else if r < 0.62 then Region = 'West';
else if r < 0.84 then Region = 'South';
else Region = 'North';
Credit_Score = round(rand('normal', 700, 90));
if Credit_Score < 300 then Credit_Score = 300;
if Credit_Score > 850 then Credit_Score = 850;
Balance = round(exp(rand('normal', 9.2, 0.9)), 0.01);
Status = 'Active';
output;
end;
drop r;
format Balance dollar12.2;
run;
data work.accounts_refreshed;
set work.accounts_refreshed work.accounts_new;
run;
proc casutil;
load data=work.accounts_baseline outcaslib="casuser"
casout="accounts_baseline" replace;
load data=work.accounts_refreshed outcaslib="casuser"
casout="accounts_refreshed" replace;
list tables incaslib="casuser";
quit;
In the SAS log, confirm that ACCOUNTS_BASELINE and ACCOUNTS_REFRESHED appear in the list tables output.
CASUTIL Procedure
Step 2: Row-level comparison with PROC SQL
PROC SQL set operators give you precise, code-driven comparisons. EXCEPT returns rows in the first query that are not in the second. INTERSECT returns rows common to both.
Note on outobs=20: this caps the display at 20 rows to keep demo output short. The resulting “Statement terminated early due to OUTOBS” warning is expected and simply means there were more than 20 differences.
/* 2a. In BASELINE but NOT in REFRESHED = dropped records */
proc sql outobs=20;
title "Sample: Rows in BASELINE but NOT in REFRESHED (dropped records)";
select * from casuser.accounts_baseline
except
select * from casuser.accounts_refreshed;
quit;
/* 2b. In REFRESHED but NOT in BASELINE = new / changed records */
proc sql outobs=20;
title "Sample: Rows in REFRESHED but NOT in BASELINE (new / changed records)";
select * from casuser.accounts_refreshed
except
select * from casuser.accounts_baseline;
quit;
/* 2c. Count of identical rows in both datasets.
A derived table (subquery in FROM) MUST be aliased in PROC SQL. */
proc sql;
title "Count of identical rows in both datasets";
select count(*) as Matching_Rows
from (
select * from casuser.accounts_baseline
intersect
select * from casuser.accounts_refreshed
) as matched_rows;
quit;
title;
Step 3: Column-level diff table with FedSQL
Why FedSQL, not PROC SQL? Plain PROC SQL cannot CREATE a CAS table when the source tables are also in CAS; you hit the “PROC SQL does not support CREATE TABLE AS ... SASVIYA libname references” error. PROC FedSQL runs inside the CAS session, so CAS-to-CAS table creation works. This produces DIFF_DETAIL, keeping only rows where at least one column changed and showing the before and after values plus numeric differences.
proc fedsql sessref=mySession;
create table casuser.diff_detail {options replace=true} as
select
b.Account_ID,
b.Region as Region,
b.Balance as Base_Balance,
r.Balance as Refresh_Balance,
(r.Balance - b.Balance) as Balance_Diff,
b.Credit_Score as Base_Score,
r.Credit_Score as Refresh_Score,
(r.Credit_Score - b.Credit_Score) as Score_Diff,
b.Status as Base_Status,
r.Status as Refresh_Status
from casuser.accounts_baseline b
inner join casuser.accounts_refreshed r
on b.Account_ID = r.Account_ID
where abs(r.Balance - b.Balance) > 0.01
or b.Credit_Score <> r.Credit_Score
or b.Status <> r.Status
;
quit;
/* Confirm the table exists and show its row count */
proc casutil;
contents casdata="diff_detail" incaslib="casuser";
quit;
/* Preview the first 20 changed rows */
proc print data=casuser.diff_detail(obs=20) noobs;
title "Sample of column-level differences (DIFF_DETAIL)";
run;
title;
Tip: the abs(...) > 0.01 test avoids false positives from floating-point rounding on Balance.
Step 4: Dropped and new records via anti-joins
Do not use a correlated NOT EXISTS subquery against CAS tables in PROC SQL. Because the inner query references a column from the outer query, PROC SQL cannot push it down to CAS as a single set operation. It evaluates the subquery row by row on the compute server, and the session appears to hang.
A LEFT JOIN with a null check on the key, an anti-join, is set-based, runs entirely inside CAS, and returns immediately.
proc fedsql sessref=mySession;
/* Rows present in baseline but missing from refreshed = dropped */
create table casuser.dropped_records {options replace=true} as
select b.Account_ID
from casuser.accounts_baseline b
left join casuser.accounts_refreshed r
on b.Account_ID = r.Account_ID
where r.Account_ID is null;
/* Rows present in refreshed but absent from baseline = new */
create table casuser.new_records {options replace=true} as
select r.Account_ID
from casuser.accounts_refreshed r
left join casuser.accounts_baseline b
on r.Account_ID = b.Account_ID
where b.Account_ID is null;
quit;
Note: Changed rows are accounts present in both tables with different values. Dropped and new rows are accounts that exist in only one of the two.
Step 6: Promote tables for Visual Analytics
PROMOTE makes a CAS table global, meaning it is visible outside the current session and appears in Visual Analytics (VA) under your personal caslib. Without promotion, tables are session-scoped and VA cannot see them.
/* ---------- Step 6: Promote tables for Visual Analytics ---------- */
proc casutil;
promote casdata="diff_detail" incaslib="casuser"
outcaslib="casuser" casout="diff_detail";
promote casdata="accounts_baseline" incaslib="casuser"
outcaslib="casuser" casout="accounts_baseline";
promote casdata="accounts_refreshed" incaslib="casuser"
outcaslib="casuser" casout="accounts_refreshed";
list tables incaslib="casuser";
quit;
Step 7: Verify the patterns before building charts
Because the data is generated with structure, confirm the changes are real before opening Visual Analytics. Run the following and check the results in the log.
/* Spread of the changes - should NOT be all zeros */
proc means data=casuser.diff_detail n mean min max;
var Balance_Diff Score_Diff;
title "Distribution of balance and score changes";
run;
/* Where the changes landed - drives the regional bar chart */
proc sql;
title "Changed accounts by region";
select Region,
count(*) as Changed_Accounts,
mean(Balance_Diff) as Avg_Balance_Change format=dollar12.2
from casuser.diff_detail
group by Region
order by calculated Changed_Accounts desc;
quit;
title;
You are looking for a non-zero spread in Balance_Diff and a meaningful count of changed accounts in each region. If Balance_Diff is all zeros, the refresh did not apply; re-run from Step 0.
Step 8: Investigate visually in Visual Analytics
Open SAS Visual Analytics and choose New report. Add DIFF_DETAIL as the primary data source, and add ACCOUNTS_BASELINE and ACCOUNTS_REFRESHED for the total counts.
Balance drift scatter plot. Assign Base_Balance to the X axis, Refresh_Balance to the Y axis, and Region to Color. Points on the diagonal are unchanged; points pulling away from it are the accounts the refresh altered.
Scatter Plot
Regional breakdown (bar chart). Assign Region to the Category role and Account_ID to the measure role to see how many accounts changed in each region.
Bar Chart1
Average balance drift by region (bar chart). Assign REGION to the Category role and Balance_Diff to the Measure role, then set the aggregation to Average.
Key insight: PROC SQL and Visual Analytics are not competitors, they are stages. Use PROC SQL and FedSQL to detect differences programmatically, then use Visual Analytics to investigate and communicate them, all on the same in-memory CAS engine with no data movement between layers.
Adapting this to your own data
Replace the DATA steps in Step 1 with a load of your real tables. Everything downstream works unchanged.
proc casutil;
load casdata="your_baseline.sashdat" incaslib="somelib"
casout="accounts_baseline" outcaslib="casuser" replace;
load casdata="your_refreshed.sashdat" incaslib="somelib"
casout="accounts_refreshed" outcaslib="casuser" replace;
quit;
Key takeaways
- ACCOUNTS_BASELINE, ACCOUNTS_REFRESHED, and DIFF_DETAIL are synthetic and reproducible via the seeds (42, 1234, 99).
- Build in WORK first, then load to CAS, to avoid empty or unpredictable CAS DATA-step output.
- Use FedSQL for CAS-to-CAS table creation to avoid the SASVIYA libname error.
- Prefer set-based anti-joins to correlated subqueries when working at CAS scale.
- Promote your tables so Visual Analytics can consume them.
Ready to see what SAS Viya Copilot can do?
Visit the Tips & Tricks page for setup guidance, demos, and practical examples that show how Copilot supports your workflows.
