Business Context
A large retail chain needs to reconcile inventory records between their central database and a regional warehouse report to identify discrepancies in stock levels and product listings.
Data Preparation
Creation of two datasets: 'central_db' (system of record) and 'warehouse_scan' (physical count), with some overlapping and some unique items.
Copied!
| 1 | DATA casuser.central_db; LENGTH SKU $10; INPUT SKU $ Quantity; DATALINES; A100 50 |
| 2 | B200 75 |
| 3 | C300 20 |
| 4 | D400 10 |
| 5 | ; RUN; DATA casuser.warehouse_scan; LENGTH SKU $10; INPUT SKU $ Quantity; DATALINES; A100 50 |
| 6 | B200 70 |
| 7 | E500 5 |
| 8 | C300 20 |
| 9 | ; RUN; |
Étapes de réalisation
1
Execute comparison grouping by SKU to find missing items and quantity mismatches.
Copied!
| 1 | |
| 2 | PROC CAS; |
| 3 | SIMPLE.compare / TABLE={name='central_db'} table2={name='warehouse_scan'} inputs={{name='SKU'}} casOut={name='missing_in_warehouse', replace=true} casOut2={name='unexpected_in_warehouse', replace=true} freqOut={name='stock_diff_summary', replace=true}; |
| 4 | |
| 5 | RUN; |
| 6 | |
| 7 | QUIT; |
| 8 |
2
Verify the frequency output to see matched vs unmatched counts.
Copied!
| 1 | |
| 2 | PROC CAS; |
| 3 | TABLE.fetch / TABLE={name='stock_diff_summary'}; |
| 4 | |
| 5 | RUN; |
| 6 | |
| 7 | QUIT; |
| 8 |
Expected Result
The 'missing_in_warehouse' table should contain SKU D400. The 'unexpected_in_warehouse' table should contain SKU E500. The 'stock_diff_summary' shows B200 has differences in Quantity if included in copyVars, or simply that the groups exist in both.