SAS xml mapper XPATH problem

[Satish_Parida]

Hi All,

I have added the following columns in the mapper file.

<COLUMN name="DateTimeStamp">
	<PATH syntax="XPathENR">/{1}ODM/{1}ClinicalData/{1}SubjectData/{1}AuditRecord/{1}DateTimeStamp</PATH>
	<TYPE>numeric</TYPE>
	<DATATYPE>datetime</DATATYPE>
	<FORMAT width="19">IS8601DT</FORMAT>
	<INFORMAT width="19">IS8601DT</INFORMAT>
</COLUMN>


<COLUMN name="DateTimeStamp">
	<PATH syntax="XPathENR">/{1}ODM/{1}ClinicalData/{1}SubjectData/{1}StudyEventData/{1}FormData/{1}ItemGroupData/{1}ItemData/{1}AuditRecord/{1}DateTimeStamp</PATH>
	<TYPE>numeric</TYPE>
	<DATATYPE>datetime</DATATYPE>
	<FORMAT width="19">IS8601DT</FORMAT>
	<INFORMAT width="19">IS8601DT</INFORMAT>
</COLUMN>

Here instead of getting the second column in DateTimeStamp it creates a new column.

Is there any way we can map 2 XPATHS to one Column name?

Thank you in Advance.

[ChrisNZ]

Is there any way we can map 2 XPATHS to one Column name?

I am no expert, but not as far as I know.

[Satish_Parida]

The reason I am looking for this solution is I have the {1}AuditRecord element in multiple places in the clinical data hierarchy.

So instead of creating multiple tables, I want to some how extract the data in the same columns.

It will reduce 25 columns to 5 columns.

Thank you in Advance.

[ChrisNZ]

Can't you just read DateTimeStamp1, DateTimeStamp2 ... from the different locations and derive DateTimeStamp=coalescec( DateTimeStamp1, DateTimeStamp2, etc )
?

[Satish_Parida]

Yes, That is the current solution we are using.

In the current solution every XML element is a SAS table with 2 ordinal columns, we then join them.

We are trying to optimize the current process, by reducing number of tables we are coping from XML to SAS hence reducing number of ordinal columns.

Here the road block is the AuditRecord element which can appear at different hierarchy levels per record, but it basically represents the timestamp of the record action.

Easiest way is to download all as different tables and then collase. But to reduce IO I am trying to read all of them in a single column.

How ever it looks impossible.

Thank you.

[ChrisNZ]

Do you need several tables?Can't you have several columns and do all the processing in one go?

[Satish_Parida]

That is also doable, we will have 5 datetime columns, we wanted 1 column so:

1. Not to select 4 additional columns (reduce writing time)
2. Do a collase to create the final datetime column (reduce processing time).

3. We have 5 of these type of columns (transactionid, user, location, source, actionname and datetimestamp) so it becomes 25 columns in place of just 5.

All this to reduce IO usage.

[ChrisNZ · Accepted solution]

If you just calculate

DateTimeStamp=coalescec( of DateTimeStamp1 - DateTimeStamp5 );
drop DateTimeStamp1 - DateTimeStamp5 ;

when reading the XML file, you'll have just one step and the lowest possible IOs.