Business Context
A retail company needs to upload its daily sales transactions into CAS for analysis. The process runs every morning, and the previous day's data must be completely replaced with the new dataset to ensure reports are based on the freshest data available. This scenario tests the fundamental ability to create and overwrite a CAS table.
Data Preparation
Creation of two distinct datasets representing sales from two different days. This simulates the daily refresh cycle.
Copied!
| 1 | DATA WORK.SALES_DAY1; |
| 2 | FORMAT transaction_time datetime20.; |
| 3 | call streaminit(123); |
| 4 | DO transaction_id = 1 to 100; |
| 5 | store_id = rand('integer', 1, 5); |
| 6 | product_id = 'P' || put(rand('integer', 100, 200), z3.); |
| 7 | sale_amount = round(rand('uniform') * 500, 0.01); |
| 8 | transaction_time = '21NOV2025:10:00:00'dt + (transaction_id * 300); |
| 9 | OUTPUT; |
| 10 | END; |
| 11 | RUN; |
| 12 | |
| 13 | DATA WORK.SALES_DAY2; |
| 14 | FORMAT transaction_time datetime20.; |
| 15 | call streaminit(456); |
| 16 | DO transaction_id = 101 to 250; |
| 17 | store_id = rand('integer', 1, 5); |
| 18 | product_id = 'P' || put(rand('integer', 100, 200), z3.); |
| 19 | sale_amount = round(rand('uniform') * 800, 0.01); |
| 20 | transaction_time = '22NOV2025:10:00:00'dt + (transaction_id * 300); |
| 21 | OUTPUT; |
| 22 | END; |
| 23 | RUN; |
Étapes de réalisation
1
Initial load of the first day's sales data. This creates the base table.
Copied!
| 1 | PROC CAS; |
| 2 | TABLE.addTable / |
| 3 | caslib='casuser' |
| 4 | TABLE='DAILY_SALES' |
| 5 | datatable='SALES_DAY1' |
| 6 | vars={{name='transaction_id', type='int64'}, |
| 7 | {name='store_id', type='int64'}, |
| 8 | {name='product_id', type='varchar', LENGTH=4}, |
| 9 | {name='sale_amount', type='double', FORMAT='DOLLAR8.2'}, |
| 10 | {name='transaction_time', type='double', FORMAT='DATETIME20.'}}; |
| 11 | RUN; |
| 12 | QUIT; |
2
Load the second day's sales data using 'replace=TRUE' to overwrite the existing table. A label is also added.
Copied!
| 1 | PROC CAS; |
| 2 | TABLE.addTable / |
| 3 | caslib='casuser' |
| 4 | TABLE='DAILY_SALES' |
| 5 | datatable='SALES_DAY2' |
| 6 | replace=TRUE |
| 7 | label='Daily Sales Transactions - Refreshed Data' |
| 8 | vars={{name='transaction_id', type='int64'}, |
| 9 | {name='store_id', type='int64'}, |
| 10 | {name='product_id', type='varchar', LENGTH=4}, |
| 11 | {name='sale_amount', type='double', FORMAT='DOLLAR8.2'}, |
| 12 | {name='transaction_time', type='double', FORMAT='DATETIME20.'}}; |
| 13 | RUN; |
| 14 | QUIT; |
3
Verification step: Check the number of rows and the table label to confirm the replacement was successful.
Copied!
| 1 | PROC CAS; |
| 2 | TABLE.tableInfo / caslib='casuser' name='DAILY_SALES'; |
| 3 | TABLE.fetch / TABLE={caslib='casuser' name='DAILY_SALES'} from=1 to=5; |
| 4 | RUN; |
| 5 | QUIT; |
Expected Result
The final 'DAILY_SALES' table in the 'casuser' caslib should contain exactly 150 rows (from SALES_DAY2). The table's label must be 'Daily Sales Transactions - Refreshed Data'. The fetched rows should show transaction_ids starting from 101, confirming the original data was purged.