SAS Intelligent Decisioning: Transpose datagrid column values in DS2 code file

[pierreol]

Hello,

I need to manipulate the layout of a datagrid in a DS2 code file within a SAS Intelligent Decisioning flow.

The datagrid (let's all it TRANSACTIONS) is essentially parsed JSON content, which is in the following structure (with some sample data):

FIN_YEAR	JSON_KEY	JSON_VALUE	INDEX	GROUP
2024	trxType	SAL	1	internal.SalesData
2024	trxValue	2300.50	1	internal.SalesData
2024	trxAdjusted	FALSE	1	internal.SalesData
2024	trxType	ADJ	2	internal.SalesData
2024	trxValue	100	2	internal.SalesData
2024	trxAdjusted	FALSE	2	internal.SalesData
2024	trxType	SAL	1	internal.Reversals
2024	trxValue	2300.50	1	internal.Reversals
2024	trxAdjusted	TRUE	1	internal.Reversals

I need to transpose this (into a datagrid output called TRANSACTIONS_OUT), so the output is in the format below. Essentially the JSON_KEY should be used as the new column headers, JSON_VALUE as the value, with results grouped by the remaining columns:

FIN_YEAR	INDEX	GROUP	trxType	trxValue	trxAdjusted
2024	1	internal.SalesData	SAL	2300.50	FALSE
2024	2	internal.SalesData	ADJ	100	FALSE
2024	1	internal.Reversals	SAL	2300.50	TRUE

Embarrassingly, the furthest I've got is bringing the datagrid into the code file - all my other attempted code fails.

package "${PACKAGE_NAME}" /inline;

method execute(package datagrid TRANSACTIONS, in_out package datagrid TRANSACTIONS_OUT);

Any advice/sample code would be very much appreciated!

Thanks,

O