Edge Case: Reconciling Financial Ledgers with Mismatched Columns and Missing Data

Business Context

An accounting department is consolidating transaction records from a subsidiary's ledger into the company's general ledger. The subsidiary's system includes extra, non-financial columns (`InternalCode`, `Notes`) that should be ignored. Additionally, some source records may have missing key identifiers, which must still be appended for later investigation.

About the Set : table

Loading, saving, and managing in-memory tables.

Discover all actions of table

Data Preparation

Creates a `general_ledger` (target) with a specific schema and a `subsidiary_ledger` (source) with extra columns and missing values in `TransactionID`.

Copied!

1	DATA mycas.general_ledger;
2	LENGTH TransactionID 8 Amount 8 AccountID $ 10;
3	INPUT TransactionID Amount AccountID $;
4	DATALINES;
5	1001 150.25 ACC_A
6	1002 300.00 ACC_B
7	;
8	RUN;
9
10	DATA mycas.subsidiary_ledger;
11	LENGTH TransactionID 8 Amount 8 AccountID $ 10 InternalCode $ 5 Notes $ 50;
12	INPUT TransactionID Amount AccountID $ InternalCode $ Notes $;
13	DATALINES;
14	2001 99.99 ACC_C ABCDE 'Initial Entry'
15	. 50.00 ACC_D FGHIJ 'Review needed'
16	2003 120.75 ACC_C KLMNO 'Final Entry'
17	;
18	RUN;

Étapes de réalisation

Append the subsidiary ledger to the general ledger. The action should automatically handle column mapping by name.

Copied!

1	PROC CAS;
2	TABLE.append /
3	SOURCE={name='subsidiary_ledger'}
4	target={name='general_ledger'};
5	RUN;

Verify that the schema of the target table has not changed and that the extra columns from the source were not added.

Copied!

1
2	PROC CAS;
3	TABLE.columnInfo / TABLE={name='general_ledger'};
4	RUN;
5

Fetch and display the content of the updated target table to confirm that rows with missing TransactionIDs were appended correctly.

Copied!

1
2	PROC CAS;
3	TABLE.fetch / TABLE={name='general_ledger'};
4	RUN;
5

Expected Result

The `general_ledger` table should contain 5 rows (2 original + 3 from source). Its schema must remain unchanged, containing only `TransactionID`, `Amount`, and `AccountID`. The columns `InternalCode` and `Notes` from the source must be ignored. The appended row with the missing `TransactionID` should appear in the final table with a null value for that specific field.

Voir la documentation technique de append