topic Re: SAS xml mapper XPATH problem in SAS Programming

SAS xml mapper XPATH problem

Satish_Parida — Wed, 14 Nov 2018 13:30:27 GMT

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.

Re: SAS xml mapper XPATH problem

ChrisNZ — Wed, 14 Nov 2018 23:17:44 GMT

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

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

Re: SAS xml mapper XPATH problem

Satish_Parida — Thu, 15 Nov 2018 08:40:05 GMT

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.

Re: SAS xml mapper XPATH problem

ChrisNZ — Thu, 15 Nov 2018 21:25:56 GMT

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

Re: SAS xml mapper XPATH problem

Satish_Parida — Thu, 15 Nov 2018 09:13:57 GMT

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.

Re: SAS xml mapper XPATH problem

ChrisNZ — Thu, 15 Nov 2018 10:23:10 GMT

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

Re: SAS xml mapper XPATH problem

Satish_Parida — Thu, 15 Nov 2018 11:12:19 GMT

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.

Re: SAS xml mapper XPATH problem

ChrisNZ — Thu, 15 Nov 2018 21:29:19 GMT

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.