Business Context
For a regulatory audit, a bank must extract loan data while strictly removing Personally Identifiable Information (PII) like 'SSN'. The process must also handle data quality issues by excluding rows where the 'Status' is missing (null). The target table is critical and must overwrite any existing version to ensure no stale data remains.
Data Preparation
Creation of a loan dataset containing PII (SSN) and some missing Status values to test filtering.
Copied!
| 1 | |
| 2 | DATA casuser.loans_pii; |
| 3 | LENGTH STATUS $10; |
| 4 | DO i = 1 to 50; |
| 5 | Loan_ID = i; |
| 6 | SSN = '999-00-' || put(i, z4.); |
| 7 | IF mod(i, 10) = 0 THEN STATUS = ''; |
| 8 | ELSE STATUS = 'Active'; |
| 9 | OUTPUT; |
| 10 | END; |
| 11 | |
| 12 | RUN; |
| 13 |
Étapes de réalisation
1
Copy table excluding 'SSN' (via var selection) and filtering out null 'Status'. Force replacement.
Copied!
| 1 | |
| 2 | PROC CAS; |
| 3 | TABLE.copyTable / TABLE={name='loans_pii', caslib='casuser', vars={'Loan_ID', 'Status'}, where='Status is not null'}, casout={name='audit_clean_loans', caslib='casuser', replace=true}; |
| 4 | |
| 5 | RUN; |
| 6 |
2
Attempt to access the dropped SSN column (expecting it to be missing) and verify row count.
Copied!
| 1 | |
| 2 | PROC CAS; |
| 3 | TABLE.columnInfo / TABLE={name='audit_clean_loans', caslib='casuser'}; |
| 4 | |
| 5 | RUN; |
| 6 |
Expected Result
The 'audit_clean_loans' table is created without the 'SSN' column. Rows with missing 'Status' are excluded (count should be 45 instead of 50). The table successfully replaced any previous version.