The datagrid macro, %DCM_CONVERT_DATAGRID_TO_TABLE, unpacks a column of json-encoded datagrids into their own tables. When setting MERGE=YES, the resulting tables are merged into a single larger table. However, this merged table does not keep track of which rows belong to which datagrids. It would be ideal if the macro took an optional argument, KEYCOL , that specifies a key column, to be read from the parent table and inserted into each row of the resulting datagrid tables. This way when they are merged, the new key column can link the parent table to the merged datagrid table. E.g. proc sql;
CONNECT USING db_connection;
create table WORK.parent_table as select * from
connection to db_connection(
select *
from schema_x.table_y
limit 1000;
);
disconnect from db_connection;
quit;
run;
%DCM_CONVERT_DATAGRID_TO_TABLE (
GRIDTABLE=WORK.parent_table , /* name of table with datagrid column. */
GRIDCOL=dg_col, /* name of the column that contains the DG rows. */
FIXEDMETADATA=YES, /* YES|NO. YES if metadata for each DG is the same. */
MERGE=YES, /* YES|NO. YES if you want to merge all the datagrids into a single table. */
KEYCOL=parent_primary_key_col, /* append this column from the parent table to the output table(s) */
OUTLIB=WORK, /* name of libref to write outputs table(s) to. */
PROMOTE=NO /* YES|NO. YES to promote output as a global CAS table. Only if OUTLIB is a CAS library.*/ )
... View more
