The game is on! Get a quick update on Hackathon progress, mentor resources, and why now is the perfect time to start capturing the milestones that will help tell your team's story at submission time. ... View more

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. Datasets Columns shared by ACCOUNTS_BASELINE and ACCOUNTS_REFRESHED: DataColumns   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;   DIFF_DETAIL Columns   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; Takeaway: At CAS scale, prefer set-based anti-joins over correlated subqueries.   Step 5: Validation summary in the log Every count below is a simple, single-table aggregate, so each runs instantly. Counts are captured into macro variables and printed as a labeled block.   proc sql noprint; select count(*) into :Baseline_Rows trimmed from casuser.accounts_baseline; select count(*) into :Refreshed_Rows trimmed from casuser.accounts_refreshed; select count(*) into :Changed_Rows trimmed from casuser.diff_detail; select count(*) into :Dropped_Rows trimmed from casuser.dropped_records; select count(*) into :New_Rows trimmed from casuser.new_records; quit; data _null_; put " "; put "----------- VALIDATION SUMMARY -----------"; put "Baseline rows : &Baseline_Rows"; put "Refreshed rows : &Refreshed_Rows"; put "Changed rows : &Changed_Rows"; put "Dropped rows : &Dropped_Rows"; put "New rows : &New_Rows"; put "------------------------------------------"; put " "; run;   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.    BarChart2   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.   ... View more

Team Name Car RamRod Track Student Use Case Auto loan lending decisions. Technology SAS Viya Region U.S. Team lead Joshua Team members @jrod04      Is your team interested in participating in an interview? N Optional: Expand on your technology expertise     ... View more

Enter a nomination for a chance to win a trip to SAS Innovate 2027! Nominations are open now - November 20, 2026 ... View more

%INCLUDE /sasdata/path_to_file/file.sas statement returns with ERROR when ran in scheduler. It works fine when run in Studio but throws error when scheduled. WARNING: Physical file does not exist, ERROR: Cannot open %INCLUDE file sasdata/path_to_file/file.sas This is on SASVIYA 4 2026.06 ... View more